Mysql Table Partitioning

To create a partitioned table:

DELIMITER// CREATE TABLE Vmmoniterdata (id_ bigint notNULLauto_increment, Vmid varchar (75)NULL, CPU varchar (75)NULL, Memory varchar (75)NULL, Bpsread varchar (75)NULL, Bpswrite varchar (95gNULL, intranetRX0 varchar (95gNULL, intranetRX1 varchar (95gNULL, intranetWX0 varchar (95gNULL, intranetWX1 varchar (95gNULL, TimeStamp datetimeNULL, PRIMARY KEY (id_,timestamp)) engine innodbpartition by RANGE (To_days (TimeStamp)) (PARTITION p1 VALUES Less THAN (To_days (' 20161101 ') , PARTITION p2 VALUES less THAN (to_days (' 20161102 ') , PARTITION P3 VALUES less THAN (to_days (' 20161103 ') , PARTITION P4 VALUES less THAN (to_days (' 20161104 ') , PARTITION P5 VALUES less THAN (to_days (' 20161105 ') , PARTITION P6 VALUES less THAN (to_days (' 20161106 ') , PARTITION P7 VALUES less THAN (to_days (' 20161107 ') , PARTITION P8 VALUES less THAN (to_days (' 20161108 ') , PARTITION p9 VALUES less THAN (to_days (' 20161109 ') , PARTITION p10 VALUES less THAN (to_days (' 20161110 ') ));//DELIMITER;

To add a partition table:

ALTER TABLE vmmoniterdata ADD PARTITION (PARTITION p315 VALUES less THAN (to_days (' 22190527 ')));

To view the table partitioning situation:

SELECT  partition_name part,   partition_expression expr,   partition_description descr,   table_ Rows from  information_schema.partitions WHERE  = SCHEMA () and   table_name

Add Partition Maximum Value

ALTER TABLE vmmoniterdata ADD PARTITION (PARTITION p11 VALUES less THAN maxvalue);

Automatically add partitions every day

Mysql Table Partitioning