快乐学习
前程无忧、中华英才非你莫属!

Online DDL工具的安装与使用

   最近经常在线上经常遇到有性能问题的SQL,有些表没有合理添加索引,有些表添加的索引不合理,各种各样的问题,导致SQL的执行效率不高。这时DBA们不得不重构SQL,使其达到最好的性能,这时我们往往要在线添加或者删除索引、字段等等的操作。如果是MySQL 5.5的版本在DDL方面是要付出代价的,虽然已经有了Fast index Creation,但是在添加字段还是会锁表的,而且在添加删除辅助索引是会加S锁,也就是无法进行写操作。所以,这里就有相关工具的出现,那就是pt-online-schema-change和oak-online-alter-table,都实现了Online DDL,但是每个工具都有相应自己的限制。如果是MySQL5.6以及更高的版本,已经开始支持Online DDL,我们下面的研究,针对MySQL5.6之前的版本。

 

主要介绍两个工具:oak-online-alter-table & pt-online-schema-change

 

一、oak-online-alter-table安装及使用

openark工具包是一组用于MySQL的实用工具,该工具集解决日常维护任务,这些工作比较复杂和耗时。其中oak-online-alter-table就是该工具集中的一个工具,该工具执行非阻塞ALTER TABLE的操作。当然还有其他的工具,有关openark工具说明可以参考http://code.openark.org/forge/openark-kit

1)安装openark工具包(安装依赖)

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

2)openark软件包下载:(一般只有海外服务器才可以直接在服务器上下载,下载个包相信难不了大家的)

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

3)openark的安装:

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

 

 该工具提供了以下三种基本功能:

1、一个非阻塞ALTER TABLE操作,以下几种情况都是支持的

1)添加列 (新列必须有一个默认值)

2)删除列 (旧表必须有一个单列的唯一索引)

3)修改列 (改变字段类型,包括唯一键的列)

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

5)删除索引(旧表必须有一个单列的唯一索引)

6) 修改表引擎:当处理非事务性引擎应该格外注意

7)添加外键约束

2、(可能会在未来版本不再支持):创建一个镜像表,与原始表同步,只要不发生如下操作:

1)对原始表ALTER TABLE操作

2)对原始表TRUNCATE操作

3)使用LOAD DATA INFILE向原始表导入数据

4)对原始表OPTIMIZE TABLE操作

3、一个空的ALTER,重建一个表:释放磁盘空间和重新组织表,相当于优化表。

 

oak-online-alter-table的一些使用限制进行说明
1、在该表上面至少有一个单列的UNIQUE KEY

2、更改原始表为单个字段的唯一索引

3、该表没有定义触发器“AFTER”(oak会自己创建触发器)

4、应该没有外键约束(FOREIGN KEY)

5、表名长度不超过57个字符

 

oak-online-alter-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)。在操作完成时才出现磁盘空间恢复(取决于你的存储引擎和配置)。

 

 

测试如下:

1、添加一个字段

[root@xuanzhi-mysql ~]# oak-online-alter-table -uroot -p123456 -S /data/mysql-5.5.40/mysql.sock --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@xuanzhi-mysql ~]# 

很明显提示有触发器,也是上面提到的使用限制。

[root@xuanzhi-mysql ~]# oak-online-alter-table -uroot -p123456 -S /data/mysql-5.5.40/mysql.sock --table=sakila.actor --alter="ADD COLUMN name VARCHAR(64) DEFAULT ''"      
-- Connecting to MySQL
-- Table sakila.actor 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).

提示了有外键约束,无论是父表还是子表 ,如果有外键的话,都是不允许添加字段的。

下面给t1表添加一个辅助索引看看

mysql> show create table t1G
*************************** 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)

mysql> 

在name字段添加普通索引:

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

可以看到提示说没有唯一键,也是上面提及到的使用限制。我们添加一个唯一键再看看:

mysql> alter table t1 add unique key (id); 
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 

 

