好久没有写博客了，今天发表一篇吧：）
通常的在线进行表字段的增减都会造成表所，如果表较小还能接受，如果过大则这个锁持续的时间会让人比较烦恼，对业务持续性影响较大。
Percona 提供了一款关于MySQL管理的工具集很强大，包括了在线DDL工具 pt-online-schema-change
安装就不介绍了，自行百度吧
下面的原理介绍摘自网络http://www.it165.net/pro/html/201312/8928.html，写的比较清晰，就不重复造轮子了。
+++++++++++++++++++++
模仿MySQL的alter，但不同的是在alter操作更改表结构的时候不用锁定表，也就是说执行alter的时候不会阻塞写和读取操作，客户端可以继续都和修改数据。注意执行这个工具的时候必须做好备份，操作之前最好详细读一下官方文档。
1、如果存在外键，根据alter-foreign-keys-method参数的值，检测外键相关的表，做相应设置的处理。没有使用 --alter-foreign-keys-method 指定特定的值，该工具不予执行
2、创建一个新的表，表结构为修改后的数据表，用于从源数据表向新表中导入数据。
3、创建触发器，用于记录从拷贝数据开始之后，对源数据表继续进行数据修改的操作记录下来，用于数据拷贝结束后，执行这些操作，保证数据不会丢失。如果表中已经定义了触发器这个工具就不能工作了。
4、拷贝数据，从源数据表中拷贝数据到新表中。
5、修改外键相关的子表，根据修改后的数据，修改外键关联的子表。
6、rename源数据表为old表，把新表rename为源表名，并将old表删除。
7、删除触发器。
+++++++++++++++++++
相关的参数请 --help
1. 进行--dry-run测试
[root@master ~]# pt-online-schema-change --user=root --password=root --host=localhost --alter='ENGINE=Innodb' D=baby,t=baby_account --dry-run
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
Child tables:
`baby`.`baby_account_billing_contract` (approx. 21 rows)
`baby`.`baby_account_tactics` (approx. 13308 rows)
`baby`.`baby_account_tactics_bak` (approx. 1672 rows)
`baby`.`baby_billing` (approx. 11856 rows)
`baby`.`baby_billing_apply` (approx. 128 rows)
`baby`.`baby_order_bak_haochongpei` (approx. 1 rows)
You did not specify --alter-foreign-keys-method, but there are foreign keys that reference the table. Please read the tool's documentation carefully.
##表有主键，对主键的处理方式需要明确指出
--alter-foreign-keys-method 说明：
如何把外键引用到新表?需要特殊处理带有外键约束的表,以保证它们可以应用到新表.当重命名表的时候,外键关系会带到重命名后的表上。
该工具有两种方法,可以自动找到子表,并修改约束关系。
auto 在rebuild_constraints和drop_swap两种处理方式中选择一个
rebuild_constraints使用 ALTER TABLE语句先删除外键约束,然后再添加.如果子表很大的话,会导致长时间的阻塞。
drop_swap 执行FOREIGN_KEY_CHECKS=0,禁止外键约束,删除原表,再重命名新表。这种方式很快,也不会产生阻塞,但是有风险：
1, 在删除原表和重命名新表的短时间内,表是不存在的,程序会返回错误。
2, 如果重命名表出现错误,也不能回滚了.因为原表已经被删除。
none 类似"drop_swap"的处理方式,但是它不删除原表,并且外键关系会随着重命名转到老表上面。
我这里指定的是auto自动选择，可以看到下文的提示
[root@master ~]# pt-online-schema-change --user=root --password=root --host=localhost --alter='ENGINE=Innodb' D=baby,t=baby_account --alter-foreign-keys-method=auto --dry-run
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
Child tables:
`baby`.`baby_account_billing_contract` (approx. 21 rows)
`baby`.`baby_account_tactics` (approx. 13308 rows)
`baby`.`baby_account_tactics_bak` (approx. 1672 rows)
`baby`.`baby_billing` (approx. 11856 rows)
`baby`.`baby_billing_apply` (approx. 128 rows)
`baby`.`baby_order_bak_haochongpei` (approx. 1 rows)
Will automatically choose the method to update foreign keys.
Starting a dry run. `baby`.`baby_account` will not be altered. Specify --execute instead of --dry-run to alter the table.
Creating new table...
Created new table baby._baby_account_new OK.
Altering new table...
Altered `baby`.`_baby_account_new` OK.
Not creating triggers because this is a dry run.
Not copying rows because this is a dry run.
Not determining the method to update foreign keys because this is a dry run.
Not swapping tables because this is a dry run.
Not updating foreign key constraints because this is a dry run.
Not dropping old table because this is a dry run.
Not dropping triggers because this is a dry run.
2016-08-29T14:31:59 Dropping new table...
2016-08-29T14:31:59 Dropped new table OK.
Dry run complete. `baby`.`baby_account` was not altered.
执行操作，看到报错，因为我这环境是主从复制的环境，所以--check-replication-filters参数需要明确指出
[root@master ~]# pt-online-schema-change --user=root --password=root --host=localhost --alter='ENGINE=Innodb' D=baby,t=baby_account --alter-foreign-keys-method=auto --execute
Found 2 slaves:
client.wboy.com
client1.wboy.com
Will check slave lag on:
client.wboy.com
client1.wboy.com
Replication filters are set on these hosts:
client.wboy.com
binlog_ignore_db = mysql,test,information_schema,performance_schema
client1.wboy.com
binlog_ignore_db = mysql,test,information_schema,performance_schema
Please read the --check-replication-filters documentation to learn how to solve this problem. at /usr/local/bin/pt-online-schema-change line 8493.
[root@master ~]# pt-online-schema-change --user=root --password=root --host=localhost --alter='ENGINE=Innodb' D=baby,t=baby_account --alter-foreign-keys-method=auto --no-check-replication-filters --execute
Found 2 slaves:
client.wboy.com
client1.wboy.com
Will check slave lag on:
client.wboy.com
client1.wboy.com
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
Child tables:
`baby`.`baby_account_billing_contract` (approx. 21 rows)
`baby`.`baby_account_tactics` (approx. 13308 rows)
`baby`.`baby_account_tactics_bak` (approx. 1672 rows)
`baby`.`baby_billing` (approx. 11856 rows)
`baby`.`baby_billing_apply` (approx. 128 rows)
`baby`.`baby_order_bak_haochongpei` (approx. 1 rows)
Will automatically choose the method to update foreign keys.
Altering `baby`.`baby_account`...
Creating new table...
Created new table baby._baby_account_new OK.
Waiting forever for new table `baby`.`_baby_account_new` to replicate to client1.wboy.com...
Waiting for client1.wboy.com: 0% 00:00 remain
Waiting for client1.wboy.com: 0% 00:00 remain
Altering new table...
Altered `baby`.`_baby_account_new` OK.
2016-08-29T14:54:50 Creating triggers...
2016-08-29T14:54:50 Created triggers OK.
2016-08-29T14:54:50 Copying approximately 20189 rows...
Replica lag is 32 seconds on client1.wboy.com. Waiting.
Replica lag is 60 seconds on client1.wboy.com. Waiting.
Copying `baby`.`baby_account`: 13% 06:43 remain
Replica lag is 32 seconds on client1.wboy.com. Waiting.
Replica lag is 60 seconds on client1.wboy.com. Waiting.
Copying `baby`.`baby_account`: 58% 01:33 remain
Replica lag is 31 seconds on client1.wboy.com. Waiting.
Replica lag is 59 seconds on client1.wboy.com. Waiting.
Copying `baby`.`baby_account`: 99% 00:01 remain
2016-08-29T14:58:04 Copied rows OK.
2016-08-29T14:58:04 Max rows for the rebuild_constraints method: 6168
Determining the method to update foreign keys...
2016-08-29T14:58:04 `baby`.`baby_account_billing_contract`: 21 rows; can use rebuild_constraints
2016-08-29T14:58:04 `baby`.`baby_account_tactics`: too many rows: 13308; must use drop_swap
2016-08-29T14:58:04 Drop-swapping tables...
2016-08-29T14:58:04 Analyzing new table...
2016-08-29T14:58:05 Dropped and swapped tables OK.
Not dropping old table because --no-drop-old-table was specified.
2016-08-29T14:58:05 Dropping triggers...
2016-08-29T14:58:05 Dropped triggers OK.
Successfully altered `baby`.`baby_account`.
169
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
