大虾们，你们的业务上线前，是不是能保证将来的大表都已经规划好分区了呢？如果有几个当时不可预见的漏网之鱼，随着这些业务表越来越大，无论是出于性能考虑还是归档历史数据，你或许会有一个需求：将普通表转换为分区表。如果是oracle，官方提供了在线重定义的包。那mysql官方没有该怎么办？
找老板要停机时间导出导入？
脑补下老板可能的反应：“啊呀呀呀~~~我的刀呢？”
不管是砍你，砍工资还是砍预算，都令人害怕！
好在天无绝人之路，老板刀在哪儿我管不了。
反正我也得有刀：
pt-online-schema-change
这个工具大家一定很熟悉了，它可以在线砍字段、干索引等。
除此之外，它真的可以在线重定义分区表。
下面我们来个小试牛刀。
操作系统版本：Red Hat Enterprise Linux Server release 6.6 (Santiago)
mysql版本：5.7.17
database: zhaoqing
table: test
mysql> show create table test\G
Table: test
Create Table: CREATE TABLE `test` (
`ROUND_ID` bigint(11) NOT NULL,
`USER_ID` bigint(11) NOT NULL,
`CREATE_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`ROUND_ID`,`USER_ID`,`CREATE_TIME`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
| 6752559 |
+----------+
1 row in set (1.10 sec)
计划按照CREATE_TIME字段作为分区键，按照月进行分区。
mysql> select min(CREATE_TIME),max(CREATE_TIME) from test;
+---------------------+-----------------
| min(CREATE_TIME) | max(CREATE_TIME) |
+---------------------+-----------------
| 2017-12-28 00:00:00 | 2018-01-04 10:23:40 |
+---------------------+-----------------
1 row in set (0.00 sec)
使用pt-online-schema-change工具，将test表按照CREATE_TIME字段按月转换为分区表，为了方便展示，加入以下参数：
--no-swap-tables
--no-drop-old-table
--no-drop-new-table
注意：不进行表的重命名和删除操作！！
分区的语句，见下文红色字体部分
pt-online-schema-change --charset=utf8 --user=root --socket=/tmp/mysql_3769.sock D=zhaoqing,t=test --recursion-method none --execute --no-swap-tables --no-drop-old-table --no-drop-new-table --alter "PARTITION BY RANGE ( UNIX_TIMESTAMP(CREATE_TIME))(PARTITION p201712 VALUES LESS THAN (1514736000) ENGINE = InnoDB, PARTITION p201801 VALUES LESS THAN (1517414400) ENGINE = InnoDB)"
实验过程中，并未对test表做dml操作，工具运行过程日志如下：
[test@test ~]$ pt-online-schema-change --charset=utf8 --user=root --socket=/tmp/mysql_3769.sock D=zhaoqing,t=test --recursion-method none --execute --no-swap-tables --no-drop-old-table --no-drop-new-table --alter "PARTITION BY RANGE ( UNIX_TIMESTAMP(CREATE_TIME))(PARTITION p201712 VALUES LESS THAN (1514736000) ENGINE = InnoDB, PARTITION p201801 VALUES LESS THAN (1517414400) ENGINE = InnoDB)"
No slaves found. See --recursion-method if host mysqlbackup has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
analyze_table, 10, 1
copy_rows, 10, 0.25
create_triggers, 10, 1
drop_triggers, 10, 1
swap_tables, 10, 1
update_foreign_keys, 10, 1
Altering `zhaoqing`.`test`...
Creating new table...
Created new table zhaoqing._test_new OK.
Altering new table...
Altered `zhaoqing`.`_test_new` OK.
2018-01-04T19:09:13 Creating triggers...
2018-01-04T19:09:13 Created triggers OK.
2018-01-04T19:09:13 Copying approximately 6181140 rows...
Copying `zhaoqing`.`test`: 23% 01:35 remain
Copying `zhaoqing`.`test`: 48% 01:02 remain
Copying `zhaoqing`.`test`: 71% 00:36 remain
Copying `zhaoqing`.`test`: 94% 00:07 remain
2018-01-04T19:11:33 Copied rows OK.
Not dropping old table because --no-drop-old-table was specified.
2018-01-04T19:11:33 Dropping triggers...
2018-01-04T19:11:33 Dropped triggers OK.
Not dropping the new table `zhaoqing`.`_test_new` because --no-drop-new-table was specified. To drop the new table, execute:
DROP TABLE IF EXISTS `zhaoqing`.`_test_new`;
Successfully altered `zhaoqing`.`test`.
运行成功后，查看新旧两张表的表结构和行数：
mysql> show tables;
+--------------------+
| Tables_in_zhaoqing |
+--------------------+
| _test_new |
| test |
+--------------------+
2 rows in set (0.00 sec)
原表test为非分区表
mysql> show create table test\G
Table: test
Create Table: CREATE TABLE `test` (
`ROUND_ID` bigint(11) NOT NULL,
`USER_ID` bigint(11) NOT NULL,
`CREATE_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`ROUND_ID`,`USER_ID`,`CREATE_TIME`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
新表_test_new为分区表：
mysql> show create table _test_new\G
Table: _test_new
Create Table: CREATE TABLE `_test_new` (
`ROUND_ID` bigint(11) NOT NULL,
`USER_ID` bigint(11) NOT NULL,
`CREATE_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`ROUND_ID`,`USER_ID`,`CREATE_TIME`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE ( UNIX_TIMESTAMP(CREATE_TIME))
(PARTITION p201712 VALUES LESS THAN (1514736000) ENGINE = InnoDB,
PARTITION p201801 VALUES LESS THAN (1517414400) ENGINE = InnoDB) */
1 row in set (0.00 sec)
两张表行数相同：
mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
| 6752559 |
+----------+
1 row in set (1.10 sec)
mysql> select count(*) from _test_new;
+----------+
| count(*) |
+----------+
| 6752559 |
+----------+
1 row in set (1.12 sec)
再来看看ibd文件，_test_new#P#p201712.ibd和_test_new#P#p201801.ibd 为_test_new表的两个分区，大小加起来等于test.ibd
[test@test zhaoqing]$ ll
total 3653672
-rw-r----- 1 mysql mysql 61 Dec 18 18:19 db.opt
-rw-r----- 1 mysql mysql 9626 Jan 4 10:27 test.frm
-rw-r----- 1 mysql mysql 1870659584 Jan 4 10:42 test.ibd
-rw-r----- 1 mysql mysql 9626 Jan 4 19:09 _test_new.frm
-rw-r----- 1 mysql mysql 1027604480 Jan 4 19:16 _test_new#P#p201712.ibd
-rw-r----- 1 mysql mysql 843055104 Jan 4 19:16 _test_new#P#p201801.ibd
注意分区必须能够包含普通表中的所有数据，否则将报错1526 Table has no partition for value xxxxx;
如过程中不能容忍主从较高的延迟，可用参数--max-lag --check-interval --check-slave-lag来控制。
若在过程中不能容忍较高负载，可用参数--chunk-size --max-load --critical-load 等来控制，避免对整个系统造成不可控影响。
肿么样，是不是很棒？
用pt-online-schema-change
轻松解决在Mysql在线重定义分区表
