一.用法介绍
pt-online-schema-change [OPTIONS] DSN
options 可以自行查看 help，DNS 为你要操作的数据库和表。这里有两个参数需要介绍一下：
--dry-run
这个参数不建立触发器，不拷贝数据，也不会替换原表。只是创建和更改新表。
--execute
这个参数的作用和前面工作原理的介绍的一样，会建立触发器，来保证最新变更的数据会影响至新表。注意：如果不加这个参数，这个工具会在执行一些检查后退出。
二.pt-osc工具实战案例
基本信息：
(product)root@localhost [lots]> select @@version;
+---------------------+
| @@version |
+---------------------+
| 10.0.20-MariaDB-log |
+---------------------+
1 row in set (0.00 sec)
(product)root@localhost [lots]> select @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| ROW |
+-----------------+
1 row in set (0.00 sec)
(product)root@localhost [lots]> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set (0.00 sec)
pt-osc版本：percona-toolkit-2.2.10
场景一：对没有表的主键增加字段测试
找出mysql实例中没有主键的表：
(product)root@localhost [(none)]> Select t.table_schema,t.table_name,t.engine,t.table_rows
-> From information_schema.tables as t
-> Left join information_schema.table_constraints as tc
-> On tc.table_schema=t.table_schema
-> And tc.table_name=t.table_name
-> And tc.constraint_type='PRIMARY KEY'
-> Where t.table_type='BASE TABLE'
-> And tc.constraint_type is null
-> And t.table_schema not in ('mysql','performance_schema','information_schema');
+--------------+------------+--------+------------+
| table_schema | table_name | engine | table_rows |
+--------------+------------+--------+------------+
| test | test_load | InnoDB | 496602 |
+--------------+------------+--------+------------+
(product)root@localhost [(none)]> show create table test.test_load\G
*************************** 1. row ***************************
Table: test_load
Create Table: CREATE TABLE `test_load` (
`a` int(11) DEFAULT NULL,
`b` char(80) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.02 sec)
cd /apps/tool/percona-toolkit-2.2.10/bin
./pt-online-schema-change -h 10.24.65.31 -u root -p safe2016 --alter='add column c int' --execute D=test,t=test_load
[apps@mvxl0782 bin]$ ./pt-online-schema-change -u root -h 10.24.65.31 -p safe2016 --alter='add column c int' --execute D=test,t=test_load
Cannot connect to MySQL: DBI connect('test;host=10.24.65.31;mysql_read_default_group=client','root',...) failed: Access denied for user'root'@'10.24.65.31' (using
password: YES) at ./pt-online-schema-change line 2261
上面执行报错，提示没权限连接，增加权限如下：
grant all privileges on *.* to root@'10.24%' identified by "safe2016";
[apps@mvxl0782 bin]$ ./pt-online-schema-change --host=10.24.65.31 -u root --password=safe2016 --alter='add column c int' --execute D=test,t=test_load
Found 1 slaves:
mvxl0783
Will check slave lag on:
mvxl0783
Cannot chunk the original table `test`.`test_load`: There is no good index and the table is oversized. at ./pt-online-schema-change line 5442.
若表没有主键，ddl操作会报上面错。
场景二：在线增加字段操作
OS下表t_order_tracking所占空间为19386073088 byte(
-rw-r--r-- 1 apps apps 19386073088 May 20 11:08 t_order_tracking.ibd
(product)root@10.24.65.31 [lots]> select count(*) from t_order_tracking;
+----------+
| count(*) |
+----------+
| 57894815 |
+----------+
1 row in set (13.82 sec)
(product)root@10.24.65.31 [lots]> show table status like 't_order_tracking'\G
*************************** 1. row ***************************
Name: t_order_tracking
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 31077422
Avg_row_length: 217
Data_length: 6744440832
Max_data_length: 0
Index_length: 3564109824
Data_free: 5242880
Auto_increment: 60182992
Create_time: 2016-07-24 15:08:32
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
[apps@mvxl0782 bin]$ ./pt-online-schema-change --host=10.24.65.31 -u root -p safe2016 --alter='add column last_updated_date datetime default current_timestamp on
update current_timestamp' --print --execute D=lots,t=t_order_tracking
一个20G大表约36分钟完成增加字段操作。
场景三：添加索引
[apps@mvxl0782 bin]$ ./pt-online-schema-change --host=10.24.65.31 -u root -p safe2016 --alter='add key tot_ix1(BRANCH_CODE)' --print --execute
D=lots,t=t_order_tracking
一个20G大表约31分钟完成增加索引操作。
场景四：修改字段
[apps@mvxl0782 bin]$ time ./pt-online-schema-change --host=10.24.65.31 -u root -p safe2016 --alter='modify CUSTOMER_ORDER_NO varchar(60) NOT NULL' --execute
D=lots,t=t_order_tracking
一个20G大表约31分钟完成修改字段操作。