天天看點
傳回

pt-online-schema-change VS oak-online-alter-table

前言

在上篇文章中提到了MySQL 5.6 Online DDL，如果是MySQL 5.5的版本在DDL方面是要付出代價的，雖然已經有了Fast index Creation，但是在添加字段還是會鎖表的，而且在添加删除輔助索引是會加S鎖，也就是無法進行寫操作。是以，這裡就有相關工具的出現，那就是pt-online-schema-change和oak-online-alter-table，都實作了Online DDL，但是每個工具都有相應自己的限制，下面讓我慢慢道來。

一.oak-online-alter-table

openark工具包是一組用于MySQL的實用工具，該工具集解決日常維護任務，這些工作比較複雜和耗時。其中oak-online-alter-table就是該工具集中的一個工具，該工具執行非阻塞ALTER TABLE的操作。當然還有其他的工具，童鞋們自行查閱資料。

（1）安裝openark工具包（安裝依賴）

[root@yayun-mysql-server ~]# yum install python-mysqldb MySQL-python -y

（2）軟體下載下傳

[root@yayun-mysql-server ~]# wget https://openarkkit.googlecode.com/files/openark-kit-196-1.noarch.rpm

（3）安裝

[root@yayun-mysql-server ~]# rpm -ivh openark-kit-196-1.noarch.rpm 
Preparing...                ########################################### [100%]
   1:openark-kit            ########################################### [100%]
[root@yayun-mysql-server ~]#

在使用oak-online-alter-table對表執行ALTER TABLE時存在如下限制：

在該表上面至少有一個單列的UNIQUE KEY

更改原始表為單個字段的唯一索引

該表沒有定義觸發器"AFTER"（oak會自己建立觸發器）

該表沒有FOREIGN KEY

表名長度不超過57個字元

改工具提供了以下三種基本功能

1.一個非阻塞ALTER TABLE操作:添加列或索引,删除列或索引,修改列,表字元集轉換等,都是支援的,如下：

添加列(新列必須有一個預設值)

删除列(舊表必須有一個單列的唯一索引)

修改列(改變字段類型,包括唯一鍵的列)

添加索引(普通索引,唯一索引,全文索引。)

删除索引(舊表必須有一個單列的唯一索引)

修改表引擎:有效,但應格外注意當處理事務性引擎

添加外鍵限制

2.一個空的ALTER,重建一個表:釋放磁盤空間和重新組織表，相當于優化表。

3.(可能會在未來版本不再支援):建立一個鏡像表,與原始表同步,隻要不發生如下操作:

對原始表ALTER TABLE

對原始表TRUNCATE

使用LOAD DATA INFILE向原始表導入資料

對原始表OPTIMIZE TABLE

工具原理：

該工具運作時，它允許INSERT，UPDATE，DELETE，REPLACE原始表。但是不允許TRUNCATE,ALTER,REPAIR OPTIMIZE或者其他方式對原表進行操作。

該工具适用于InnoDB表，MyISAM表，或以其他任何表級鎖的存儲引擎（(MEMORY, ARCHIVE)）。該工具工作原理是建立一個鏡像表的同時，它慢慢與原始表同步。直到同步完成，要做到這一點，該工具必須在原始表建立AFTER INSERT, AFTER UPDATE, AFTER DELETE觸發器。鏡像表與原始表同步發生在幾個步驟。在這些步驟中，資料被從原始表複制到鏡像表。這是以行塊進行，這個大小是可以用chunk-size選項配置的。當一個塊被複制，在（MyISAM，ARCHIVE,MEMORY）存儲引擎上有讀鎖，或包含在該塊上面的行記錄（innodb），較小的塊——更快的鎖被移除,允許更大的并發性。對于寫密集型應用，它可能是可取的，允許對塊之間的停頓，以使盡可能減少影響。這可以使用sleep-ratio選項進行配置。而塊之間停頓時沒有被加鎖。即便如此，對性能的影響是在運作應用程式時，這是由于觸發器被添加到表上和DML語句在向鏡像表同步。它需要有足夠的磁盤空間來容納改變的表（如一個正常的ALTER TABLE）。在操作完成時才出現磁盤空間恢複（取決于你的存儲引擎和配置）。