[root@xuanzhi-mysql ~]# oak-online-alter-table -uroot -p123456 -S /data/mysql-5.5.40/mysql.sock --table=sakila.t1 --alter="ADD KEY(name)"
-- Connecting to MySQL
-- Table sakila.t1 is of engine innodb
-- Checking for UNIQUE columns on sakila.t1, by which to chunk
-- Possible UNIQUE KEY column names in sakila.t1:
-- - id
-- Table sakila.__oak_t1 has been created
-- Table sakila.__oak_t1 has been altered
-- Checking for UNIQUE columns on sakila.__oak_t1, by which to chunk
-- Possible UNIQUE KEY column names in sakila.__oak_t1:
-- - id
-- Checking for UNIQUE columns on sakila.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 sakila.t1 has been renamed to sakila.__arc_t1,
-- and table sakila.__oak_t1 has been renamed to sakila.t1
-- Table sakila.__arc_t1 was found and dropped
-- ALTER TABLE completed
[root@xuanzhi-mysql ~]# 

可以看到已经添加成功了,我们查看下表结构,是否真的成功了:(上面的输出有一个警告,不用理会,是因为我是空表,没有记录)

mysql> show create table t1G
*************************** 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)

mysql> 

在添加字段时如果设置了NOT NULL,但没有给默认值,也会报警告

[root@xuanzhi-mysql ~]# oak-online-alter-table -uroot -p123456 -S /data/mysql-5.5.40/mysql.sock --table=sakila.t1 --alter="ADD COLUMN sex VARCHAR(64) not null"          
-- Connecting to MySQL
-- Table sakila.t1 is of engine innodb
-- Checking for UNIQUE columns on sakila.t1, by which to chunk
-- Possible UNIQUE KEY column names in sakila.t1:
-- - id
-- Table sakila.__oak_t1 has been created
-- Table sakila.__oak_t1 has been altered
-- Checking for UNIQUE columns on sakila.__oak_t1, by which to chunk
-- Possible UNIQUE KEY column names in sakila.__oak_t1:
-- - id
-- Checking for UNIQUE columns on sakila.t1, by which to chunk
-- - Found following possible unique keys:
-- - id (int)
-- Chosen unique key is 'id'
-- Shared columns: salarey, id, name
-- 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 sakila.t1: ADD COLUMN sex VARCHAR(64) not...
-- Copying range (1), (1), progress: 100%/usr/local/bin/oak-online-alter-table:84: Warning: Field 'sex' doesn't have a default value
  num_affected_rows = cursor.execute(query)
-- Copying range 100% complete. Number of rows: 1
-- - Reminder: altering sakila.t1: ADD COLUMN sex VARCHAR(64) not...
-- Deleting range (1), (1), progress: 100%
-- Deleting range 100% complete. Number of rows: 0
-- Table sakila.t1 has been renamed to sakila.__arc_t1,
-- and table sakila.__oak_t1 has been renamed to sakila.t1
-- Table sakila.__arc_t1 was found and dropped
-- ALTER TABLE completed
[root@xuanzhi-mysql ~]#
查看表结构,还是添加成功了的。
mysql> desc t1;
+---------+-------------+------+-----+---------+-------+| Field   | Type        | Null | Key | Default | Extra |+---------+-------------+------+-----+---------+-------+| id      | int(11)     | YES  | UNI | NULL    |       || name    | char(20)    | YES  | MUL | NULL    |       || sex     | varchar(64) | NO   |     | NULL    |       |+---------+-------------+------+-----+---------+-------+4 rows in set (0.00 sec)

mysql> 

oak-online-alter-table还有更多的操作,同学们自己测试哈。

 

下面测试一下各种操作是否会锁表,这也是我们最关心的问题。

1、我们直接在mysql终端直接执行添加字段看看有什么情况:

