mysql表空间传输_mysql之表空间传输

说明&＃xff1a;MySQL(5.6.6及以上)&＃xff0c;innodb_file_per_table开启。

1.1. 操作步骤&＃xff1a;

0. 目标服务器创建相同表结构

1. 目的服务器&＃xff1a; ALTER TABLE t DISCARD TABLESPACE;

2. 源服务器 &＃xff1a; FLUSH TABLES t FOR EXPORT;

3. 从源服务器上 拷贝t.ibd, t.cfg文件到目的服务器

4. 源服务器&＃xff1a; UNLOCK TABLES;

5. 目的服务器&＃xff1a; ALTER TABLE t IMPORT TABLESPACE;

1.2. 演示

将多实例的 [mysql5711] 中 burn_test 库下的test_purge表 &＃xff0c;传输到 [mysql57112]中 burn_test2 库下的test_purge表

1.2.1. 准备工作

1. 在 目标服务器 上创建表空间

-- 源服务器 [mysql5711]

mysql> select * from burn_test.test_purge;

&＃43;----&＃43;------&＃43;

| a | b |

&＃43;----&＃43;------&＃43;

| 1 | 10 |

| 3 | 30 |

| 4 | 40 |

| 5 | 50 |

| 6 | 60 |

| 7 | 70 |

| 8 | 80 |

| 10 | 100 |

&＃43;----&＃43;------&＃43;

8 rows in set (0.01 sec)

-- 目标服务器 [mysql57112]

--

-- test_purge在 目标服务器 上不存在&＃xff0c;先创建该表

mysql> CREATE TABLE &＃96;test_purge&＃96; (

&＃96;a&＃96; int(11) NOT NULL AUTO_INCREMENT,

&＃96;b&＃96; int(11) DEFAULT NULL,

PRIMARY KEY (&＃96;a&＃96;),

UNIQUE KEY &＃96;b&＃96; (&＃96;b&＃96;)

) ENGINE&＃61;InnoDB AUTO_INCREMENT&＃61;11 DEFAULT CHARSET&＃61;utf8mb4&＃xff1b;

Query OK, 0 rows affected (0.16 sec)

2. 创建完成后进行检查

#

# 目标服务器

#

[[email protected] burn_test_2]> ll | grep test_purge

-rw-r-----. 1 mysql mysql 8578 Mar 21 10:31 test_purge.frm # 表结构

-rw-r-----. 1 mysql mysql 57344 Mar 21 10:31 test_purge.ibd # 表空间&＃xff0c;需要通过 DISCARD 将表空间文件删除

ALTER TABLE test_purge DISCARD TABLESPACE; 的含义是 保留test_purge.frm 文件&＃xff0c; 删除test_purge.ibd

3. 通辟 discard 删除ibd文件

-- 目标服务器

mysql> alter table test_purge discard tablespace;

Query OK, 0 rows affected (0.04 sec)

mysql> show tables;

&＃43;-----------------------&＃43;

| Tables_in_burn_test_2 |

&＃43;-----------------------&＃43;

| test_backup1 |

| test_purge |

&＃43;-----------------------&＃43;

2 rows in set (0.00 sec)

mysql> select * from test_purge;

ERROR 1814 (HY000): Tablespace has been discarded for table ‘test_purge‘

[[email protected] burn_test_2]> ll | grep test_purge

-rw-r-----. 1 mysql mysql 8578 Mar 21 10:31 test_purge.frm

1.2.2. 导出表空间

1. 在源服务器上&＃xff0c;通辟 export 命令导出表空间(同时加读锁)

-- 源服务器

mysql> flush table test_purge for export; -- 其实是对这个表加一个读锁

Query OK, 0 rows affected (0.00 sec)

2. 将导出的 cfg文件 和 ibd文件 &＃xff0c; 拷贝到目标服务器 的数据库下

#

# 源服务器

#

[[email protected] burn_test]> ll | grep test_purge

-rw-r-----. 1 mysql mysql 462 Mar 21 10:58 test_purge.cfg # export后&＃xff0c;多出来的文件&＃xff0c;里面保存了一些元数据信息

-rw-r-----. 1 mysql mysql 8578 Mar 4 15:41 test_purge.frm

-rw-r-----. 1 mysql mysql 57344 Mar 5 15:28 test_purge.ibd

[[email protected] burn_test]> cp test_purge.cfg test_purge.ibd /data/mysql_data/5.7.11_2/burn_test_2/ # 拷贝表空间和cfg文件&＃xff0c;远程请使用scp(本地多实例演示&＃xff0c;这里的库名是不同的)

3. 导出表空间后&＃xff0c;尽快解锁

-- 源服务器

mysql> unlock tables; -- 尽快的解锁

Query OK, 0 rows affected (0.00 sec)

注意&＃xff1a;一定要先拷贝cfg和ibd文件&＃xff0c;然后才能unlock&＃xff0c;因为 unlock 的时候&＃xff0c; cfg文件会被删除

# 源服务器上的日志

[Note] InnoDB: Stopping purge # 其实stop purge&＃xff0c;找个测试的表 for export 即可

[Note] InnoDB: Writing table metadata to ‘./burn_test/test_purge.cfg‘

[Note] InnoDB: Table &＃96;burn_test&＃96;.&＃96;test_purge&＃96; flushed to disk

[Note] InnoDB: Deleting the meta-data file ‘./burn_test/test_purge.cfg‘ # unlock table后&＃xff0c;该文件自动被删除

[Note] InnoDB: Resuming purge # unlock后&＃xff0c;恢复purge线程

4. 在目标服务器上 修改 cfg文件和ibd文件的 权限

#

# 目标服务器

#

[[email protected] burn_test_2]> chown mysql.mysql test_purge.cfg test_purge.ibd

5. 在目标服务器上通辟 import 命令导入表空间

-- 目标服务器

--

mysql> alter table test_purge import tablespace; -- 导入表空间

Query OK, 0 rows affected (0.24 sec)

mysql> select * from test_purge; -- 可以读取到从源服务器拷贝过来的数据

&＃43;----&＃43;------&＃43;

| a | b |

&＃43;----&＃43;------&＃43;

| 1 | 10 |

| 3 | 30 |

| 4 | 40 |

| 5 | 50 |

| 6 | 60 |

| 7 | 70 |

| 8 | 80 |

| 10 | 100 |

&＃43;----&＃43;------&＃43;

8 rows in set (0.00 sec)

# error.log中出现的信息

InnoDB: Importing tablespace for table ‘burn_test/test_purge‘ that was exported from host ‘MyServer‘

注意&＃xff1a;

表的名称必须相同 &＃xff0c;经过上述测试&＃xff0c;库名可以不同

该方法也可以用于分区表的备份和恢复

原文&＃xff1a;https://www.cnblogs.com/andy6/p/9718889.html