mysql分区表备份_MySQL 分区表原理及数据备份转移实战

mysql>SELECT*FROM`t1`WHERE`uid`=89757AND`atime`EXPLAINPARTITIONSSELECT*FROM`t1`WHERE`uid`=89757AND`atime`

同时也要注意，进行表分区以后，mysql存放的数据文件夹中该表的存放文件也被拆分为多个-rw-r-----1mysqlmysql8.7K2月1414:49t1.frm

-rw-r-----1mysqlmysql36M2月1414:50t1#P#p0.ibd

-rw-r-----1mysqlmysql64M2月1414:50t1#P#p1.ibd

-rw-r-----1mysqlmysql92M2月1414:50t1#P#p2.ibd

-rw-r-----1mysqlmysql64M2月1414:50t1#P#p3.ibd

实际生产环境中，大多是采用另外一种方式:新建一个和原来表一样的分区表，然后把数据从原表导出，接着导入新表，最后建立普通索引。

mysql>CREATETABLE`t2`(

`id`int(10)unsignedNOTNULLAUTO_INCREMENTCOMMENT'表主键',

`pid`int(10)unsignedNOTNULLCOMMENT'产品ID',

`price`decimal(15,2)NOTNULLCOMMENT'单价',

`num`int(11)NOTNULLCOMMENT'购买数量',

`uid`int(10)unsignedNOTNULLCOMMENT'客户ID',

`atime`datetimeNOTNULLCOMMENT'下单时间',

`utime`int(10)unsignedNOTNULLDEFAULT0COMMENT'修改时间',

`isdel`tinyint(4)NOTNULLDEFAULT'0'COMMENT'软删除标识',

PRIMARYKEY(`id`,`atime`)

)

PARTITIONBYRANGECOLUMNS(atime)(

PARTITIONp0VALUESLESSTHAN('2016-01-01'),

PARTITIONp1VALUESLESSTHAN('2016-02-01'),

PARTITIONp2VALUESLESSTHAN('2016-03-01'),

PARTITIONp3VALUESLESSTHAN('2016-04-01'),

PARTITIONp4VALUESLESSTHAN('2016-05-01'),

PARTITIONp5VALUESLESSTHAN('2016-06-01'),

PARTITIONp6VALUESLESSTHAN('2016-07-01'),

PARTITIONp7VALUESLESSTHAN('2016-08-01'),

PARTITIONp8VALUESLESSTHAN('2016-09-01'),

PARTITIONp9VALUESLESSTHAN('2016-10-01'),

PARTITIONp10VALUESLESSTHAN('2016-11-01'),

PARTITIONp11VALUESLESSTHAN('2016-12-01'),

PARTITIONp12VALUESLESSTHAN('2017-01-01'),

PARTITIONp13VALUESLESSTHAN('2017-02-01'),

PARTITIONp14VALUESLESSTHAN('2017-03-01'),

PARTITIONp15VALUESLESSTHAN('2017-04-01'),

PARTITIONp16VALUESLESSTHAN('2017-05-01'),

PARTITIONp17VALUESLESSTHAN('2017-06-01'),

PARTITIONp18VALUESLESSTHAN('2017-07-01'),

PARTITIONp19VALUESLESSTHAN('2017-08-01'),

PARTITIONp20VALUESLESSTHAN('2017-09-01'),

PARTITIONp21VALUESLESSTHAN('2017-10-01'),

PARTITIONp22VALUESLESSTHAN('2017-11-01'),

PARTITIONp23VALUESLESSTHAN('2017-12-01'),

PARTITIONp24VALUESLESSTHAN('2018-01-01'),

PARTITIONp25VALUESLESSTHAN('2018-02-01'),

PARTITIONp26VALUESLESSTHAN('2018-03-01'),

PARTITIONp27VALUESLESSTHAN('2018-04-01'),

PARTITIONp28VALUESLESSTHAN('2018-05-01'),

PARTITIONp29VALUESLESSTHAN('2018-06-01'),

PARTITIONp30VALUESLESSTHAN('2018-07-01'),

PARTITIONp31VALUESLESSTHAN('2018-08-01'),

PARTITIONp32VALUESLESSTHAN('2018-09-01'),

PARTITIONp33VALUESLESSTHAN('2018-10-01'),

PARTITIONp34VALUESLESSTHAN('2018-11-01'),

PARTITIONp35VALUESLESSTHAN('2018-12-01'),

PARTITIONp36VALUESLESSTHANMAXVALUE

);

注:表主键只有id，而分区字段是atime, 这里主键要修改为 id,stsdate 联合主键，分区表要求分区字段要是主键或者是主键的一部分!!!

mysql>EXPLAINPARTITIONSSELECT*FROM`t2`G;

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

id:1

select_type:SIMPLE

table:t2

