pt-online-schema-change 的使用

目录

pt-online-schema-change 最佳实践

pt的详细步骤

Step 1: Create the new table.
Step 2: Alter the new, empty table.  
		This should be very quick, or die if the user specified a bad alter statement.
Step 3: Create the triggers to capture changes on the original table and apply them to the new table.
Step 4: Copy rows.
Step 5: Rename tables: orig -> old, new -> orig
Step 6: Update foreign key constraints if there are child tables.
Step 7: Drop the old table.
		DROP TABLE IF EXISTS `_xx_old`
		DROP TRIGGER IF EXISTS `pt_osc_xx_xx_del`;
		DROP TRIGGER IF EXISTS `pt_osc_xx_xx_upd`;
		DROP TRIGGER IF EXISTS `pt_osc_xx_xx_ins`;
done

一、常用参数解读

1.0 生产环境使用的参数

inception调用pt-online-schema-change,相关参数如下:

inception_osc_alter_foreign_keys_method = rebuild_constraints
inception_osc_check_alter = on
inception_osc_check_interval = 5
inception_osc_check_replication_filters = OFF
inception_osc_chunk_size = 1000
inception_osc_chunk_size_limit = 4
inception_osc_chunk_time = 1
inception_osc_critical_thread_connected = 4000
inception_osc_critical_thread_running = 300
inception_osc_drop_new_table = on
inception_osc_drop_old_table = on
inception_osc_max_lag = 3
inception_osc_max_thread_connected = 2500
inception_osc_max_thread_running = 200
inception_osc_min_table_size = 16
inception_osc_recursion_method = none

以上inception参数对应的pt-online-schema-change的命令参数如下:

pt-online-schema-change 
	--alter " xx " 
	--alter-foreign-keys-method=rebuild_constraints 
	--check-alter=yes 
	--check-interval=5 
	--check-replication-filters=no 
	--chunk-size=1000
	--chunk-size-limit=4
	--chunk-time=1
	--critical-load=thread_connected:4000,thread_running:300
	--max-load=thread_connected:2500,thread_running:200
	--drop-new-table=yes
	--drop-old-table=yes
	--max-lag=3
	--recursion-method=none

1.1 基本用法

  • pt-online-schema-change [OPTIONS] DSN
  • pt-online-schema-change —alter “ADD COLUMN c1 INT” D=sakila,t=actor
  • pt-online-schema-change —alter “ENGINE=InnoDB” D=sakila,t=actor

1.2 安全的pt-online-schema-change

默认情况下,pt-online-schema-change 是不会修改表的,除非你显示的指定了 —execute
pt-online-schema-change 有一系列动作来阻住一切不期望的后果发生,包括 自动检测复制,以及以下相关措施

  • 大部分情况下,pt-online-schema-change会拒绝给没有主键和唯一键的表做操作,可以参考 —alter 了解更多信息
  • 如果检测到复制过滤(ignore-db,do-db等),pt-online-schema-change会拒绝操作,可以参考 —[no]check-replication-filters 了解更多信息
  • 如果发现复制严重的厉害,那么会暂停copy数据,可以参考 —max-lag 了解更多的信息
  • 如果发现服务器负载非常高,那么也会暂停或者停止相关操作,可以参考 —max-load and —critical-load 了解更多信息
  • 该工具默认会设置 innodb_lock_wait_timeout=1 和 lock_wait_timeout=60来减少竞争 , 参考 —set-vars 了解更多信息
  • 如果有外键约束,那么禁止改表,出发你指定 —alter-foreign-keys-method.
  • Percona XtraDB Cluster中禁止修改MYISAM的表

1.3 常用参数

  • --dry-run and --execute , 这两个是互斥的参数,一个是打印,一个是执行

    --alter

通过这个选项,就不需要alter table关键字了。 你可以通过逗号来指定多个修改操作。

* 以下列出--alter中的一些限制,大家谨记和避免  

1. 原表必须要有主键或唯一键,因为delete触发器需要用到,否则会报错  

2. rename子句,不允许给表重命名  

2.1 不能通过删除一列,然后再新增一列的方式来完成对列的重命名操作  

3. 新增字段,如果这个字段是NOT NULL,必须要指定default值,否则报错。  你必须指定默认值  

