如何优雅简单的备份远程数据库到本机？

多个 vps 的数据库授权一个 vps(A)访问，打算在 vps(A)运行 python ，把其余的 vps 数据库都备份到 vps （ A ）

* 首先， mysqldump 好像只能备份到该数据库所在的 vps 上，不开 ftp 的情况下可有方法拉回来？

* 在python中使用 sql 语句取数据，如表字段（ DESC user ），表数据（ SELECT * FROM user ），然后再处理这些数据成备份格式的 sql 文件，略嫌麻烦。

各位大大，还有其他好的方法吗？

Supplement 1 · May 30, 2016

只要在 vps(A)上运行 python 就可以拉回来至 vps A ，其他 vps 不作另外处理。

大概 50 个 vps ，每个数据库的表量不大，几十 KB 左右，日后 vps 数量还会持续增加

Supplement 2 · May 30, 2016

没想到引发战争，实在抱歉😥。我觉得提出一个思路就好了，至于优劣，能罗列也是极好，没必要引战，以上个人愚见。总之还是谢谢大家的指点。☺️☺️

VPS

数据库

SQL

user

60 replies • 2016-06-01 08:51:38 +08:00

lslqtz

May 30, 2016 via iPhone

通过脚本导出数据库，然后另一台机子也搞一个：我一般是计划任务运行一个脚本，然后导出后 post 到另一台机子。

clino

May 30, 2016

dump 以后 rsync?

alex321

May 30, 2016

crontab 中 mysqldump 到 vps 的 dropbox 同步目录，搞定。
或者 crontab 中增加一个 scp 到 A 。。。

zyqf

May 30, 2016 via Android

@lslqtz 机子有点多，一个个部署实在麻烦。

zyqf

May 30, 2016 via Android

@alex321
@clino

额，我的意思是：只要在 vps(A)上运行 python 就可以拉回来。其他 vps 不作另外处理。

zyqf

May 30, 2016 via Android

补充下，大约 50 个 vps 。

lslqtz

May 30, 2016 via iPhone

@zyqf 从 A 上用脚本连远程数据库，存进文件夹，打包文件夹， post 到远程。

hujianxin

May 30, 2016

fabric 可以试试。

clino

May 30, 2016

你所谓的拉回来是拉到 vps A 上还是你本地的 PC 上?

loading

May 30, 2016 via Android

主从数据库，就是比较慢。

别搞错

zyqf

May 30, 2016 via Android

@clino 拉到 vps A

zyqf

May 30, 2016 via Android

@lslqtz 好，晚上试试

xfwduke

May 30, 2016

mysqldump 肯定可以远程备份的
开个有权限的账号就行了

alex321

May 30, 2016

@zyqf 那就 A 上 crontab 50 个远程 mysqldump 到 A ，然后打包到 dropbox 目录，要拉取文件的机器上装个 dropbox ；或者在要拉取备份文件的机器上开个计划任务，定期 scp A 的 mysql 备份打包文件。。
话说这东西，我 dropbox 就在跑，备份着 4 台机器的数据的数据库和一台机器的 web 文件；另有一个七牛的 bucket 也在跑数据库备份和 web 文件。。。。

cxbig

May 30, 2016

- 在 A 上用 ssh tunnel ，远程执行 dump 命令，打包传输一次性解决，或分开先打包成文件，再用 scp 拖回 A 机器。
- 如果这些 vps 都是内网，可以将 A 机的某个文件夹 mount 到各 vps ，在 vps 上做 cronjob 定时 dump ，随后 A 机直接操作文件。

O3YwA1ENkb7i35XJ

May 30, 2016

@zyqf 你听谁说的 mysqldump 只能备份到数据库所在的服务器?
mysqldump 这个程序在哪台机器上运行, 数据就是备份到哪台机器上的.

按你描述的内容,你直接在 vps(A) 上执行

mysqldump -C --all-databases -h VPS1_IP -u root -p > VPS1.sql

mysqldump -C --all-databases -h VPS2_IP -u root -p > VPS2.sql

就可以把 VPS1/VPS2 上的数据库备份到你当前的 vps(A) 上了.

O3YwA1ENkb7i35XJ

May 30, 2016

上面那些回复的人, 我都怀疑他们是否有认真看清楼主的描述, 以及是否用过 mysqldump ?

zyqf

May 30, 2016 via Android

@xqin mysqldump 好像只能备份到该数据库所在的 vps 上，不开 ftp 的情况下可有方法拉回来？

用了好像，因为百度了下，没有找到相关信息，也许关键词不对。我不太肯定

zyqf

May 30, 2016 via Android

@xqin 实在谢谢，这个命令对现有的代码来讲是最简单的

