mysql:pt-online-schema-change 在线修改表、删除表数据【转】

pt-online-schema-change
名字：pt-online-schema-change - ALTER tables without locking them. 在线改表

下载地址：
https://www.percona.com/downloads/percona-toolkit/3.0.12/binary/redhat/6/x86_64/percona-toolkit-3.0.12-1.el6.x86_64.rpm
https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html#downloading
官方文档：https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html

1.首先了解一下pt-online-schema-change工作原理，其实原理很简单

1. 1、如果存在外键，根据alter-foreign-keys-method参数的值，检测外键相关的表，做相应设置的处理。没有使用 --alter-foreign-keys-method 指定特定的值，该工具不予执行
2. 2、创建一个新的表，表结构为修改后的数据表，用于从源数据表向新表中导入数据。
3. 3、创建触发器，用于记录从拷贝数据开始之后，对源数据表继续进行数据修改的操作记录下来，用于数据拷贝结束后，执行这些操作，保证数据不会丢失。如果表中已经定义了触发器这个工具就不能工作了。
4. 4、拷贝数据，从源数据表中拷贝数据到新表中。
5. 5、修改外键相关的子表，根据修改后的数据，修改外键关联的子表。
6. 6、rename源数据表为old表，把新表rename为源表名，并将old表删除。
7. 7、删除触发器。

2.使用方法：

1. pt-online-schema-change [OPTIONS] DSN
2.  
3. 具体说下 OPTIONS 的一些常用的参数：
4. 复制代码
5.  
6. --user:
7. -u，连接的用户名
8.  
9. --password：
10. -p，连接的密码
11.  
12. --database：
13. -D，连接的数据库
14.  
15. --port
16. -P，连接数据库的端口
17.  
18. --host:
19. -h，连接的主机地址
20.  
21. --socket:
22. -S，连接的套接字文件
23.  
24. --ask-pass
25. 隐式输入连接MySQL的密码
26.  
27. --charset
28. 指定修改的字符集
29.  
30. --defaults-file
31. -F，读取配置文件
32.  
33. --alter：
34. 结构变更语句，不需要alter table关键字。可以指定多个更改，用逗号分隔。如下场景，需要注意：
35. 不能用RENAME来重命名表。
36. 列不能通过先删除，再添加的方式进行重命名，不会将数据拷贝到新列。
37. 如果加入的列非空而且没有默认值，则工具会失败。即其不会为你设置一个默认值，必须显示指定。
38. 删除外键(drop foreign key constrain_name)时，需要指定名称_constraint_name，而不是原始的constraint_name。
39. 如：CONSTRAINT `fk_foo` FOREIGN KEY (`foo_id`) REFERENCES `bar` (`foo_id`)，需要指定：--alter "DROP FOREIGN KEY _fk_foo"
40.  
41. --alter-foreign-keys-method
42. 如何把外键引用到新表?需要特殊处理带有外键约束的表,以保证它们可以应用到新表.当重命名表的时候,外键关系会带到重命名后的表上。
43. 该工具有两种方法,可以自动找到子表,并修改约束关系。
44. auto： 在rebuild_constraints和drop_swap两种处理方式中选择一个。
45. rebuild_constraints：使用 ALTER TABLE语句先删除外键约束,然后再添加.如果子表很大的话,会导致长时间的阻塞。
46. drop_swap： 执行FOREIGN_KEY_CHECKS=0,禁止外键约束,删除原表,再重命名新表。这种方式很快,也不会产生阻塞,但是有风险：
47. 1, 在删除原表和重命名新表的短时间内,表是不存在的,程序会返回错误。
48. 2, 如果重命名表出现错误,也不能回滚了.因为原表已经被删除。
49. none： 类似"drop_swap"的处理方式,但是它不删除原表,并且外键关系会随着重命名转到老表上面。
50.  
51. --[no]check-alter
52. 默认yes，语法解析。配合--dry-run 和 --print 一起运行，来检查是否有问题（change column，drop primary key）。
53.  
54. --max-lag
55. 默认1s。每个chunk拷贝完成后，会查看所有复制Slave的延迟情况。要是延迟大于该值，则暂停复制数据，直到所有从的滞后小于这个值，使用Seconds_Behind_Master。如果有任何从滞后超过此选项的值，则该工具将睡眠--check-interval指定的时间，再检查。如果从被停止，将会永远等待，直到从开始同步，并且延迟小于该值。如果指定--check-slave-lag，该工具只检查该服务器的延迟，而不是所有服务器。
56.  
57. --check-slave-lag
58. 指定一个从库的DSN连接地址,如果从库超过--max-lag参数设置的值,就会暂停操作。
59.  
60. --recursion-method
61. 默认是show processlist，发现从的方法，也可以是host，但需要在从上指定report_host，通过show slave hosts来找到，可以指定none来不检查Slave。
62.  
63. METHOD USES
64. =========== ==================
65. processlist SHOW PROCESSLIST
66. hosts SHOW SLAVE HOSTS
67. dsn=DSN DSNs from a table
68. none Do not find slaves
69. 指定none则表示不在乎从的延迟。
70.  
71. --check-interval
72. 默认是1。--max-lag检查的睡眠时间。
73.  
74. --[no]check-plan
75. 默认yes。检查查询执行计划的安全性。
76.  
77. --[no]check-replication-filters
78. 默认yes。如果工具检测到服务器选项中有任何复制相关的筛选，如指定binlog_ignore_db和replicate_do_db此类。发现有这样的筛选，工具会报错且退出。因为如果更新的表Master上存在，而Slave上不存在，会导致复制的失败。使用–no-check-replication-filters选项来禁用该检查。
79.  
80. --[no]swap-tables
81. 默认yes。交换原始表和新表，除非你禁止--[no]drop-old-table。
82.  
83. --[no]drop-triggers
84. 默认yes，删除原表上的触发器。 --no-drop-triggers 会强制开启 --no-drop-old-table 即：不删除触发器就会强制不删除原表。
85.  
86. --new-table-name
87. 复制创建新表的名称，默认%T_new。
88.  
89. --[no]drop-new-table
90. 默认yes。删除新表，如果复制组织表失败。
91.  
92. --[no]drop-old-table
93. 默认yes。复制数据完成重命名之后，删除原表。如果有错误则会保留原表。
94.  
95. --max-load
96. 默认为Threads_running=25。每个chunk拷贝完后，会检查SHOW GLOBAL STATUS的内容，检查指标(thread)是否超过了指定的阈值。如果超过，则先暂停。这里可以用逗号分隔，指定多个条件，每个条件格式：status指标=MAX_VALUE或者status指标:MAX_VALUE。如果不指定MAX_VALUE，那么工具会指定其为当前值的120%。
97.  
98. --critical-load
99. 默认为Threads_running=50。用法基本与--max-load类似，如果不指定MAX_VALUE，那么工具会指定其为当前值的200%。如果超过指定值，则工具直接退出，而不是暂停。
100.  
101. --default-engine
102. 默认情况下，新的表与原始表是相同的存储引擎，所以如果原来的表使用InnoDB的，那么新表将使用InnoDB的。在涉及复制某些情况下，很可能主从的存储引擎不一样。使用该选项会默认使用默认的存储引擎。
103.  
104. --set-vars
105. 设置MySQL变量，多个用逗号分割。默认该工具设置的是： wait_timeout=10000 innodb_lock_wait_timeout=1 lock_wait_timeout=60
106.  
107. --chunk-size-limit
108. 当需要复制的块远大于设置的chunk-size大小,就不复制.默认值是4.0，一个没有主键或唯一索引的表,块大小就是不确定的。
109.  
110. --chunk-time
111. 在chunk-time执行的时间内,动态调整chunk-size的大小,以适应服务器性能的变化，该参数设置为0,或者指定chunk-size,都可以禁止动态调整。
112.  
113. --chunk-size
114. 指定块的大小,默认是1000行,可以添加k,M,G后缀.这个块的大小要尽量与--chunk-time匹配，如果明确指定这个选项,那么每个块就会指定行数的大小.
115.  
116. --[no]check-plan
117. 默认yes。为了安全,检查查询的执行计划.默认情况下,这个工具在执行查询之前会先EXPLAIN,以获取一次少量的数据,如果是不好的EXPLAIN,那么会获取一次大量的数据，这个工具会多次执行EXPALIN,如果EXPLAIN不同的结果,那么就会认为这个查询是不安全的。
118.  
119. --statistics
120. 打印出内部事件的数目，可以看到复制数据插入的数目。
121.  
122. --dry-run
123. 创建和修改新表，但不会创建触发器、复制数据、和替换原表。并不真正执行，可以看到生成的执行语句，了解其执行步骤与细节。--dry-run与--execute必须指定一个，二者相互排斥。和--print配合最佳。
124.  
125. --execute
126. 确定修改表，则指定该参数。真正执行。--dry-run与--execute必须指定一个，二者相互排斥。
127.  
128. --print
129. 打印SQL语句到标准输出。指定此选项可以让你看到该工具所执行的语句，和--dry-run配合最佳。
130.  
131. --progress
132. 复制数据的时候打印进度报告，二部分组成：第一部分是百分比，第二部分是时间。
133.  
134. --quiet
135. -q，不把信息标准输出。

