---
title: "再次折腾Mysql主主同步配置,方案适合: Mysql5.1x-5.5x"
url: "https://yjyj.net/learn/centos/1756.html"
author: "小 虾 (生猛大蝦编辑部)"
published_at: "2012-12-31T10:50:14+08:00"
updated_at: "2013-01-12T08:32:43+08:00"
categories: "CentOS 学习"
tags: "CentOS教程, LNMP环境, MySQL数据库, VPS主机评测, 网页视觉设计"
summary: "本文手把手整理 MySQL 5.1.x-5.5.x 在 CentOS 5.6 上搭建主主同步的实战方案，覆盖同步用户创建、my.cnf 关键参数、双主互挂与自增冲突处理，适合小 VPS 扛流量高峰时做数据冗余与故障切换。"
source: "生猛大蝦 (https://yjyj.net)"
---

# 再次折腾Mysql主主同步配置,方案适合: Mysql5.1x-5.5x

> 来源：[生猛大蝦 (yjyj.net)](https://yjyj.net/learn/centos/1756.html) · 作者：小 虾 · 发布时间：2012-12-31

## 30 秒核心看点 (TL;DR)

- 双主同步核心是互设 replication 用户，并在两台机器上分别配置 server-id、binlog 与同步规则。
- 通过 auto_increment_offset 和 auto_increment_increment 避免双主写入时主键自增冲突，适合双向写入场景。
- 方案重点是让两台主机都能接收对方写入，提升可用性，但配置细节多，适合老版本 MySQL 环境。

放假了,无聊宅在家上网的人越来越多,这几天囧客圈的流量也渐渐大了起来,直至哥这台小巧的512MB ram VPS无法支撑,频频爆内存死机,还是不得不鄙视下香港的线路,5MB的小带宽,服务器还这么不耐操.
考虑这种流量高峰一般只有节假日才会出现,元旦+春节也就那么一个月的样子,为了这个升级VPS增加费用很不值得,有钱还不如多给老婆买点水果.
本着节约不浪费的精神,哥准备把另外一台LA的1GB ram VPS利用起来,话说pr机房最近真是越来越给力了,联通电信访问都舒畅无比,希望不要是临时现象,继续给力下去吧!
说实话选方案的时候哥真是纠结无比,一个用了10几年win系统的纯洁又善良的美工,让哥来搞centos真是肉体跟精神上的双重折磨,好在有广大技术牛人分享自己的方法,虾爷真是跪谢!
nginx反代就不考虑了,并发一大,HK的脆弱小vps照样会爆内存,wp用了supercache也一样,东哥写的cos-html-cache很牛逼,但是我的url结构不支持,囧!
言归正传,还是苦逼的研究Mysql主主同步配置吧,虽然折腾人,但是好在绿色环保,搞定了也安心一点.

下面给出配置方案:


**需求: MYSQL主主同步**

**0、环境描述**
 

```
　　服务器A(主) 192.168.1.10
　　服务器B(主) 192.168.1.20
　　Mysql版本：5.1.x - 5.5.x
　　System OS：CentOS 5.6 X32
　　主从需同步的数据库内容保持一致
```

 


**1、MYSQL主主同步配置过程**

需要配置同步的数据库: data

------------------------------------------------------------------------------------------

a)创建同步用户
　　在主服务器上为从服务器建立一个连接帐户(datauser)，该帐户必须授予REPLICAITON SLAVE权限。
　　这里服务器A和服务器B互为主从，所以都要分别建立一个同步用户。

　　服务器A (192.168.1.10) :

```
grant replication slave on *.* to 'datauser'@'192.168.1.20' identified by '123456789';
flush privileges;
```

 

　　服务器B (192.168.1.20) :

```
grant replication slave on *.* to 'datauser'@'192.168.1.10' identified by '123456789';
flush privileges;
```

 

------------------------------------------------------------------------------------------

b). 修改 /etc/my.cnf 配置文件,为其添加以下内容:

　　服务器A (192.168.1.10) :

```
[mysqld]
server-id=1 #设置一个id
#innodb_flush_log_at_trx_commit=2 #这个不用了可以删掉
log-bin=mysql-bin #mysql二进制日志
#需要记录进制日志的数据库
binlog-do-db=data #需要记录进制日志的数据库.如果有多个数据库可用逗号分隔,或者使用多个binlog-do-db选项
binlog-ignore-db=mysql #不需要记录进制日志的数据库.如果有多个数据库可用逗号分隔,或者使用多个binlog-do-db选项
#需要同步的数据库
replicate-do-db=data #需要进行同步的数据库.如果有多个数据库可用逗号分隔,或者使用多个binlog-do-db选项
replicate-ignore-db=mysql,information_schema #不需要同步的数据库.如果有多个数据库可用逗号分隔,或者使用多个binlog-do-db选项
#同步参数:
#保证slave挂在任何一台master上都会接收到另一个master的写入信息
log-slave-updates
sync_binlog=1
auto_increment_offset=1
auto_increment_increment=2
slave-skip-errors=all #过滤掉一些没啥大问题的错误
#以下内容在高版本的Mysql上已经没用了,可以删掉:
#master-host=192.168.1.20
#master-user=datauser
#master-password=123456789
#master-port=3306
```

 


　　服务器B (192.168.1.20) :

