mysql在不同实例中迁移表方法

 

mysql在不同实例中迁移表方法

mariadb官网参考说明：

https://mariadb.com/kb/en/innodb-file-per-table-tablespaces/#copying-transportable-tablespaces

 

需求描述：mysql在不同数据之间迁移表
源数据库：192.168.142.238 dbserver.tbl_name
目标数据库：192.168.142.239 db239.tbl_name

第一步：192.168.142.238锁表
root@MariaDB0 18:40:  [dbserver]> FLUSH TABLES tbl_name FOR EXPORT;
Query OK, 0 rows affected (0.047 sec)

# 把数据拷贝一份
[root@db238 dbserver]# ll tbl_name.*
-rw-rw---- 1 mysql mysql     1203 Mar 13 17:23 tbl_name.frm
-rw-rw---- 1 mysql mysql 41943040 Mar 13 17:24 tbl_name.ibd
[root@db238 dbserver]# cp tbl_name.ibd /tmp/
# 192.168.142.238解锁
root@MariaDB0 18:41:  [dbserver]> UNLOCK TABLES;
Query OK, 0 rows affected (0.041 sec)

第二步：192.168.142.239
root@MariaDB0 18:44:  [db239]> CREATE TABLE `tbl_name` (
  `uid` int(11) NOT NULL,
  `total` int(11) DEFAULT NULL,
  `rankTitle` varchar(50) DEFAULT NULL,
  `perct` decimal(3,1) DEFAULT NULL,
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

root@MariaDB0 18:44:  [db239]> ALTER TABLE tbl_name DISCARD TABLESPACE;
# 从源数据服务器上传输数据到目标机器相应目录
[root@db238 tmp]# scp /tmp/tbl_name.ibd root@192.168.142.239:/data/mysql/data/db239/
[root@vm-es-01 db239]# chown mysql.mysql tbl_name.ibd 
root@MariaDB0 18:44:  [db239]>  ALTER TABLE tbl_name IMPORT  TABLESPACE;
Query OK, 0 rows affected, 1 warning (0.237 sec)

root@MariaDB0 18:46:  [db239]> select * from tbl_name limit 1;
+-----+-------+--------------------+-------+
| uid | total | rankTitle          | perct |
+-----+-------+--------------------+-------+
|   0 | 10000 | 宇宙好大学         |   1.0 |
+-----+-------+--------------------+-------+
1 row in set (0.002 sec)

root@MariaDB0 18:47:  [db239]>