mysql> show processlist;
+----+------+-----------+------------+---------+------+---------------------------------+------------------------------------------------------------+| Id | User | Host      | db         | Command | Time | State                           | Info                                                       |+----+------+-----------+------------+---------+------+---------------------------------+------------------------------------------------------------+| 25 | root | localhost | distribute | Query   |   53 | copy to tmp table               | alter table test1 add  age int(3)                          || 26 | root | localhost | distribute | Query   |   28 | Waiting for table metadata lock | insert into test1(name,code) values ('xuanhi','aa102') || 29 | root | localhost | distribute | Query   |    0 | NULL                            | show processlist                                           |+----+------+-----------+------------+---------+------+---------------------------------+------------------------------------------------------------+3 rows in set (0.00 sec)

 

相信大家都看到,在添加表字段的时候,会把整个表锁住,所以看到session2窗口插入数据时,要等待释放锁。

2、我们再测试上添加普通索引看看会不会锁表:

可以看到session2窗口也一样要等待的。如果对一个很大的表直接进行添加或者删除字段和索引的时候,会阻塞所有对该表的DML操作的,所以在线上谨慎直接添加或者删除字段和索引

 

我们用oak-online-alter-table

[root@xuanzhi-mysql ~]# oak-online-alter-table -uroot -p123456 -S /data/mysql-5.5.40/mysql.sock --table=distribute.test1 --alter="ADD COLUMN address VARCHAR(64)"
.....
......
-- Deleting range (6999930), (7000930), progress: 99%
-- Deleting range (7000930), (7001930), progress: 99%
-- Deleting range (7001930), (7003029), progress: 99%
-- Deleting range (7003029), (7004073), progress: 99%
-- Deleting range (7004073), (7005073), progress: 99%
-- Deleting range (7005073), (7006073), progress: 99%
-- Deleting range (7006073), (7007898), progress: 99%
-- Deleting range (7007898), (7009605), progress: 99%
-- Deleting range (7009605), (7011189), progress: 99%
-- - Reminder: altering distribute.test1: ADD COLUMN address VARCHAR(64)...
-- Deleting range (7011189), (7012194), progress: 99%
-- Deleting range (7012194), (7013286), progress: 99%
-- Deleting range (7013286), (7014146), progress: 99%
-- Deleting range (7014146), (7014146), progress: 100%
-- Deleting range 100% complete. Number of rows: 0
-- Table distribute.test1 has been renamed to distribute.__arc_test1,
-- and table distribute.__oak_test1 has been renamed to distribute.test1
-- Table distribute.__arc_test1 was found and dropped
-- ALTER TABLE completed

在seesion2进行DML操作:

mysql> delete from test1 where name="xuanhi4";
Query OK, 1 row affected (2.69 sec)

mysql> insert into test1(name,code) values ('xuanhi4','aa1333');
Query OK, 1 row affected (0.04 sec)

mysql> select * from test1 where code='aa1333';
+---------+---------+--------+| id      | name    | code   |+---------+---------+--------+| 7014150 | xuanhi4 | aa1333 |+---------+---------+--------+1 row in set (1.12 sec)

