【深度】MYSQL ddl原理及千万级表修改不停服解决方案

mysql 的DDL语句在执行的时候会锁表，在数据量大的情况下锁表就会严重影响正常的数据写入，innodb存储引擎在DDL时执行操作如下：

1. 按照原始表(original_table)的表结构和ddl语句，新建一个不可见的临时表(temporary_table)
2. 在原表上面加上WRITE LOCK,阻塞所有的更新操作(insert、delete、update等操作)
3. 执行insert into tmp_table select * from original_table
4. rename original_table和tmp_table,最后drop original_table
5. 最后释放掉write lock

以上步骤可以发现，操作在表锁定的情况是只能查询，不能写入。为了解决这个问题，PERCONA公司推出了一个不会阻塞的工具pt-online-schema-change，成熟的mysql线上千万级表修改不停服解决方案。

pt-online-schema-change操作步骤如下：

1. 首先创建和执行的alter操作的表一样空的表结构
2. 创建三个触发器，监听新的写入数据到新表
3. 复制旧数据到新表
4. 删除触发器，删除旧表
5. 重命名新表为原表名称

命令参数如下：

pt-online-schema-change 
-h=ip_address,
-u=user_name,
D=database,
t=table 
--alter "add column shop_id int(11) DEFAULT NULL " 
--set-vars 
--lock-wait-timeout=3 
--ask-pass -
-execute

本机自测语句如下：

pt-online-schema-change 
--user=root  
--port=3306 
--host=192.168.204.77  
--alter "ADD COLUMN f_id int default 0" D=beeper2_tms,
t=order_list 
--execute;

执行结果如下：

ult 0" D=beeper2_tms,t=order_list --execute;
No slaves found.  See --recursion-method if host yn-vm-204-77.xhj.com has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `beeper2_tms`.`order_list`...
Creating new table...
Created new table beeper2_tms._order_list_new OK.
Altering new table...
Altered `beeper2_tms`.`_order_list_new` OK.
2018-08-19T10:06:20 Creating triggers...
2018-08-19T10:06:20 Created triggers OK.
2018-08-19T10:06:20 Copying approximately 148917 rows...
2018-08-19T10:06:33 Copied rows OK.
2018-08-19T10:06:33 Analyzing new table...
2018-08-19T10:06:33 Swapping tables...
2018-08-19T10:06:33 Swapped original and new tables OK.
2018-08-19T10:06:33 Dropping old table...
2018-08-19T10:06:33 Dropped old table `beeper2_tms`.`_order_list_old` OK.
2018-08-19T10:06:33 Dropping triggers...
2018-08-19T10:06:33 Dropped triggers OK.
Successfully altered `beeper2_tms`.`order_list`.

详细用法建议查看官方文档 pt-online-schema-change

参考文档
https://www.percona.com/doc/percona-toolkit/1.0/pt-online-schema-change.html
https://www.cnblogs.com/piperck/p/5131289.html