前言
在平时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)
添加字段
如果没有主键将无法添加字段，否则会报错，因为delete触发器需要利用主键。
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字段
需要指定default值，否则将报错。
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.
近期评论