```
[mysqld]
server-id=2 #设置一个不同的id
#innodb_flush_log_at_trx_commit=2 #这个不用了可以删掉
log-bin=mysql-bin #mysql二进制日志
#需要记录进制日志的数据库
binlog-do-db=data #需要记录进制日志的数据库.如果有多个数据库可用逗号分隔,或者使用多个binlog-do-db选项
binlog-ignore-db=mysql #不需要记录进制日志的数据库.如果有多个数据库可用逗号分隔,或者使用多个binlog-do-db选项
#需要同步的数据库
replicate-do-db=data #需要进行同步的数据库.如果有多个数据库可用逗号分隔,或者使用多个binlog-do-db选项
replicate-ignore-db=mysql,information_schema #不需要同步的数据库.如果有多个数据库可用逗号分隔,或者使用多个binlog-do-db选项
#同步参数:
#保证slave挂在任何一台master上都会接收到另一个master的写入信息
log-slave-updates
sync_binlog=1
auto_increment_offset=2
auto_increment_increment=2
slave-skip-errors=all #过滤掉一些没啥大问题的错误
#以下内容在高版本的Mysql上已经没用了,可以删掉:
#master-host=192.168.1.10
#master-user=datauser
#master-password=123456789
#master-port=3306
```

 


---------------------------------------------------------------------------------------

c). 分别重启服务器A、B上的mysql服务 :

 

```
# mysql-restart
 
Restarting MySQL...
Stoping MySQL...
Starting MySQL...
```

 

---------------------------------------------------------------------------------------

d)、分别在服务器A、B上查看做为主服务器状态

**在操作前锁表的目的是为了生产环境中不让进新的数据，好让从服务器定位同步位置。初次同步完成后，记得解锁。**


先执行: 

```
mysql>flush tables with read lock;
```

 

接着: 
　　服务器A (192.168.1.10) :

```
mysql> show master status\G
*************************** 1. row ***************************
            File: mysql-bin.000001
        Position: 155
    Binlog_Do_DB: data
Binlog_Ignore_DB: mysql
1 row in set (0.00 sec)
```

 

　　服务器B (192.168.1.20) :

```
mysql> show master status\G
*************************** 1. row ***************************
            File: mysql-bin.000001
        Position: 106
    Binlog_Do_DB: data
Binlog_Ignore_DB: mysql
1 row in set (0.00 sec)
```

 

---------------------------------------------------------------------------------------

e). 分别在服务器A、B上用change master语句指定同步位置 :

　　服务器A (192.168.1.10) :

```
mysql>change master to master_host='192.168.1.20', master_user='data', master_password='123456789', master_log_file='mysql-bin.000001', master_log_pos=106;
```

 

　　服务器B (192.168.1.20) :

```
mysql>change master to master_host='192.168.1.10', master_user='data', master_password='123456789', master_log_file='mysql-bin.000001', master_log_pos=155;
```



**注：master_log_file，master_log_pos由上面主服务器查出的状态值中确定。master_log_file对应File，master_log_pos对应Position。
　　mysql 5.x以上版本已经不支持在配置文件中指定主服务器相关选项。**

解锁服务器:

```
mysql>unlock tables;
```

 


---------------------------------------------------------------------------------------

f). 分别在服务器A、B上启动从服务器线程 :


```
mysql>start slave;
```

 


分别在服务器A、B上查看从服务器状态 :


```
mysql>show slave status\G;
 
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
```

 

查看以上两项的值，均为Yes则表示状态正常。






不得不提一个很严重的地方:

第一次设置好之后不管怎么查看 从服务器状态都是:

Slave_IO_Running: No
Slave_SQL_Running: Yes

这真是让哥百撕不得骑姐,纠结了一番去看自己去年那篇同步的日志才发现又出现了相同的问题,这里着重记录一下:

 

```
Last_IO_Errno: 1593
                Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).
```

 




**提示server_id相同这个傻逼错误的解决办法:**

用如下命令查看: 

```
show variables like 'server_id';
```

 

发现，mysql并没有从my.cnf文件中更新server_id，既然这样就只能手动修改了 
复制代码 代码如下: 

```
mysql> stop slave;
mysql> set global server_id=2; #此处的数值和my.cnf里设置的一样就行 
mysql> start slave;
```

 

如此执行后，同步恢复了正常.





> 
**在配置第三台slave的时候终于发现了问题所在,为什么我总是修改server_id没用了!
原来是因为my.cnf里面下方还有个server_id的默认设置 = 1 
我了个去,哥几乎3个小时浪费在这上面啦!!!
引以为戒!**






感谢这篇文章的作者: http://www.mike.org.cn/articles/mysql-master-slave-sync-conf-detail/









.

## 延伸问答与排错指南 (FAQ)

### MySQL主主同步最关键的配置是什么？
最关键的是两台服务器都要设置不同的 server-id，开启二进制日志，并互相创建 replication slave 权限的同步用户。同时要配置 replicate-do-db、binlog-do-db 等规则，确保需要同步的库被正确记录和回放。

### 为什么要设置 auto_increment 参数？
主主同步时两边都可能写入同一张表，如果自增主键规则相同，容易出现主键冲突。通过 auto_increment_offset 和 auto_increment_increment 让两台主机生成不同步长的自增值，可以显著降低冲突风险。

### 这套方案适合哪些 MySQL 版本？
原文方案明确面向 MySQL 5.1.x 到 5.5.x 这类老版本环境，配合 CentOS 5.6 使用。高版本 MySQL 的部分参数写法已经变化，尤其是 master-host、master-user 这类旧配置项不再适用。

---
更多 Linux 运维、WordPress 性能优化、前端工程化、Shopify 独立站与极客折腾指南：[生猛大蝦全站文章归档 (https://yjyj.net/archive)](https://yjyj.net/archive)
