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)

添加字段


如果没有主键将无法添加字段,否则会报错,因为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.

Write a Reply or Comment

电子邮件地址不会被公开。 必填项已用*标注