3.安装

1. 下载最新的pt-online-schema-change
2. [root@18c data]# wget https://www.percona.com/downloads/percona-toolkit/3.0.12/binary/redhat/6/x86_64/percona-toolkit-3.0.12-1.el6.x86_64.rpm
3. 安装
4. [root@18c data]# rpm -ivh percona-toolkit-3.0.12-1.el6.x86_64.rpm
5. warning: percona-toolkit-3.0.12-1.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
6. error: Failed dependencies:
7. perl(DBI) >= 1.13 is needed by percona-toolkit-3.0.12-1.el6.x86_64
8. perl(DBD::mysql) >= 1.0 is needed by percona-toolkit-3.0.12-1.el6.x86_64
9. perl(Time::HiRes) is needed by percona-toolkit-3.0.12-1.el6.x86_64
10. perl(IO::Socket::SSL) is needed by percona-toolkit-3.0.12-1.el6.x86_64
11. perl(Term::ReadKey) is needed by percona-toolkit-3.0.12-1.el6.x86_64
12.  
13. 安装相关的依赖包：
14. [root@18c data]# yum install perl-TermReadKey.x86_64
15. [root@18c data]# yum install perl-DBI
16. [root@18c data]# yum install perl-DBD-MySQL
17. [root@18c data]# yum install perl-Time-HiRes
18. [root@18c data]# yum install perl-IO-Socket-SSL
19. 然后再次安装
20. [root@18c data]# rpm -ivh percona-toolkit-3.0.12-1.el6.x86_64.rpm
21. warning: percona-toolkit-3.0.12-1.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
22. Preparing... ########################################### [100%]
23. 1:percona-toolkit ########################################### [100%]
24.  

