大表在线更改表结构 pt-online-schema-change

1. pt-osc工作过程

1.创建一个和要执行 alter 操作的表一样的新的空表结构(是alter之前的结构)
2.在新表执行alter table 语句（速度很快）
3.在原表中创建触发器3个触发器分别对应insert,update,delete操作
4.以一定块大小从原表拷贝数据到临时表，拷贝过程中通过原表上的触发器在原表进行的写操作都会更新到新建的临时表
5.Rename 原表到old表中，在把临时表Rename为原表
6.如果有参考该表的外键，根据alter-foreign-keys-method参数的值，检测外键相关的表，做相应设置的处理
7.默认最后将旧原表删除**

2.存在的限制

1.原表上不能有触发器存在 （mysql5.7之后支持多个触发器）
2.表必须存在主键，且由于触发器原因对于主键的修改可能会有异常
DELETE触发器和INSERT触发器逻辑上没有任何问题。
但对于UPDATE触发器来说，如果某条记录已经拷贝到中间表中，此时，有针对该记录的UPDATE操作，且修改的是主键，此时，针对中间表触发的“REPLACE INTO test.__t2_new (id) VALUES (NEW.id)”操作只会插入一条新的记录，而不会删除原来的记录。 参考例子https://www.cnblogs.com/ivictor/p/5989189.html
3.强制中断或者失败后新表 和 触发器没有删除**

以下是mysql general log 显示的实际效果

