如何最高效地从远程 mysql 上拷贝一个 table？

[mornlight]

机器A在公网，机器B在内网，客观原因限制，B可访问A，A不能访问B。A的数据库里有某table，大约几万行，B作为后台服务器，要做到从内网拿资源的同时从A中拿数据，再执行业务。

问：如何每次执行业务时最高效地让B拥有一个table的拷贝，存入B自己的mysql中？不是主从备份，不是实时一致，大概每15分钟会执行一次。

我自己原先想的方法：

不考虑存入B的mysql中，而是每次执行时程序都从A中select * ，运行结束就没了。但怕几万行数据可能量太大，通过网络传输会太慢而且占了A的带宽资源。

不知各位V友有何建议？有价值的回复必感谢。

[suckli]

不知道直接使用 rsync 同步数据表文件是否可行~

[qsun]

federated table

https://dev.mysql.com/doc/refman/5.0/en/federated-use.html

[Livid]

如果是文本数据的话，SELECT 出来之后，用 gzip 压缩一下。

比如可以在 A 上有个 web service，提供这几万行数据，然后用 NGINX 反代这个 web service 并设置 gzip on。

或者，每 15 分钟在 A 上写入到一个 txt 中，并 git push，然后从 B 上 git pull。如果是文本数据并且每次变化不大的话，就只需要传输压缩过的变化了的部分了。

[mornlight]

@suckli B机器上的mysql一直在运行，直接替换文件会不会产生麻烦？

[mornlight]

@qsun 试了一下，蛮好用的功能，感谢

[mornlight]

@Livid 我需要存到数据库里，这样用文本传输会不会绕的弯太大了点

[ETiV]

我现在外面跑的异地备份脚本是:

ssh database "/usr/bin/mysqldump -u${REMOTE_DB_USER} -p${REMOTE_DB_PASS} ${REMOTE_DB} ${REMOTE_TABLE} | /bin/bzip2 -zqc --best" | bunzip2 | /usr/bin/mysql -u${DB_USER} -p${DB_PASS} ${DB}

只有一行.
意思是, ssh 登录到 database, 而后执行 dump > bzip, 并输出到 stdout.
输出出来的内容会被执行脚本的服务接收进 stdin, 然后由 bunzip 直接解压缩出来, 再塞回 mysql 服务器.

由于是备份用的, 多数跑在凌晨三四点, 所以效率不需要很高.

如果你需要高效率, 根据mysql手册, 使用 LOAD DATA INFILE, 性能更快. 不过可能需要传输文件, 可以根据上面的脚本改一改.

* 当从一个文本文件装载一个表时，使用LOAD DATA INFILE。这通常比使用很多INSERT语句快20倍。参见13.2.5节，“LOAD DATA INFILE语法”。

http://dev.mysql.com/doc/refman/5.1/zh/optimization.html
7.2.16. INSERT语句的速度

[mornlight]

@ETiV 学习了，感谢。我现在在尝试使用2楼federated引擎的方法，把两台机器数据库放一起来，备份也方便些。因为A机器是centos，B机器是win server，在B上放个脚本备份就可以了。