MySQL 数据库定时同步

一、核心目标

将 源服务器 192.168.1.100 上的所有 MySQL 数据库，通过 mysqldump 导出，并实时导入到 目标服务器 192.168.1.200 中，实现数据同步。

二、前置条件

1. 网络连通性：确保目标服务器 192.168.1.200 可以通过 SSH 连接到源服务器 192.168.1.100。
2. 权限：
   • 在 192.168.1.100 上，拥有可以通过 SSH 登录的用户（user）和密码。
   • 在 192.168.1.100 上，拥有 MySQL 的 root 或具有全局读取权限的用户密码（mypassword）。
   • 在 192.168.1.200 上，拥有 MySQL 的 root 或具有全局写入权限的用户密码（mypassword）。
3. 工具安装：
   • 192.168.1.100 上必须安装 mysqldump 工具（通常在 mysql-client 包中）。
   • 192.168.1.200 上必须安装 mysql 客户端工具。

三、操作步骤（在目标服务器 192.168.1.200 上执行）

1. 配置 SSH 免密登录（关键，用于自动化）

为了让定时任务（如 cron）能自动执行，需要配置从目标服务器到源服务器的免密 SSH 登录。

# 在目标服务器 192.168.1.200 上执行
# 1. 生成 SSH 密钥对（如果还没有）
ssh-keygen -t rsa

# 2. 将公钥复制到源服务器 192.168.1.100
#    执行后会提示输入源服务器 user 用户的密码
ssh-copy-id user@192.168.1.100

# 3. 验证免密登录是否成功
#    如果能直接登录，无需输入密码，则配置成功
ssh user@192.168.1.100 "hostname"

2. 执行同步命令

在 目标服务器 192.168.1.200 的命令行中，执行以下核心同步命令。

# 注意：此命令在目标服务器 192.168.1.200 上执行
ssh user@192.168.1.100 '/home/db/mysql/product/bin/mysqldump -uroot -pmypassword -P 3306 --single-transaction --set-gtid-purged=OFF --all-databases' | mysql -uroot -pmypassword -P 3306

四、命令参数详解

参数	作用
ssh user@192.168.1.100 '...'	通过 SSH 远程登录到源服务器 192.168.1.100，并执行单引号内的命令。
/home/db/mysql/product/bin/mysqldump	mysqldump 命令的绝对路径，确保在任何环境下都能被正确找到。
-uroot	指定 MySQL 的用户名，这里是 root。
-pmypassword	指定 MySQL 的密码。注意：-p 和密码之间没有空格。
-P 3306	指定 MySQL 的端口号，这里是默认的 3306。
--single-transaction	（核心参数） 对 InnoDB 存储引擎，此参数会创建一个事务快照，实现热备份，备份期间不锁表，对线上业务影响极小。
--set-gtid-purged=OFF	（关键参数） 如果源或目标服务器开启了 GTID（全局事务ID），此参数可以避免因 GTID 集合冲突导致的导入失败。它会阻止 mysqldump 在备份文件中写入 GTID 相关声明。
--all-databases	指定导出源服务器上的所有数据库。
|	管道符。它将左侧 ssh 命令的所有输出（即 .sql 备份文件的内容），作为右侧 mysql 命令的输入。这避免了生成中间文件，非常高效。
mysql -uroot -pmypassword -P 3306	在本地服务器（即 192.168.1.200）上执行的 mysql 命令，它会读取从管道传来的 SQL 语句并执行，从而完成数据导入。

五、重要提示与最佳实践

1. 安全警告：在命令行中明文写密码（-pmypassword）会被记录在命令历史中，存在安全风险。强烈建议使用 ~/.my.cnf 配置文件来存储数据库密码。

   • 在源服务器 192.168.1.100 的 ~/.my.cnf 中添加：

     [mysqldump]
     user = root
     password = mypassword
     port = 3306

   • 在目标服务器 192.168.1.200 的 ~/.my.cnf 中添加：

     [mysql]
     user = root
     password = mypassword
     port = 3306

   • 配置后，命令可简化为：ssh user@192.168.1.100 '/home/db/mysql/product/bin/mysqldump --single-transaction --set-gtid-purged=OFF --all-databases' | mysql

2. 自动化：此命令非常适合放入 crontab 中，实现每日定时备份或同步。例如，每天凌晨 2 点执行：

   0 2 * * * ssh user@192.168.1.100 '/home/db/mysql/product/bin/mysqldump --single-transaction --set-gtid-purged=OFF --all-databases' | mysql >> /var/log/mysql_sync.log 2>&1

3. 日志记录：建议将命令的输出和错误信息重定向到日志文件（如 >> /var/log/mysql_sync.log 2>&1），以便于问题排查。

---