MySQL Online DDL--pt-online-change-schema测试

---

欢迎转载，请注明作者、出处。

作者：张正

blog：http://space.itpub.net/26355921 

QQ：176036317

如有疑问，欢迎联系。

---

 因为MySQL在5.5及5.5之前的版本中对Online DDL支持不是太好，可能会引发一些Bug。目前大多数场景中都是使用percona公司提供的pt-online-change-schema工具 进行Online DDL。

     pt-online-change-schema原理为：

1. 创建需要执行alter操作的原表的一个临时表，然后在临时表中更改表结构。

2. 在原表中创建触发器（3个）三个触发器分别对应insert,update,delete操作

3. 从原表拷贝数据到临时表，拷贝过程中在原表进行的写操作都会更新到新建的临时表。

4. Rename 原表到old表中，在把临时表Rename为原表，最后将原表删除(可能不删除)，将原表上所创建的触发器删除。

服务器情况：

mysql> show variables like 'innodb_buffer_pool_size';

+-------------------------+------------+

| Variable_name           | Value      |

+-------------------------+------------+

| innodb_buffer_pool_size | 1572864000 |

+-------------------------+------------+

1 row in set (0.00 sec)

表account和account2完全相同：

mysql> select count(AccountId) from account;

+------------------+

| count(AccountId) |

+------------------+

|          2021433 |

+------------------+

1 row in set (2.91 sec)

mysql> select count(AccountId) from account2;

+------------------+

| count(AccountId) |

+------------------+

|          2021433 |

+------------------+

1 row in set (1.63 sec)

mysql> checksum table account;

+---------------+------------+

| Table         | Checksum   |

+---------------+------------+

| test.account  | 3618473368 |

+---------------+------------+

1 row in set (12.30 sec)

mysql> checksum table account2;

+--------------+------------+

| Table        | Checksum   |

+--------------+------------+

| test.account2| 3618473368 |

+--------------+------------+

1 row in set (15.51 sec)

一。

对account表进行操作，无其他任何连接：

mysql> show processlist;

+-----+------+-----------+------+---------+------+-------+------------------+

| Id  | User | Host      | db   | Command | Time | State | Info             |

+-----+------+-----------+------+---------+------+-------+------------------+

| 121 | root | localhost | test | Query   |    0 | NULL  | show processlist |

+-----+------+-----------+------+---------+------+-------+------------------+

1 row in set (0.00 sec)

执行SQL：

mysql> update account set NickName='test result' where AccountId between 100000 and 100090;

Query OK, 91 rows affected (0.12 sec)

Rows matched: 91  Changed: 91  Warnings: 0

mysql> update account set address='test address result' where AccountId between 889917 and 889523;

Query OK, 0 rows affected (0.00 sec)

mysql> update account set IpAddress='10.10.10.1' where AccountId between 1387652 and 1387930;

Query OK, 279 rows affected (0.50 sec)

Rows matched: 279  Changed: 279  Warnings: 0

mysql> update account set newNickName='test' where AccountId between 387652 and 387949;

Query OK, 298 rows affected (0.34 sec)

Rows matched: 298  Changed: 298  Warnings: 0

执行DDL：

mysql>alter table account add column uuid varchar(60) null default null after childrenInHS;

Query OK, 2021433 rows affected (10 min 6.80 sec)

Records: 2021433  Duplicates: 0  Warnings: 0

用时10分钟。

检查checksum：

mysql> checksum table account;

+--------------+------------+

| Table        | Checksum   |

+--------------+------------+

| test.account | 2787974151 |

+--------------+------------+

1 row in set (1 min 36.62 sec)

二。

account2表使用pt-online-change-schema：

/usr/bin/pt-online-schema-change --user=root --host=127.0.0.1 --port=3306 --charset=utf8 --nodrop-old-table --alter="add column uuid varchar(60) null default null after childrenInHS " D=test,t=account2 --exec

# A software update is available:

