Percona-Toolkit系列之pt-online-schema-change MySQL DDL利器

前言

在平时MySQL的运维过程中，经常会遇到表结构的变更。在表比较小的时候，直接进行变更，时间较短，但是当表非常大的时候，这么做会导致应用卡死，服务不可用。
目前InnoDB引擎是通过以下步骤来进行DDL的：
1 利用DDL之后的语句创建一张临时表
2 在原表上加write lock，阻塞所有DML操作
3 将原表数据复制到临时表
4 将临时表和原表重命名，然后drop原始表
5 释放 write lock。

在这个DDL过程中，针对大表进行的write lock将持续非常长的时间，我们可以用为此 perconal 推出一个工具 pt-online-schema-change，在进行DDL的时候不堵塞原表的读写。

工作原理：

如果表有外键，除非使用 –alter-foreign-keys-method 指定特定的值，否则工具不予执行。
1 创建一张和原表一样的空表结构。
2 执行空表的DDL
3 在原表上创建触发器，将对原表的修改操作记录下来。
4 复制数据到新的空表中，复制完成后，应用修改记录。
注意:如果表中已经定义了触发器这个工具就不能工作了。
5 复制完成后在重命名原表和新的表

使用介绍

创建测试表

create table test_osc (id int)

创建主键

shell > pt-online-schema-change --alter="add primary key (id)"  \
      h=localhost,u=root,p=root,D=test,t=test_osc --execute
No slaves found.  See --recursion-method if host rht6-mysql has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
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 test.test_osc...
Creating new table...
Created new table test._test_osc_new OK.
Altering new table...
Altered test._test_osc_new OK.
2016-11-10T19:34:26 Creating triggers...
2016-11-10T19:34:26 Created triggers OK.
2016-11-10T19:34:26 Copying approximately 1 rows...
2016-11-10T19:34:26 Copied rows OK.
2016-11-10T19:34:26 Analyzing new table...
2016-11-10T19:34:26 Swapping tables...
2016-11-10T19:34:26 Swapped original and new tables OK.
2016-11-10T19:34:26 Dropping old table...
2016-11-10T19:34:26 Dropped old table test._test_osc_old OK.
2016-11-10T19:34:26 Dropping triggers...
2016-11-10T19:34:26 Dropped triggers OK.
Successfully altered test.test_osc.
root@localhost [test] 07:34:38>>>show create table test_osc\G
*************************** 1. row ***************************
       Table: test_osc
Create Table: CREATE TABLE test_osc (
  id int(11) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

添加字段

The new table test._test_osc_new does not have a PRIMARY KEY
or a unique index which is required for the DELETE trigger.
shell > pt-online-schema-change --alter="add name varchar(5)"
h=localhost,u=root,p=root,D=test,t=test_osc --execute
No slaves found. See --recursion-method if host rht6-mysql has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
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 test.test_osc...
Creating new table...
Created new table test._test_osc_new OK.
Altering new table...
Altered test._test_osc_new OK.
2016-11-10T19:37:19 Creating triggers...
2016-11-10T19:37:19 Created triggers OK.
2016-11-10T19:37:19 Copying approximately 1 rows...
2016-11-10T19:37:19 Copied rows OK.
2016-11-10T19:37:19 Analyzing new table...
2016-11-10T19:37:19 Swapping tables...
2016-11-10T19:37:19 Swapped original and new tables OK.
2016-11-10T19:37:19 Dropping old table...
2016-11-10T19:37:19 Dropped old table test._test_osc_old OK.
2016-11-10T19:37:19 Dropping triggers...
2016-11-10T19:37:19 Dropped triggers OK.
Successfully altered test.test_osc
root@localhost [test] 07:35:54>>>show create table test_osc\G
*************************** 1. row ***************************
       Table: test_osc
Create Table: CREATE TABLE test_osc (
  id int(11) NOT NULL,
  name varchar(5) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

修改字段为非null

pt-online-schema-change --alter="modify name varchar(5) not null "
h=localhost,u=root,p=root,D=test,t=test_osc --execute
No slaves found. See --recursion-method if host rht6-mysql has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
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 test.test_osc...
Creating new table...
Created new table test._test_osc_new OK.
Altering new table...
Altered test._test_osc_new OK.
2016-11-10T19:40:16 Creating triggers...
2016-11-10T19:40:16 Created triggers OK.
2016-11-10T19:40:16 Copying approximately 1 rows...
2016-11-10T19:40:16 Dropping triggers...
2016-11-10T19:40:16 Dropped triggers OK.
2016-11-10T19:40:16 Dropping new table...
2016-11-10T19:40:16 Dropped new table OK.
test.test_osc was not altered.
2016-11-10T19:40:16 Error copying rows from test.test_osc to test._test_osc_new:
2016-11-10T19:40:16 Copying rows caused a MySQL error 1048:
Level: Warning
Code: 1048
Message: Column 'name' cannot be null
Query: INSERT LOW_PRIORITY IGNORE INTO test._test_osc_new (id, name)
SELECT id, name FROM test.test_osc LOCK IN SHARE MODE /*pt-online-schema-change 5033 copy table*/

添加非null字段

pt-online-schema-change --alter="add address varchar(20) not null default 'china'"

h=localhost,u=root,p=root,D=test,t=test_osc --execute
No slaves found. See --recursion-method if host rht6-mysql has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
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 test.test_osc...
Creating new table...
Created new table test._test_osc_new OK.
Altering new table...
Altered test._test_osc_new OK.
2016-11-10T19:43:37 Creating triggers...
2016-11-10T19:43:37 Created triggers OK.
2016-11-10T19:43:37 Copying approximately 1 rows...
2016-11-10T19:43:37 Copied rows OK.
2016-11-10T19:43:37 Analyzing new table...
2016-11-10T19:43:37 Swapping tables...
2016-11-10T19:43:37 Swapped original and new tables OK.
2016-11-10T19:43:37 Dropping old table...
2016-11-10T19:43:37 Dropped old table test._test_osc_old OK.
2016-11-10T19:43:37 Dropping triggers...
2016-11-10T19:43:37 Dropped triggers OK.
Successfully altered test.test_osc.