O3YwA1ENkb7i35XJ

May 30, 2016

@zyqf 另外目前的命令是要手工输入密码的, 但是也可以通过参数来指定密码.
格式为:

mysqldump.exe --all-databases -h 8.8.8.8 -C -u root --password=这里写 mysql 账号的密码 > backup.sql

这样在备份的时候就不用手工输入密码了.

zyqf

May 30, 2016 via Android

@xqin 十分感激，我晚上回去用电脑实验下，楼上的方法我也会参考的，谢谢各位的建议😊😊

des

May 30, 2016

数据库版本一致的情况下，直接复制二进制文件最快

zyqf

May 30, 2016 via Android

@des 然而版本在 5.3-5.6 间波动😂😂

qooweds

May 30, 2016

远程修改 crontab,加一条命令就行了,50 台机器就是一个循环的事.
fabric 应该也可以吧

goodryb

May 30, 2016

@xqin 这么激动干啥，这么简单的问题处理起来方法多了，前面的人也没说不能远程 dump 吧
照你这么说，你知道楼主的 VPS 是一家服务商的吗？就算是一家服务商，内网互通吗？假如内网不互通，要是 VPS 的 mysql 没有公网访问，你这个方法能用吗？

开放条件下，什么可能都有，为啥这么急躁呢。
我倒是觉得 15 楼
@cxbig 的第二个方法最简单

wingtatlee

May 30, 2016

又開始吵架的節奏...😄

cxbig

May 30, 2016

@xqin
- 远程使用 mysqldump 势必要在 server 端开启访问端口和设置账户密码和 IP ，这是很危险的。
- 另外 3306 端口是不加密的，把明文密码放在这种远程命令里更是危险。
请不要误导他人

O3YwA1ENkb7i35XJ

May 30, 2016

@goodryb 你说 @cxbig 的方法好, 说明你还没明白楼主目前的处境,
楼主是可以登陆 vsp(A), 且 vps(A) 是可以访问他所要备份数据库的那些机器的.
所以不存在开什么 ssh tunnel, 楼主直接 ssh 登陆 vps(A) 然后执行 mysqldump 就可以把其他的那 50 台机器的数据库备份至 vps(A).

@cxbig 你根本就没明白楼主的环境, 他要备份的那几十台机器的 3306 只有 vps(A)可以访问得到.
另外 3306 加不加密, 我不知道, 我也没研究过.

密码只是做为参数传递给 mysqldump 这个命令, 至于 mysqldump 在向 mysql 服务器交互的时候是不是加密的是由 mysql 远程访问协议所决定的.

另外你的 3306 端口不加密的依据是哪来的? 是谁在误导人?

O3YwA1ENkb7i35XJ

May 30, 2016

@goodryb 你第一段中描述的那些内容已经说明你没有认真看楼主的问题.
我再帮他重复一遍.
```
多个 vps 的数据库授权一个 vps(A)访问，打算在 vps(A)运行 python ，把其余的 vps 数据库都备份到 vps （ A ）

* 首先， mysqldump 好像只能备份到该数据库所在的 vps 上，不开 ftp 的情况下可有方法拉回来？
```

goodryb

May 30, 2016

@xqin 你这么抠细节，那你看看楼主的补充里面
“只要在 vps(A)上运行 python 就可以拉回来至 vps A ，其他 vps 不作另外处理。”，
那我能不能说 “你没有认真看楼主的问题.” ？

毫无意义，这里只是讨论方法，用不用楼主自己选，不要动不动就放地图炮

O3YwA1ENkb7i35XJ

May 30, 2016

@goodryb 难道我回复的方法, 需要在其他的 vps 上做处理吗?

goodryb

May 30, 2016

@xqin

“只要在 vps(A)上——运行 python ——就可以拉回来至 vps A ，其他 vps 不作另外处理。”，

你根本就没明白楼主的环境，楼主就是要用 python

O3YwA1ENkb7i35XJ

May 30, 2016

@goodryb 哈哈哈哈, 笑死我了.
都有不需要 python 的方法了, 不是更省事了? 强拉 python 来为自己做辩护有意思吗?

O3YwA1ENkb7i35XJ

May 30, 2016

@goodryb 另外楼主考虑用 python 的原因, 就是因为他用百度搜索 mysqldump 的用法之后,
以为 mysqldump 把备份后的数据保存至 mysql 的服务器上(即其他的 vps 上),
才考虑用 python 来写代码解决.

现在已经告诉楼主他百度出来的结果是错的, 所以就根本不存在继续写 python 代码来备份数据这一说了.

goodryb

May 30, 2016

@xqin 你是楼主开的马甲？楼主的情况你都知道？挖个坑你就真的跳进去了？

