拷贝ibd实现MySQL的数据导入

官方手册介绍：

<a href="http://dev.mysql.com/doc/refman/5.7/en/tablespace-copying.html" target="_blank">http://dev.mysql.com/doc/refman/5.7/en/tablespace-copying.html</a>

<a href="http://dev.mysql.com/doc/refman/5.7/en/innodb-transportable-tablespace-examples.html" target="_blank">http://dev.mysql.com/doc/refman/5.7/en/innodb-transportable-tablespace-examples.html</a>

实验环境:

  阿里云主机（双核4G，普通云盘，2个节点都是yum安装的mysql-community-server5.6  ）

  db1.table1的大小（ibd文件大小 1.2GB 备份出的sql文件大小为750MB）

test1节点上:

1

2

3

4

5

6

7

<code>&gt; use db1;</code>

<code>&gt; FLUSH TABLE table1 FOR EXPORT;</code>

<code># cd /var/lib/mysql/db1</code>

<code># scp table1.ibd table1.cfg root@test2:/root/   # 因为是内网传输，因此这步花费不到1分钟的时间</code>

<code>&gt; UNLOCK tables;</code>

test2节点上：

8

9

10

<code>&gt; truncate table table1;</code>

<code>&gt; alter table table1 discard tablespace;</code>

<code># cp /root/table1.* /var/lib/mysql/db1/</code>

<code># chown mysql.mysql /var/lib/mysql/db1/ -R</code>

<code>&gt; alter table table1 </code><code>import</code> <code>tablespace;   </code><code># 这一步花费了45s</code>

<code>&gt; </code><code>select</code> <code>count(*) from table1;   可以看到数据全部导入了</code>

上面的这种方法导数据全部过程花费了3分钟吧。

而同样的数据，测试使用sql文件导入的花费的时间：  10分钟。  二者高下立判。

附后来写的一个传输并导入远程主机的脚本：

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

<code>#!/bin/bash</code>

<code># Description: 简单粗暴的将表空间文件传输到其他节点,并实现导入操作,线上使用出问题后果自行承担</code>

<code># Date: 2017/01/08</code>

<code># Author: Lee</code>

<code>LOG=</code><code>"/tmp/trans.log"</code>

<code>LOCAL_USER=</code><code>'root'</code>

<code>LOCAL_PASS=</code><code>'123456'</code>

<code>DATADIR=</code><code>"/var/lib/mysql/"</code>

<code>MYSQL=</code><code>'/usr/bin/mysql'</code>

<code># step1 传输*.ibd *.cfg文件到其他节点 </code>

<code># 参数说明：</code>

<code># $1 数据库名称</code>

<code># $2 表名称</code>

<code># $3 目标主机的地址</code>

<code>if</code> <code>[ $</code><code># -eq 3 ]; then</code>

<code>  </code><code>$MYSQL -u$LOCAL_USER -p$LOCAL_PASS -e </code><code>"use $1; FLUSH TABLE $2 FOR EXPORT; system scp $DATADIR$1/$2.ibd $3:/root; system scp $DATADIR$1/$2.cfg $3:/root; UNLOCK TABLES;"</code> <code>&gt; </code><code>/dev/null</code> <code>2&gt;&amp;1</code>

<code>else</code>

<code>  </code><code>echo</code> <code>-e </code><code>"\n\033[31mUsage: sh $0 数据库名 表名 远程主机IP\033[0m\n"</code> <code>&amp;&amp; </code><code>exit</code> <code>1</code>

<code>fi</code>

<code>if</code> <code>[ $? -</code><code>eq</code> <code>0 ]; </code><code>then</code>

<code>  </code><code>echo</code> <code>-e </code><code>"\033[32m 传输到远程主机成功  $(date +"</code><code>%F %T</code><code>") \033[0m"</code> <code>| </code><code>tee</code> <code>-a $LOG</code>

<code>  </code><code>echo</code> <code>-e </code><code>"\033[32m 传输到远程主机失败  $(date +"</code><code>%F %T</code><code>") \033[0m"</code> <code>| </code><code>tee</code> <code>-a $LOG &amp;&amp; </code><code>exit</code> <code>10</code>

<code># step2 ssh 连接到远程节点，将拷贝的数据导入数据库</code>

<code>REMOTE_USER=</code><code>"root"</code>

<code>REMOTE_PASS=</code><code>"123456"</code>

<code>ssh</code> <code>root@$3 </code><code>"$MYSQL -u$REMOTE_USER -p$REMOTE_PASS -e 'truncate table $1.$2; alter table $1.$2 discard tablespace;system cp /root/$2.* /var/lib/mysql/$1/;system chown mysql.mysql -R /var/lib/mysql;alter table $1.$2 import tablespace;'"</code> <code>&gt; </code><code>/dev/null</code> <code>2&gt;&amp;1</code>

<code>  </code><code>echo</code> <code>-e </code><code>"\033[32m 导入到远程主机成功  $(date +"</code><code>%F %T</code><code>") \033[0m"</code> <code>| </code><code>tee</code> <code>-a $LOG</code>

<code>  </code><code>echo</code> <code>-e </code><code>"\033[32m 导入到远程主机失败  $(date +"</code><code>%F %T</code><code>") \033[0m"</code> <code>| </code><code>tee</code> <code>-a $LOG</code>

<code></code>

本文转自 lirulei90 51CTO博客，原文链接：http://blog.51cto.com/lee90/1890250，如需转载请自行联系原作者