percona-tools工具在线DDL的使用

  1. 云栖社区>
  2. 博客>
  3. 正文

percona-tools工具在线DDL的使用

技术小胖子 2017-11-10 23:30:00 浏览1476
展开阅读全文

好久没有写博客了,今天发表一篇吧:)

通常的在线进行表字段的增减都会造成表所,如果表较小还能接受,如果过大则这个锁持续的时间会让人比较烦恼,对业务持续性影响较大。


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`.


     本文转自andylhz 51CTO博客,原文链接:http://blog.51cto.com/andylhz2009/1843944,如需转载请自行联系原作者



网友评论

登录后评论
0/500
评论
技术小胖子
+ 关注