mysql在不同实例中迁移表方法
mariadb官网参考说明:
需求描述: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]>
标签:name,18,db239,实例,tbl,mysql,迁移,root
来源: https://www.cnblogs.com/bjx2020/p/12533918.html