MySQL主从同步搭建与数据备份配置

admin

MySQL主从同步搭建与数据备份配置指南

一、MySQL主从同步搭建

1. 环境准备

• 主服务器(Master): 192.168.1.100
  
• 从服务器(Slave): 192.168.1.101
  
• 确保两台服务器MySQL版本一致或兼容
  

2. 主服务器配置

修改MySQL配置文件(my.cnf或my.ini)

1. [mysqld]
2. server-id = 1
3. log_bin = mysql-bin
4. binlog_format = ROW  # 推荐使用ROW格式
5. binlog-do-db = your_database_name  # 可选，指定要同步的数据库
6. # binlog-ignore-db = mysql  # 可选，指定不同步的数据库

复制代码

创建同步专用用户

1. CREATE USER 'repl'@'%' IDENTIFIED BY 'repl_password';
2. GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
3. FLUSH PRIVILEGES;

复制代码

获取主服务器状态

1. SHOW MASTER STATUS;
2. -- 记录File和Position值，后续从服务器配置需要

复制代码

3. 从服务器配置

修改MySQL配置文件

1. [mysqld]
2. server-id = 2
3. relay-log = mysql-relay-bin
4. read_only = 1  # 设置为只读模式

复制代码

配置主从同步

1. CHANGE MASTER TO
2. MASTER_HOST='192.168.1.100',
3. MASTER_USER='repl',
4. MASTER_PASSWORD='repl_password',
5. MASTER_LOG_FILE='mysql-bin.000001',  -- 从主服务器获取的值
6. MASTER_LOG_POS=107;                 -- 从主服务器获取的值
8. START SLAVE;
9. SHOW SLAVE STATUS\G  -- 检查同步状态，确保Slave_IO_Running和Slave_SQL_Running都是Yes

复制代码

4. 验证主从同步

• 在主服务器上创建测试表或插入数据
  
• 检查从服务器是否同步了这些更改
  

二、MySQL数据备份配置

1. 逻辑备份(mysqldump)

全量备份

1. mysqldump -u username -p --all-databases --single-transaction > full_backup_$(date +%F).sql

复制代码

增量备份(结合二进制日志)

• 定期备份二进制日志文件
  
• 记录当前二进制日志位置
  

2. 物理备份(文件系统级)

使用Percona XtraBackup(推荐)

1. # 安装Percona XtraBackup
2. # 完整备份
3. innobackupex --user=username --password=password /path/to/backup/dir
5. # 准备备份(应用事务日志)
6. innobackupex --apply-log /path/to/backup/dir
8. # 恢复备份
9. innobackupex --copy-back /path/to/backup/dir

复制代码

3. 自动化备份脚本示例

1. #!/bin/bash
2. BACKUP_DIR="/backup/mysql"
3. DATE=$(date +%Y%m%d%H%M%S)
4. USER="backup_user"
5. PASS="backup_password"
7. # 创建备份目录
8. mkdir -p $BACKUP_DIR/$DATE
10. # 执行全量备份
11. mysqldump -u$USER -p$PASS --single-transaction --all-databases > $BACKUP_DIR/$DATE/full_backup.sql
13. # 备份二进制日志(可选)
14. MYSQL_BINLOG_FILE=$(mysql -u$USER -p$PASS -e "SHOW MASTER STATUS\G" | grep File | awk '{print $2}')
15. cp /var/lib/mysql/mysql-bin.* $BACKUP_DIR/$DATE/
17. # 清理旧备份(保留最近7天)
18. find $BACKUP_DIR -type d -mtime +7 -exec rm -rf {} \;

复制代码

4. 备份策略建议

• 全量备份：每周一次完整备份
  
• 增量备份：每天进行二进制日志备份
  
• 异地备份：将备份文件复制到远程服务器或云存储
  
• 测试恢复：定期测试备份文件的可恢复性
  

三、注意事项

• 安全性：
  
• 同步账户应限制访问IP
  
• 备份文件应加密存储
  
• 定期更换同步账户密码
  
• 性能考虑：
  
• 主从同步可能增加主服务器负载
  
• 大事务可能导致同步延迟
  
• 监控主从服务器的性能指标
  
• 故障处理：
  
• 定期检查主从同步状态
  
• 准备故障切换方案
  
• 记录并分析同步错误
  
• 版本兼容性：
  
• 主从服务器MySQL版本应兼容
  
• 升级时应先升级从服务器再升级主服务器
  

通过以上配置，您可以建立一个可靠的MySQL主从同步环境和数据备份策略，确保数据的安全性和可用性。