楼主哪里告诉你
“考虑用 python 的原因, 就是因为他用百度搜索 mysqldump 的用法之后,
以为 mysqldump 把备份后的数据保存至 mysql 的服务器上(即其他的 vps 上),
才考虑用 python 来写代码解决.”

这个问题，楼主不明白，楼上的 V 友都不懂？

“上面那些回复的人, 我都怀疑他们是否有认真看清楼主的描述, 以及是否用过 mysqldump ?”
还要专门一个回复来个反问句？你想干嘛，是不是楼上的 V 友给你道歉认错？

O3YwA1ENkb7i35XJ

May 30, 2016

@goodryb 不需要道歉.

我专门回复的那句话, 就是在问这些人, 是否真的理解楼主的问题, 是否真的有用过 mysqldump ?

楼主说 mysqldump 备份的数据是在远程的 mysql 服务器, 那就真的是在远程的 mysql 服务器上?

真正用过 mysqldump 的人, 会不知道这个数据是备份在哪的?

GG668v26Fd55CP5W

May 30, 2016 via iPhone

@goodryb 潜台词：我不是针对谁，而是说在座的各位都是 XX ，哈哈哈哈

O3YwA1ENkb7i35XJ

May 30, 2016

@cxbig MySQL 协议分析
http://hutaow.com/blog/2013/11/06/mysql-protocol-analysis/

请拿出证据证明你所描述的说法:
"- 另外 3306 端口是不加密的，把明文密码放在这种远程命令里更是危险。 "

goodryb

May 30, 2016

@xqin 你说的这个方法， 13 楼不是早就回复了？你认真看过楼上的回复么？

另外：
技术上的问题，就从技术上来讨论，就算是楼上众 V 友没用过 mysqldump ，也是再为楼主想办法，闻道有先后，不知道也没什么，你知道，告诉他正确的方法就是，能帮助别人也是一种快乐。

但是不要在回答问题的时候使用这种反问句来质问别人，不想上升到个人素质修养层面。

如果你请教别人一个问题，别人回答了之后，还要加上一个反问句“这都不知道？”，你心里舒服吗？

goodryb

May 30, 2016

@falcon05 你算是 Get 到 G 点了

O3YwA1ENkb7i35XJ

May 30, 2016

@goodryb 我没别的意思, 我的反问只是想提醒他们, 错误的方向不仅帮助不了别人, 更会误导别人.

另外你说的 13 楼是有说过, 他说的 `mysqldump 肯定可以远程备份的 `, 对楼主有什么帮助?

我回复的更详细些不好吗?

goodryb

May 30, 2016

@xqin 没说你详细不好，上一个回复已经说的很清楚了“你知道，告诉他正确的方法就是，能帮助别人也是一种快乐”

13 楼的回复是很简略，如果楼主有心跟进，继续追问或者自己搜索就是，不管百度还是 google ，“ mysqldump+远程备份”都能搜出无数答案。

可以做伸手党，但是不能一直做伸手党
授人以鱼不如授人以渔
这些道理我相信大家都懂，但是我们更需要的是大气谦和的态度。

PS ：
“我的反问只是想提醒他们, 错误的方向不仅帮助不了别人, 更会误导别人.”
回复某人的时候请先 @他，否则，你的回复，楼上这些 V 友不会有任何提示，仅此而已。

cxbig

May 30, 2016

@xqin 这些远程通信安不安全基于配置， LZ 没有说明他强化过这些配置，而你提供的 mysqldump 命令也没有指定 --ssl 之类的申明，我当然会理解为不安全。

likuku

May 30, 2016

能接受瞬断 mysql 的话，可以
瞬间锁表 /关闭 mysql + 数据分区 fsfreeze ＋数据分区作快照

数据分区解冻 + mysql 释放表锁 /启动 mysql

将数据分区快照里的数据目录整个 rsync 到远程备份机 (定期进行的话，由于 rsync 是差异传输，也并不慢)

likuku

May 30, 2016

mysql mysqldump 也都支持 ssl 连接

O3YwA1ENkb7i35XJ

May 30, 2016

@cxbig 不指定 --ssl 参数也不代表不安全, 且根据那篇文章中的分析 mysql 协议本身就有认证机制, 且密码也不是明文传输. 所以你后面的我误导人的说法不成立.

@goodryb :)

RIcter

May 30, 2016

nfs 呀（

cxbig

May 30, 2016

@xqin 你以为不明文传输就抓不到？有协议机制也要确保开启才行。你可以去搜搜
“ using wireshark to sniff mysql passwords ”

http://forums.mysql.com/read.php?30,372341,374096#msg-374096

O3YwA1ENkb7i35XJ

May 30, 2016

