MySQL数据库分区修改【原创】

之前有个表分区添加时s201607添加成s201617，所以在查询7月份数据时报错

错误的

alter table statistics_ticket add partition (partition s201617 values less than (201607));

正确的

alter table statistics_ticket add partition (partition s201607 values less than (201607));

因为range的分区没法直接修改，只能先删除最后分区至要修改的分区，在重新添加分区，再添加数据。

 

具体操作步骤：

1）完整表备份

mysqldump -u root -p'***' -t databasename tablename > /home/statistics_ticket_20160819_1800.sql &

 

2）7、8月表数据备份不导出结构

 

mysqldump -u root -p'***' -t databasename tablename --where="FLIGHT_DATE between '2016-07-01' and '2016-08-31'" > /home/statistics_ticket_20160819_0708_data_1800.sql &

 

在备机上进行完整备份

 

/home/backup/script/basebackup.bash

 

 

3）先统计表有多少行

select count(*) from statistics_ticket where FLIGHT_DATE between '2016-07-01' and '2016-07-31';

select count(*) from statistics_ticket where FLIGHT_DATE between '2016-07-01' and '2016-08-31';

 

4）删除分区

 

测试

alter table statistics_ticket drop partition s201901;

 

alter table statistics_ticket drop partition s201617;

                                                                                 

alter table statistics_ticket drop partition s201608;                                    

alter table statistics_ticket drop partition s201609;                                    

alter table statistics_ticket drop partition s201610;                                    

alter table statistics_ticket drop partition s201611;                                    

alter table statistics_ticket drop partition s201612;                                    

alter table statistics_ticket drop partition s201701;                                    

alter table statistics_ticket drop partition s201702;                                    

alter table statistics_ticket drop partition s201703;                                    

alter table statistics_ticket drop partition s201704;                                     

alter table statistics_ticket drop partition s201705;                                    

alter table statistics_ticket drop partition s201706;                                    

alter table statistics_ticket drop partition s201707;                                    

alter table statistics_ticket drop partition s201708;                                    

alter table statistics_ticket drop partition s201709;                                     

alter table statistics_ticket drop partition s201710;                                    

alter table statistics_ticket drop partition s201711;                                    

alter table statistics_ticket drop partition s201712;                                     

alter table statistics_ticket drop partition s201801;                                    

alter table statistics_ticket drop partition s201802;                                    

alter table statistics_ticket drop partition s201803;                                    

alter table statistics_ticket drop partition s201804;                                    

alter table statistics_ticket drop partition s201805;                                    

alter table statistics_ticket drop partition s201806;                                    

alter table statistics_ticket drop partition s201807;                                    

alter table statistics_ticket drop partition s201808;                                    

alter table statistics_ticket drop partition s201809;                                    

alter table statistics_ticket drop partition s201810;                                    

alter table statistics_ticket drop partition s201811;                                     

alter table statistics_ticket drop partition s201812;                                    

alter table statistics_ticket drop partition s201901;

 

在查看分区添加是否成功

show create table statistics_ticket\G;

 

 

5）添加分区

alter table statistics_ticket add partition (partition s201607 values less than (201607));

 

alter table statistics_ticket add partition (partition s201608 values less than (201608));

alter table statistics_ticket add partition (partition s201609 values less than (201609));

alter table statistics_ticket add partition (partition s201610 values less than (201610));

alter table statistics_ticket add partition (partition s201611 values less than (201611));

alter table statistics_ticket add partition (partition s201612 values less than (201612));

alter table statistics_ticket add partition (partition s201701 values less than (201701));

alter table statistics_ticket add partition (partition s201702 values less than (201702));

alter table statistics_ticket add partition (partition s201703 values less than (201703));

alter table statistics_ticket add partition (partition s201704 values less than (201704));

alter table statistics_ticket add partition (partition s201705 values less than (201705));

alter table statistics_ticket add partition (partition s201706 values less than (201706));

alter table statistics_ticket add partition (partition s201707 values less than (201707));

alter table statistics_ticket add partition (partition s201708 values less than (201708));

alter table statistics_ticket add partition (partition s201709 values less than (201709));

alter table statistics_ticket add partition (partition s201710 values less than (201710));

alter table statistics_ticket add partition (partition s201711 values less than (201711));

alter table statistics_ticket add partition (partition s201712 values less than (201712));

alter table statistics_ticket add partition (partition s201801 values less than (201801));

alter table statistics_ticket add partition (partition s201802 values less than (201802));

alter table statistics_ticket add partition (partition s201803 values less than (201803));

alter table statistics_ticket add partition (partition s201804 values less than (201804));

alter table statistics_ticket add partition (partition s201805 values less than (201805));

alter table statistics_ticket add partition (partition s201806 values less than (201806));

alter table statistics_ticket add partition (partition s201807 values less than (201807));

alter table statistics_ticket add partition (partition s201808 values less than (201808));

alter table statistics_ticket add partition (partition s201809 values less than (201809));

alter table statistics_ticket add partition (partition s201810 values less than (201810));

alter table statistics_ticket add partition (partition s201811 values less than (201811));

alter table statistics_ticket add partition (partition s201812 values less than (201812));

alter table statistics_ticket add partition (partition s201901 values less than (201901));

 

 

在查看分区添加是否成功

show create table statistics_ticket\G;

 

 

导入数据

source /home/

 

看统计表有多少行是否和之前的一致

select count(*) from statistics_ticket where FLIGHT_DATE between '2016-07-01' and '2016-07-31';

select count(*) from statistics_ticket where FLIGHT_DATE between '2016-07-01' and '2016-08-31';

 

6）让用户测试