文登区住房和城乡建设局网站,wordpress 纯代码seo,佛山网站建设收费标准,网站访客跟踪前言 在上篇文章中提到了MySQL 5.6 Online DDL#xff0c;如果是MySQL 5.5的版本在DDL方面是要付出代价的#xff0c;虽然已经有了Fast index Creation#xff0c;但是在添加字段还是会锁表的#xff0c;而且在添加删除辅助索引是会加S锁#xff0c;也就是无法进行写操作。… 前言 在上篇文章中提到了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工具包安装依赖 [rootyayun-mysql-server ~]# yum install python-mysqldb MySQL-python -y 2软件下载 [rootyayun-mysql-server ~]# wget https://openarkkit.googlecode.com/files/openark-kit-196-1.noarch.rpm 3安装 [rootyayun-mysql-server ~]# rpm -ivh openark-kit-196-1.noarch.rpm
Preparing... ########################################### [100%]1:openark-kit ########################################### [100%] [rootyayun-mysql-server ~]# 在使用oak-online-alter-table对表执行ALTER TABLE时存在如下限制 在该表上面至少有一个单列的UNIQUE KEY更改原始表为单个字段的唯一索引该表没有定义触发器AFTERoak会自己创建触发器该表没有FOREIGN KEY表名长度不超过57个字符 改工具提供了以下三种基本功能 1.一个非阻塞ALTER TABLE操作:添加列或索引,删除列或索引,修改列,表字符集转换等,都是支持的,如下添加列(新列必须有一个默认值)删除列(旧表必须有一个单列的唯一索引)修改列(改变字段类型,包括唯一键的列)添加索引(普通索引,唯一索引,全文索引。)删除索引(旧表必须有一个单列的唯一索引)修改表引擎:有效,但应格外注意当处理事务性引擎添加外键约束 2.一个空的ALTER,重建一个表:释放磁盘空间和重新组织表相当于优化表。 3.(可能会在未来版本不再支持):创建一个镜像表,与原始表同步,只要不发生如下操作:对原始表ALTER TABLE对原始表TRUNCATE使用LOAD DATA INFILE向原始表导入数据对原始表OPTIMIZE TABLE 工具原理 该工具运行时它允许INSERTUPDATEDELETEREPLACE原始表。但是不允许TRUNCATE,ALTER,REPAIR OPTIMIZE或者其他方式对原表进行操作。该工具适用于InnoDB表MyISAM表或以其他任何表级锁的存储引擎(MEMORY, ARCHIVE)。该工具工作原理是创建一个镜像表的同时它慢慢与原始表同步。直到同步完成要做到这一点该工具必须在原始表创建AFTER INSERT, AFTER UPDATE, AFTER DELETE触发器。镜像表与原始表同步发生在几个步骤。在这些步骤中数据被从原始表复制到镜像表。这是以行块进行这个大小是可以用chunk-size选项配置的。当一个块被复制在MyISAMARCHIVE,MEMORY存储引擎上有读锁或包含在该块上面的行记录innodb较小的块——更快的锁被移除,允许更大的并发性。对于写密集型应用它可能是可取的允许对块之间的停顿以使尽可能减少影响。这可以使用sleep-ratio选项进行配置。而块之间停顿时没有被加锁。即便如此对性能的影响是在运行应用程序时这是由于触发器被添加到表上和DML语句在向镜像表同步。它需要有足够的磁盘空间来容纳改变的表如一个正常的ALTER TABLE。在操作完成时才出现磁盘空间恢复取决于你的存储引擎和配置。 测试如下 首先添加一个字段看看 [rootyayun-mysql-server ~]# oak-online-alter-table -S /tmp/mysqld.sock -u root -p 123456 --tablesakila.film --alterADD 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. [rootyayun-mysql-server ~]# 很明显提示有触发器也是上面提到的限制。 [rootyayun-mysql-server ~]# oak-online-alter-table -S /tmp/mysqld.sock -u root -p 123456 --tableemployees.titles --alterADD 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). [rootyayun-mysql-server ~]# 很明显提示有外键也是上面提到的限制 (rootlocalhost 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 ) ENGINEInnoDB DEFAULT CHARSETutf8 1 row in set (0.00 sec) (rootlocalhost 16:51:00)[dyy] 下面给t1表添加一个辅助索引看看 [rootyayun-mysql-server ~]# oak-online-alter-table -S /tmp/mysqld.sock -u root -p 123456 --tabledyy.t1 --alterADD 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 [rootyayun-mysql-server ~]# 提示没有唯一键这也是上面提到的限制添加一个唯一键然后我们再尝试添加索引 (rootlocalhost 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 (rootlocalhost 16:53:41)[dyy] [rootyayun-mysql-server ~]# oak-online-alter-table -S /tmp/mysqld.sock -u root -p 123456 --tabledyy.t1 --alterADD 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 [rootyayun-mysql-server ~]# 可以看见添加成功我们看看表结构是否真的成功了。上面的输出有一个警告不用理会是因为我是空表没有记录。 (rootlocalhost 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) ) ENGINEInnoDB DEFAULT CHARSETutf8 1 row in set (0.00 sec) (rootlocalhost 16:57:10)[dyy] 在添加字段时如果设置了NOT NULL但没有给默认值也会报警告但是不人性化起码不应该抛代码在什么行嘛直接最后给一个Warning多好 [rootyayun-mysql-server ~]# oak-online-alter-table -S /tmp/mysqld.sock -u root -p 123456 --tabledyy.t1 --alterADD 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 doesnt 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 查看表结构还是添加成功了的。 (rootlocalhost 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 (rootlocalhost 17:22:55)[sbtest] select count(*) from sbtest; ---------- | count(*) | ---------- | 10000000 | ---------- 1 row in set (0.00 sec) (rootlocalhost 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) ) ENGINEInnoDB AUTO_INCREMENT10000001 DEFAULT CHARSETutf8 1 row in set (0.00 sec) (rootlocalhost 17:23:13)[sbtest] 1首先添加一个字段看看session1执行alter table操作session 2执行DML操作 session 1 [rootyayun-mysql-server ~]# oak-online-alter-table -S /tmp/mysqld.sock -u root -p 123456 --tablesbtest.sbtest --alterADD 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 (rootlocalhost 17:25:52)[sbtest] select * from sbtest where id100; --------------------------------------------------------------- | id | k | c | pad | --------------------------------------------------------------- | 100 | 0 | | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt | --------------------------------------------------------------- 1 row in set (0.00 sec) (rootlocalhost 17:26:03)[sbtest] delete from sbtest where id100; Query OK, 1 row affected (0.17 sec) (rootlocalhost 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 (rootlocalhost 17:26:54)[sbtest] update sbtest set k101 where id1111; Query OK, 1 row affected (0.48 sec) Rows matched: 1 Changed: 1 Warnings: 0 (rootlocalhost 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) (rootlocalhost 17:27:45)[sbtest] 可以发现session 1的添加字段的操作并不影响session 2的操作。2添加一个辅助索引看看神马情况 session 1 [rootyayun-mysql-server ~]# oak-online-alter-table -S /tmp/mysqld.sock -u root -p 123456 --tablesbtest.sbtest --alterADD 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% session 2 (rootlocalhost 17:32:23)[sbtest] delete from sbtest where id103; Query OK, 1 row affected (0.09 sec) (rootlocalhost 17:32:32)[sbtest] update sbtest set k101 where id103; Query OK, 0 rows affected (0.00 sec) Rows matched: 0 Changed: 0 Warnings: 0 (rootlocalhost 17:32:39)[sbtest] select * from sbtest where id2000; ---------------------------------------------------------------- | id | k | c | pad | ---------------------------------------------------------------- | 2000 | 0 | | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt | ---------------------------------------------------------------- 1 row in set (0.03 sec) (rootlocalhost 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) (rootlocalhost 17:32:58)[sbtest] 依然读写不受影响。所以总体来说还是很好使用只是有上面提到的那些限制需要注意一下。 我在说mysql 5.6 Online DDL的时候测试过如果在执行alter table之前已经有一个慢查询或者结果集比较大的查询那么此时执行ALTER TABLE是会导致锁表的那么我们测试一下oak-online-alter-table是否会一样。 session 1sbtest表有1000w记录 (rootlocalhost 17:41:07)[sbtest] select * from sbtest; session 2 [rootyayun-mysql-server ~]# oak-online-alter-table -S /tmp/mysqld.sock -u root -p 123456 --tablesbtest.sbtest --alterADD KEY(pad) session 3 (rootlocalhost 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) (rootlocalhost 17:38:10)[(none)] 可以看见该工具的删除触发器操作在等待锁。好了这里的情况和我在说MySQL 5.6 Online DDL的情况一样大家自行查阅资料。好了oak-online-alter-table就说这么多了。 二.pt-online-schema-change 改工具是percona-toolkit工具包中其中的一个工具简单说和oak-online-alter-table有着一样的功能。都是实现在线架构改变的工具。其他的我就不多说了。重点说工作原理注意事项。 1安装依赖包 [rootyayun-mysql-server ~]# yum install perl-IO-Socket-SSL perl-DBD-MySQL perl-Time-HiRes -y 2下载软件 [rootyayun-mysql-server ~]# wget http://www.percona.com/downloads/percona-toolkit/LATEST/RPM/percona-toolkit-2.2.8-1.noarch.rpm 3安装软件 [rootyayun-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%] [rootyayun-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 测试表结构如下 rootlocalhost : 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 ) ENGINEInnoDB DEFAULT CHARSETutf8 1 row in set (0.00 sec) rootlocalhost : dyy 23:39:01 给表t1添加一个字段 [rootyayun-mysql-server ~]# pt-online-schema-change --alteradd column name char(20) --userroot Ddyy,tt1 --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. [rootyayun-mysql-server ~]# 可以发现提示表没有主键或者唯一键所以添加失败。添加主键以后再进行测试。 [rootyayun-mysql-server ~]# pt-online-schema-change --alteradd column name char(20) --userroot Ddyy,tt1 --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. [rootyayun-mysql-server ~]# 可以看见已经成功咯下面给表添加一个字段但是设置NOT NULL但是不给默认值看看神马情况 [rootyayun-mysql-server ~]# pt-online-schema-change --alteradd column last_name char(20) not null --userroot Ddyy,tt1 --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 doesnt 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*/ [rootyayun-mysql-server ~]# 可以看见我没有给默认值添加字段失败下面给一个默认值再瞧瞧 [rootyayun-mysql-server ~]# pt-online-schema-change --alteradd column last_name char(20) not null default yayun --userroot Ddyy,tt1 --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. [rootyayun-mysql-server ~]# 可以看见已经成功了。现在需要做的是对大表做一下测试比如添加索引添加字段是否会导致锁表。这里用的测试表还是前面文章提到的sysbench生成的1000w数据session 1 [rootyayun-mysql-server ~]# pt-online-schema-change --alteradd column name char(20) not null default yayun --userroot Dsbtest,tsbtest --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 session 2 rootlocalhost : sbtest 23:55:40 select * from sbtest where id100; --------------------------------------------------------------- | id | k | c | pad | --------------------------------------------------------------- | 100 | 0 | | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt | --------------------------------------------------------------- 1 row in set (0.00 sec) rootlocalhost : sbtest 23:55:44 select * from sbtest where id100; --------------------------------------------------------------- | id | k | c | pad | --------------------------------------------------------------- | 100 | 0 | | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt | --------------------------------------------------------------- 1 row in set (0.00 sec) rootlocalhost : sbtest 23:56:03 select * from sbtest where id1000; ---------------------------------------------------------------- | id | k | c | pad | ---------------------------------------------------------------- | 1000 | 0 | | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt | ---------------------------------------------------------------- 1 row in set (0.00 sec) rootlocalhost : sbtest 23:56:09 delete from sbtest where id1000; Query OK, 1 row affected (0.77 sec) rootlocalhost : sbtest 23:56:25 update sbtest set k100 where id100; Query OK, 1 row affected (0.50 sec) Rows matched: 1 Changed: 1 Warnings: 0 rootlocalhost : sbtest 23:57:02 发现DML操作依然是没问题的。而且没有oak-online-alter-table那么多限制最后测试一下在执行alter table之前有一个大的查询看是否导致锁等待这个在MySQL 5.6以及oak-online-alter-table都有这个问题。 测试后发现依然存在锁的问题 rootlocalhost : (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) rootlocalhost : (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 转自 pt-online-schema-change VS oak-online-alter-table - yayun - 博客园 https://www.cnblogs.com/gomysql/p/3777607.html 转载于:https://www.cnblogs.com/paul8339/p/10444139.html