測試如下：

首先添加一個字段看看

[root@yayun-mysql-server ~]# oak-online-alter-table -S /tmp/mysqld.sock -u root -p 123456 --table=sakila.film --alter="ADD COLUMN name VARCHAR(64) DEFAULT ''",
-- Connecting to MySQL
-- Table sakila.film is of engine innodb
-- ERROR: Errors found. Initiating cleanup
-- Tables unlocked
-- ERROR: Table must not have any 'AFTER' triggers defined.
[root@yayun-mysql-server ~]#

很明顯提示有觸發器，也是上面提到的限制。

[root@yayun-mysql-server ~]# oak-online-alter-table -S /tmp/mysqld.sock -u root -p 123456 --table=employees.titles --alter="ADD COLUMN name VARCHAR(64) DEFAULT ''",
-- Connecting to MySQL
-- Table employees.titles is of engine innodb
-- ERROR: Errors found. Initiating cleanup
-- Tables unlocked
-- ERROR: Table must not have any foreign keys defined (neither as parent nor child).
[root@yayun-mysql-server ~]#

很明顯提示有外鍵，也是上面提到的限制

(root@localhost 16:50:53)[dyy]> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `name` char(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

(root@localhost 16:51:00)[dyy]>

下面給t1表添加一個輔助索引看看

[root@yayun-mysql-server ~]# oak-online-alter-table -S /tmp/mysqld.sock -u root -p 123456 --table=dyy.t1 --alter="ADD KEY(name)"
-- Connecting to MySQL
-- Table dyy.t1 is of engine innodb
-- Checking for UNIQUE columns on dyy.t1, by which to chunk
-- Possible UNIQUE KEY column names in dyy.t1:
-- ERROR: Errors found. Initiating cleanup
-- Tables unlocked
-- ERROR: Table must have a UNIQUE KEY on a single column
[root@yayun-mysql-server ~]#

提示沒有唯一鍵，這也是上面提到的限制，添加一個唯一鍵然後我們再嘗試添加索引

(root@localhost 16:53:37)[dyy]> alter table t1 add unique key (id); 
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

(root@localhost 16:53:41)[dyy]>       
[root@yayun-mysql-server ~]# oak-online-alter-table -S /tmp/mysqld.sock -u root -p 123456 --table=dyy.t1 --alter="ADD KEY(name)"
-- Connecting to MySQL
-- Table dyy.t1 is of engine innodb
-- Checking for UNIQUE columns on dyy.t1, by which to chunk
-- Possible UNIQUE KEY column names in dyy.t1:
-- - id
-- Table dyy.__oak_t1 has been created
-- Table dyy.__oak_t1 has been altered
-- Checking for UNIQUE columns on dyy.__oak_t1, by which to chunk
-- Possible UNIQUE KEY column names in dyy.__oak_t1:
-- - id
-- Checking for UNIQUE columns on dyy.t1, by which to chunk
-- - Found following possible unique keys:
-- - id (int)
-- Chosen unique key is 'id'
-- Shared columns: id, name
-- Created AD trigger
-- Created AU trigger
-- Created AI trigger
-- Attempting to lock tables

-- Tables locked WRITE
/usr/local/bin/oak-online-alter-table:84: Warning: No data - zero rows fetched, selected, or processed
  num_affected_rows = cursor.execute(query)
-- id (min, max) values: ([None], [None])
-- Tables unlocked
-- Table dyy.t1 has been renamed to dyy.__arc_t1,
-- and table dyy.__oak_t1 has been renamed to dyy.t1
-- Table dyy.__arc_t1 was found and dropped
-- ALTER TABLE completed
[root@yayun-mysql-server ~]#

可以看見添加成功，我們看看表結構，是否真的成功了。上面的輸出有一個警告，不用理會，是因為我是空表，沒有記錄。

(root@localhost 16:56:50)[dyy]> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `name` char(20) DEFAULT NULL,
  UNIQUE KEY `id` (`id`),
  KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

(root@localhost 16:57:10)[dyy]>

在添加字段時如果設定了NOT NULL，但沒有給預設值，也會報警告，但是不人性化（起碼不應該抛代碼在什麼行嘛，直接最後給一個Warning多好）

[root@yayun-mysql-server ~]# oak-online-alter-table -S /tmp/mysqld.sock -u root -p 123456 --table=dyy.t1 --alter="ADD COLUMN salarey VARCHAR(64) not null"
-- Connecting to MySQL
-- Table dyy.t1 is of engine innodb
-- Checking for UNIQUE columns on dyy.t1, by which to chunk
-- Possible UNIQUE KEY column names in dyy.t1:
-- - id
-- Table dyy.__oak_t1 has been created
-- Table dyy.__oak_t1 has been altered
-- Checking for UNIQUE columns on dyy.__oak_t1, by which to chunk
-- Possible UNIQUE KEY column names in dyy.__oak_t1:
-- - id
-- Checking for UNIQUE columns on dyy.t1, by which to chunk
-- - Found following possible unique keys:
-- - id (int)
-- Chosen unique key is 'id'
-- Shared columns: id, name, address
-- Created AD trigger
-- Created AU trigger
-- Created AI trigger
-- Attempting to lock tables

-- Tables locked WRITE
-- id (min, max) values: ([1L], [1L])
-- Tables unlocked
-- - Reminder: altering dyy.t1: ADD COLUMN salarey VARCHAR(64)...
-- Copying range (1), (1), progress: 100%
/usr/local/bin/oak-online-alter-table:84: Warning: Field 'salarey' doesn't have a default value
  num_affected_rows = cursor.execute(query)
-- Copying range 100% complete. Number of rows: 1
-- - Reminder: altering dyy.t1: ADD COLUMN salarey VARCHAR(64)...
-- Deleting range (1), (1), progress: 100%
-- Deleting range 100% complete. Number of rows: 0
-- Table dyy.t1 has been renamed to dyy.__arc_t1,
-- and table dyy.__oak_t1 has been renamed to dyy.t1
-- Table dyy.__arc_t1 was found and dropped
-- ALTER TABLE completed

檢視表結構，還是添加成功了的。

(root@localhost 17:08:22)[(none)]> desc dyy.t1;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | YES  | UNI | NULL    |       |
| name    | char(20)    | YES  | MUL | NULL    |       |
| address | varchar(64) | YES  |     | NULL    |       |
| salarey | varchar(64) | NO   |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

好了，更多的測試同學們自行測試哈，下面測試一下各種操作是否會鎖表，這也是我們最關心的問題。測試的表是使用sysbench生成的1000w資料，具體的指令請閱讀前面的文章MySQL 5.6 Online DDL

(root@localhost 17:22:55)[sbtest]> select count(*) from sbtest;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (0.00 sec)

(root@localhost 17:22:57)[sbtest]> show create table sbtest\G
*************************** 1. row ***************************
       Table: sbtest
Create Table: CREATE TABLE `sbtest` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=10000001 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

(root@localhost 17:23:13)[sbtest]>

（1）首先添加一個字段看看session1執行alter table操作，session 2執行DML操作

session 1

[root@yayun-mysql-server ~]# oak-online-alter-table -S /tmp/mysqld.sock -u root -p 123456 --table=sbtest.sbtest --alter="ADD COLUMN address VARCHAR(64)"                  
-- Connecting to MySQL
-- Table sbtest.sbtest is of engine innodb
-- Checking for UNIQUE columns on sbtest.sbtest, by which to chunk
-- Possible UNIQUE KEY column names in sbtest.sbtest:
-- - id
-- Table sbtest.__oak_sbtest has been created
-- Table sbtest.__oak_sbtest has been altered
-- Checking for UNIQUE columns on sbtest.__oak_sbtest, by which to chunk
-- Possible UNIQUE KEY column names in sbtest.__oak_sbtest:
-- - id
-- Checking for UNIQUE columns on sbtest.sbtest, by which to chunk
-- - Found following possible unique keys:
-- - id (int)
-- Chosen unique key is 'id'
-- Shared columns: c, pad, k, id
-- Created AD trigger
-- Created AU trigger
-- Created AI trigger
-- Attempting to lock tables

-- Tables locked WRITE
-- id (min, max) values: ([1L], [10000000L])
-- Tables unlocked
-- - Reminder: altering sbtest.sbtest: ADD COLUMN address VARCHAR(64)...
-- Copying range (1), (1000), progress: 0%
-- Copying range (1000), (2000), progress: 0%
-- Copying range (2000), (3000), progress: 0%
-- Copying range (3000), (4000), progress: 0%
-- Copying range (4000), (5000), progress: 0%
-- Copying range (5000), (6000), progress: 0%
-- Copying range (6000), (7000), progress: 0%
-- Copying range (7000), (8000), progress: 0%
-- Copying range (8000), (9000), progress: 0%
-- Copying range (9000), (10000), progress: 0%

session 2

(root@localhost 17:25:52)[sbtest]> select * from sbtest where id=100;
+-----+---+---+----------------------------------------------------+
| id  | k | c | pad                                                |
+-----+---+---+----------------------------------------------------+
| 100 | 0 |   | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |
+-----+---+---+----------------------------------------------------+
1 row in set (0.00 sec)

(root@localhost 17:26:03)[sbtest]> delete from sbtest where id=100;         
Query OK, 1 row affected (0.17 sec)

(root@localhost 17:26:24)[sbtest]> insert into sbtest select 100,0,null,'cccc';
Query OK, 1 row affected, 1 warning (0.07 sec)
Records: 1  Duplicates: 0  Warnings: 1

(root@localhost 17:26:54)[sbtest]> update sbtest set k=101 where id=1111;
Query OK, 1 row affected (0.48 sec)
Rows matched: 1  Changed: 1  Warnings: 0

(root@localhost 17:27:40)[sbtest]> show processlist;
+----+------+-----------+--------+---------+------+-------+------------------+
| Id | User | Host      | db     | Command | Time | State | Info             |
+----+------+-----------+--------+---------+------+-------+------------------+
| 53 | root | localhost | sbtest | Query   |    1 | NULL  | show processlist |
| 54 | root | localhost | sbtest | Query   |    0 | NULL  | commit           |
+----+------+-----------+--------+---------+------+-------+------------------+
2 rows in set (0.08 sec)

(root@localhost 17:27:45)[sbtest]>

可以發現session 1的添加字段的操作并不影響session 2的操作。

（2）添加一個輔助索引看看神馬情況

[root@yayun-mysql-server ~]# oak-online-alter-table -S /tmp/mysqld.sock -u root -p 123456 --table=sbtest.sbtest --alter="ADD KEY(pad)"                                 
-- Connecting to MySQL
-- Table sbtest.sbtest is of engine innodb
-- Dropped custom trigger sbtest_AD_oak
-- Dropped custom trigger sbtest_AU_oak
-- Dropped custom trigger sbtest_AI_oak
-- Checking for UNIQUE columns on sbtest.sbtest, by which to chunk
-- Possible UNIQUE KEY column names in sbtest.sbtest:
-- - id
-- Table sbtest.__oak_sbtest was found and dropped
-- Table sbtest.__oak_sbtest has been created
-- Table sbtest.__oak_sbtest has been altered
-- Checking for UNIQUE columns on sbtest.__oak_sbtest, by which to chunk
-- Possible UNIQUE KEY column names in sbtest.__oak_sbtest:
-- - id
-- Checking for UNIQUE columns on sbtest.sbtest, by which to chunk
-- - Found following possible unique keys:
-- - id (int)
-- Chosen unique key is 'id'
-- Shared columns: c, pad, k, id
-- Created AD trigger
-- Created AU trigger
-- Created AI trigger
-- Attempting to lock tables

-- Tables locked WRITE
-- id (min, max) values: ([1L], [10000000L])
-- Tables unlocked
-- - Reminder: altering sbtest.sbtest: ADD KEY(pad)...
-- Copying range (1), (1000), progress: 0%
-- Copying range (1000), (2000), progress: 0%
-- Copying range (2000), (3000), progress: 0%
-- Copying range (3000), (4000), progress: 0%
-- Copying range (4000), (5000), progress: 0%
-- Copying range (5000), (6000), progress: 0%
-- Copying range (6000), (7000), progress: 0%
-- Copying range (7000), (8000), progress: 0%
-- Copying range (8000), (9000), progress: 0%
-- Copying range (9000), (10000), progress: 0%
-- Copying range (10000), (11000), progress: 0%
-- Copying range (11000), (12000), progress: 0%
-- Copying range (12000), (13000), progress: 0%      
(root@localhost 17:32:23)[sbtest]> delete from sbtest where id=103;            
Query OK, 1 row affected (0.09 sec)

(root@localhost 17:32:32)[sbtest]> update sbtest set k=101 where id=103;       
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

(root@localhost 17:32:39)[sbtest]> select * from sbtest where id=2000;
+------+---+---+----------------------------------------------------+
| id   | k | c | pad                                                |
+------+---+---+----------------------------------------------------+
| 2000 | 0 |   | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |
+------+---+---+----------------------------------------------------+
1 row in set (0.03 sec)

(root@localhost 17:32:50)[sbtest]> show processlist;
+----+------+-----------+--------+---------+------+-------+------------------+
| Id | User | Host      | db     | Command | Time | State | Info             |
+----+------+-----------+--------+---------+------+-------+------------------+
| 53 | root | localhost | sbtest | Query   |    0 | NULL  | show processlist |
| 55 | root | localhost | sbtest | Query   |    0 | NULL  | commit           |
+----+------+-----------+--------+---------+------+-------+------------------+
2 rows in set (0.03 sec)

(root@localhost 17:32:58)[sbtest]>

依然讀寫不受影響。是以總體來說還是很好使用，隻是有上面提到的那些限制需要注意一下。

我在說mysql 5.6 Online DDL的時候測試過如果在執行alter table之前已經有一個慢查詢或者結果集比較大的查詢，那麼此時執行ALTER TABLE是會導緻鎖表的，那麼我們測試一下oak-online-alter-table是否會一樣。

session 1（sbtest表有1000w記錄）

(root@localhost 17:41:07)[sbtest]> select * from sbtest;      
[root@yayun-mysql-server ~]# oak-online-alter-table -S /tmp/mysqld.sock -u root -p 123456 --table=sbtest.sbtest --alter="ADD KEY(pad)"

session 3

(root@localhost 17:37:54)[(none)]> show processlist;
+----+------+-----------+--------+---------+------+---------------------------------+---------------------------------------------+
| Id | User | Host      | db     | Command | Time | State                           | Info                                        |
+----+------+-----------+--------+---------+------+---------------------------------+---------------------------------------------+
| 53 | root | localhost | sbtest | Query   |    9 | Sending data                    | select * from sbtest                        |
| 56 | root | localhost | NULL   | Query   |    0 | NULL                            | show processlist                            |
| 57 | root | localhost | sbtest | Query   |    5 | Waiting for table metadata lock | DROP TRIGGER IF EXISTS sbtest.sbtest_AD_oak |
+----+------+-----------+--------+---------+------+---------------------------------+---------------------------------------------+
3 rows in set (0.05 sec)

(root@localhost 17:38:10)[(none)]>

可以看見該工具的删除觸發器操作在等待鎖。好了，這裡的情況和我在說MySQL 5.6 Online DDL的情況一樣，大家自行查閱資料。好了，oak-online-alter-table就說這麼多了。

二.pt-online-schema-change

改工具是percona-toolkit工具包中其中的一個工具，簡單說和oak-online-alter-table有着一樣的功能。都是實作線上架構改變的工具。其他的我就不多說了。重點說工作原理，注意事項。

（1）安裝依賴包

[root@yayun-mysql-server ~]# yum install perl-IO-Socket-SSL perl-DBD-MySQL perl-Time-HiRes -y

（2）下載下傳軟體

[root@yayun-mysql-server ~]# wget http://www.percona.com/downloads/percona-toolkit/LATEST/RPM/percona-toolkit-2.2.8-1.noarch.rpm

（3）安裝軟體

[root@yayun-mysql-server ~]# rpm -ivh percona-toolkit-2.2.8-1.noarch.rpm 
warning: percona-toolkit-2.2.8-1.noarch.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
Preparing...                ########################################### [100%]
   1:percona-toolkit        ########################################### [100%]
[root@yayun-mysql-server ~]#

大概工作原理：

（1）如果存在外鍵，根據alter-foreign-keys-method參數的值，檢測外鍵相關的表，針對相應的設定進行處理。

（2）建立一個新的表，表結構為修改後的資料表，用于從源資料表向新表中導入資料。

（3）建立觸發器，在複制資料開始之後，将對源資料表繼續進行資料修改的操作記錄下來，以便在資料複制結束後執行這些操作，保證資料不會丢失。

（4）複制資料，從源資料表中複制資料到新表中。

（5）修改外鍵相關的子表，根據修改後的資料，修改外鍵關聯的子表。

（6）更改源資料表為old表，把新表改為源表名，并将old表删除。

（7）删除觸發器。

存在如下限制：

（1）對操作的表必須要有主鍵或者唯一鍵

（2）增加的字段如果為NOT NULL，會報錯，需要添加預設值才可以成功。

實際測試：

用法

pt-online-schema-change [OPTIONS] DSN

測試表結構如下

root@localhost : dyy 23:38:47> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `age` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

root@localhost : dyy 23:39:01>

給表t1添加一個字段

[root@yayun-mysql-server ~]# pt-online-schema-change --alter="add column name char(20)" --user=root D=dyy,t=t1 --execute

# A software update is available:
#   * The current version for Percona::Toolkit is 2.2.8.

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 `dyy`.`t1`...
Creating new table...
Created new table dyy._t1_new OK.
Altering new table...
Altered `dyy`.`_t1_new` OK.
2014-06-09T23:41:04 Dropping new table...
2014-06-09T23:41:04 Dropped new table OK.
`dyy`.`t1` was not altered.
The new table `dyy`.`_t1_new` does not have a PRIMARY KEY or a unique index which is required for the DELETE trigger.
[root@yayun-mysql-server ~]#

可以發現提示表沒有主鍵或者唯一鍵，是以添加失敗。添加主鍵以後再進行測試。

[root@yayun-mysql-server ~]# pt-online-schema-change --alter="add column name char(20)" --user=root D=dyy,t=t1 --execute
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 `dyy`.`t1`...
Creating new table...
Created new table dyy._t1_new OK.
Altering new table...
Altered `dyy`.`_t1_new` OK.
2014-06-09T23:45:51 Creating triggers...
2014-06-09T23:45:51 Created triggers OK.
2014-06-09T23:45:51 Copying approximately 1 rows...
2014-06-09T23:45:51 Copied rows OK.
2014-06-09T23:45:51 Swapping tables...
2014-06-09T23:45:51 Swapped original and new tables OK.
2014-06-09T23:45:51 Dropping old table...
2014-06-09T23:45:51 Dropped old table `dyy`.`_t1_old` OK.
2014-06-09T23:45:51 Dropping triggers...
2014-06-09T23:45:51 Dropped triggers OK.
Successfully altered `dyy`.`t1`.
[root@yayun-mysql-server ~]#

可以看見已經成功咯，下面給表添加一個字段，但是設定NOT NULL，但是不給預設值，看看神馬情況

[root@yayun-mysql-server ~]# pt-online-schema-change --alter="add column last_name char(20) not null" --user=root D=dyy,t=t1 --execute       
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 `dyy`.`t1`...
Creating new table...
Created new table dyy._t1_new OK.
Altering new table...
Altered `dyy`.`_t1_new` OK.
2014-06-09T23:50:48 Creating triggers...
2014-06-09T23:50:48 Created triggers OK.
2014-06-09T23:50:48 Copying approximately 1 rows...
2014-06-09T23:50:48 Dropping triggers...
2014-06-09T23:50:48 Dropped triggers OK.
2014-06-09T23:50:48 Dropping new table...
2014-06-09T23:50:48 Dropped new table OK.
`dyy`.`t1` was not altered.
2014-06-09T23:50:48 Error copying rows from `dyy`.`t1` to `dyy`.`_t1_new`: 2014-06-09T23:50:48 Copying rows caused a MySQL error 1364:
    Level: Warning
     Code: 1364
  Message: Field 'last_name' doesn't have a default value
    Query: INSERT LOW_PRIORITY IGNORE INTO `dyy`.`_t1_new` (`id`, `age`, `name`) SELECT `id`, `age`, `name` FROM `dyy`.`t1` LOCK IN SHARE MODE /*pt-online-schema-change 3479 copy table*/

[root@yayun-mysql-server ~]#

可以看見我沒有給預設值，添加字段失敗，下面給一個預設值，再瞧瞧

[root@yayun-mysql-server ~]# pt-online-schema-change --alter="add column last_name char(20) not null default 'yayun'" --user=root D=dyy,t=t1 --execute
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 `dyy`.`t1`...
Creating new table...
Created new table dyy._t1_new OK.
Altering new table...
Altered `dyy`.`_t1_new` OK.
2014-06-09T23:52:40 Creating triggers...
2014-06-09T23:52:40 Created triggers OK.
2014-06-09T23:52:40 Copying approximately 1 rows...
2014-06-09T23:52:40 Copied rows OK.
2014-06-09T23:52:40 Swapping tables...
2014-06-09T23:52:40 Swapped original and new tables OK.
2014-06-09T23:52:40 Dropping old table...
2014-06-09T23:52:40 Dropped old table `dyy`.`_t1_old` OK.
2014-06-09T23:52:40 Dropping triggers...
2014-06-09T23:52:40 Dropped triggers OK.
Successfully altered `dyy`.`t1`.
[root@yayun-mysql-server ~]#

可以看見已經成功了。現在需要做的是對大表做一下測試，比如添加索引，添加字段是否會導緻鎖表。這裡用的測試表還是前面文章提到的sysbench生成的1000w資料

[root@yayun-mysql-server ~]# pt-online-schema-change --alter="add column name char(20) not null default 'yayun'" --user=root D=sbtest,t=sbtest --execute    
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 `sbtest`.`sbtest`...
Creating new table...
Created new table sbtest._sbtest_new OK.
Altering new table...
Altered `sbtest`.`_sbtest_new` OK.
2014-06-09T23:55:58 Creating triggers...
2014-06-09T23:55:58 Created triggers OK.
2014-06-09T23:55:58 Copying approximately 480065 rows...
Copying `sbtest`.`sbtest`:  36% 00:52 remain
Copying `sbtest`.`sbtest`:  66% 00:29 remain      
root@localhost : sbtest 23:55:40> select * from sbtest where id=100;
+-----+---+---+----------------------------------------------------+
| id  | k | c | pad                                                |
+-----+---+---+----------------------------------------------------+
| 100 | 0 |   | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |
+-----+---+---+----------------------------------------------------+
1 row in set (0.00 sec)

root@localhost : sbtest 23:55:44> select * from sbtest where id=100;
+-----+---+---+----------------------------------------------------+
| id  | k | c | pad                                                |
+-----+---+---+----------------------------------------------------+
| 100 | 0 |   | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |
+-----+---+---+----------------------------------------------------+
1 row in set (0.00 sec)

root@localhost : sbtest 23:56:03> select * from sbtest where id=1000;
+------+---+---+----------------------------------------------------+
| id   | k | c | pad                                                |
+------+---+---+----------------------------------------------------+
| 1000 | 0 |   | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |
+------+---+---+----------------------------------------------------+
1 row in set (0.00 sec)

root@localhost : sbtest 23:56:09> delete from sbtest where id=1000;         
Query OK, 1 row affected (0.77 sec)

root@localhost : sbtest 23:56:25> update sbtest set k=100 where id=100;               
Query OK, 1 row affected (0.50 sec)
Rows matched: 1  Changed: 1  Warnings: 0

root@localhost : sbtest 23:57:02>

發現DML操作依然是沒問題的。而且沒有oak-online-alter-table那麼多限制，最後測試一下在執行alter table之前有一個大的查詢，看是否導緻鎖等待，這個在MySQL 5.6以及oak-online-alter-table都有這個問題。

測試後發現依然存在鎖的問題

root@localhost : (none) 00:39:09> show full processlist;
+----+------+-----------+--------+---------+------+---------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Id | User | Host      | db     | Command | Time | State                           | Info                                                                                                                                                                                |
+----+------+-----------+--------+---------+------+---------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 38 | root | localhost | sbtest | Query   |  325 | Sending data                    | select * from sbtest                                                                                                                                                                |
| 39 | root | localhost | NULL   | Query   |    1 | NULL                            | show full processlist                                                                                                                                                               |
| 40 | root | localhost | sbtest | Query   |   36 | Waiting for table metadata lock | CREATE TRIGGER `pt_osc_sbtest_sbtest_del` AFTER DELETE ON `sbtest`.`sbtest` FOR EACH ROW DELETE IGNORE FROM `sbtest`.`__sbtest_new` WHERE `sbtest`.`__sbtest_new`.`id` <=> OLD.`id` |
| 41 | root | localhost | sbtest | Sleep   |  310 |                                 | NULL                                                                                                                                                                                |
+----+------+-----------+--------+---------+------+---------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
4 rows in set (1.28 sec)

root@localhost : (none) 00:42:33>

總結：

pt-online-schema-change比oak-online-alter-table工具更好用，并且存在的限制較少，oak-online-alter-table對有外鍵的表是沒有辦法的。對于已經執行了大的查詢，這時恰好執行ALTER TABLE操作，都會導緻鎖表。是以一般選擇避開業務高峰期執行。是以還是要在業務量較低且沒有大查詢時執行Online DDL。

參考資料：

http://openarkkit.googlecode.com/svn/trunk/openarkkit/doc/html/oak-online-alter-table.html（自備梯子）

http://www.percona.com/doc/percona-toolkit/2.1/pt-online-schema-change.html

作者：Atlas

出處：Atlas的部落格 http://www.cnblogs.com/gomysql

您的支援是對部落客最大的鼓勵，感謝您的認真閱讀。本文版權歸作者所有，歡迎轉載，但請保留該聲明。如果您需要技術支援，本人亦提供有償服務。

mysql Online DDL
上一篇: MySQL 5.5 伺服器變量詳解(一)
下一篇: MySQL 5.6 Online DDL

繼續閱讀