MySQL数据库主从同步配置详解

MySQL主从同步的原理与作用

MySQL同步的核心机制依赖于二进制日志（binlog）的传递与应用，其基本流程如下：

1. 写入日志：主服务器（Master）将所有的变更事件（如更新、删除、表结构改变等）记录到二进制日志中。
2. 传输日志：从服务器（Slave）的IO线程连接主服务器，获取这些二进制日志内容，并在本地保存为中继日志（relay log）。
3. 重演操作：从服务器的SQL线程读取本地中继日志，并重放其中的变更事件，从而实现数据的一致性。

在实际应用中，主从同步主要具备以下价值：

• 热备份：作为有效的数据备份手段，且在备份期间不会阻断主服务器的服务。
• 读写分离：通过主库负责写入、从库负责读取的方式，均衡数据库负载。
• 高可用性与灾备：当主服务器发生故障时，可以迅速切换到从服务器以维持业务运行。

主从同步配置实操

第一步：准备环境与数据同步

在进行配置前，需确保主从数据库版本一致且尽量高于5.5版本。同时，应保证从库的数据与主库保持一致，以避免初始阶段出现严重的数据差异。

第二步：主数据库（Master）配置

首先修改 /etc/my.cnf 文件开启主从同步所需的基础功能：

# /etc/my.cnf
# 指定二进制日志文件路径与前缀
log-bin=/home/mysql/logs/binlog/bin-log
# 主数据库端唯一标识ID，不可重复
server-id = 1
# 单个binlog最大大小限制，超过此值自动分割
max_binlog_size = 500M
# 日志缓存大小
binlog_cache_size = 128K
# 指定需要同步的数据库（多行写多个）
binlog-do-db = adb
# 指定不同步的数据库
binlog-ignore-db = mysql
# Slave从Master读取日志时是否更新新日志，保持与主库一致
log-slave-updates
# binlog保留天数（MySQL 5.0以下不支持该参数）
expire_logs_day=2
# binlog格式设为MIXED，防止主键重复问题
binlog_format="MIXED"

关键参数说明：
server-id：必须唯一。同一组主从中该标识号不能重复，若配置文件中存在多个，以第一个为准。
log-bin：用于开启二进制日志功能，并指定目录和前缀名称。
binlog-do-db / binlog-ignore-db：分别控制只同步或不同步指定的数据库，支持添加多行进行定义。

完成配置后重启MySQL，并创建专用的同步账号：

# 授权slave账号对全库的复制权限
mysql> grant replication slave on *.* to 'slave'@'%' identified by '111111';
# 刷新权限使配置生效
mysql> flush privileges;

最后，查询主库当前状态并记录文件位置和偏移量，执行完毕后务必不要操作主库以免状态值变动：

mysql> show master status;
+------------------+----------+--------------+------------------+
| File    | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000009 |  196 |             |                  |
+------------------+----------+--------------+------------------+
1 row in set

第三步：从数据库（Slave）配置

进入从库服务器，同样编辑配置文件，但只需关注其唯一的标识：

# 从数据库端ID号，需与Master不同
server-id = 2

进入MySQL客户端，通过 change master to 语句建立从库与主库的连接关系，填入上一步获取的文件名和位置：

# 配置连接信息，替换为实际的主库IP、账号密码及同步坐标
mysql> change master to 
master_host='192.168.1.2',
master_user='slave',
master_password='111111',
master_log_file='mysql-bin.000009',
master_log_pos=196;

随后启动同步进程：

mysql> start slave;

执行以下命令检查同步状态：

mysql> show slave status\G;

观察输出结果中的关键字段：
Slave_IO_Running 和 Slave_SQL_Running 两项均必须显示为 Yes，否则表明同步失败。这两项通常也用于检测从服务器是否正常运行。

至此，基础的主从同步配置已完成。建议新建测试库表并进行插入、修改操作，验证数据是否能成功同步至从库。

常见维护与排错参数

在长期使用中，可根据需求调整相关参数：

Master端常用配置补充：

• sync_binlog：控制binlog刷盘频率（如每5次事务写入一次），可减少崩溃数据损失，但有一定性能损耗。
• expire_logs_days：统一设置日志文件的保留天数。

Slave端常用维护命令：

# 停止主从同步
mysql> stop slave;
# 修改断线重连超时时间（单位秒）
mysql> change master to master_connect_retry=50;
# 重新开启主从同步
mysql> start slave;

在实际开发中，可通过SQL脚本定期检查同步状态，例如判断 Slave_IO_Running 和 Slave_SQL_Running 是否为 'Yes'，以便及时发现并处理异常。

以上就是MySQL数据库设置主从同步的详细内容，更多关于MySQL数据库优化的资料请关注本站其它相关文章！