mysql online ddl和pt_mysql原生在线ddl和pt-osc原理解析

一、MySQL ddl

的问题现状在运维mysql数据库时，我们总会对数据表进行ddl

变更，修改添加字段或者索引，对于mysql 而已，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的时候，原表是只能读不能写的。

二、MySQL

5.6原生ddl原理和用法 从mysql

5.6开始支持在线ddl操作，其原理：

在执行创建或者删除操作同时，将DML操作日志写入到一个缓存中，待完成后再将重做应用到表上，以此达到数据的一致性。

这个缓存的大小由参数innodb_online_alter_log_max_size控制，默认大小为128MB,支持动态修改。

实现方式分两种：copy和inplace，copy方式会锁表，如修改字段和对主键操作是采用copy方式。

onlineDDL语法：

alter table

ALTER [COLUMN] col_name {SET DEFAULT literal |

DROP DEFAULT}

ADD [COLUMN]

col_name column_definition [FIRST|AFTER

col_name]

CHANGE [COLUMN] old_col_name new_col_name

column_definition [FIRST|AFTER col_name]

MODIFY [COLUMN] col_name

column_definition

[FIRST | AFTER col_name],

ALGORITHM [=] {DEFAULT|INPLACE|COPY} LOCK [=]

{DEFAULT|NONE|SHARED|EXCLUSIVE};

简单的说就是原来的语句上，加个ALGORITHM=xxx LOCK=XXXX

ALGORITHM指定了onlineDDL时候是使用COPY,还是INPLACE，

(1)COPY表示执行DDL的时候会创建临时表。

(2)INPLACE表示不需要创建临时表。(inplace英文单词是原地的意思)

(3)DEFAULT表示根据参数old_alter_table来判断是通过INPLACE还是COPY的算法，old_alter_table参数默认为OFF，表示采用INPLACE的方式。

LOCK部分为索引创建或删除时对表添加锁的情况，默认是default，可选择的如下：

(1)NONE，目标表不添加任何锁，可以进行读写操作，不阻塞任何操作。如果手工指定NONE，但是onlineDDL不支持SHARE模式，返回一个错误信息，告诉你用SHARE摸索。

(2)SHARE，对操作表加一个S锁。不阻塞读操作。写操作会阻塞，将会发生等待MDL锁，如果手工指定SHARE，但是onlineDDL不支持SHARE模式，将返回一个错误信息。

(3)EXCLUSIVE，执行索引创建或删除时，对目标表加上一个X锁。读写事务均不能进行。会阻塞所有的线程。这和COPY方式类似，但是不需要像COPY方式那样创建一张临时表。

(4)DEFAULT，该模式首先会判断当前操作是否可以使用NONE模式，若不能，则判断是否可以使用SHARE模式，最后判断是否可以使用EXCLUSIVE模式。也就是说DEFAULT会通过判断事务的最大

并发性来判断执行DDL的模式。

三、pt-osc工作原理解析 perconal 推出一个工具

pt-online-schema-change

，其特点是修改过程中不会造成读写阻塞。 对一个20G的大表用pt工具增加字段来解读工作原理，如下：

[apps@mvxl0782 bin]$ ./pt-online-schema-change --host=10.24.65.31

-u root -p safe2016 --alter='add column last_updated_date datetime

default current_timestamp on update current_timestamp' --print

--execute D=lots,t=t_o_tr

Found 1 slaves:

mvxl0783

Will check slave lag on:

mvxl0783

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 `lots`.`t_o_tr`...

Creating new table... ----------建立一个新的空表CREATE TABLE

`lots`.`_t_o_tr_new` (

`ID` int(11) NOT NULL AUTO_INCREMENT COMMENT

'ID',

`CUSTOMER_ORDER_NO` varchar(50) NOT NULL COMMENT

'?????',

`ORDER_NO` varchar(50) NOT NULL COMMENT

'?????',

`EX_CODE` varchar(20) DEFAULT NULL COMMENT

'????',

`EX_NAME` varchar(50) DEFAULT NULL COMMENT

'????',

`EX_NO` varchar(100) DEFAULT NULL COMMENT

'????',

-----------中间省略----------------------------------

) ENGINE=InnoDB AUTO_INCREMENT=60182992 DEFAULT CHARSET=utf8

COMMENT='?????'

Created new table lots._t_o_tr_new OK.

Waiting forever for new table `lots`.`_t_o_tr_new` to replicate to

mvxl0783...

Altering new table... --------给空表增加字段ALTER TABLE

`lots`.`_t_o_tr_new` add column last_updated_date datetime default

current_timestamp on update current_timestamp

Altered `lots`.`_t_o_tr_new` OK.

2016-07-24T21:37:33 Creating triggers... -----在原表上增加delete/update/insert的after

triggerCREATE

TRIGGER `pt_osc_lots_t_o_tr_del` AFTER DELETE ON `lots`.`t_o_tr`

FOR EACH ROW DELETE IGNORE FROM `lots`.`_t_o_tr_new` WHERE

`lots`.`_t_o_tr_new`.`id` <=>

OLD.`id`

CREATE TRIGGER `pt_osc_lots_t_o_tr_upd` AFTER UPDATE ON

`lots`.`t_o_tr` FOR EACH ROW REPLACE INTO

..................

CREATE TRIGGER `pt_osc_lots_t_o_tr_ins` AFTER INSERT ON

`lots`.`t_o_tr` FOR EACH ROW REPLACE INTO

..................

2016-07-24T21:37:33 Created triggers OK.