2019-01-18T15:55:03.493989Z 2997 Connect root@bogon on yanglun using TCP/IP 开始连接
### 设置变量，省略###
2019-01-18T15:55:06.512344Z 2997 Query USE yanglun
2019-01-18T15:55:06.512533Z 2997 Query SHOW CREATE TABLE yanglun.a #查看表结构
2019-01-18T15:55:06.512753Z 2997 Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE /
2019-01-18T15:55:06.513301Z 2997 Query EXPLAIN SELECT * FROM yanglun.a WHERE 1=1 #查看表大小
2019-01-18T15:55:06.513888Z 2997 Query SELECT table_schema, table_name FROM information_schema.key_column_usage WHERE referenced_table_schema=‘yanglun’ AND referenced_table_name=‘a’
2019-01-18T15:55:06.598579Z 2997 Query SHOW VARIABLES LIKE ‘wsrep_on’
2019-01-18T15:55:06.600479Z 2997 Query /!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := ‘’, @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 /
2019-01-18T15:55:06.600630Z 2997 Query USE yanglun
2019-01-18T15:55:06.600825Z 2997 Query SHOW CREATE TABLE yanglun.a
2019-01-18T15:55:06.601019Z 2997 Query /!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE /
2019-01-18T15:55:06.601279Z 2997 Query CREATE TABLE yanglun._a_new (
id int(11) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 #新建表 表名为 在原表名前加下划线 后加下划线new
2019-01-18T15:55:06.604584Z 2997 Query ALTER TABLE yanglun._a_new add column b varchar(20) default “haha” #修改新建表表结构
2019-01-18T15:55:06.609144Z 2997 Query /!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := ‘’, @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 /
2019-01-18T15:55:06.609266Z 2997 Query USE yanglun
2019-01-18T15:55:06.609458Z 2997 Query SHOW CREATE TABLE yanglun._a_new
2019-01-18T15:55:06.609652Z 2997 Query /!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE /
2019-01-18T15:55:06.610520Z 2997 Query SELECT TRIGGER_SCHEMA, TRIGGER_NAME, DEFINER, ACTION_STATEMENT, SQL_MODE, CHARACTER_SET_CLIENT, COLLATION_CONNECTION, EVENT_MANIPULATION, ACTION_TIMING FROM INFORMATION_SCHEMA.TRIGGERS WHERE EVENT_MANIPULATION = ‘DELETE’ AND ACTION_TIMING = ‘AFTER’ AND TRIGGER_SCHEMA = ‘yanglun’ AND EVENT_OBJECT_TABLE = ‘a’ #查看原表的触发器
2019-01-18T15:55:06.611289Z 2997 Query SELECT TRIGGER_SCHEMA, TRIGGER_NAME, DEFINER, ACTION_STATEMENT, SQL_MODE, CHARACTER_SET_CLIENT, COLLATION_CONNECTION, EVENT_MANIPULATION, ACTION_TIMING FROM INFORMATION_SCHEMA.TRIGGERS WHERE EVENT_MANIPULATION = ‘UPDATE’ AND ACTION_TIMING = ‘AFTER’ AND TRIGGER_SCHEMA = ‘yanglun’ AND EVENT_OBJECT_TABLE = ‘a’
2019-01-18T15:55:06.611889Z 2997 Query SELECT TRIGGER_SCHEMA, TRIGGER_NAME, DEFINER, ACTION_STATEMENT, SQL_MODE, CHARACTER_SET_CLIENT, COLLATION_CONNECTION, EVENT_MANIPULATION, ACTION_TIMING FROM INFORMATION_SCHEMA.TRIGGERS WHERE EVENT_MANIPULATION = ‘INSERT’ AND ACTION_TIMING = ‘AFTER’ AND TRIGGER_SCHEMA = ‘yanglun’ AND EVENT_OBJECT_TABLE = ‘a’
2019-01-18T15:55:06.612460Z 2997 Query SELECT TRIGGER_SCHEMA, TRIGGER_NAME, DEFINER, ACTION_STATEMENT, SQL_MODE, CHARACTER_SET_CLIENT, COLLATION_CONNECTION, EVENT_MANIPULATION, ACTION_TIMING FROM INFORMATION_SCHEMA.TRIGGERS WHERE EVENT_MANIPULATION = ‘DELETE’ AND ACTION_TIMING = ‘BEFORE’ AND TRIGGER_SCHEMA = ‘yanglun’ AND EVENT_OBJECT_TABLE = ‘a’
2019-01-18T15:55:06.613020Z 2997 Query SELECT TRIGGER_SCHEMA, TRIGGER_NAME, DEFINER, ACTION_STATEMENT, SQL_MODE, CHARACTER_SET_CLIENT, COLLATION_CONNECTION, EVENT_MANIPULATION, ACTION_TIMING FROM INFORMATION_SCHEMA.TRIGGERS WHERE EVENT_MANIPULATION = ‘UPDATE’ AND ACTION_TIMING = ‘BEFORE’ AND TRIGGER_SCHEMA = ‘yanglun’ AND EVENT_OBJECT_TABLE = ‘a’
2019-01-18T15:55:06.613630Z 2997 Query SELECT TRIGGER_SCHEMA, TRIGGER_NAME, DEFINER, ACTION_STATEMENT, SQL_MODE, CHARACTER_SET_CLIENT, COLLATION_CONNECTION, EVENT_MANIPULATION, ACTION_TIMING FROM INFORMATION_SCHEMA.TRIGGERS WHERE EVENT_MANIPULATION = ‘INSERT’ AND ACTION_TIMING = ‘BEFORE’ AND TRIGGER_SCHEMA = ‘yanglun’ AND EVENT_OBJECT_TABLE = ‘a’
2019-01-18T15:55:06.614274Z 2997 Query CREATE TRIGGER pt_osc_yanglun_a_del AFTER DELETE ON yanglun.a FOR EACH ROW DELETE IGNORE FROM yanglun._a_new WHERE yanglun._a_new.id <=> OLD.id #创建触发器
2019-01-18T15:55:06.615701Z 2997 Query CREATE TRIGGER pt_osc_yanglun_a_upd AFTER UPDATE ON yanglun.a FOR EACH ROW BEGIN DELETE IGNORE FROM yanglun._a_new WHERE !(OLD.id <=> NEW.id) AND yanglun._a_new.id <=> OLD.id;REPLACE INTO yanglun._a_new (id) VALUES (NEW.id);END
2019-01-18T15:55:06.617010Z 2997 Query CREATE TRIGGER pt_osc_yanglun_a_ins AFTER INSERT ON yanglun.a FOR EACH ROW REPLACE INTO yanglun._a_new (id) VALUES (NEW.id)
2019-01-18T15:55:06.618610Z 2997 Query EXPLAIN SELECT * FROM yanglun.a WHERE 1=1
2019-01-18T15:55:06.619609Z 2997 Query EXPLAIN SELECT id FROM yanglun.a LOCK IN SHARE MODE /explain pt-online-schema-change 10341 copy table/
2019-01-18T15:55:06.619968Z 2997 Query INSERT LOW_PRIORITY IGNORE INTO yanglun._a_new (id) SELECT id FROM yanglun.a LOCK IN SHARE MODE /pt-online-schema-change 10341 copy table/ #拷贝表数据到新表
2019-01-18T15:55:06.620699Z 2997 Query SHOW WARNINGS
2019-01-18T15:55:06.621093Z 2997 Query SHOW GLOBAL STATUS LIKE ‘Threads_running’
2019-01-18T15:55:06.622367Z 2997 Query ANALYZE TABLE yanglun._a_new / pt-online-schema-change */
2019-01-18T15:55:06.623550Z 2997 Query RENAME TABLE yanglun.a TO yanglun._a_old, yanglun._a_new TO yanglun.a #修改表名
2019-01-18T15:55:06.627337Z 2997 Query DROP TABLE IF EXISTS yanglun._a_old #删除旧表
2019-01-18T15:55:06.629588Z 2997 Query DROP TRIGGER IF EXISTS yanglun.pt_osc_yanglun_a_del
2019-01-18T15:55:06.629959Z 2997 Query DROP TRIGGER IF EXISTS yanglun.pt_osc_yanglun_a_upd
2019-01-18T15:55:06.630274Z 2997 Query DROP TRIGGER IF EXISTS yanglun.pt_osc_yanglun_a_ins #删除触发器
2019-01-18T15:55:06.630758Z 2997 Query SHOW TABLES FROM yanglun LIKE ‘_a_new’
2019-01-18T15:55:06.631449Z 2998 Quit
2019-01-18T15:55:06.631614Z 2997 Quit#结束退出

3.命令示例:

perl /usr/bin/pt-online-schema-change --alter MODIFY COLUMN aaaaa int(4) NULL DEFAULT 62 COMMENT ‘所有推送日期’ AFTER bbbbb --print --charset utf8 --chunk-time 1.000000 --critical-load Threads_connected:2000,Threads_running:80 --max-load Threads_connected:1500,Threads_running:80 --recurse=1 --check-interval 5.000000 --no-check-replication-filters --alter-foreign-keys-method=none --force --execute --statistics --max-lag 3.000000 --no-version-check --recursion-method=processlist --progress percentage,1 --user=xxx --password=xxx --host=1.1.1.1–port=3306 D=database1,t=table1

备注：
如果没有trigger权限，那么用户不会看到pt-online-schema-change创建的触发器