MySQL 主从复制环境搭建及物理备份恢复完整教程（基于 Percona XtraBackup）

MySQL 主从复制环境搭建及物理备份恢复完整教程（基于 Percona XtraBackup）

重要信息梳理

A服务器角色与环境

角色	主机名/标识	IP 地址	MySQL 监听端口	备注
主库	A服务器	192.168.100.60	4461	数据源，开启 binlog
从库	B服务器	192.168.100.168	默认端口或配置	用于复制和恢复备份

用户及权限创建

• backup 备份用户

  • 仅允许 localhost 登录。
  • 拥有权限：RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT, CREATE, INSERT, SELECT。
  • 适合用作备份账号，支持 XtraBackup 等物理备份工具。
  • 密码示例：backup_password

• repl 复制用户

  • 限定仅 192.168.100.168（从库B服务器）访问。
  • 仅赋予复制权限 REPLICATION SLAVE。
  • 密码示例：repl_password

• 权限刷新：

  FLUSH PRIVILEGES;
  

---

---

1. 在主库(A服务器)创建所需用户

1. 创建备份用户

CREATE USER 'backup'@'localhost' IDENTIFIED BY 'backup_password';

• 创建一个名为 backup 的用户，只允许从 localhost 登录。
• 登录密码为 'backup_password'。
• localhost 限制该用户只能从本机连接 MySQL。

GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'backup'@'localhost';

• 给 backup 用户授予以下权限：
  • RELOAD：允许执行 FLUSH 操作，如刷新日志等。
  • LOCK TABLES：允许使用 LOCK TABLES，常用于一致性备份。
  • PROCESS：允许查看所有线程（用于监控当前连接）。
  • REPLICATION CLIENT：允许查看主从复制状态。

GRANT CREATE, INSERT, SELECT ON *.* TO 'backup'@'localhost';

• 继续赋予备份用户以下权限：
  • CREATE：允许创建数据库对象（如表）。
  • INSERT：允许插入数据。
  • SELECT：允许查询数据。

📌 注意：有些备份工具如 mysqldump 可能还需 EVENT 权限（视你的具体使用而定）。

---

2. 创建复制用户

CREATE USER 'repl'@'192.168.100.168' IDENTIFIED BY 'repl_password';

• 创建一个名为 repl 的用户，仅允许从 IP 为 192.168.100.168 的主机连接。
• 用于配置 MySQL 的主从复制。

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.100.168';

• 给 repl 用户授予主从复制所需的权限：
  • REPLICATION SLAVE：允许该用户从主库读取二进制日志，用于同步数据。

---

3. 刷新权限

FLUSH PRIVILEGES;

• 通知 MySQL 立即重新加载用户权限。
• 在 MySQL 8.0 及以上，CREATE USER 和 GRANT 命令会自动生效，不一定需要这条命令，但加上是个好习惯。

2. 在A服务器执行全量备份

🧱 备份命令

xtrabackup --backup \
--user=backup \
--password=backup_password \
--target-dir=/home/environmentConfiguration/mysql/mysql_data_back \
--datadir=/home/environmentConfiguration/mysql/mysql-data

🔍 解析：

参数	说明
--backup	表示执行备份操作。
--user=backup	使用名为 backup 的 MySQL 用户执行备份操作（需要具有足够权限）。
--password=backup_password	登录 backup 用户所需的密码。
--target-dir=/home/environmentConfiguration/mysql/mysql_data_back	备份文件的存储路径，备份结果将保存到这个目录。
--datadir=/home/environmentConfiguration/mysql/mysql-data	指定 MySQL 数据实际存放的目录（通常是配置文件中的 datadir），用于找到需要备份的数据。

✅ 作用：

• 执行物理热备份，将 datadir 中的数据直接复制到 target-dir。
• 不会锁表或停止服务，适合线上环境。

问题：

InnoDB: Error number 24 means 'Too many open files'

• 这是在使用 xtrabackup 进行备份时，MySQL 尝试打开太多 .ibd 表空间文件导致的，系统默认的打开文件数限制不够。

原因分析：

MySQL 数据目录中有大量 .ibd 文件（独立表空间），而 xtrabackup 会尝试一次性打开很多文件进行扫描和备份。如果系统对某个进程允许打开的文件数太小，就会报错。

解决方法：

• 方法 1：临时调整当前 shell 的限制（适合测试）

ulimit -n 65535

然后再次运行 xtrabackup 命令。如果成功，说明就是这个问题。

• 方法 2：修改系统文件，永久增加 open files 限制

自行百度……

---

🧹 准备备份

xtrabackup --prepare --target-dir=/home/environmentConfiguration/mysql/mysql_data_back

🔍 解析：

参数	说明
--prepare	准备阶段，对刚才备份的数据进行恢复前处理。
--target-dir=...	指定刚才备份文件的存储目录。

✅ 作用：

• 将刚才物理备份下来的数据做一次恢复前“准备”：
  • 回滚未提交的事务（crash recovery）。
  • 应用 redo 日志。
• 准备完成后，备份文件才能用于恢复（复制回 datadir 并启动数据库）。

3. 传输备份文件到B服务器

将本地的 MySQL 备份目录内容完整、递归地同步到远程A服务器 192.168.100.168 的 /home/mysql/backUp/ 目录中，并显示传输进度。如果目标目录中有多余的旧文件，也会被删除。

📦 执行命令

rsync -avh --partial --info=progress2 --delete /home/environmentConfiguration/mysql/mysql_data_back/ root@192.168.100.168:/home/mysql/backUp/

