MYSQL OAK ONLINE DDL

OAK工具ONLINE DDL:
原理:
1. 创建一个临时表,包含DDL所产生的改变。
2. 在原表上创建三个触发器:INSERT, UPDATE, DELETE。
3. COPY原表的数据到临时表,这个时候就算原表有数据也会同时插入到临时表,这样才能保持数据的一致性。
4. 执行RENAME操作:将原表COPY成临时表,新生成的临时表COPY成生产业务表。
5. 删除触发器。
6. 删除临时表。

依赖:
1. 表必须有主键。
2. 在RENAME时不能对表有大的操作。
3. 不要有触发器。
4. 不能删除外键,创建外键。

 

1. 安装所依赖的软件包:
yum install MySQL-python -y

2. 查看表是否有触发器,如果有则备份后进行删除。如果没有则可以执行下一步。
原因: 因为一个表上一种类型的触发器只能建一个,OAK的工具在ONLINE DDL时是将原表COPY内容到一张临时表,然后通过触发器的方式在上面建三个触发器:
INSERT, UPDATE, DELETE的操作复制到临时表。 因此这里会有冲突。

3. 准备验证脚本:
select sum(crc32(concat(ifnull(id,'NULL'),ifnull(o_id,'NULL')))) as sum from t_test union all select sum(crc32(concat(ifnull(id,'NULL'),ifnull(o_id,'NULL')))) as sum from t_test_new_20140729 ;
脚本的意思: 这个脚本是SUM主键ID的值进行比较,因为表的主键是由自增ID。

4. 执行变更:
python oak-online-alter-table -u root --ask-pass -S /tmp/mysql_3306.sock -d tpcc1000 -t t_test -g t_test_new_20140729 -a "add column name varchar(9) not null default ''" --sleep=300 --skip-delete-pass
-- Connecting to MySQL
Password: 
-- Table tpcc1000.t_test is of engine innodb
-- Checking for UNIQUE columns on tpcc1000.t_test, by which to chunk
-- Possible UNIQUE KEY column names in tpcc1000.t_test:
-- - id
-- Table tpcc1000.t_test_new_20140729 has been created
-- Table tpcc1000.t_test_new_20140729 has been altered
-- Checking for UNIQUE columns on tpcc1000.t_test_new_20140729, by which to chunk
-- Possible UNIQUE KEY column names in tpcc1000.t_test_new_20140729:
-- - id
-- Checking for UNIQUE columns on tpcc1000.t_test, by which to chunk
-- - Found following possible unique keys:
-- - id (int)
-- Chosen unique key is 'id'
-- Shared columns: id, o_id
-- Created AD trigger
-- Created AU trigger
-- Created AI trigger
-- Attempting to lock tables

