mysql表分区迁移_MySQL5.7传输表空间——迁移分区表

Mysql 传输表空间--将InnoDB分区表复制到另一个实例(二)

实验环境:(都是mysql5.7)

源库:192.168.2.200      mysql5.7.16    zhangdb下的emp_2分区表的

目标库:192.168.2.100    mysql5.7.18    test下  (将zhangdb的emp表,导入到目标库的test schema下)

--:在源数据库中创建测试分区表emp_2,然后导入数据

MySQL [zhangdb]> CREATE TABLE emp_2(

id BIGINT unsigned NOT NULL AUTO_INCREMENT,

x VARCHAR(500) NOT NULL,

y VARCHAR(500) NOT NULL,

PRIMARY KEY(id)

)

PARTITION BY RANGE COLUMNS(id)

(

PARTITION p1 VALUES LESS THAN (1000),

PARTITION p2 VALUES LESS THAN (2000),

PARTITION p3 VALUES LESS THAN (3000)

);

(接着创建存储过程,导入测试数据)

DELIMITER //

CREATE PROCEDURE insert_batch()

begin

DECLARE num INT;

SET num=1;

WHILE num < 3000 DO

IF (num%10000=0) THEN

COMMIT;

END IF;

INSERT INTO emp_2 VALUES(NULL, REPEAT('X', 500), REPEAT('Y', 500));

SET num=num+1;

END WHILE;

COMMIT;

END //

DELIMITER ;

mysql> select TABLE_NAME,PARTITION_NAME from information_schema.partitions where table_schema='zhangdb';

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

| TABLE_NAME | PARTITION_NAME |

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

| emp        | NULL           |

| emp_2      | p1             |

| emp_2      | p2             |

| emp_2      | p3             |

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

4 rows in set (0.00 sec)

mysql> select count(*) from emp_2 partition (p1);

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

| count(*) |

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

|      999 |

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

1 row in set (0.00 sec)

mysql> select count(*) from emp_2 partition (p2);

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

| count(*) |

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

|     1000 |

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

1 row in set (0.00 sec)

mysql> select count(*) from emp_2 partition (p3);

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

| count(*) |

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

|     1000 |

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

1 row in set (0.00 sec)

从上面可以看出,emp_2分区表已经创建完成,并且有3个子分区,每个分区都有一点数据。

--:在目标数据库中,创建emp_2表的结构,不要数据(要在源库,使用show create table emp_2\G 的方法 查看创建该表的sql)

MySQL [test]> CREATE TABLE `emp_2` (

`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

`x` varchar(500) NOT NULL,

`y` varchar(500) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=3000 DEFAULT CHARSET=utf8mb4

/*!50500 PARTITION BY RANGE  COLUMNS(id)

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

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

PARTITION p3 VALUES LESS THAN (3000) ENGINE = InnoDB) */ ;

[root@localhost test]# ll

-rw-r----- 1 mysql mysql 98304 May 25 15:58 emp_2#P#p0.ibd

-rw-r----- 1 mysql mysql 98304 May 25 15:58 emp_2#P#p1.ibd

-rw-r----- 1 mysql mysql 98304 May 25 15:58 emp_2#P#p2.ibd

注意:

※约束条件、字符集等等也必须一致,建议使用show create table t1; 来获取创建表的SQL,否则在新服务器上导入表空间的时候会提示1808错误。

--:在目标数据库上,丢弃分区表的表空间

MySQL [test]> alter table emp_2 discard tablespace;

Query OK, 0 rows affected (0.12 sec)

[root@localhost test]# ll    ---这时候在看,刚才的3个分区的idb文件都没有了

-rw-r----- 1 mysql mysql  8604 May 25 04:14 emp_2.frm

--:在源数据库上运行FLUSH TABLES … FOR EXPORT 锁定表并生成.cfg元数据文件,最后将cfg和ibd文件传输到目标数据库中

mysql> flush tables emp_2 for export;

Query OK, 0 rows affected (0.00 sec)

[root@localhost zhangdb]# scp emp_2* root@192.168.2.100:/mysql/data/test/   --将文件cp到目标数据库

mysql> unlock tables;   ---最后将表的锁是否

--:在目标数据库中对文件授权,然后导入表空间查看数据是否完整可用

[root@localhost test]# chown mysql.mysql emp_2#*

MySQL [test]> alter table emp_2 import tablespace;

Query OK, 0 rows affected (0.96 sec)

MySQL [test]> select count(*) from emp_2;

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

| count(*) |

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

|     2999 |

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

1 row in set (0.63 sec)

从上面的查看得知,分区表都已经导入到目标数据库中了,

另外,也可以将部分子分区导入到目标数据库中,(往往整个分区表会很大,可用只将需要用到的子分区导入到目标数据库中),

将部分子分区导入到目标数据库的方法是:

1、在创建目标表的时候,只需要创建要导入的分区即可,如: 只创建了p2 p3两个分区

CREATE TABLE `emp_2` (

`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

`x` varchar(500) NOT NULL,

`y` varchar(500) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=3000 DEFAULT CHARSET=utf8mb4

/*!50500 PARTITION BY RANGE  COLUMNS(id)

(

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

PARTITION p3 VALUES LESS THAN (3000) ENGINE = InnoDB) */

2、从源库cp到目标库的文件,当然也就是这俩的,就不需要其他分区的了,

3、其他的操作方法都一样了。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值