pt-online-schema-change 是什么?
pt-online-schema-change是Percona工具包的一员,用于修改表而不会造成读锁或者写锁;
pt-online-schema-change详细描述:
pt-online-schema-change工作在一个副本上,所以原表没有被lock,client可以继续进行read和write操作;pt-online-schema-change会创建一个空表,然后根据需要对空表进行modify,之后将原表数据复制到新表,复制完成后就用RENAME将新表替换原表,完成后 drop 原表;
pt-online-schema-change修改以块为单位,修改中根据情况自行调整块大小(类似checksum)
为了安全,如果没执行--execute,则不会发生修改;工具会有多种措施,防止不必要的负载,如:
1, 多数情况下,如果没有PRIMARY KEY或者UNIQUE KEY,则拒绝操作(--alter)
2, 如果检测到replication filters,则拒绝操作(--[no]check-replication-filters)
3, 如果有较大的延时,则暂停操作("--max-lag")
4, 如果有较大的负载则暂停或放弃操作("--max-load" and "--critical-load)
5, 工具会设置自己会话的锁超时时间"innodb_lock_wait_timeout=1"lock_wait_timeout=60,所以会更少 地影响其他事物(修改时间"--set-vars")
6, 默认如果有外键约束则拒绝修改(忽略约束"--alter-foreign-keys-method")
7, 不支持Percona XtraDB Cluster(Percona高可用解决方案)
工具会忽略mysql 5.7+版本的"GENERATED"列,因为它根据表达式计算列值
输出信息:
复制表的时候会显示进度, 如果要显示额外信息,则--print, 如果指定--statistics 最后会显示计数信息
选项OPTIONS
--alter
不需要加alter table关键字,如pt-online-schema-change --alter "ADD COLUMN c2 INT" D=apple,t=a
限制:
1, 必须要有PRIMARY KEY或者UNIQUE KEY,因为它会在程序进行的时候创建DELETE 触发器,来保证新表跟着老表一起更新;
2, RENAME不会被采用去RENAME TABLE
3, 如果增加列的时候指定NOT NULL,并且未提供默认值,则失败;
4, 如果要删除外键,需要指定 "_constraint_name"而不是 "constraint_name"
5, 如果要将MYISAM表修改为Innodb会报错
--[no]analyze-before-swap 当新表与原表交换的前执行analyze table;
--ask-pass 连接的时候会要求提供密码
--charset 默认字符集
--[no]check-alter 对于危险的操作会产生告警,如删除主键或者重命名列;
--check-interval 当主从延时为--max-lag 秒的时候,检查sleep的时间;例如设置为10的时候,主从延时产生,则暂停10秒,再检查延时,如果依然有有延时,停止10s再检查; 默认1s
--[no]check-plan 是否在执行前用EXPLAIN检查语句
--[no]check-replication-filters 是否检查过滤器,如果有任何过滤器的时候,操作都会被放弃;(如:binlog_ignore_db replicate_do_db)
--check-slave-lag 如果slave的时间戳低于"--max-lag"的时候则暂停操作
--chunk-index 指定chunk的索引,工具默认会选择最合适的索引,当然你也可以手动指定
--chunk-index-columns 有复合索引的时候,指定索引列
--chunk-size chunk的行数,默认1000
--chunk-size-limit 默认4,chunk超过应有值的4倍大小则跳过。如果没有唯一索引,则chunk 大小是不精确的,工具会用EXPLAIN评估大小,如果超过需要chunk大小的n倍,则跳过该chunk;
--chunk-time 默认0.5, 工具会根据每次复制数据花费的时间自动调整chunk大小,尽可能使每次时间都相同;=0则不调节
--config 读取配置文件,多个文件逗号分隔;如果指定则必须是第一个参数
--critical-load 默认Threads_running=50; 每次chunk执行后会自动用SHOW GLOBAL STATUS检查负载情况,如果超过阈值则放弃;
--database -D 指定库
--default-engine 该选项将导致新表使用系统默认引擎; 而不是原有表一致的引擎
--data-dir v 5.6+ 在不同的分区上创建新表
--remove-data-dir 如果原表已通过--data-dir 创建,该参数会删除它并在默认datadir下创建新表
--defaults-file 从文件中读取选项
--[no]drop-new-table 如果复制原表失败则删除新表; 也可以no-xxx来保留新表
--[no]drop-old-table rename新表后drop旧表,可以no-xxx来保留旧表
--[no]drop-triggers 删除旧表的触发器;"--no-drop-triggers" forces "--no-drop-old-table".
--dry-run 创建并修改新表,但不创建触发器,也不复制表,或者替换原表,与--execute互斥
--execute 执行操作 与 --dry-run互斥
--[no]check-unique-key-change 如果--alter尝试增加唯一索引的的话,则工具不会运行; 因为增加唯一索引如果列有重复值,会发生丢失数据的情况;因为INSERT的时候默认采用"INSERT IGNORE"
--force 强制运行,可能打破外键约束
--help 帮助
--host | -h 指定host
--max-lag 默认1s, 如果主从延时的时间超过这个值,则复制会暂停"--check-interval"秒时间;然后再检查,直到主从延时小于该值;如果指定了 "--check-slave-lag",则只会检查指定slave延时,而不是检查所有slave;如果有任何SLAVE stop了,那么工具会一直等待下去;每次停止的时候都会打印报告
--max-load 默认 : Threads_running=25
每次复制chunk后检查负载,如果超过该值则暂停;类似--critical-load
--preserve-triggers 保留原表的触发器,不删除
--new-table-name 指定新表的名字,默认是 tablename_new
--null-to-not-null 修改允许null值为not null
--password 指定密码
--pause-file 改参数指定的文件存在的时,操作将会暂停
--pid 新建pid文件
--port 指定连接端口
--print 将会显示工具执行的命令
--progress 显示进度,默认: time,30
两部分组成,第一部分percentage,time或者iterations; 第二部分多久更新一次
--quiet -q
不打印消息到屏幕; 禁用--progress, ERROR和告警还是会打印
--recurse
当发现有slave的时候,指定递归的层数 , int值
--recursion-method 查找Slave的递归方法
METHOD USES
=========== ==================
processlist SHOW PROCESSLIST
hosts SHOW SLAVE HOSTS
dsn=DSN DSNs from a table
none Do not find slaves
--skip-check-slave-lag 检查SLAVE的时候,指定该SLAVE跳过;
--slave-user 设置连接slave的用户;可以指定用更少权限的用户连接SLAVE,但是所有slave都必须有该账户
--slave-password --slave-user 的密码
--set-vars 默认:
wait_timeout=10000
innodb_lock_wait_timeout=1
lock_wait_timeout=60
--sleep 复制每个chunk的时候暂停多少s,默认0
--socket 指定连接socket
--statistics 打印计数器的数据,当有告警的时候这个参数比较有用
-[no]swap-tables 指定是否替换旧表
--tries 重试次数;
--user 连接用户名
--version 版本
--[no]version-check 检查Percona Toolkit的最新版本
[root@localhost tmp]# pt-online-schema-change --alter "ADD COLUMN c3 INT" D=apple,t=a -u root -h localhost -p password --execute
No slaves found. See --recursion-method if host localhost.localdomain has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
# A software update is available:
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 `apple`.`a`...
Creating new table...
Created new table apple._a_new OK.
Altering new table...
Altered `apple`.`_a_new` OK.
2018-09-05T12:57:33 Creating triggers...
2018-09-05T12:57:33 Created triggers OK.
2018-09-05T12:57:33 Copying approximately 2 rows...
2018-09-05T12:57:33 Copied rows OK.
2018-09-05T12:57:33 Analyzing new table...
2018-09-05T12:57:33 Swapping tables...
2018-09-05T12:57:34 Swapped original and new tables OK.
2018-09-05T12:57:34 Dropping old table...
2018-09-05T12:57:34 Dropped old table `apple`.`_a_old` OK.
2018-09-05T12:57:34 Dropping triggers...
2018-09-05T12:57:34 Dropped triggers OK.
Successfully altered `apple`.`a`.