mysql如何配置远程备份数据库_设置SSH隧道与自动化脚本-mysql教程-PHP中文网

不能直接设 bind_address=0.0.0.0，因会暴露3306端口引发安全风险；应使用ssh隧道加密转发（如3307端口），配合mysqldump --single-transaction等参数安全备份，并通过mysql_config_editor管理凭据、set -e保障脚本健壮性。

mysql如何配置远程备份数据库_设置ssh隧道与自动化脚本

MySQL 远程备份为什么不能直接开 `bind_address=0.0.0.0`？

因为绝大多数云服务器或生产环境的 MySQL 实例，根本没打算对外暴露 3306 端口——防火墙拦着、安全组卡着、MySQL 自身也默认只监听本地。硬开远程访问，等于把数据库裸奔扔到公网，ERROR 1045 (28000) 都是小事，扫库、勒索、删库都可能一夜间发生。

真正安全的做法是绕过公网暴露，用 SSH 隧道建立加密通道，让本地 mysqldump 像连本机一样连远程库。

隧道端口（比如 3307）只在本地生效，不被外部扫描到
认证走 SSH 密钥，不依赖 MySQL 密码明文传输
即使 MySQL 用户权限最小化（只给 SELECT + LOCK TABLES），也能完成一致备份

怎么建 SSH 隧道并用 `mysqldump` 备份？

核心就一条命令：先起隧道，再 dump。别用 ssh -f -N 后台挂起再手动跑 dump——容易因隧道断开导致备份截断或锁表超时。

推荐用单次阻塞式隧道，保证 dump 完成后才释放连接：

ssh -L 3307:127.0.0.1:3306 user@remote-host -N -o ExitOnForwardFailure=yes -o ConnectTimeout=10 &
sleep 2
mysqldump -h 127.0.0.1 -P 3307 -u dbuser -p'dbpass' --single-transaction --routines --triggers dbname > backup.sql
kill %1

-L 3307:127.0.0.1:3306 表示把远程服务器的 3306 映射到本地 3307；注意远程端的 127.0.0.1 是指它自己，不是你本地
ExitOnForwardFailure=yes 很关键——如果端口被占或网络不通，SSH 直接失败，不会静默假连接
--single-transaction 对 InnoDB 有效，避免全库锁；但 MyISAM 表仍会锁，得看存储引擎
备份完立刻 kill %1，别留僵尸隧道，否则下次脚本执行会报 bind: Address already in use

自动化脚本里怎么处理密码和错误恢复？

别把 MySQL 密码写进命令行，ps aux 能直接看到；也别放配置文件里还 chmod 不对——mysqldump 会优先读 ~/.my.cnf，但要求权限必须是 600，否则报错 Warning: World-writable config file。

MySQL

编写正确的MySQL查询，避免字符集、索引和锁方面的常见陷阱。

下载

更稳妥的是用 mysql_config_editor 存加密凭据：

mysql_config_editor set --login-path=remote-backup --host=127.0.0.1 --port=3307 --user=dbuser --password

然后 mysqldump --login-path=remote-backup dbname 就行。注意：--login-path 指向的是本地隧道端口，不是远程地址。

脚本开头加 set -e，任何命令失败立即退出，防止隧道没起来就 dump
用 if ! ssh -o ConnectTimeout=5 -o BatchMode=yes user@remote-host echo ok > /dev/null; then exit 1; fi 提前探测 SSH 可达性
备份文件名带上 $(date +\%Y\%m\%d_\%H\%M)，别用 $(date)——空格和冒号在某些 shell 下会出问题

备份大库时 `mysqldump` 卡住或 OOM 怎么办？

不是所有“卡住”都是网络问题。如果表特别大（比如几百 GB 的单表），mysqldump 默认把整个结果集缓存在内存里再写磁盘，容易触发本地内存不足，mysqldump 进程被系统 kill，日志里只留个 Killed。

两个硬核解法：

加 --skip-extended-insert：每行 INSERT 独立，减少单次输出缓冲压力（但文件体积变大，还原稍慢）
用 --tab=/path/to/dir 导出为纯文本（CSV 格式），配合 SELECT ... INTO OUTFILE，完全绕过客户端内存缓冲——但要求 MySQL 有写入该路径的权限，且不能跨服务器导出
终极方案：改用 mydumper，支持多线程、断点续传、压缩，对百 GB 级备份友好得多；不过它不走 SSH 隧道，得配好远程 MySQL 的最小权限账号 + SSL 加密

最常被忽略的一点：SSH 隧道本身也有超时，默认 ServerAliveInterval 是 0。长备份下建议在 ~/.ssh/config 里加 ServerAliveInterval 60，不然连接静默断开，dump 就停在半路。