mysql 表改为分区表_MySQL普通表转换为分区表

博客文章除注明转载外，均为原创。zhangjing|

| 100 | dbwatcher |

| 150 | zj        |

| 200 | xiaozhang |

| 250 | lisi      |

| 300 | zhangsan  |

| 350 | mazi      |

+-----+-----------+

8 rows in set (0.02 sec)

开始进行转换

[root@mysql57 ~]# pt-online-schema-change h=localhost,D=test,t=tt --recursion-method none --execute --alter "PARTITION BY RANGE(id)  (

>     PARTITION p0 VALUES LESS THAN (100),

>     PARTITION p1 VALUES LESS THAN (200),

>     PARTITION p2 VALUES LESS THAN (300),

>     PARTITION p3 VALUES LESS THAN MAXVALUE)"

No slaves found.  See --recursion-method if host mysql57 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 `test`.`tt`...

Creating new table...

Created new table test._tt_new OK.

Altering new table...

Altered `test`.`_tt_new` OK.

2017-04-11T18:27:39 Creating triggers...

2017-04-11T18:27:39 Created triggers OK.

2017-04-11T18:27:39 Copying approximately 8 rows...

2017-04-11T18:27:39 Copied rows OK.

2017-04-11T18:27:39 Analyzing new table...

2017-04-11T18:27:39 Swapping tables...

2017-04-11T18:27:39 Swapped original and new tables OK.

2017-04-11T18:27:39 Dropping old table...

2017-04-11T18:27:39 Dropped old table `test`.`_tt_old` OK.

2017-04-11T18:27:39 Dropping triggers...

2017-04-11T18:27:39 Dropped triggers OK.

Successfully altered `test`.`tt`.

转换完成，检查转换结果

数据：

(dbwacher)root@localhost [test]> select * from tt partition(p1);

+-----+-----------+

| id  | name      |

+-----+-----------+

| 100 | dbwatcher |

| 150 | zj        |

+-----+-----------+

2 rows in set (0.00 sec)

(dbwacher)root@localhost [test]> select * from tt partition(p2);

+-----+-----------+

| id  | name      |

+-----+-----------+

| 200 | xiaozhang |

| 250 | lisi      |

+-----+-----------+

2 rows in set (0.00 sec)

(dbwacher)root@localhost [test]> explain  select * from tt where id=150;

+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |

+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

|  1 | SIMPLE      | tt    | p1         | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |

+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

1 row in set, 1 warning (0.00 sec)

最后看看转换后表结构如下：

(dbwacher)root@localhost [test]> show create table tt\G

*************************** 1. row ***************************

Table: tt

Create Table: CREATE TABLE `tt` (

`id` int(11) NOT NULL,

`name` varchar(20) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

/*!50100 PARTITION BY RANGE (id)

(PARTITION p0 VALUES LESS THAN (100) ENGINE = InnoDB,

PARTITION p1 VALUES LESS THAN (200) ENGINE = InnoDB,

PARTITION p2 VALUES LESS THAN (300) ENGINE = InnoDB,

PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */

1 row in set (0.00 sec)

至此使用pt-online-change-schema将普通表在线转换为分区表。

---The end

阅读(1630) | 评论(0) | 转发(0) |