mysql pt online ddl_MySQL Online DDL 工具之Pt

古城微笑少年丶 2022-10-31 04:02 221阅读 0赞

MySQL DDL:

DDL是一个令所有MySQL DBA诟病的一个功能，因为在MySQL中在对表进行DDL时，会锁表，当表比较小比如小于1W行时，对前端影响较小，当时遇到千万级别的表，就会影响前端应用对表的写操作！

InnoDB引擎是通过以下步骤来进行DDL的：

1、按照原始表(original\_table)的表结构和DDL语句，新建一个不可见的临时表(tmp\_table)

2、在原表上加write lock，阻塞所有更新操作(insert、delete、update等)

3、执行insert into tmp\_table select \* from original\_table

4、rename original\_table和tmp\_table，最后drop original\_table

5、释放 write lock。

可以看见在InnoDB执行DDL的时候，原表是只能读不能写的。为此 Percona 推出一个工具 pt-online-schema-change，其特点是修改过程中不会造成读写阻塞。

工作原理：

如果表有外键，除非使用 –alter-foreign-keys-method 指定特定的值，否则工具不予执行。

1 创建一个和你要执行 alter 操作的表一样的空表结构。

2 执行表结构修改，然后从原表中的数据到copy到 表结构修改后的表，

3 在原表上创建触发器将 copy 数据的过程中，在原表的更新操作 更新到新表.

注意:如果表中已经定义了触发器这个工具就不能工作了。

4 copy 完成以后，用rename table 新表代替原表，默认删除原表。

用法介绍：

pt-online-schema-change \[OPTIONS\] DSN

options 可以自行查看 help，DNS 为你要操作的数据库和表。这里有两个参数需要介绍一下：

–dry-run

这个参数不建立触发器，不拷贝数据，也不会替换原表。只是创建和更改新表。

–execute

这个参数的作用和前面工作原理的介绍的一样，会建立触发器，来保证最新变更的数据会影响至新表。注意：如果不加这个参数，这个工具会在执行一些检查后退出。

依赖条件:

操作的表必须有主键否则 报如下错误:\[\[email protected\] ~\]$ pt-online-schema-change -uroot -proot -h10.10.10.188 --alter='add column vname varchar(20)' --execute D=test,t=michael

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 \`test\`.\`michael\`...

Creating new table...

Created new table test.\_michael\_new OK.

Altering new table...

Altered \`test\`.\`\_michael\_new\` OK.

Dropping new table...

Dropped new table OK.

\`test\`.\`michael\` was not altered.

The new table \`test\`.\`\_michael\_new\` does not have a PRIMARY KEY or a unique index which is required for the DELETE trigger.

字段vname没有添加成功！mysql> show create table michaelG

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

Table: michael

Create Table: CREATE TABLE \`michael\` (

\`id\` int,

\`region\_id\` bigint(20),

\`cityCode\` varchar(6),

\`address\` varchar(255)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

mysql>

添加主键：mysql> alter table michael modify id int unsigned primary key;

Query OK, 0 rows affected (0.06 sec)

Records: 0 Duplicates: 0 Warnings: 0

\[\[email protected\] ~\]$ pt-online-schema-change -uroot -proot -h10.10.10.188 --alter='add column vname varchar(20)' --execute D=test,t=michael

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 \`test\`.\`michael\`...

Creating new table...

Created new table test.\_michael\_new OK.

Altering new table...

Altered \`test\`.\`\_michael\_new\` OK.

Creating triggers...

Created triggers OK.

Copying approximately 2993 rows...

Copied rows OK.

Swapping tables...

Swapped original and new tables OK.

Dropping old table...

Dropped old table \`test\`.\`\_michael\_old\` OK.

Dropping triggers...

Dropped triggers OK.

Successfully altered \`test\`.\`michael\`.

\[\[email protected\] ~\]$

mysql> show create table michaelG

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

Table: michael

Create Table: CREATE TABLE \`michael\` (

\`id\` int,

\`region\_id\` bigint(20),

\`cityCode\` varchar(6),

\`address\` varchar(255),

\`vname\` varchar(20)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

添加多个字段：\[\[email protected\] ~\]$ pt-online-schema-change -uroot -proot -h10.10.10.188 --alter='add column vrname varchar(20)','add column aname varchar(20),add column bname varchar(30)' --execute D=test,t=guoqing

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 \`test\`.\`guoqing\`...

Creating new table...

Created new table test.\_guoqing\_new OK.

Altering new table...

Altered \`test\`.\`\_guoqing\_new\` OK.

Creating triggers...

Created triggers OK.

Copying approximately 2964 rows...

Copied rows OK.

Swapping tables...

Swapped original and new tables OK.

Dropping old table...

Dropped old table \`test\`.\`\_guoqing\_old\` OK.

Dropping triggers...

Dropped triggers OK.

Successfully altered \`test\`.\`guoqing\`.

\[\[email protected\] ~\]$

mysql> show create table michaelG

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

Table: michael

Create Table: CREATE TABLE \`michael\` (

\`id\` int,

\`region\_id\` bigint(20),

\`cityCode\` varchar(6),

\`address\` varchar(255),

\`vname\` varchar(20),

\`vrname\` varchar(20),

\`aname\` varchar(20),

\`bname\` varchar(20)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

删除字段：\[\[email protected\] ~\]$ pt-online-schema-change -uroot -proot -h10.10.10.188 --alter='drop column vrname','drop column aname,drop column bname' --execute D=test,t=guoqing

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 \`test\`.\`guoqing\`...

Creating new table...

Created new table test.\_guoqing\_new OK.

Altering new table...

Altered \`test\`.\`\_guoqing\_new\` OK.

Creating triggers...