MySQL partition table with tables already in the data table

1. Partitioning of existing tables due to large amount of data

Operation mode.
You can use ALTER TABLE to make changes to a partitioned table, which creates a partitioned table, then automatically copy the data and then delete the original table.
Guessing server resource consumption is relatively large.

ALTER TABLE tbl_rtdata PARTITION by RANGE (Month (fld_date))
(
PARTITION p_apr VALUES Less THAN (to_days (' 2012-05-01 ')),
PARTITION P_may VALUES Less THAN (to_days (' 2012-06-01 ')),
PARTITION p_dec VALUES less THAN MAXVALUE);
**

2 Create a new partition table that is the same as the original table, then export the data from the original table and pour it into the new table. **

(The original table primary key only has ID, and my partition field is Stsdate, where the primary key is to be modified to id,stsdate Federated primary key, partition table requires partition field if primary key or part of primary key)

Operation Process
Use the second option. First create the partition table, then export the original table data, the new table name to the original table name, and then insert, and finally establish a normal index.

Create a partitioned table

CREATE TABLE ' apdailysts_p ' (' id ' int (one) not null auto_increment, ' ap_id ' int (one) not NULL, ' Mac ' VARCHAR (+) not NUL L, ' liveinfo ' longtext not null, ' Livetime ' INT (one) not null, ' stsdate ' DATE not null, ' lastmodified ' DATETIME not NUL     L, PRIMARY KEY (' id ', ' stsdate ')) PARTITION by RANGE COLUMNS (stsdate) (PARTITION p0 VALUES less THAN (' 2016-06-01 '), PARTITION P1 values less THAN (' 2016-07-01 '), PARTITION P2 values less THAN (' 2016-08-01 '), PARTITION P3 values Less THAN (' 2016-09-01 '), PARTITION P4 values less THAN (' 2016-10-01 '), PARTITION P5 values less THAN (' 2016-11-01 ')  , PARTITION P6 values less THAN (' 2016-12-01 '), PARTITION P7 values less THAN (' 2017-01-01 '), PARTITION P8 values Less THAN (' 2017-02-01 '), PARTITION p9 values less THAN (' 2017-03-01 '), PARTITION p10 values less THAN (' 2017-05-01 '), PARTITION p11 values less THAN (' 2017-06-01 '), PARTITION p12 values less THAN (' 2017-07-01 '), PARTITION p13 V Alues less THAN (' 2017-08-01 '), PARTITION p14 values less THAN (' 2017-09-01 '), PARTITION p15 values less THAN MAXVALUE); 

Exporting data

mysqldump -u dbname -p --no-create-info dbname apdailysts  > apdailysts.sql

Modify the table name, import data (10 minutes to import finished, 200w, 8g a little more data), test, delete the original table.

The test can be used normally and observed for several days.

MySQL partition table with tables already in the data table