partitions:p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15,p16,p17,p18,p19,p20,p21,p22,p23,p24,p25,p26,p27,p28,p29,p30,p31,p32,p33,p34,p35,p36

type:ALL

possible_keys:NULL

key:NULL

key_len:NULL

ref:NULL

rows:1

filtered:100.00

Extra:NULL

1rowinset,2warnings(0.00sec)

*******************************************插入数据*************************************************

INSERTINTO`t2`(`pid`,`price`,`num`,`uid`,`atime`)SELECT`pid`,`price`,`num`,`uid`,`atime`FROM`t1`;

QueryOK,4194304rowsaffected(1min18.54sec)

Records:4194304Duplicates:0Warnings:0

或采用导出数据再导入数据，可再添加索引

mysqldump-udbname-p--no-create-infodbnamet2>t2.sql

修改表名，导入数据，测试下ok，删除原来的表。

2)使用LIST分区模式(如果原表存在主键强烈创建新表时，把原主键和要分区字段作为联合主键一并创建)

mysql>CREATETABLE`tb01`(

`id`int(10)unsignedNOTNULLAUTO_INCREMENTCOMMENT'表主键',

`pid`int(10)unsignedNOTNULLCOMMENT'产品ID',

`price`decimal(15,2)NOTNULLCOMMENT'单价',

`num`int(11)NOTNULLCOMMENT'购买数量',

`uid`int(10)unsignedNOTNULLCOMMENT'客户ID',

`atime`datetimeNOTNULLCOMMENT'下单时间',

`utime`int(10)unsignedNOTNULLDEFAULT0COMMENT'修改时间',

`isdel`tinyint(4)NOTNULLDEFAULT'0'COMMENT'软删除标识',

PRIMARYKEY(`id`,`num`)

);

*****************************插入测试数据******************************************************

INSERTINTO`tb01`(`pid`,`price`,`num`,`uid`,`atime`)SELECT`pid`,`price`,`num`,`uid`,`atime`FROM`tb`;

QueryOK,3145728rowsaffected(46.26sec)

Records:3145728Duplicates:0Warnings:0

mysql>ALTERTABLEtb01PARTITIONBYLIST(num)

(

PARTITIONpl01VALUESIN(1,3),

PARTITIONpl02VALUESIN(2,4),

PARTITIONpl03VALUESIN(5,7),

PARTITIONpl04VALUESIN(6,8),

PARTITIONpl05VALUESIN(9,10)

);

QueryOK,3145728rowsaffected(48.86sec)

Records:3145728Duplicates:0Warnings:0

存放mysql数据文件中生成，以下文件

-rw-r-----1mysqlmysql8.7K2月1511:35tb01.frm

-rw-r-----1mysqlmysql56M2月1511:36tb01#P#pl01.ibd

-rw-r-----1mysqlmysql32M2月1511:36tb01#P#pl02.ibd

-rw-r-----1mysqlmysql36M2月1511:36tb01#P#pl03.ibd

-rw-r-----1mysqlmysql36M2月1511:36tb01#P#pl04.ibd

-rw-r-----1mysqlmysql52M2月1511:36tb01#P#pl05.ibd

mysql>EXPLAINPARTITIONSSELECT*FROM`tb01`;

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

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

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

|1|SIMPLE|tb01|pl01,pl02,pl03,pl04,pl05|ALL|NULL|NULL|NULL|NULL|3136392|100.00|NULL|

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

1rowinset,2warnings(0.00sec)

3)COLUMNS分区

创建多列分区表tb02，这里两列都不是联合主键

mysql>CREATETABLEtb02(

->aintnotnull,

->bintnotnull

->)

->PARTITIONBYRANGECOLUMNS(a,b)(

->partitionp0valueslessthan(0,10),

->partitionp1valueslessthan(10,20),

->partitionp2valueslessthan(10,30),

->partitionp3valueslessthan(maxvalue,maxvalue)

->);

mysql>EXPLAINPARTITIONSSELECT*FROM`tb02`;#查看

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

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

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

|1|SIMPLE|tb02|p0,p1,p2,p3|ALL|NULL|NULL|NULL|NULL|1|100.00|NULL|

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

1rowinset,2warnings(0.00sec)

mysql>insertintotb02values(11,13);#手工插入测试数据

QueryOK,1rowaffected(0.01sec)

mysql>selectPARTITION_NAME,PARTITION_EXPRESSION,TABLE_ROWSfrominformation_schema.partitionswheretable_schema=schema()andtable_name='tb02';

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

|PARTITION_NAME|PARTITION_EXPRESSION|TABLE_ROWS|

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

|p0|`a`,`b`|0|

|p1|`a`,`b`|0|

|p2|`a`,`b`|0|

|p3|`a`,`b`|1|

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

4rowsinset(0.03sec)