🔍 参数逐项解析

参数	作用
rsync	高效、快速的远程同步工具。适合用于传输大量文件，如数据库备份。
-a	“归档模式” 等价于 -rlptgoD，表示递归复制、保留符号链接、权限、时间戳、用户/组、设备信息等。
-v	显示详细过程（verbose），便于观察进度与错误。
-h	以人类可读方式显示文件大小（如 1.1K, 234M）
--partial	如果中途传输中断，保留已传输的部分，下一次可断点续传。
--info=progress2	显示详细进度信息，包括整体进度和速率等。
--delete	如果目标目录中存在源目录中没有的文件，将删除目标目录中的这些文件，保持两个目录一致。小心使用。

📂 路径解释

路径	说明
/home/environmentConfiguration/mysql/mysql_data_back/	本地 MySQL 备份文件目录，结尾的 / 表示“目录的内容”，不包含此目录本身。
root@192.168.100.168:/home/mysql/backUp/	远程A服务器（A A服务器）的目标目录，将通过 SSH 登录 root 用户进行文件传输。

✅ 使用建议

• 确保你对目标目录具有 root 远程登录权限（或改成普通用户）；

• --delete 会删除目标目录中不在源目录的文件，一定要谨慎使用；

• 可以考虑加上 -z 开启压缩（如带宽有限时；）：

• 开启 -z 压缩后会消耗 CPU 性能，追求速度建议不开启 -z

  rsync -avz --partial ...
  

4. 在B服务器还原备份

4.1 停止B服务器 MySQL 服务

# 关闭正在运行的 MySQL 服务，防止恢复过程中数据库写入或文件冲突。
systemctl stop mysqld

4.2 清空B服务器的数据目录，包括 MySQL 等数据库

# 清空原有的数据目录，包括所有数据库、表、系统库等。
rm -rf /home/mysql/data/*

⚠️ 注意事项：

• 一定要确保备份完整无误再执行！
• rm -rf 是危险命令，路径写错可能导致误删其他目录。

4.3 使用 xtrabackup 还原数据

# 将备份数据文件恢复（拷贝）到 MySQL 的数据目录中
xtrabackup --copy-back --target-dir=/home/mysql/backUp --datadir=/home/mysql/data

参数说明：

• --copy-back：表示从备份目录将数据文件复制回指定数据目录。
• --target-dir=...：指定你的备份路径（即 xtrabackup 备份输出的位置）。
• --datadir=...：指定目标 MySQL 数据存放目录，恢复会复制数据到此处。

✅ 说明：XtraBackup 不会自己清空 datadir，所以前面的 rm -rf 是必要的。

4.4 授权

# 把 /home/mysql/data 目录下的所有文件和目录的所有者改为 mysql 用户和组
chown -R mysql:mysql /home/mysql/data

• MySQL 服务以 mysql 用户身份运行，必须有权限访问和修改数据文件。

• 如果不修改权限，可能出现 “permission denied” 启动失败。

5. 配置从库(B服务器)

# 修改A服务器的MySQL配置文件 my.cnf
[mysqld]
server_id=2                    # 确保与主库不同
relay_log=/var/log/mysql/relay-bin
read_only=1

6. 启动从库并配置复制

6.1 启动从库(B服务器) MySQL 服务

systemctl start mysqld

✅ 前提：数据目录已经使用 xtrabackup --copy-back 成功还原，并且权限设置正确。

6.2 获取备份的 binlog 位置

# 查看 XtraBackup 期间备份时的 binlog 位点信息
cat /home/mysql/backUp/xtrabackup_binlog_info

• 文件内容格式（举例）：

  mysql-bin.000010  534083413
  

• ✅ 说明：这是 MySQL 主库在你进行备份时对应的二进制日志文件名和位置，设置主从复制时必须用到，保证从库数据与主库同步起点一致。

6.3 配置主从复制

CHANGE MASTER TO
  MASTER_HOST='192.168.100.60',
  MASTER_PORT=4461,
  MASTER_USER='repl',
  MASTER_PASSWORD='replpassward',
  MASTER_LOG_FILE='mysql-bin.000010',
  MASTER_LOG_POS=534083413;

• 参数解释：

参数	含义
MASTER_HOST	原主库 IP 地址
MASTER_PORT	主库监听端口（这里是 4461）
MASTER_USER	用于复制的账户名（通常需要 REPLICATION SLAVE 权限）
MASTER_PASSWORD	对应账户的密码
MASTER_LOG_FILE	binlog 文件名，来自 xtrabackup_binlog_info
MASTER_LOG_POS	binlog 的位置，同样来自 xtrabackup_binlog_info

✅ 注意事项：

• 该命令设置后，会覆盖之前的复制配置（如有）。
• 必须保证主库已经开启了 binlog，并且没有被清理到该位置之前的数据。

6.4 启动复制线程

# 启动复制线程（包括 IO 线程和 SQL 线程）
START SLAVE;

✅ 执行成功后，从库开始从主库读取 binlog 并同步数据。

6.5 检查从库状态

# 查看当前从库状态，确认是否配置成功
SHOW SLAVE STATUS\G

• 重点查看以下字段：

字段	含义
Slave_IO_Running	IO 线程状态（应为 Yes）
Slave_SQL_Running	SQL 线程状态（应为 Yes）
Seconds_Behind_Master	与主库的延迟时间（应为 0 或很小）
Last_IO_Error / Last_SQL_Error	是否有报错

✅ 两个线程都为 Yes，且无错误，即为配置成功。