pt-online-schema-change使用介绍

工作原理：

如果存在外键，根据alter-foreign-keys-method参数的值，检测外键相关的表，针对相应的设置进行处理。
创建一个新的表，表结构为修改后的数据表，用于从源数据表向新表中导入数据。
创建触发器，在复制数据开始之后，将对源数据表继续进行数据修改的操作记录下来，以便在数据复制结束后执行这些操作，保证数据不会丢失。
复制数据，从源数据表中复制数据到新表中。
修改外键相关的子表，根据修改后的数据，修改外键关联的子表。
更改源数据表为old表，把新表改为源表名，并将old表删除。
删除触发器。

注意：
对操作的表必须要有主键或者唯一键
增加的字段如果为NOT NULL，会报错，需要添加默认值才可以成功。

[root@managed bin]# ./pt-online-schema-change --alter="add column yy varchar(22)" -u root -p root -S /mysql/data/mysql.sock D=bhs,t=pp --execute
Found 1 slaves:
slv2
Will check slave lag on:
slv2
Operation, tries, wait:
copy_rows, 10, 0.25
create_triggers, 10, 1
drop_triggers, 10, 1
swap_tables, 10, 1
update_foreign_keys, 10, 1
Altering bhs.pp...
Creating new table...
Created new table bhs._pp_new OK.
Altering new table...
Altered bhs._pp_new OK.
2017-11-21T13:40:39 Creating triggers...
2017-11-21T13:40:39 Created triggers OK.
2017-11-21T13:40:39 Copying approximately 2 rows...
2017-11-21T13:40:39 Copied rows OK.
2017-11-21T13:40:39 Swapping tables...
2017-11-21T13:40:39 Swapped original and new tables OK.
2017-11-21T13:40:39 Dropping old table...
2017-11-21T13:40:39 Dropped old table bhs._pp_old OK.
2017-11-21T13:40:39 Dropping triggers...
2017-11-21T13:40:39 Dropped triggers OK.
Successfully altered bhs.pp.

[root@managed bin]# ./pt-online-schema-change --alter="add column y varchar(22) not null" -u root -p root -S /mysql/data/mysql.sock D=bhs,t=pp --execute
Found 1 slaves:
slv2
Will check slave lag on:
slv2
Operation, tries, wait:
copy_rows, 10, 0.25
create_triggers, 10, 1
drop_triggers, 10, 1
swap_tables, 10, 1
update_foreign_keys, 10, 1
Altering bhs.pp...
Creating new table...
Created new table bhs._pp_new OK.
Altering new table...
Altered bhs._pp_new OK.
2017-11-21T13:42:08 Creating triggers...
2017-11-21T13:42:09 Created triggers OK.
2017-11-21T13:42:09 Copying approximately 2 rows...
2017-11-21T13:42:09 Dropping triggers...
2017-11-21T13:42:09 Dropped triggers OK.
2017-11-21T13:42:09 Dropping new table...
2017-11-21T13:42:09 Dropped new table OK.
bhs.pp was not altered.
2017-11-21T13:42:09 Error copying rows from bhs.pp to bhs._pp_new: 2017-11-21T13:42:09 Copying rows caused a MySQL error 1364:

Level: Warning
 Code: 1364

Message: Field 'y' doesn't have a default value

Query: INSERT LOW_PRIORITY IGNORE INTO `bhs`.`_pp_new` (`id`, `name`, `yy`) SELECT `id`, `name`, `yy` FROM `bhs`.`pp` LOCK IN SHARE MODE /*pt-online-schema-change 3824 copy table*/

[root@managed bin]# ./pt-online-schema-change --alter="add column y varchar(22) not null default 0" -u root -p root -S /mysql/data/mysql.sock D=bhs,t=pp --execute
Found 1 slaves:
slv2
Will check slave lag on:
slv2
Operation, tries, wait:
copy_rows, 10, 0.25
create_triggers, 10, 1
drop_triggers, 10, 1
swap_tables, 10, 1
update_foreign_keys, 10, 1
Altering bhs.pp...
Creating new table...
Created new table bhs._pp_new OK.
Altering new table...
Altered bhs._pp_new OK.
2017-11-21T13:44:32 Creating triggers...
2017-11-21T13:44:32 Created triggers OK.
2017-11-21T13:44:32 Copying approximately 2 rows...
2017-11-21T13:44:32 Copied rows OK.
2017-11-21T13:44:32 Swapping tables...
2017-11-21T13:44:32 Swapped original and new tables OK.
2017-11-21T13:44:32 Dropping old table...
2017-11-21T13:44:32 Dropped old table bhs._pp_old OK.
2017-11-21T13:44:32 Dropping triggers...
2017-11-21T13:44:32 Dropped triggers OK.
Successfully altered bhs.pp.