CyberBit

Python+MySQL

17. pt-online-schema-change

 

在平时MySQL的运维过程中,经常会遇到表结构的变更。在表比较小的时候,直接进行变更,时间较短,但是当表非常大的时候,这么做会导致应用卡死,服务不可用。
目前InnoDB引擎是通过以下步骤来进行DDL的:
1 利用DDL之后的语句创建一张临时表
2 在原表上加write lock,阻塞所有DML操作
3 将原表数据复制到临时表
4 将临时表和原表重命名,然后drop原始表
5 释放 write lock。

在这个DDL过程中,针对大表进行的write lock将持续非常长的时间,我们可以用为此 perconal 推出一个工具 pt-online-schema-change,在进行DDL的时候不堵塞原表的读写。

工作原理:
如果表有外键,除非使用 --alter-foreign-keys-method 指定特定的值,否则工具不予执行。
1 创建一张和原表一样的空表结构。
2 执行空表的DDL
3 在原表上创建触发器,将对原表的修改操作记录下来。
4 复制数据到新的空表中,复制完成后,应用修改记录。
注意:如果表中已经定义了触发器这个工具就不能工作了。
5 复制完成后在重命名原表和新的表


============================================


create table t02(id int);


pt-online-schema-change --alter="add name varchar(50) not null default ''" \
h=192.168.100.101,P=3306,u=admin,p=admin,D=db01,t=t02 \
--dry-run --print \
--execute


pt-online-schema-change --alter="modify id int unsigned not null auto_increment primary key" \
h=192.168.100.101,P=3306,u=admin,p=admin,D=db01,t=t02 \
--dry-run --print \
--execute


pt-online-schema-change --alter="add index idx_name_score(name,score)" \
h=192.168.100.101,P=3306,u=admin,p=admin,D=db01,t=t02 \
--dry-run --print \
--execute


pt-online-schema-change --alter="drop index idx_finger" \
h=192.168.100.101,P=3306,u=admin,p=admin,D=db01,t=t02 \
--dry-run --print \
--execute

 

pt-online-schema-change --alter="engine=innodb" \
h=192.168.100.101,P=3306,u=admin,p=admin,D=db01,t=t02 \
--dry-run --print \
--execute

 


================================================

【DSN】

指定时注意大小写敏感,“=”左右不能有空格,多个值之间用逗号分隔

1. A charset

2. D database

3. F mysql_read_default_file

4. h host

5. p password

6. P port

7. S mysql_socket

8. t table

9. u user

 

【具体执行进程解析】

现在执行一个改表语句并开启general log 观察一下

1
pt-online-schema-change --alter 'add column c1 int' u=username,S=/data/mysql.sock,D=test,t=a --execute
  

1. 首先就是各种show,各种set,有兴趣自己去看看,主要就是对权限的检查,超时时间的设定,当前系统的繁忙程度;
然后就是对表的检查,如是否有触发器的存在,以及如下查询:

explain SELECT * FROM `test`.`a` WHERE 1=1;

SELECT table_schema, table_name FROM information_schema.key_column_usage
WHERE referenced_table_schema='test' AND referenced_table_name='a';

SHOW CREATE TABLE `test`.`a`;
到这里表的情况检查完毕


2. 现在就开始建新表,注意名字的改变,a变成了_a_new

并在这个空表上直接alter

ALTER TABLE `test`.`_a_new` add column c1 int
然后做一下检查看alter是否成功

SHOW CREATE TABLE `test`.`_a_new`
   

3. 建立触发器

CREATE TRIGGER `pt_osc_test_a_del` AFTER DELETE ON `test`.`a` FOR EACH ROW DELETE IGNORE
FROM `test`.`_a_new` WHERE `test`.`_a_new`.`id` <=> OLD.`id`

CREATE TRIGGER `pt_osc_test_a_upd` AFTER UPDATE ON `test`.`a` FOR EACH ROW
REPLACE INTO `test`.`_a_new` (`id`, `name`, `type`, `b`) VALUES (NEW.`id`, <br>NEW.`name`, NEW.`type`, NEW.`b`)

CREATE TRIGGER `pt_osc_test_a_ins` AFTER INSERT ON `test`.`a` FOR EACH ROW
REPLACE INTO `test`.`_a_new` (`id`, `name`, `type`, `b`) VALUES (NEW.`id`, <br>NEW.`name`, NEW.`type`, NEW.`b`)
  

4. 通过explain来判断执行chunk拷贝的成本,第一个chunk的大小固定为1000行,后面的chunk根据自己的指定,如chunk-time来确定大小

EXPLAIN SELECT `id`, `name`, `type`, `b` FROM `test`.`a` FORCE INDEX(`PRIMARY`)
WHERE ((`id` >= '1')) AND ((`id` <= '1000')) LOCK IN SHARE MODE

确定不会影响系统正常运行后,执行insert操作,将原始表中的数据按照当前chunk大小拷贝到新表中


INSERT LOW_PRIORITY IGNORE INTO `test`.`_a_new` (`id`, `name`, `type`, `b`)
SELECT `id`, `name`, `type`, `b` FROM `test`.`a` FORCE INDEX(`PRIMARY`)
WHERE ((`id` >= '1')) AND ((`id` <= '1000')) LOCK IN SHARE MODE
 

5. 一个chunk拷贝结束后立即对系统负载进行检查


SHOW GLOBAL STATUS LIKE 'Threads_running'
没问题的话就继续explain,insert,负载太高的话就暂停拷贝等待负载降低,以此类推,直到所有拷贝结束


6. 拷贝结束后,对新表状态是否进行检查

ANALYZE TABLE `test`.`_a_new`
如果正常OK就往下走,如果不OK就删掉新表或不删报错退出(根据参数指定),默认是删掉


7. 确定新表没有问题后就用新表来代替旧表,注意旧表的名字在这个时候也会改一下

RENAME TABLE `test`.`a` TO `test`.`_a_old`, `test`.`_a_new` TO `test`.`a`
  

8. 替换成功后默认是将旧表删掉

DROP TABLE IF EXISTS `test`.`_a_old`
 

9. 将之前建的触发器删掉

DROP TRIGGER IF EXISTS `test`.`pt_osc_test_a_del`

DROP TRIGGER IF EXISTS `test`.`pt_osc_test_a_upd`

DROP TRIGGER IF EXISTS `test`.`pt_osc_test_a_ins`
  

10. 最后再确定一下新表是否改名成功


SHOW TABLES FROM `test` LIKE '\_a\_new'

SHOW TABLES FROM `test` LIKE 'a'

改表完成!!!

 

posted on 2018-12-01 09:21  CyberBit  阅读(325)  评论(0编辑  收藏  举报

导航