mysql> update test1 set name="xuanhi5"  where id=7014150;
Query OK, 1 row affected (0.11 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> 

可以看到执行成功的返回时间都是很少的,执行操作的时候并没有锁等待。

我们添加索引看看,是否要锁等待:

[root@xuanzhi-mysql ~]# oak-online-alter-table -uroot -p123456 -S /data/mysql-5.5.40/mysql.sock --table=distribute.test1 --alter="ADD KEY (name,age)"
...
....
......
-- Deleting range (7004073), (7005073), progress: 99%
-- Deleting range (7005073), (7006073), progress: 99%
-- Deleting range (7006073), (7007898), progress: 99%
-- Deleting range (7007898), (7009605), progress: 99%
-- Deleting range (7009605), (7011189), progress: 99%
-- - Reminder: altering distribute.test1: ADD KEY (name,age)...
-- Deleting range (7011189), (7012194), progress: 99%
-- Deleting range (7012194), (7013286), progress: 99%
-- Deleting range (7013286), (7014151), progress: 99%
-- Deleting range (7014151), (7014151), progress: 100%
-- Deleting range 100% complete. Number of rows: 0
-- Table distribute.test1 has been renamed to distribute.__arc_test1,
-- and table distribute.__oak_test1 has been renamed to distribute.test1
-- Table distribute.__arc_test1 was found and dropped
-- ALTER TABLE completed

 

show processlist 查看是否有锁表:

mysql> show processlist;
+----+------+-----------+------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+| Id | User | Host      | db         | Command | Time | State        | Info                                                                                                 |+----+------+-----------+------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+| 25 | root | localhost | distribute | Sleep   |  544 |              | NULL                                                                                                 || 26 | root | localhost | distribute | Query   |    1 | NULL         | show processlist                                                                                     || 29 | root | localhost | distribute | Sleep   | 1260 |              | NULL                                                                                                 || 31 | root | localhost | distribute | Query   |    1 | Sending data | INSERT IGNORE INTO distribute.__oak_test1 (`age`, `code`, `id`, `name`, `address`)
            (SELE |+----+------+-----------+------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+4 rows in set (0.45 sec)

mysql> 

可以看到使用工具在线添加索引和字段都不会锁表,有兴趣的朋友,可以测试删除字段和索引的,其实一样的,我这里就不测试了。mysql5.6虽然支持了Online DDL,但mysql 5.6 Online DDL的时候测试过如果在执行alter table之前已经有一个慢查询或者结果集比较大的查询,那么此时执行ALTER TABLE是会导致锁表的,用oak-online-alter-table也是一样的要等待锁的释放,本人已经测试过。(mysql5.6 online ddl的博客下次有空会补上)

mysql> show processlist;
+----+------+-----------+------------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------------+| Id | User | Host      | db         | Command | Time | State                           | Info                                                                                                 |+----+------+-----------+------------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------------+| 25 | root | localhost | distribute | Query   |    0 | NULL                            | show processlist                                                                                     || 26 | root | localhost | distribute | Sleep   |  961 |                                 | NULL                                                                                                 || 29 | root | localhost | distribute | Query   |   19 | Sending data                    | select * from test1                                                                                  || 32 | root | localhost | distribute | Query   |    6 | Waiting for table metadata lock | CREATE TRIGGER distribute.test1_AD_oak AFTER DELETE ON distribute.test1
        FOR EACH ROW
        |+----+------+-----------+------------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------------+4 rows in set (0.25 sec)

mysql> 

oak-online-alter-table工具的使用,暂时说到这里,有兴趣的朋友可以参考官网

http://openarkkit.googlecode.com/svn/trunk/openarkkit/doc/html/oak-online-alter-table.html(自备梯子)

 

二、pt-online-schema-change

该工具是percona-toolkit工具包中其中的一个工具,简单说和oak-online-alter-table有着一样的功能。都是实现在线架构改变的工具。其他的我就不多说了。重点说工作原理和注意事项。

(1)安装依赖包:

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

(2)下载软件:

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

(3)安装软件

[root@xuanzhi-mysql ~]# 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@xuanzhi-mysql ~]# 

大概工作原理如下:

(1)如果存在外键,根据alter-foreign-keys-method参数的值,检测外键相关的表,针对相应的设置进行处理。

(2)创建一个新的表,表结构为修改后的数据表,用于从源数据表向新表中导入数据。

(3)创建触发器,在复制数据开始之后,将对源数据表继续进行数据修改的操作记录下来,以便在数据复制结束后执行这些操作,保证数据不会丢失。

(4)复制数据,从源数据表中复制数据到新表中。

(5)修改外键相关的子表,根据修改后的数据,修改外键关联的子表。

(6)更改源数据表为old表,把新表改为源表名,并将old表删除。

(7)删除触发器。

存在如下限制:

(1)对操作的表必须要有主键或者唯一键

(2)增加的字段如果为NOT NULL,会报错,需要添加默认值才可以成功。

 

测试:

使用方法:

[root@xuanzhi-mysql ~]# pt-online-schema-change 
Usage: pt-online-schema-change [OPTIONS] DSN

测试表test2结构如下:

mysql> show create table test2G
*************************** 1. row ***************************
       Table: test2
Create Table: CREATE TABLE `test2` (
  `id` int(11) DEFAULT NULL,
  `age` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> 

给表test2添加一个字段:

[root@xuanzhi-mysql ~]# pt-online-schema-change --alter="add column name char(20)" --user=root --password=123456 --socket=/data/mysql-5.5.40/mysql.sock  D=sakila,t=test2 --execute
No slaves found.  See --recursion-method if host redis-master has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
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 `sakila`.`test2`...
Creating new table...
Created new table sakila._test2_new OK.
Altering new table...
Altered `sakila`.`_test2_new` OK.
2015-05-27T22:48:07 Dropping new table...
2015-05-27T22:48:07 Dropped new table OK.
`sakila`.`test2` was not altered.
The new table `sakila`.`_test2_new` does not have a PRIMARY KEY or a unique index which is required for the DELETE trigger.
[root@xuanzhi-mysql ~]# 

可以发现提示表没有主键或者唯一键,所以添加失败。添加主键以后再进行测试。

mysql> alter table test2 add primary key (id);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 
[root@xuanzhi-mysql ~]# pt-online-schema-change --alter="add column name char(20)" --user=root --password=123456 --socket=/data/mysql-5.5.40/mysql.sock  D=sakila,t=test2 --execute
No slaves found.  See --recursion-method if host redis-master has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
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 `sakila`.`test2`...
Creating new table...
Created new table sakila._test2_new OK.
Altering new table...
Altered `sakila`.`_test2_new` OK.
2015-05-27T22:51:54 Creating triggers...
2015-05-27T22:51:54 Created triggers OK.
2015-05-27T22:51:54 Copying approximately 1 rows...
2015-05-27T22:51:54 Copied rows OK.
2015-05-27T22:51:54 Swapping tables...
2015-05-27T22:51:54 Swapped original and new tables OK.
2015-05-27T22:51:54 Dropping old table...
2015-05-27T22:51:54 Dropped old table `sakila`.`_test2_old` OK.
2015-05-27T22:51:54 Dropping triggers...
2015-05-27T22:51:54 Dropped triggers OK.
Successfully altered `sakila`.`test2`.
[root@xuanzhi-mysql ~]# 

可以看到添加了主键后,就成功添加了。但是设置NOT NULL,但是不给默认值,看看神马情况

 

[root@xuanzhi-mysql ~]# pt-online-schema-change --alter="add column last_name char(20) not null" --user=root --password=123456 --socket=/data/mysql-5.5.40/mysql.sock  D=sakila,t=test2 --execute   
No slaves found.  See --recursion-method if host redis-master has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
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 `sakila`.`test2`...
Creating new table...
Created new table sakila._test2_new OK.
Altering new table...
Altered `sakila`.`_test2_new` OK.
2015-05-27T23:00:36 Creating triggers...
2015-05-27T23:00:36 Created triggers OK.
2015-05-27T23:00:36 Copying approximately 1 rows...
2015-05-27T23:00:36 Dropping triggers...
2015-05-27T23:00:36 Dropped triggers OK.
2015-05-27T23:00:36 Dropping new table...
2015-05-27T23:00:36 Dropped new table OK.
`sakila`.`test2` was not altered.
2015-05-27T23:00:36 Error copying rows from `sakila`.`test2` to `sakila`.`_test2_new`: 2015-05-27T23:00:36 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 `sakila`.`_test2_new` (`id`, `age`, `name`) SELECT `id`, `age`, `name` FROM `sakila`.`test2` LOCK IN SHARE MODE /*pt-online-schema-change 4369 copy table*/

[root@xuanzhi-mysql ~]# 

可以看到没有给默认值的情况下,添加字段失败,下面给一个默认值再看看:

[root@xuanzhi-mysql ~]# pt-online-schema-change --alter="add column last_name char(20) not null default 'xuanzhi'" --user=root --password=123456 --socket=/data/mysql-5.5.40/mysql.sock  D=sakila,t=test2 --execute  
No slaves found.  See --recursion-method if host redis-master has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
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 `sakila`.`test2`...
Creating new table...
Created new table sakila._test2_new OK.
Altering new table...
Altered `sakila`.`_test2_new` OK.
2015-05-27T23:03:22 Creating triggers...
2015-05-27T23:03:22 Created triggers OK.
2015-05-27T23:03:22 Copying approximately 1 rows...
2015-05-27T23:03:22 Copied rows OK.
2015-05-27T23:03:22 Swapping tables...
2015-05-27T23:03:22 Swapped original and new tables OK.
2015-05-27T23:03:22 Dropping old table...
2015-05-27T23:03:22 Dropped old table `sakila`.`_test2_old` OK.
2015-05-27T23:03:22 Dropping triggers...
2015-05-27T23:03:22 Dropped triggers OK.
Successfully altered `sakila`.`test2`.
[root@xuanzhi-mysql ~]# 

可以看见已经成功了,相信更多的小伙伴想看看对大表添加字段和索引会不会锁表,莫急,下面我们一起来道道:

下面对一个有六百多万行数据的测试表进行添加字段看是否会锁表:(如果想自己造数据的同学可以用sysbench生成的1000w数据)

mysql> show create table xuanzhiG
*************************** 1. row ***************************
       Table: xuanzhi
Create Table: CREATE TABLE `xuanzhi` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(32) DEFAULT NULL,
  `age` int(3) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code_index` (`code`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=7014157 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> select count(*) from xuanzhi;    
+----------+| count(*) |+----------+|  6862437 |+----------+1 row in set (0.03 sec)

mysql> 


测试1、在线添加字段并查看是否有阻塞DML操作

[root@xuanzhi-mysql ~]# pt-online-schema-change --alter="add column name char(20) not null default 'man'" --user=root --password=123456 --socket=/data/mysl-5.5.40/mysql.sock  D=sakila,t=xuanzhi --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 `sakila`.`xuanzhi`... Creating new table... Created new table sakila._xuanzhi_new OK. Altering new table... Altered `sakila`.`_xuanzhi_new` OK. 2015-05-28T01:38:44 Creating triggers... 2015-05-28T01:38:44 Created triggers OK. 2015-05-28T01:38:44 Copying approximately 6862437 rows... Copying `sakila`.`xuanzhi`: 29% 01:13 remain Copying `sakila`.`xuanzhi`: 51% 00:55 remain Copying `sakila`.`xuanzhi`: 66% 00:45 remain Copying `sakila`.`xuanzhi`: 73% 00:43 remain Copying `sakila`.`xuanzhi`: 78% 00:40 remain Copying `sakila`.`xuanzhi`: 85% 00:31 remain Copying `sakila`.`xuanzhi`: 91% 00:20 remain Copying `sakila`.`xuanzhi`: 95% 00:10 remain 2015-05-28T01:43:07 Copied rows OK. 2015-05-28T01:43:07 Swapping tables... 2015-05-28T01:43:21 Swapped original and new tables OK. 2015-05-28T01:43:21 Dropping old table... 2015-05-28T01:43:21 Dropped old table `sakila`.`_xuanzhi_old` OK. 2015-05-28T01:43:21 Dropping triggers... 2015-05-28T01:43:21 Dropped triggers OK. Successfully altered `sakila`.`xuanzhi`. [root@xuanzhi-mysql ~]#

 

可以看到并没有阻塞DML操作,上面执行成功的返回时间有点久,可以忽略,因为在虚拟机测试生成的报告,所以同学们懂的!!

 

测试2:在线添加索引,看是否阻塞DML操作

[root@xuanzhi-mysql ~]# pt-online-schema-change --alter="ADD KEY (name)" --user=root --password=123456 --socket=/data/mysql-5.5.40/mysql.sock  D=sakila,t=xuanzhi --execute
No slaves found.  See --recursion-method if host redis-master has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
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 `sakila`.`xuanzhi`...
Creating new table...
Created new table sakila._xuanzhi_new OK.
Altering new table...
Altered `sakila`.`_xuanzhi_new` OK.
2015-05-28T01:53:59 Creating triggers...
2015-05-28T01:53:59 Created triggers OK.
2015-05-28T01:53:59 Copying approximately 6862438 rows...
Copying `sakila`.`xuanzhi`:  15% 02:38 remain
Copying `sakila`.`xuanzhi`:  30% 02:19 remain
Copying `sakila`.`xuanzhi`:  42% 02:00 remain
Copying `sakila`.`xuanzhi`:  52% 01:49 remain
Copying `sakila`.`xuanzhi`:  60% 01:38 remain
Copying `sakila`.`xuanzhi`:  67% 01:28 remain
Copying `sakila`.`xuanzhi`:  72% 01:22 remain
Copying `sakila`.`xuanzhi`:  77% 01:10 remain
Copying `sakila`.`xuanzhi`:  83% 00:54 remain
Copying `sakila`.`xuanzhi`:  88% 00:39 remain
Copying `sakila`.`xuanzhi`:  93% 00:21 remain
Copying `sakila`.`xuanzhi`:  99% 00:02 remain
2015-05-28T02:00:05 Copied rows OK.
2015-05-28T02:00:05 Swapping tables...
2015-05-28T02:00:22 Swapped original and new tables OK.
2015-05-28T02:00:22 Dropping old table...
2015-05-28T02:00:22 Dropped old table `sakila`.`_xuanzhi_old` OK.
2015-05-28T02:00:22 Dropping triggers...
2015-05-28T02:00:22 Dropped triggers OK.
Successfully altered `sakila`.`xuanzhi`.
[root@xuanzhi-mysql ~]# 


相信大家都看到了吧,在线添加索引,对DML操作是木有阻塞的。最后查看一下表结构:

mysql> show create table xuanzhiG
*************************** 1. row ***************************
       Table: xuanzhi
Create Table: CREATE TABLE `xuanzhi` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(32) DEFAULT NULL,
  `age` int(3) DEFAULT NULL,
  `name` char(20) NOT NULL DEFAULT 'man',
  PRIMARY KEY (`id`),
  UNIQUE KEY `code_index` (`code`) USING BTREE,
  KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=7014161 DEFAULT CHARSET=utf8
1 row in set (0.08 sec)

mysql> 

成功添加了name字段,成功的在列name上添加了普通索引。

 

测试3:测试一下在执行alter table之前有一个大的查询,看是否导致锁等待,这个在MySQL 5.6以及oak-online-alter-table都有这个问题

在session1窗口:

mysql> select * from xuanzhi;

在session2和session3窗口查看:

并没有发现锁表的情况,但在网上看到的资料是会锁表的,希望同学们也自己测试下,大家一起学习哈!!

 

总结:

一、如果要在线上进行Online DDL操作,一定要做好数据备份,要考虑数据的大小情况及主从框架中如果从是提供读的,要考虑到延时等问题。

二、pt-online-schema-change比oak-online-alter-table工具更好用,并且存在的限制较少,oak-online-alter-table对有外键的表是没有办法的

三、已经执行了大的查询,这时恰好执行ALTER TABLE操作,都会导致锁表,MySQL5.6版本也有这样的情况。

四、MySQL5.6版本已经支持Online DDL了,这是一个不错的改进。如果对一个大数据表DDL,一般选择避开业务高峰期执行,所以还是要在业务量较低且没有大查询时执行Online DDL

 

参考资料:

https://openarkkit.googlecode.com/svn/trunk/openarkkit/doc/html/oak-online-alter-table.html(自备梯子)

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

http://www.cnblogs.com/gomysql/p/3777607.html

 

作者:陆炫志

出处:xuanzhi的博客 http://www.cnblogs.com/xuanzhi201111

您的支持是对博主最大的鼓励,感谢您的认真阅读。本文版权归作者所有,欢迎转载,但请保留该声明。

打赏

未经允许不得转载:同乐学堂 » Online DDL工具的安装与使用

分享到:更多 ()

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

特别的技术,给特别的你!

联系QQ:1071235258QQ群:226134712
error: Sorry,暂时内容不可复制!