-- Tables locked WRITE
-- id (min, max) values: ([1L], [100000L])
-- Tables unlocked
-- - Reminder: altering tpcc1000.t_test: add column name varchar(9) not...
-- Copying range (1), (1000), progress: 0%
-- + Will sleep for 0.3 seconds
-- Copying range (1000), (2000), progress: 1%
-- + Will sleep for 0.3 seconds
-- Copying range (2000), (3000), progress: 2%
-- + Will sleep for 0.3 seconds
-- Copying range (3000), (4000), progress: 3%
-- + Will sleep for 0.3 seconds
-- Copying range (4000), (5000), progress: 4%
-- + Will sleep for 0.3 seconds
-- Copying range (5000), (6000), progress: 5%
-- + Will sleep for 0.3 seconds
-- Copying range (6000), (7000), progress: 6%
-- + Will sleep for 0.3 seconds
-- Copying range (7000), (8000), progress: 7%
-- + Will sleep for 0.3 seconds
-- Copying range (8000), (9000), progress: 8%
-- + Will sleep for 0.3 seconds
-- Copying range (9000), (10000), progress: 9%
-- + Will sleep for 0.3 seconds
-- Copying range (10000), (11000), progress: 10%
-- + Will sleep for 0.3 seconds
-- Copying range (11000), (12000), progress: 11%
-- + Will sleep for 0.3 seconds
-- Copying range (12000), (13000), progress: 12%
-- + Will sleep for 0.3 seconds
-- Copying range (13000), (14000), progress: 13%
-- + Will sleep for 0.3 seconds
-- Copying range (14000), (15000), progress: 14%
-- + Will sleep for 0.3 seconds
-- Copying range (15000), (16000), progress: 15%
-- + Will sleep for 0.3 seconds
-- Copying range (16000), (17000), progress: 16%
-- + Will sleep for 0.3 seconds
-- Copying range (17000), (18000), progress: 17%
-- + Will sleep for 0.3 seconds
-- Copying range (18000), (19000), progress: 18%
-- + Will sleep for 0.3 seconds
-- Copying range (19000), (20000), progress: 19%
-- + Will sleep for 0.3 seconds
-- - Reminder: altering tpcc1000.t_test: add column name varchar(9) not...
-- Copying range (20000), (21000), progress: 20%
-- + Will sleep for 0.3 seconds
-- Copying range (21000), (22000), progress: 21%
-- + Will sleep for 0.3 seconds
-- Copying range (22000), (23000), progress: 22%
-- + Will sleep for 0.3 seconds
-- Copying range (23000), (24000), progress: 23%
-- + Will sleep for 0.3 seconds
-- Copying range (24000), (25000), progress: 24%
-- + Will sleep for 0.3 seconds
-- Copying range (25000), (26000), progress: 25%
-- + Will sleep for 0.3 seconds
-- Copying range (26000), (27000), progress: 26%
-- + Will sleep for 0.3 seconds
-- Copying range (27000), (28000), progress: 27%
-- + Will sleep for 0.3 seconds
-- Copying range (28000), (29000), progress: 28%
-- + Will sleep for 0.3 seconds
-- Copying range (29000), (30000), progress: 28%
-- + Will sleep for 0.3 seconds
-- Copying range (30000), (31000), progress: 30%
-- + Will sleep for 0.3 seconds
-- Copying range (31000), (32000), progress: 31%
-- + Will sleep for 0.3 seconds
-- Copying range (32000), (33000), progress: 32%
-- + Will sleep for 0.3 seconds
-- Copying range (33000), (34000), progress: 33%
-- + Will sleep for 0.3 seconds
-- Copying range (34000), (35000), progress: 34%
-- + Will sleep for 0.3 seconds
-- Copying range (35000), (36000), progress: 35%
-- + Will sleep for 0.3 seconds
-- Copying range (36000), (37000), progress: 36%
-- + Will sleep for 0.3 seconds
-- Copying range (37000), (38000), progress: 37%
-- + Will sleep for 0.3 seconds
-- Copying range (38000), (39000), progress: 38%
-- + Will sleep for 0.3 seconds
-- Copying range (39000), (40000), progress: 39%
-- + Will sleep for 0.3 seconds
-- - Reminder: altering tpcc1000.t_test: add column name varchar(9) not...
-- Copying range (40000), (41000), progress: 40%
-- + Will sleep for 0.3 seconds
-- Copying range (41000), (42000), progress: 41%
-- + Will sleep for 0.3 seconds
-- Copying range (42000), (43000), progress: 42%
-- + Will sleep for 0.3 seconds
-- Copying range (43000), (44000), progress: 43%
-- + Will sleep for 0.3 seconds
-- Copying range (44000), (45000), progress: 44%
-- + Will sleep for 0.3 seconds
-- Copying range (45000), (46000), progress: 45%
-- + Will sleep for 0.3 seconds
-- Copying range (46000), (47000), progress: 46%
-- + Will sleep for 0.3 seconds
-- Copying range (47000), (48000), progress: 47%
-- + Will sleep for 0.3 seconds
-- Copying range (48000), (49000), progress: 48%
-- + Will sleep for 0.3 seconds
-- Copying range (49000), (50000), progress: 49%
-- + Will sleep for 0.3 seconds
-- Copying range (50000), (51000), progress: 50%
-- + Will sleep for 0.3 seconds
-- Copying range (51000), (52000), progress: 51%
-- + Will sleep for 0.3 seconds
-- Copying range (52000), (53000), progress: 52%
-- + Will sleep for 0.3 seconds
-- Copying range (53000), (54000), progress: 53%
-- + Will sleep for 0.3 seconds
-- Copying range (54000), (55000), progress: 54%
-- + Will sleep for 0.3 seconds
-- Copying range (55000), (56000), progress: 55%
-- + Will sleep for 0.3 seconds
-- Copying range (56000), (57000), progress: 56%
-- + Will sleep for 0.3 seconds
-- Copying range (57000), (58000), progress: 56%
-- + Will sleep for 0.3 seconds
-- Copying range (58000), (59000), progress: 57%
-- + Will sleep for 0.3 seconds
-- Copying range (59000), (60000), progress: 59%
-- + Will sleep for 0.3 seconds
-- - Reminder: altering tpcc1000.t_test: add column name varchar(9) not...
-- Copying range (60000), (61000), progress: 60%
-- + Will sleep for 0.3 seconds
-- Copying range (61000), (62000), progress: 61%
-- + Will sleep for 0.3 seconds
-- Copying range (62000), (63000), progress: 62%
-- + Will sleep for 0.3 seconds
-- Copying range (63000), (64000), progress: 63%
-- + Will sleep for 0.3 seconds
-- Copying range (64000), (65000), progress: 64%
-- + Will sleep for 0.3 seconds
-- Copying range (65000), (66000), progress: 65%
-- + Will sleep for 0.3 seconds
-- Copying range (66000), (67000), progress: 66%
-- + Will sleep for 0.3 seconds
-- Copying range (67000), (68000), progress: 67%
-- + Will sleep for 0.3 seconds
-- Copying range (68000), (69000), progress: 68%
-- + Will sleep for 0.3 seconds
-- Copying range (69000), (70000), progress: 69%
-- + Will sleep for 0.3 seconds
-- Copying range (70000), (71000), progress: 70%
-- + Will sleep for 0.3 seconds
-- Copying range (71000), (72000), progress: 71%
-- + Will sleep for 0.3 seconds
-- Copying range (72000), (73000), progress: 72%
-- + Will sleep for 0.3 seconds
-- Copying range (73000), (74000), progress: 73%
-- + Will sleep for 0.3 seconds
-- Copying range (74000), (75000), progress: 74%
-- + Will sleep for 0.3 seconds
-- Copying range (75000), (76000), progress: 75%
-- + Will sleep for 0.3 seconds
-- Copying range (76000), (77000), progress: 76%
-- + Will sleep for 0.3 seconds
-- Copying range (77000), (78000), progress: 77%
-- + Will sleep for 0.3 seconds
-- Copying range (78000), (79000), progress: 78%
-- + Will sleep for 0.3 seconds
-- Copying range (79000), (80000), progress: 79%
-- + Will sleep for 0.3 seconds
-- - Reminder: altering tpcc1000.t_test: add column name varchar(9) not...
-- Copying range (80000), (81000), progress: 80%
-- + Will sleep for 0.3 seconds
-- Copying range (81000), (82000), progress: 81%
-- + Will sleep for 0.3 seconds
-- Copying range (82000), (83000), progress: 82%
-- + Will sleep for 0.3 seconds
-- Copying range (83000), (84000), progress: 83%
-- + Will sleep for 0.3 seconds
-- Copying range (84000), (85000), progress: 84%
-- + Will sleep for 0.3 seconds
-- Copying range (85000), (86000), progress: 85%
-- + Will sleep for 0.3 seconds
-- Copying range (86000), (87000), progress: 86%
-- + Will sleep for 0.3 seconds
-- Copying range (87000), (88000), progress: 87%
-- + Will sleep for 0.3 seconds
-- Copying range (88000), (89000), progress: 88%
-- + Will sleep for 0.3 seconds
-- Copying range (89000), (90000), progress: 89%
-- + Will sleep for 0.3 seconds
-- Copying range (90000), (91000), progress: 90%
-- + Will sleep for 0.3 seconds
-- Copying range (91000), (92000), progress: 91%
-- + Will sleep for 0.3 seconds
-- Copying range (92000), (93000), progress: 92%
-- + Will sleep for 0.3 seconds
-- Copying range (93000), (94000), progress: 93%
-- + Will sleep for 0.3 seconds
-- Copying range (94000), (95000), progress: 94%
-- + Will sleep for 0.3 seconds
-- Copying range (95000), (96000), progress: 95%
-- + Will sleep for 0.3 seconds
-- Copying range (96000), (97000), progress: 96%
-- + Will sleep for 0.3 seconds
-- Copying range (97000), (98000), progress: 97%
-- + Will sleep for 0.3 seconds
-- Copying range (98000), (99000), progress: 98%
-- + Will sleep for 0.3 seconds
-- Copying range (99000), (100000), progress: 99%
-- + Will sleep for 0.3 seconds
-- Copying range 100% complete. Number of rows: 100000
-- Ghost table creation completed. Note that triggers on tpcc1000.t_test were not removed
这个时候表上的触发器并未删除,原表和临时表都是存在的,因此还需要以下步骤:

