lichaoyue888 发表于 2018-10-9 07:10:29

MySQL在线修改表结构pt-osc

  MySQL在线修改表结构pt-osc
  重所周知 MySQL的DDL操作操作是相比比较昂贵的。因为MySQL在修改表期间会阻塞任何读写操作。
  基本上业务处于瘫痪。如果数据量较大可能需要好几个小时才能完成,无法容忍这个操作。Percona开发了一系列的工具 Percona Toolkit包,其中有一个工具pt-online-schema-change可以在线执行DDL操作,不会阻塞读写操作从而影响业务程序。当然也有其他的工具 例如 MySQL5.6的online ddl 还有gh-ost 本文主要讲pt-online-schema-change在线修改表结构。
  原理部分
  环境概述
Percona-Server-5.7.17-11  
Percona-toolkit-3.0.3-1.el7.x86_64
  表结构
CREATE TABLE `test` (  
`id` int(40) NOT NULL,
  
`name` char(12) DEFAULT NULL,
  
PRIMARY KEY (`id`)
  
) ENGINE=MyISAM DEFAULT CHARSET=utf8
  操作修改非主键 name字段
  一。准备工作

[*]  设置当前回话参数 session级别
SHOW VARIABLES LIKE 'innodb\_lock_wait_timeout'; SET SESSION innodb_lock_wait_timeout=1  
SET SESSION lock_wait_timeout=60 SET SESSION wait_timeout=10000
  
innodb_lock_wait_timeout=1
  
lock_wait_timeout=60
  
wait_timeout=10000
  2.收集MySQL信息
SHOW VARIABLES LIKE 'version%'  
SHOW ENGINES
  
SHOW VARIABLES LIKE 'innodb_version'
  
SHOW VARIABLES LIKE 'innodb_stats_persistent'
  
SELECT @@SERVER_ID
  
SHOW GRANTS FOR CURRENT_USER()
  
SHOW FULL PROCESSLIST
  
SHOW GLOBAL STATUS LIKE 'Threads_running'
  
SHOW GLOBAL STATUS LIKE 'Threads_running'
  
SELECT CONCAT(@@hostname, @@port)
  
SHOW TABLES FROM `test2` LIKE 'test1'
  
SHOW TRIGGERS FROM `test2` LIKE 'test1'
  二 正式开始
  1.创建跟旧表一模一样的新表
CREATE TABLE `test2`.`_test1_new` (  
`id` int(30) NOT NULL,
  
`name` char(27) DEFAULT NULL,
  
PRIMARY KEY (`id`)
  
) ENGINE=MyISAM DEFAULT CHARSET=utf8
  2.在新表上修改表结构
ALTER TABLE `test2`.`_test1_new` modify name char(27)  3.创建触发器
CREATE TRIGGER `pt_osc_test2_test1_del` AFTER DELETE ON `test2`.`test1` FOR EACH ROW DELETE IGNORE FROM `test2`.`_test1_new` WHERE `test2`.`_test1_new`.`id`OLD.`id`  #删除操作
CREATE TRIGGER `pt_osc_test2_test1_upd` AFTER UPDATE ON `test2`.`test1` FOR EACH ROW BEGIN DELETE IGNORE FROM `test2`.`_test1_new` WHERE !(OLD.`id`NEW.`id`) AND `test2`.`_test1_new`.`id`OLD.`id`;REPLACE INTO `test2`.`_test1_new` (`id`, `name`) VALUES (NEW.`id`, NEW.`name`)  #更新操作
CREATE TRIGGER `pt_osc_test2_test1_ins` AFTER INSERT ON `test2`.`test1` FOR EACH ROW REPLACE INTO `test2`.`_test1_new` (`id`, `name`) VALUES (NEW.`id`, NEW.`name`)  #插入操作
  4.插入到旧表
EXPLAIN SELECT `id`, `name` FROM `test2`.`test1` LOCK IN SHARE MODE IGNORE INTO `test2`.`_test1_new` (`id`, `name`) SELECT `id`, `name` FROM `test2`.`test1` LOCK IN SHARE MODE /*pt-online-schema-change 6291 copy table*/  #有锁操作LOCK IN SHARE MODE
  三 收尾工作
SHOW WARNINGS  
SELECT @@SERVER_ID
  
SHOW GRANTS FOR CURRENT_USER()
  
SHOW FULL PROCESSLIST
  
SHOW GLOBAL STATUS LIKE 'Threads_running'
  
ANALYZE TABLE `test2`.`_test1_new` /* pt-online-schema-change */
  
RENAME TABLE `test2`.`test1` TO `test2`.`_test1_old`, `test2`.`_test1_new` TO `test2`.`test1`
  
DROP TABLE IF EXISTS `test2`.`_test1_old`
  
ROP TRIGGER IF EXISTS `test2`.`pt_osc_test2_test1_del`
  
DROP TRIGGER IF EXISTS `test2`.`pt_osc_test2_test1_upd`
  
DROP TRIGGER IF EXISTS `test2`.`pt_osc_test2_test1_ins`
  
SHOW TABLES FROM `test2` LIKE '\_test1\_new'
  概述

[*]  查看收集MySQL信息
[*]  创建一个和原表表结构一样的new表 然后在new表中更改表结构。
[*]  在原表创建3个触发器 三个触发器分别对应 insert update delete 操作
[*]  从原表拷贝数据到new表 拷贝过程中原表进行的写操作都会更新到临时表
[*]  copy完成后rename 原表为old表 接着将new表rename原表 最后删除old表和触发器
  四 操作注意事项

[*]  Read the tool’s documentation
[*]  Review the tool’s known “BUGS”
[*]  Test the tool on a non-production server
[*]  Backup your production server and verify the backups
  总结 先看一遍工具文档,用之前先做测试,备份 备份 备份。在执行在线修改表结构的时候,最好选择业务低峰期,不要把old表删掉。
  五 pt-osc限制

[*]  In most cases the tool will refuse to operate unless a PRIMARY KEY or UNIQUE INDEX is present in the table. See --alter for details.
[*]  The tool refuses to operate if it detects replication filters. See --check-replication-filters for details.
[*]  The tool pauses the data copy operation if it observes any replicas that are delayed in replication. See --max-lagfor details.
[*]  The tool pauses or aborts its operation if it detects too much load on the server. See --max-load and --critical-load for details.
[*]  The tool sets innodb_lock_wait_timeout=1 and (for MySQL 5.5 and newer) lock_wait_timeout=60 so that it is more likely to be the victim of any lock contention, and less likely to disrupt other transactions. These values can be changed by specifying --set-vars.
[*]
  The tool refuses to>
[*]
  The tool cannot>
  六 注意事项
  1.先看一遍工具文档,用之前先做测试,备份 备份 备份。
  2.在执行在线修改表结构的时候,最好选择业务低峰期,不要把old表删掉。
  3.必须有主键,无法使用,必须有主键,必须有主键,必须有主键,必须有主键。
  4.pt-osc如果改变外键约束,拒绝工作,除非指定--alter-foreign-keys-method。
  5.操作的时候需要指定字符集 防止乱码。
  参考
  https://www.percona.com/doc/percona-toolkit/2.2/pt-online-schema-change.html


页: [1]
查看完整版本: MySQL在线修改表结构pt-osc