@cxbig 你有看你发的连接的日期吗?

cxbig

May 30, 2016

@xqin 这种硬抬杠没太大意义。毕竟我不知道 LZ 怎么搭的环境、怎样的安全配置，至少我就绝不会用这种可能被黑的方法。

O3YwA1ENkb7i35XJ

May 30, 2016

@cxbig mysql 的抓包数据分析.
http://wenku.baidu.com/view/62b5516869eae009581becec.html

MySQL 认证协议
http://www.bitscn.com/pdb/mysql/201407/226725.html

得到用户名是可能的, 因为用户名是明文传输的, 但密码不是.

cxbig

May 30, 2016

@xqin
我并没有说密码是明文，如果在通信时没有指定加密方式，它很有可能是 SHA1 加密，而 scramble 也是很容易拿到的。破解 SHA1 似乎已不是难事。
SHA256 是 5.6 才有，而且不是默认开启。
另，不指定加密方式，那么其他数据库交互的内容多半也是明文传输的。

kanezeng

May 30, 2016

要求不高的话，找台机器装 Navicat ，连上远程 MySql 后，可以设定时备份到本地，前提是本地机器开着。

wclebb

May 30, 2016

什么时候优雅成了懒惰的代名词了……

msg7086

May 31, 2016

只有我是用的 MariaDB 多来源复制吗。

jellybool

May 31, 2016

1. ssh
2. mysqldump
3. git 仓库管理导出的数据库文件
4. 本机脚本实现 git pull
5. mac 定时执行 shell

我现在就是使用 mac 的日历，每天定时三个时间点备份。

holajamc

May 31, 2016

刚刚好最近写了一个
http://holajelly.cc/?p=114

lumen

May 31, 2016

通过 ssh 远程执行 mysqldump 命令，在远程用 bzip2 压缩
重定向到本地的 .bz2 文件：
ssh USER@HOST "mysqldump --all-databases --single-transaction | bzip2 " > /path/to/local/file.HOST.sql.bz2

这样一个命令就搞定了远程、加密、压缩、存储
写成脚本，通过配置文件来添加机器及数据库就好

xfwduke

Jun 1, 2016

额... 想不到还被当典型了-_-
因为看到 po 主说 mysqldump 不能远程备份,觉得他应该知道怎么用,所以也没想到要详细点

在要求比较严格的生产环境, 确实不太建议用 mysqldump 做远程备份.
这里有 2 个说法
1. 如果远程机器和 db 机器都是内网, 远程备份的速度一般没本地快. 而备份时候对 db 的影响还是不小的 --- 主要是负载
2. 如果走外网, 让 mysql 监听到外网 ip, 这肯定是不对的

用 mysqldump 在 db 机器本地做备份. 考虑到 db 机器本身可能损坏导致数据丢失, 所以备份的结果肯定需要放到异机器保留
这个 MySQL 自己没这样的功能, 需要做一些周边系统的开发. 一般会做出有这样功能的支持系统
1. 在本地做 mysqldump 备份
2. 自动用各种可选的文件传输协议( ssh, ftp, 网络磁盘) 把备份放到异机器
3. db 本地以磁盘空间做参考, 考虑保留最近的 N 份备份
4. 远程机器保留更长的 M 份 (M>N)
5. db 机器还需要把 binlog 做远程备份, 保留的时间段至少要 >=M

再回过头来说 mysqldump
这个工具如果不加任何参数, 是需要有 lock 权限, 对于生产环境是没法用的. 但是只要添加了适当的参数, 权限就只需要 select

通常, 在使用 innodb 引擎的 MySQL 上, mysqldump 的命令行参数是

mysqldump -u${USER} -p${PWD} --default-character-set=${CHARSET} --single-transaction --skip-opt -q --create-options -R ${DB1} ${DB2} ... | gzip > backup_result_file.sql.gz

解释下几个重要的
1. --single-transaction 对于 innodb 引擎会建立全局一致性事务, 保证所有表都处于相同的时间状态
2. --skip-opt 关闭备份前获取全局锁
3. -q 禁止 client 缓存结果, 这个在本地备份场景极其重要, 防止因为数据过多, mysqldump 占用过多内存引起 OOM
4. 字符集指定, 这个非常重要. 通常如果无法确定 db 字符集或者字符集有多种兼容性差的组合----如同时又 GBK 和 UTF-8, 那么选用 BINARY 会比较好

其他的随便看看 -help 就好了

xfwduke

Jun 1, 2016

追加解释下
对于 myisam 引擎, 无论如何都会需要 lock, 否则备份出来的数据没有意义 --- mysqldump 好像不管怎么组合参数, 遇到 myisam 表都会自己去加个 lock table