4. 如果是DROP FOREIGN KEY constraint_name , 那么必须指定 _ 加上 constraint_name , 而不是 constraint_name。 
	举例: CONSTRAINT `fk_foo` FOREIGN KEY (`foo_id`) REFERENCES `bar` (`foo_id`)
	你必须指定: --alter "DROP FOREIGN KEY _fk_foo"  而不是  --alter "DROP FOREIGN KEY fk_foo".

5. 必须确保数据库高于5.0版本,因为5.0版本转换MYSIAM到InnoDB会出错
  • [no]check-alter

默认yes, 给—alter 做一些检测

* 列的重命名

在之前的版本 CHANGE COLUMN name new_name 这个操作是会丢失数据的,现在的工具修复了

但是,由于pt代码并不是full-blown SQL parser,所以,你应该先 --dry-run and --print , 确认下renamed的列名是否正确,以确保无误  

* 删除主键

删除主键是很危险的事情,尽量不要做这样的动作
  • —alter-foreign-keys-method

我们的规范不允许有外键,如果有外键,我们采取其他方式DDL

如何把外键引用到新表?需要特殊处理带有外键约束的表,以保证它们可以应用到新表.当重命名表的时候,外键关系会带到重命名后的表上。
该工具有两种方法,可以自动找到子表,并修改约束关系。
    auto: 在rebuild_constraints和drop_swap两种处理方式中选择一个。
    rebuild_constraints:使用 ALTER TABLE语句先删除外键约束,然后再添加.如果子表很大的话,会导致长时间的阻塞。
    drop_swap: 执行FOREIGN_KEY_CHECKS=0,禁止外键约束,删除原表,再重命名新表。这种方式很快,也不会产生阻塞,但是有风险:
		    1, 在删除原表和重命名新表的短时间内,表是不存在的,程序会返回错误。
    		2, 如果重命名表出现错误,也不能回滚了.因为原表已经被删除。
	none: 类似"drop_swap"的处理方式,但是它不删除原表,并且外键关系会随着重命名转到老表上面。
  • —host=xxx —user=xxx —password=xxx
连接实例信息,缩写-h xxx -u xxx -p xxx,密码可以使用参数--ask-pass 手动输入。
  • D=db_name,t=table_name
指定要ddl的数据库名和表名
  • —charset
最好设置为MySQL默认字符集: utf8
  • —check-interval
默认1秒,检测--max-lag
  • —[no]check-replication-filters

默认yes

如果发现任何服务器有 binlog_ignore_db and replicate_do_db , 那么就报错
  • —check-slave-lag
指定一个从库的DSN连接地址,如果从库超过--max-lag参数设置的值,就会暂停操作。
  • —[no]swap-tables
默认yes。交换原始表和新表,除非你禁止--[no]drop-old-table。
  • —max-lag
默认1s。
每个chunk拷贝完成后,会查看所有复制Slave的延迟情况。
要是延迟大于该值,则暂停复制数据,直到所有从的滞后小于这个值,使用Seconds_Behind_Master。
如果有任何从滞后超过此选项的值,则该工具将睡眠--check-interval指定的时间,再检查。
如果从被停止,将会永远等待,直到从开始同步,并且延迟小于该值。
如果指定--check-slave-lag,该工具只检查该服务器的延迟,而不是所有服务器。
  • —max-load
默认为Threads_running=25。
每个chunk拷贝完后,会检查SHOW GLOBAL STATUS的内容,检查指标是否超过了指定的阈值。
如果超过,则先暂停。
这里可以用逗号分隔,指定多个条件,
每个条件格式: status指标=MAX_VALUE 或者 status指标:MAX_VALUE。
如果不指定MAX_VALUE,那么工具会设置其为当前值的120%。
  • —critical-load
默认为Threads_running=50。
用法基本与--max-load类似,如果不指定MAX_VALUE,那么工具会这只其为当前值的200%。
如果超过指定值,则工具直接退出,而不是暂停。
  • —print
打印SQL语句到标准输出。指定此选项可以让你看到该工具所执行的语句,和--dry-run配合最佳。
  • —progress
复制数据的时候打印进度报告,二部分组成:第一部分是百分比,第二部分是时间。
  • —set-vars
设置MySQL变量,多个用逗号分割。
默认该工具设置的是: wait_timeout=10000 innodb_lock_wait_timeout=1 lock_wait_timeout=60
  • —recursion-method