2016-07-24T21:37:33 Copying approximately 31077422

rows... --- copy数据到新表INSERT LOW_PRIORITY

IGNORE INTO `lots`.`_t_o_tr_new` (`id`, ...................)) LOCK

IN SHARE MODE

SELECT `id` FROM `lots`.`t_o_tr` FORCE INDEX(`PRIMARY`) WHERE

((`id` >= ?)) ORDER BY `id` LIMIT ?, 2

Copying `lots`.`t_o_tr`: 2%

17:48 remain

Copying `lots`.`t_o_tr`: 5%

18:22 remain

Copying `lots`.`t_o_tr`: 7%

19:08 remain

Copying `lots`.`t_o_tr`: 9%

19:39 remain

Copying `lots`.`t_o_tr`: 11% 19:46

remain

Copying `lots`.`t_o_tr`: 13% 19:11

remain

Copying `lots`.`t_o_tr`: 15% 18:32

remain

Copying `lots`.`t_o_tr`: 18% 17:47

remain

Copying `lots`.`t_o_tr`: 20% 17:35

remain

Copying `lots`.`t_o_tr`: 22% 17:08

remain

Copying `lots`.`t_o_tr`: 24% 16:41

remain

Copying `lots`.`t_o_tr`: 26% 16:23

remain

Copying `lots`.`t_o_tr`: 29% 15:48

remain

Copying `lots`.`t_o_tr`: 31% 15:13

remain

Copying `lots`.`t_o_tr`: 33% 14:40

remain

Copying `lots`.`t_o_tr`: 36% 14:03

remain

Copying `lots`.`t_o_tr`: 38% 13:31

remain

Copying `lots`.`t_o_tr`: 40% 13:02

remain

Copying `lots`.`t_o_tr`: 43% 12:33

remain

Copying `lots`.`t_o_tr`: 45% 12:04

remain

Copying `lots`.`t_o_tr`: 47% 11:31

remain

Copying `lots`.`t_o_tr`: 50% 10:57

remain

Copying `lots`.`t_o_tr`: 52% 10:26

remain

Copying `lots`.`t_o_tr`: 54% 09:53

remain

Copying `lots`.`t_o_tr`: 57% 09:24

remain

Copying `lots`.`t_o_tr`: 59% 08:52

remain

Copying `lots`.`t_o_tr`: 61% 08:24

remain

Copying `lots`.`t_o_tr`: 63% 07:57

remain

Copying `lots`.`t_o_tr`: 66% 07:25

remain

Copying `lots`.`t_o_tr`: 68% 06:51

remain

Copying `lots`.`t_o_tr`: 70% 06:22

remain

Copying `lots`.`t_o_tr`: 73% 05:50

remain

Copying `lots`.`t_o_tr`: 75% 05:13

remain

Copying `lots`.`t_o_tr`: 78% 04:44

remain

Copying `lots`.`t_o_tr`: 80% 04:16

remain

Copying `lots`.`t_o_tr`: 82% 03:45

remain

Copying `lots`.`t_o_tr`: 84% 03:16

remain

Copying `lots`.`t_o_tr`: 87% 02:46

remain

Copying `lots`.`t_o_tr`: 89% 02:17

remain

Copying `lots`.`t_o_tr`: 91% 01:47

remain

Copying `lots`.`t_o_tr`: 94% 01:14

remain

Copying `lots`.`t_o_tr`: 96% 00:40

remain

Copying `lots`.`t_o_tr`: 99% 00:03

remain

2016-07-24T22:12:45 Copied rows OK.

2016-07-24T22:12:45 Swapping tables... ---将原表改名，并将新表改成原表名RENAME TABLE `lots`.`t_o_tr` TO `lots`.`_t_o_tr_old`,

`lots`.`_t_o_tr_new` TO `lots`.`t_o_tr`

2016-07-24T22:12:46 Swapped original and new tables OK.

2016-07-24T22:12:46 Dropping old table... ---删除原表DROP TABLE IF EXISTS

`lots`.`_t_o_tr_old`

2016-07-24T22:12:47 Dropped old table `lots`.`_t_o_tr_old`

OK.

2016-07-24T22:12:47 Dropping

triggers... ---删除triggerDROP TRIGGER IF EXISTS

`lots`.`pt_osc_lots_t_o_tr_del`;

DROP TRIGGER IF EXISTS `lots`.`pt_osc_lots_t_o_tr_upd`;

DROP TRIGGER IF EXISTS `lots`.`pt_osc_lots_t_o_tr_ins`;

2016-07-24T22:12:47 Dropped triggers OK.

Successfully altered `lots`.`t_o_tr`.

从上面可看到pt-osc原理如下：

1.建立一个与需要操作的表相同表结构的空表

2.给空表执行表结构修改

3.在原表上增加delete/update/insert的after trigger

4.copy数据到新表

5.将原表改名，并将新表改成原表名

6.删除原表

7.删除trigger

pt-osc限制条件：

1.表要有主键，否则会报错;

2.表不能有trigger;

3.如果表有外键，除非使用 --alter-foreign-keys-method

指定特定的值，否则工具不予执行。

使用pt-osc修改主键时注意:

原表上有个复合主键，现在要添加一个自增id作为主键，如何进行?

会涉及到以下修改动作：

1.删除复合主键定义

2.添加新的自增主键3.原复合主键字段，修改成唯一索引

需要将删除原主键、增加新主键和增加原主键为唯一键同时操作：

--alter "DROP PRIMARY KEY,add column pk int auto_increment primary

key,add unique key uk_id_k(id,k)"