至此软件已经安装好，下面就通过测试来了解如何使用该工具

创建测试数据

1. mysql> show databases;
2. +--------------------+
3. | Database |
4. +--------------------+
5. | information_schema |
6. | mysql |
7. | performance_schema |
8. | sys |
9. +--------------------+
10. 4 rows in set (0.00 sec)
11.  
12. mysql> create database test;
13. Query OK, 1 row affected (0.00 sec)
14.  
15. mysql> use test;
16. Database changed
17. mysql> CREATE TABLE users (id int(11) NOT NULL AUTO_INCREMENT COMMENT ‘用户主键‘,name varchar(20) DEFAULT ‘‘ COMMENT ‘用户名‘,PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=‘用户表‘;
18. Query OK, 0 rows affected (0.04 sec)
19. mysql> insert into users(name) values(‘test1‘),(‘test2‘),(‘test3‘);
20. Query OK, 3 rows affected (0.01 sec)
21. Records: 3 Duplicates: 0 Warnings: 0
22. mysql> show tables;
23. +----------------+
24. | Tables_in_test |
25. +----------------+
26. | users |
27. +----------------+
28. 1 row in set (0.00 sec)
29.  
30. mysql> select count(*) from users;
31. +----------+
32. | count(*) |
33. +----------+
34. | 3 |
35. +----------+
36. 1 row in set (0.00 sec)

在正式使用之前，再次了解一下常用的几个参数：

1. --nocheck-replication-filters ：不检查复制过滤器，建议启用。
2. --no-check-binlog-format : 不检查复制的binlog模式,要是binlog模式是ROW，则会报错。
3. --replicate-check-only :只显示不同步的信息。
4. --replicate= ：把checksum的信息写入到指定表中，建议直接写到被检查的数据库当中。
5. --chunk-size=50 Number of rows to select for each chunk copied. Allowable suffixes are k, M, G.默认1000
6. --critical-load="Threads_running=600" 表示线程到600自动退出,默认值50
7. --max-load="Threads_running=100" 表示线程到100暂停pt操作，默认25
8. --progress=time,2 两秒打印一次日志输出

测试各种不同的场景

1.整理表碎片

1.  
2. sql语句：alter table table_name engine=innodb
3. pt执行语句,以下2种都可以
4. (1)pt-online-schema-change --alter="engine innodb;" --execute D=test,t=users --no-check-replication-filters --max-load="Threads_running=100" --critical-load="Threads_running=120" --socket=/tmp/mysql.sock --user=root --password=123 --charset=utf8 --progress=time,2 --chunk-size=100
5. (2)pt-online-schema-change --alter="engine innodb;" --execute D=test,t=users,u=root,p=123,S=/tmp/mysql.sock --no-check-replication-filters --max-load="Threads_running=100" --critical-load="Threads_running=120" --charset=utf8 --progress=time,2 --chunk-size=100
6. 具体执行过程：
7. [root@18c ~]# pt-online-schema-change --alter="engine innodb;" --execute D=test,t=users,u=root,p=123,S=/tmp/mysql.sock --no-check-replication-filters --max-load="Threads_running=100" --critical-load="Threads_running=120" --charset=utf8 --progress=time,2 --chunk-size=100
8. No slaves found. See --recursion-method if host 18c has slaves.
9. Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
10. Operation, tries, wait:
11. analyze_table, 10, 1
12. copy_rows, 10, 0.25
13. create_triggers, 10, 1
14. drop_triggers, 10, 1
15. swap_tables, 10, 1
16. update_foreign_keys, 10, 1
17. Altering `test`.`users`...
18. Creating new table...
19. Created new table test._users_new OK.
20. Altering new table...
21. Altered `test`.`_users_new` OK.
22. 2018-12-06T13:28:05 Creating triggers...
23. 2018-12-06T13:28:05 Created triggers OK.
24. 2018-12-06T13:28:05 Copying approximately 3 rows...
25. 2018-12-06T13:28:05 Copied rows OK.
26. 2018-12-06T13:28:05 Analyzing new table...
27. 2018-12-06T13:28:05 Swapping tables...
28. 2018-12-06T13:28:05 Swapped original and new tables OK.
29. 2018-12-06T13:28:05 Dropping old table...
30. 2018-12-06T13:28:05 Dropped old table `test`.`_users_old` OK.
31. 2018-12-06T13:28:05 Dropping triggers...
32. 2018-12-06T13:28:05 Dropped triggers OK.
33. Successfully altered `test`.`users`.

2.添加字段：

1. (1)添加一个字段
2. alter table users add age varchar(10) NOT NUll DEFAULT ‘‘ COMMENT ‘性别‘；
3. pt语句： pt-online-schema-change --alter="add age varchar(10) NOT NUll DEFAULT ‘‘ COMMENT ‘性别‘ ;" --execute --print --max-lag=5 D=test,t=users,u=root,p=123,S=/tmp/mysql.sock --no-check-replication-filters --max-load="Threads_running=100" --critical-load="Threads_running=120" --charset=utf8 --chunk-size=100
4. 具体执行过程
5. [root@18c ~]# pt-online-schema-change --alter="add age varchar(10) NOT NUll DEFAULT ‘‘ COMMENT ‘性别‘ ;" --execute --print --max-lag=5 D=test,t=users,u=root,p=123,S=/tmp/mysql.sock --no-check-replication-filters --max-load="Threads_running=100" --critical-load="Threads_running=120" --charset=utf8 --chunk-size=100
6. No slaves found. See --recursion-method if host 18c has slaves.
7. Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
8. Operation, tries, wait:
9. analyze_table, 10, 1
10. copy_rows, 10, 0.25
11. create_triggers, 10, 1
12. drop_triggers, 10, 1
13. swap_tables, 10, 1
14. update_foreign_keys, 10, 1
15. Altering `test`.`users`...
16. Creating new table...
17. CREATE TABLE `test`.`_users_new` (
18. `id` int(11) NOT NULL AUTO_INCREMENT COMMENT ‘用户主键‘,
19. `name` varchar(20) DEFAULT ‘‘ COMMENT ‘用户名‘,
20. PRIMARY KEY (`id`)
21. ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT=‘用户表‘
22. Created new table test._users_new OK.
23. Altering new table...
24. ALTER TABLE `test`.`_users_new` add age varchar(10) NOT NUll DEFAULT ‘‘ COMMENT ‘æ?§å?«‘ ;
25. Altered `test`.`_users_new` OK.
26. 2018-12-06T13:36:55 Creating triggers...
27. 2018-12-06T13:36:55 Created triggers OK.
28. 2018-12-06T13:36:55 Copying approximately 3 rows...
29. INSERT LOW_PRIORITY IGNORE INTO `test`.`_users_new` (`id`, `name`) SELECT `id`, `name` FROM `test`.`users` LOCK IN SHARE MODE /*pt-online-schema-change 17461 copy table*/
30. 2018-12-06T13:36:55 Copied rows OK.
31. 2018-12-06T13:36:55 Analyzing new table...
32. 2018-12-06T13:36:55 Swapping tables...
33. RENAME TABLE `test`.`users` TO `test`.`_users_old`, `test`.`_users_new` TO `test`.`users`
34. 2018-12-06T13:36:55 Swapped original and new tables OK.
35. 2018-12-06T13:36:55 Dropping old table...
36. DROP TABLE IF EXISTS `test`.`_users_old`
37. 2018-12-06T13:36:55 Dropped old table `test`.`_users_old` OK.
38. 2018-12-06T13:36:55 Dropping triggers...
39. DROP TRIGGER IF EXISTS `test`.`pt_osc_test_users_del`
40. DROP TRIGGER IF EXISTS `test`.`pt_osc_test_users_upd`
41. DROP TRIGGER IF EXISTS `test`.`pt_osc_test_users_ins`
42. 2018-12-06T13:36:55 Dropped triggers OK.
43. Successfully altered `test`.`users`.
44. (2)添加2个或多个字段
45. sql语句：alter table users add column address varchar(100) default ‘‘ comment=‘地址‘ after age,add column telephone int default 0 comment=‘手机号码‘;
46. pt语句：pt-online-schema-change --alter="add column address varchar(100) default ‘‘ comment ‘地址‘ after age,add column telephone int default 0 comment ‘手机号码‘ after address ;" --execute --print --max-lag=5 D=test,t=users,u=root,p=123,S=/tmp/mysql.sock --no-check-replication-filters --max-load="Threads_running=100" --critical-load="Threads_running=120" --charset=utf8 --chunk-size=100
47. 具体执行过程：
48. [root@18c ~]# pt-online-schema-change --alter="add column address varchar(100) default ‘‘ comment ‘地址‘ after age,add column telephone int default 0 comment ‘手机号码‘ after address ;" --execute --print --max-lag=5 D=test,t=users,u=root,p=123,S=/tmp/mysql.sock --no-check-replication-filters --max-load="Threads_running=100" --critical-load="Threads_running=120" --charset=utf8 --chunk-size=100
49. No slaves found. See --recursion-method if host 18c has slaves.
50. Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
51. Operation, tries, wait:
52. analyze_table, 10, 1
53. copy_rows, 10, 0.25
54. create_triggers, 10, 1
55. drop_triggers, 10, 1
56. swap_tables, 10, 1
57. update_foreign_keys, 10, 1
58. Altering `test`.`users`...
59. Creating new table...
60. CREATE TABLE `test`.`_users_new` (
61. `id` int(11) NOT NULL AUTO_INCREMENT COMMENT ‘用户主键‘,
62. `name` varchar(20) DEFAULT ‘‘ COMMENT ‘用户名‘,
63. `age` varchar(10) NOT NULL DEFAULT ‘‘ COMMENT ‘性别‘,
64. PRIMARY KEY (`id`)
65. ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT=‘用户表‘
66. Created new table test._users_new OK.
67. Altering new table...
68. ALTER TABLE `test`.`_users_new` add column address varchar(100) default ‘‘ comment ‘å?°å??‘ after age,add column telephone int default 0 comment ‘æ??æ?ºå?·ç ?‘ after address ;
69. Altered `test`.`_users_new` OK.
70. 2018-12-06T14:02:40 Creating triggers...
71. 2018-12-06T14:02:40 Created triggers OK.
72. 2018-12-06T14:02:40 Copying approximately 3 rows...
73. INSERT LOW_PRIORITY IGNORE INTO `test`.`_users_new` (`id`, `name`, `age`) SELECT `id`, `name`, `age` FROM `test`.`users` LOCK IN SHARE MODE /*pt-online-schema-change 17584 copy table*/
74. 2018-12-06T14:02:40 Copied rows OK.
75. 2018-12-06T14:02:40 Analyzing new table...
76. 2018-12-06T14:02:40 Swapping tables...
77. RENAME TABLE `test`.`users` TO `test`.`_users_old`, `test`.`_users_new` TO `test`.`users`
78. 2018-12-06T14:02:40 Swapped original and new tables OK.
79. 2018-12-06T14:02:40 Dropping old table...
80. DROP TABLE IF EXISTS `test`.`_users_old`
81. 2018-12-06T14:02:40 Dropped old table `test`.`_users_old` OK.
82. 2018-12-06T14:02:40 Dropping triggers...
83. DROP TRIGGER IF EXISTS `test`.`pt_osc_test_users_del`
84. DROP TRIGGER IF EXISTS `test`.`pt_osc_test_users_upd`
85. DROP TRIGGER IF EXISTS `test`.`pt_osc_test_users_ins`
86. 2018-12-06T14:02:40 Dropped triggers OK.
87. Successfully altered `test`.`users`.

3.修改字段

1. sql语句：alter table users modify column address varchar(200) default ‘‘ comment ‘家庭住址‘；
2. pt语句: pt-online-schema-change --alter="modify column address varchar(200) default ‘‘ comment ‘家庭住址‘;" --execute --print --max-lag=5 D=test,t=users,u=root,p=123,S=/tmp/mysql.sock --no-check-replication-filters --max-load="Threads_running=100" --critical-load="Threads_running=120" --charset=utf8 --chunk-size=100
3. 具体执行过程：
4. [root@18c ~]# pt-online-schema-change --alter="modify column address varchar(200) default ‘‘ comment ‘家庭住址‘;" --execute --print --max-lag=5 D=test,t=users,u=root,p=123,S=/tmp/mysql.sock --no-check-replication-filters --max-load="Threads_running=100" --critical-load="Threads_running=120" --charset=utf8 --chunk-size=100
5. No slaves found. See --recursion-method if host 18c has slaves.
6. Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
7. Operation, tries, wait:
8. analyze_table, 10, 1
9. copy_rows, 10, 0.25
10. create_triggers, 10, 1
11. drop_triggers, 10, 1
12. swap_tables, 10, 1
13. update_foreign_keys, 10, 1
14. Altering `test`.`users`...
15. Creating new table...
16. CREATE TABLE `test`.`_users_new` (
17. `id` int(11) NOT NULL AUTO_INCREMENT COMMENT ‘用户主键‘,
18. `name` varchar(20) DEFAULT ‘‘ COMMENT ‘用户名‘,
19. `age` varchar(10) NOT NULL DEFAULT ‘‘ COMMENT ‘性别‘,
20. `address` varchar(100) DEFAULT ‘‘ COMMENT ‘地址‘,
21. `telephone` int(11) DEFAULT ‘0‘ COMMENT ‘手机号码‘,
22. PRIMARY KEY (`id`)
23. ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT=‘用户表‘
24. Created new table test._users_new OK.
25. Altering new table...
26. ALTER TABLE `test`.`_users_new` modify column address varchar(200) default ‘‘ comment ‘家庭ä½?å??‘;
27. Altered `test`.`_users_new` OK.
28. 2018-12-06T14:08:57 Creating triggers...
29. 2018-12-06T14:08:57 Created triggers OK.
30. 2018-12-06T14:08:57 Copying approximately 3 rows...
31. INSERT LOW_PRIORITY IGNORE INTO `test`.`_users_new` (`id`, `name`, `age`, `address`, `telephone`) SELECT `id`, `name`, `age`, `address`, `telephone` FROM `test`.`users` LOCK IN SHARE MODE /*pt-online-schema-change 17607 copy table*/
32. 2018-12-06T14:08:57 Copied rows OK.
33. 2018-12-06T14:08:57 Analyzing new table...
34. 2018-12-06T14:08:57 Swapping tables...
35. RENAME TABLE `test`.`users` TO `test`.`_users_old`, `test`.`_users_new` TO `test`.`users`
36. 2018-12-06T14:08:57 Swapped original and new tables OK.
37. 2018-12-06T14:08:57 Dropping old table...
38. DROP TABLE IF EXISTS `test`.`_users_old`
39. 2018-12-06T14:08:57 Dropped old table `test`.`_users_old` OK.
40. 2018-12-06T14:08:57 Dropping triggers...
41. DROP TRIGGER IF EXISTS `test`.`pt_osc_test_users_del`
42. DROP TRIGGER IF EXISTS `test`.`pt_osc_test_users_upd`
43. DROP TRIGGER IF EXISTS `test`.`pt_osc_test_users_ins`
44. 2018-12-06T14:08:57 Dropped triggers OK.
45. Successfully altered `test`.`users`.

4.添加索引

1. sql语句:alter table users add index index_age(age);
2. pt语句:pt-online-schema-change --alter="add index index_age(age);" --execute --print --max-lag=5 D=test,t=users,u=root,p=123,S=/tmp/mysql.sock --no-check-replication-filters --max-load="Threads_running=100" --critical-load="Threads_running=120" --charset=utf8 --chunk-size=100
3. 具体执行过程:
4. [root@18c ~]# pt-online-schema-change --alter="add index index_age(age);" --execute --print --max-lag=5 D=test,t=users,u=root,p=123,S=/tmp/mysql.sock --no-check-replication-filters --max-load="Threads_running=100" --critical-load="Threads_running=120" --charset=utf8 --chunk-size=100
5. No slaves found. See --recursion-method if host 18c has slaves.
6. Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
7. Operation, tries, wait:
8. analyze_table, 10, 1
9. copy_rows, 10, 0.25
10. create_triggers, 10, 1
11. drop_triggers, 10, 1
12. swap_tables, 10, 1
13. update_foreign_keys, 10, 1
14. Altering `test`.`users`...
15. Creating new table...
16. CREATE TABLE `test`.`_users_new` (
17. `id` int(11) NOT NULL AUTO_INCREMENT COMMENT ‘用户主键‘,
18. `name` varchar(20) DEFAULT ‘‘ COMMENT ‘用户名‘,
19. `age` varchar(10) NOT NULL DEFAULT ‘‘ COMMENT ‘性别‘,
20. `address` varchar(200) DEFAULT ‘‘ COMMENT ‘家庭住址‘,
21. `telephone` int(11) DEFAULT ‘0‘ COMMENT ‘手机号码‘,
22. PRIMARY KEY (`id`)
23. ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT=‘用户表‘
24. Created new table test._users_new OK.
25. Altering new table...
26. ALTER TABLE `test`.`_users_new` add index index_age(age);
27. Altered `test`.`_users_new` OK.
28. 2018-12-06T14:11:39 Creating triggers...
29. 2018-12-06T14:11:39 Created triggers OK.
30. 2018-12-06T14:11:39 Copying approximately 3 rows...
31. INSERT LOW_PRIORITY IGNORE INTO `test`.`_users_new` (`id`, `name`, `age`, `address`, `telephone`) SELECT `id`, `name`, `age`, `address`, `telephone` FROM `test`.`users` LOCK IN SHARE MODE /*pt-online-schema-change 17622 copy table*/
32. 2018-12-06T14:11:39 Copied rows OK.
33. 2018-12-06T14:11:39 Analyzing new table...
34. 2018-12-06T14:11:39 Swapping tables...
35. RENAME TABLE `test`.`users` TO `test`.`_users_old`, `test`.`_users_new` TO `test`.`users`
36. 2018-12-06T14:11:39 Swapped original and new tables OK.
37. 2018-12-06T14:11:39 Dropping old table...
38. DROP TABLE IF EXISTS `test`.`_users_old`
39. 2018-12-06T14:11:39 Dropped old table `test`.`_users_old` OK.
40. 2018-12-06T14:11:39 Dropping triggers...
41. DROP TRIGGER IF EXISTS `test`.`pt_osc_test_users_del`
42. DROP TRIGGER IF EXISTS `test`.`pt_osc_test_users_upd`
43. DROP TRIGGER IF EXISTS `test`.`pt_osc_test_users_ins`
44. 2018-12-06T14:11:39 Dropped triggers OK.
45. Successfully altered `test`.`users`.

5.删除索引

1. sql语句:alter table users drop index index_age;
2. pt语句: pt-online-schema-change --alter="drop index index_age;" --execute --print --max-lag=5 D=test,t=users,u=root,p=123,S=/tmp/mysql.sock --no-check-replication-filters --max-load="Threads_running=100" --critical-load="Threads_running=120" --charset=utf8 --chunk-size=100
3. 具体执行过程：
4. [root@18c ~]# pt-online-schema-change --alter="drop index index_age;" --execute --print --max-lag=5 D=test,t=users,u=root,p=123,S=/tmp/mysql.sock --no-check-replication-filters --max-load="Threads_running=100" --critical-load="Threads_running=120" --charset=utf8 --chunk-size=100
5. No slaves found. See --recursion-method if host 18c has slaves.
6. Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
7. Operation, tries, wait:
8. analyze_table, 10, 1
9. copy_rows, 10, 0.25
10. create_triggers, 10, 1
11. drop_triggers, 10, 1
12. swap_tables, 10, 1
13. update_foreign_keys, 10, 1
14. Altering `test`.`users`...
15. Creating new table...
16. CREATE TABLE `test`.`_users_new` (
17. `id` int(11) NOT NULL AUTO_INCREMENT COMMENT ‘用户主键‘,
18. `name` varchar(20) DEFAULT ‘‘ COMMENT ‘用户名‘,
19. `age` varchar(10) NOT NULL DEFAULT ‘‘ COMMENT ‘性别‘,
20. `address` varchar(200) DEFAULT ‘‘ COMMENT ‘家庭住址‘,
21. `telephone` int(11) DEFAULT ‘0‘ COMMENT ‘手机号码‘,
22. PRIMARY KEY (`id`),
23. KEY `index_age` (`age`)
24. ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT=‘用户表‘
25. Created new table test._users_new OK.
26. Altering new table...
27. ALTER TABLE `test`.`_users_new` drop index index_age;
28. Altered `test`.`_users_new` OK.
29. 2018-12-06T14:13:25 Creating triggers...
30. 2018-12-06T14:13:25 Created triggers OK.
31. 2018-12-06T14:13:25 Copying approximately 3 rows...
32. INSERT LOW_PRIORITY IGNORE INTO `test`.`_users_new` (`id`, `name`, `age`, `address`, `telephone`) SELECT `id`, `name`, `age`, `address`, `telephone` FROM `test`.`users` LOCK IN SHARE MODE /*pt-online-schema-change 17629 copy table*/
33. 2018-12-06T14:13:25 Copied rows OK.
34. 2018-12-06T14:13:25 Analyzing new table...
35. 2018-12-06T14:13:25 Swapping tables...
36. RENAME TABLE `test`.`users` TO `test`.`_users_old`, `test`.`_users_new` TO `test`.`users`
37. 2018-12-06T14:13:25 Swapped original and new tables OK.
38. 2018-12-06T14:13:25 Dropping old table...
39. DROP TABLE IF EXISTS `test`.`_users_old`
40. 2018-12-06T14:13:25 Dropped old table `test`.`_users_old` OK.
41. 2018-12-06T14:13:25 Dropping triggers...
42. DROP TRIGGER IF EXISTS `test`.`pt_osc_test_users_del`
43. DROP TRIGGER IF EXISTS `test`.`pt_osc_test_users_upd`
44. DROP TRIGGER IF EXISTS `test`.`pt_osc_test_users_ins`
45. 2018-12-06T14:13:25 Dropped triggers OK.
46. Successfully altered `test`.`users`.

6.修改自增ID

1. sql语句:alter table users modify column id bigint not null auto_increment;
2. pt语句: pt-online-schema-change --alter="modify column id bigint not null auto_increment;" --execute --print --max-lag=5 D=test,t=users,u=root,p=123,S=/tmp/mysql.sock --no-check-replication-filters --max-load="Threads_running=100" --critical-load="Threads_running=120" --charset=utf8 --chunk-size=100
3.  
4. 具体执行过程:
5. [root@18c ~]# pt-online-schema-change --alter="modify column id bigint not null auto_increment;" --execute --print --max-lag=5 D=test,t=users,u=root,p=123,S=/tmp/mysql.sock --no-check-replication-filters --max-load="Threads_running=100" --critical-load="Threads_running=120" --charset=utf8 --chunk-size=100
6. No slaves found. See --recursion-method if host 18c has slaves.
7. Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
8. Operation, tries, wait:
9. analyze_table, 10, 1
10. copy_rows, 10, 0.25
11. create_triggers, 10, 1
12. drop_triggers, 10, 1
13. swap_tables, 10, 1
14. update_foreign_keys, 10, 1
15. Altering `test`.`users`...
16. Creating new table...
17. CREATE TABLE `test`.`_users_new` (
18. `id` int(11) NOT NULL AUTO_INCREMENT COMMENT ‘用户主键‘,
19. `name` varchar(20) DEFAULT ‘‘ COMMENT ‘用户名‘,
20. `age` varchar(10) NOT NULL DEFAULT ‘‘ COMMENT ‘性别‘,
21. `address` varchar(200) DEFAULT ‘‘ COMMENT ‘家庭住址‘,
22. `telephone` int(11) DEFAULT ‘0‘ COMMENT ‘手机号码‘,
23. PRIMARY KEY (`id`)
24. ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT=‘用户表‘
25. Created new table test._users_new OK.
26. Altering new table...
27. ALTER TABLE `test`.`_users_new` modify column id bigint not null auto_increment;
28. Altered `test`.`_users_new` OK.
29. 2018-12-06T14:17:35 Creating triggers...
30. 2018-12-06T14:17:35 Created triggers OK.
31. 2018-12-06T14:17:35 Copying approximately 3 rows...
32. INSERT LOW_PRIORITY IGNORE INTO `test`.`_users_new` (`id`, `name`, `age`, `address`, `telephone`) SELECT `id`, `name`, `age`, `address`, `telephone` FROM `test`.`users` LOCK IN SHARE MODE /*pt-online-schema-change 17643 copy table*/
33. 2018-12-06T14:17:35 Copied rows OK.
34. 2018-12-06T14:17:35 Analyzing new table...
35. 2018-12-06T14:17:35 Swapping tables...
36. RENAME TABLE `test`.`users` TO `test`.`_users_old`, `test`.`_users_new` TO `test`.`users`
37. 2018-12-06T14:17:35 Swapped original and new tables OK.
38. 2018-12-06T14:17:35 Dropping old table...
39. DROP TABLE IF EXISTS `test`.`_users_old`
40. 2018-12-06T14:17:35 Dropped old table `test`.`_users_old` OK.
41. 2018-12-06T14:17:35 Dropping triggers...
42. DROP TRIGGER IF EXISTS `test`.`pt_osc_test_users_del`
43. DROP TRIGGER IF EXISTS `test`.`pt_osc_test_users_upd`
44. DROP TRIGGER IF EXISTS `test`.`pt_osc_test_users_ins`
45. 2018-12-06T14:17:35 Dropped triggers OK.
46. Successfully altered `test`.`users`.

7.修改列名，并添加索引

1. sql语句:alter table test change column age ages varchar(5) default ‘‘ comment ‘性别新‘ after address,add index index_ages(ages);
2. pt语句:pt-online-schema-change --alter="change column age ages varchar(5) after address,add index index_ages(ages);" --execute --print --max-lag=5 D=test,t=users,u=root,p=123,S=/tmp/mysql.sock --no-check-replication-filters --max-load="Threads_running=100" --critical-load="Threads_running=120" --charset=utf8 --chunk-size=100 --no-check-alter
3.  
4. 具体执行过程:
5. [root@18c ~]# pt-online-schema-change --alter="change column age ages varchar(5) after address,add index index_ages(ages);" --execute --print --max-lag=5 D=test,t=users,u=root,p=123,S=/tmp/mysql.sock --no-check-replication-filters --max-load="Threads_running=100" --critical-load="Threads_running=120" --charset=utf8 --chunk-size=100 --no-check-alter
6. No slaves found. See --recursion-method if host 18c has slaves.
7. Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
8. Operation, tries, wait:
9. analyze_table, 10, 1
10. copy_rows, 10, 0.25
11. create_triggers, 10, 1
12. drop_triggers, 10, 1
13. swap_tables, 10, 1
14. update_foreign_keys, 10, 1
15. Altering `test`.`users`...
16. Renaming columns:
17. age to ages
18. Creating new table...
19. CREATE TABLE `test`.`_users_new` (
20. `id` bigint(20) NOT NULL AUTO_INCREMENT,
21. `name` varchar(20) DEFAULT ‘‘ COMMENT ‘用户名‘,
22. `age` varchar(10) NOT NULL DEFAULT ‘‘ COMMENT ‘性别‘,
23. `address` varchar(200) DEFAULT ‘‘ COMMENT ‘家庭住址‘,
24. `telephone` int(11) DEFAULT ‘0‘ COMMENT ‘手机号码‘,
25. PRIMARY KEY (`id`)
26. ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT=‘用户表‘
27. Created new table test._users_new OK.
28. Altering new table...
29. ALTER TABLE `test`.`_users_new` change column age ages varchar(5) after address,add index index_ages(ages);
30. Altered `test`.`_users_new` OK.
31. 2018-12-06T14:55:27 Creating triggers...
32. 2018-12-06T14:55:27 Created triggers OK.
33. 2018-12-06T14:55:27 Copying approximately 3 rows...
34. INSERT LOW_PRIORITY IGNORE INTO `test`.`_users_new` (`id`, `name`, `ages`, `address`, `telephone`) SELECT `id`, `name`, `age`, `address`, `telephone` FROM `test`.`users` LOCK IN SHARE MODE /*pt-online-schema-change 17822 copy table*/
35. 2018-12-06T14:55:27 Copied rows OK.
36. 2018-12-06T14:55:27 Analyzing new table...
37. 2018-12-06T14:55:27 Swapping tables...
38. RENAME TABLE `test`.`users` TO `test`.`_users_old`, `test`.`_users_new` TO `test`.`users`
39. 2018-12-06T14:55:27 Swapped original and new tables OK.
40. 2018-12-06T14:55:27 Dropping old table...
41. DROP TABLE IF EXISTS `test`.`_users_old`
42. 2018-12-06T14:55:27 Dropped old table `test`.`_users_old` OK.
43. 2018-12-06T14:55:27 Dropping triggers...
44. DROP TRIGGER IF EXISTS `test`.`pt_osc_test_users_del`
45. DROP TRIGGER IF EXISTS `test`.`pt_osc_test_users_upd`
46. DROP TRIGGER IF EXISTS `test`.`pt_osc_test_users_ins`
47. 2018-12-06T14:55:27 Dropped triggers OK.
48. Successfully altered `test`.`users`.

上面做了这么多的操作，现在来看看测试表已经变成啥模样了

1. mysql> show create table users;
2. +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3. | Table | Create Table |
4. +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
5. | users | CREATE TABLE `users` (
6. `id` bigint(20) NOT NULL AUTO_INCREMENT,
7. `name` varchar(20) DEFAULT ‘‘ COMMENT ‘用户名‘,
8. `address` varchar(200) DEFAULT ‘‘ COMMENT ‘家庭住址‘,
9. `ages` varchar(5) DEFAULT NULL,
10. `telephone` int(11) DEFAULT ‘0‘ COMMENT ‘手机号码‘,
11. PRIMARY KEY (`id`),
12. KEY `index_ages` (`ages`)
13. ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT=‘用户表‘ |
14. +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
15. 1 row in set (0.00 sec)

转自

(3条消息)mysql:pt-online-schema-change 在线修改表_雨花石-CSDN博客_pt-online-schema-change https://blog.csdn.net/shiyu1157758655/article/details/84854513

参考

(3条消息)案例 - optimize table 的一些坑_weixin_34248023的博客-CSDN博客_optimize table https://blog.csdn.net/weixin_34248023/article/details/91617531

(3条消息)[mysql]清除单表大量数据方法（需保留部分数据）_yyongsheng的博客-CSDN博客_mysql truncate清除部分数据 https://blog.csdn.net/yyongsheng/article/details/82938683

MySQL大表删除工具pt-osc? - 云+社区 - 腾讯云 https://cloud.tencent.com/developer/article/1533722

PT-OSC在线DDL变更工具使用攻略 - MySQL数据库技术栈的个人空间 - OSCHINA https://my.oschina.net/u/3678773/blog/4467882

(3条消息)mysql 案例~关于pt-osc工具的用途_weixin_34212189的博客-CSDN博客_mysql ptosc 工具 https://blog.csdn.net/weixin_34212189/article/details/93262658

pt-osc原理 - 茁壮的小草 - 博客园 https://www.cnblogs.com/mysql-dba/p/9901632.html

mysql:pt-online-schema-change 在线修改表、删除表数据【转】

原文：https://www.cnblogs.com/paul8339/p/13489994.html