5. RENAME临时表回原表,并删除触发器。
(testing)root@localhost [tpcc1000]> use tpcc1000
Database changed
(testing)root@localhost [tpcc1000]> set names utf8;
Query OK, 0 rows affected (0.00 sec)

(testing)root@localhost [tpcc1000]> rename table t_test to t_test_20140729,t_test_new_20140729 to t_test;
Query OK, 0 rows affected (0.00 sec)
注意这一步,一定要放在一个事务里来操作,不然有可能产生程序出错或者数据不一致的情况。
这个执行非常的快, 但也有踩坑的时候, 如果这时有个大查询在原表,或者大的事务在原表上,不管是主库或者从库,在执行这一步时都会DELAY一会,直到获取到锁才会进行操作。

(testing)root@localhost [tpcc1000]> drop trigger t_test_AI_oak;
Query OK, 0 rows affected (0.00 sec)

(testing)root@localhost [tpcc1000]> drop trigger t_test_AU_oak;
Query OK, 0 rows affected (0.00 sec)

(testing)root@localhost [tpcc1000]> drop trigger t_test_AD_oak;
Query OK, 0 rows affected (0.00 sec)

6. 如果原表是有触发器的可以将触发器恢复过来,如果无,删除临时表就结束了。
(testing)root@localhost [tpcc1000]> desc t_test;
+-------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+----------------+
| id | int(9) | NO | PRI | NULL | auto_increment | 
| o_id | int(11) | NO | | NULL | | 
| name | varchar(9) | NO | | | | 
+-------+------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

  

posted @ 2014-07-29 23:36 SMALL-D 阅读(...) 评论(...)  编辑 收藏