#   * The current version for MySQL Community Server (GPL) is 5.5.34.

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`.`account2`...

Creating new table...

Created new table test._account2_new OK.

Altering new table...

Altered `test`.`_account2_new` OK.

2013-10-15T12:56:19 Creating triggers...

2013-10-15T12:56:20 Created triggers OK.

2013-10-15T12:56:20 Copying approximately 2019344 rows...

Copying `test`.`account2`:   2% 24:35 remain

Copying `test`.`account2`:   5% 17:14 remain

Copying `test`.`account2`:  10% 13:40 remain

Copying `test`.`account2`:  14% 11:32 remain

Copying `test`.`account2`:  18% 11:18 remain

Copying `test`.`account2`:  22% 10:38 remain

Copying `test`.`account2`:  26% 09:45 remain

Copying `test`.`account2`:  29% 09:36 remain

Copying `test`.`account2`:  32% 09:16 remain

Copying `test`.`account2`:  36% 08:52 remain

Copying `test`.`account2`:  39% 08:21 remain

Copying `test`.`account2`:  43% 07:53 remain

Copying `test`.`account2`:  46% 07:36 remain

Copying `test`.`account2`:  49% 07:16 remain

Copying `test`.`account2`:  51% 07:00 remain

Copying `test`.`account2`:  54% 06:50 remain

Copying `test`.`account2`:  56% 06:31 remain

Copying `test`.`account2`:  59% 06:09 remain

Copying `test`.`account2`:  65% 04:56 remain

Copying `test`.`account2`:  72% 03:53 remain

Copying `test`.`account2`:  80% 02:30 remain

Copying `test`.`account2`:  89% 01:14 remain

2013-10-15T13:07:54 Copied rows OK.

2013-10-15T13:07:54 Swapping tables...

2013-10-15T13:07:54 Swapped original and new tables OK.

2013-10-15T13:07:54 Dropping triggers...

2013-10-15T13:07:54 Dropped triggers OK.

Successfully altered `test`.`account2`.

用时12分钟。

同时在另一个session中执行与上面相同的SQL，以模拟Online DML：

#!/bin/bash

mysql test -e "update account2 set NickName='test result' where AccountId between 100000 and 100090;"

sleep 3

mysql test -e "update account2 set address='test address result' where AccountId between 889917 and 889523;"

sleep 7

mysql test -e "update account2 set IpAddress='10.10.10.1' where AccountId between 1387652 and 1387930;"

sleep 5

mysql test -e "update account2 set newNickName='test' where AccountId between 387652 and 387949;"

查看连接：

mysql> show processlist;

+-----+-----------+---------------------+------+---------+------+-------------------+------------------------------------------------------------------------------------------------------+

| Id  | User      | Host                | db   | Command | Time | State             | Info                                                                                                 |

+-----+-----------+---------------------+------+---------+------+-------------------+------------------------------------------------------------------------------------------------------+

| 126 | root      | localhost:42757     | test | Query   |    2 | query end         | INSERT LOW_PRIORITY IGNORE INTO `test`.`_account2_new` (`accountid`, `swid`, `username`, `password`, |

| 127 | root      | localhost:42758     | test | Sleep   |  182 |                   | NULL                                                                                                 |

| 131 | root      | localhost           | NULL | Query   |    0 | NULL              | show processlist                                                                                     |

| 135 | moni_user | 192.168.0.144:51884 | NULL | Sleep   |    0 |                   | NULL                                                                                                 |

+-----+-----------+---------------------+------+---------+------+-------------------+------------------------------------------------------------------------------------------------------+

4 rows in set (0.01 sec)

在同步过程中，创建了临时表 _account2_new ：

mysql> show tables;

+----------------+

| Tables_in_test |

+----------------+

| _account2_new  |

| account        |

| account2       |

+----------------+

由于加了--nodrop-old-table参数，同步完成后_account2_old依然存在，其为原来的account2表：

mysql> show tables;

+----------------+

| Tables_in_test |

+----------------+

| _account2_old  |

| account        |

| account2       |

+----------------+

3 rows in set (0.00 sec)

检查checksum：

mysql> checksum table account2;

+---------------+------------+

| Table         | Checksum   |

+---------------+------------+

| test.account2 | 2787974151 |

+---------------+------------+

1 row in set (14.24 sec)

上面得到account表的checksum为2787974151

表account与account2完全相同。

三。

总结：

上述表明：表account在无其他连接下，直接进行加字段 得到的结果 与 该表使用pt-online-change-schema加字段得到的结果相同。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26355921/viewspace-774421/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26355921/viewspace-774421/