默认是show processlist,发现从的方法,也可以是host,但需要在从上指定report_host,通过show slave hosts来找到,可以指定none来不检查Slave。
METHOD       USES
===========  ==================
processlist  SHOW PROCESSLIST
hosts        SHOW SLAVE HOSTS
dsn=DSN      DSNs from a table
none         Do not find slaves

指定none则表示不在乎从的延迟。

—pause-file

可以指定文件暂停pt-online-schema-change

二、使用限制

  • 哪些ddl是不可以做的,做了容易出错
1. 禁止创建唯一索引,会丢失数据,更加不允许添加 --alter-check=no,--check-unique-key-change=no  
2. 如果原表没有主键,或者也没有唯一索引,这些表是不允许用pt做DDL的  
3. 禁止对外键的表进行pt ddl
4. 禁止对表进行重命名
5. 禁止对列进行重命名,如果一定要做,也必须先print出来检测清楚列名是否正确  
6. 新增字段,NOT NULL必须要指定默认值
7. 不允许删除主键
  • 由于pt触发器原理,rowcopy会产业一堆的binlog,所以做之前要检测binlog空间是否够用,也要检测数据空间多一倍表空间是否够用
  • 禁止在业务高峰期进行pt-online-schema-change操作
  • 原表不能有触发器
  • MySQL最好设置为innodb_autoinc_lock_mode=2,否则在高并发的写入情况下,很容易产生所等待以及死锁
  • master的表结构必须跟slave的表结构一致,不允许异构,否则pt-online-schema-change的原理就是会rename,然后slave不一致的表结构会被master覆盖,谨记

三、关于触发器

  • 3.0.2之前的update触发器
REPLACE INTO `lc`.`_hb_new` (`id`, `ts`, `ts2`, `c1`) VALUES (NEW.`id`, NEW.`ts`, NEW.`ts2`, NEW.`c1`)
  • 3.0.2之后的update触发器
BEGIN 
	DELETE IGNORE FROM `lc`.`_hb_new` WHERE !(OLD.`id` <=> NEW.`id`) AND `lc`.`_hb_new`.`id` <=> OLD.`id`;
	REPLACE INTO `lc`.`_hb_new` (`id`, `ts`, `ts2`) VALUES (NEW.`id`, NEW.`ts`, NEW.`ts2`);
END
  • 原理
update触发器
    =SQL转换=> delete ignore + replace into (大于3.0.2版本)
    =SQL转换=> replace into(低于3.0.2版本,所以这个版本会有问题,如果这时候对老的主键修改,那么修改之前的值不会去掉,从而多了一些异常数据)


举例:t表中有三条数据,第一列id是主键

------
1 lc  --row1
2 lc  --row2
3 lc  --row3
------

pt-online-schema-change的原理大致四个阶段:

1. 创建临时表_t_new
2. 创建触发器
3. 老数据row copy
4. swap table


好了,我们来举个例子:

1. 创建临时表_t_new	
2. 创建触发器
3. 老数据row copy
	3.1 拷贝数据row1,row2完毕
	3.2 这时候业务有一个update语句, update t set id = 10 where id=1;
	3.3 拷贝数据row3
4. swap table

这时候脑补一下原表和新表的示意图, 这时候已经执行到3.1阶段

老表
------------
1 lc  --row1
2 lc  --row2    
3 lc  --row3
------------

新表
-----------
1 lc
2 lc
-----------

这时候脑补一下原表和新表的示意图, 这时候已经执行到3.3阶段

老表(update t set id = 10 where id=1)
------------
10 lc  --row1
2 lc  --row2
3 lc  --row3
------------

新表(3.0.2之前版本的触发器,没有delete映射,所以最终结果如下,跟老表相比已经不一致了,多了一条数据1,lc) , 触发器 replace into _t_new(id,name) values(10,lc) 
-----------
1 lc
2 lc
10 lc
3 lc
-----------

新表(3.0.2之后版本的触发器,有delete映射,所以最终结果如下,于老表的数据一致) , 触发器 delete ignore _t_new where id = 1;replace into _t_new(id,name) values(10,lc);
-----------
2 lc
10 lc
3 lc
-----------

四、错误处理

遇到错误后,继续补充完整

上一篇:GCompute - 计算三角形对的交点(2)


下一篇:camstar自定义function