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

- Link: https://yjyj.net/learn/centos/1756.html
- Published: 2012-12-31T10:50:14+08:00
- Author: 小 虾

⚡ 生猛大蝦编辑部 · 30 秒核心看点

 * 双主同步核心是互设 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、环境描述**

    ```decode:true
    　　服务器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_) :

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

　　服务器B (_192.168.1.20_) :

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

——————————————————————————————

b). 修改 /etc/my.cnf 配置文件,为其添加以下内容:

　　服务器A (_192.168.1.10_) :

    ```decode:true
    [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_) :

    ```decode:true
    [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服务 :

    ```decode:true
    # mysql-restart

    Restarting MySQL...
    Stoping MySQL...
    Starting MySQL...
    ```

—————————————————————————————

d)、分别在服务器A、B上查看做为主服务器状态

**_在操作前锁表的目的是为了生产环境中不让进新的数据，好让从服务器定位同步位置。初次
同步完成后，记得解锁。_**

先执行:

    ```decode:true
    mysql>flush tables with read lock;
    ```

接着:
 　　服务器A (_192.168.1.10_) :

    ```decode:true
    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_) :

    ```decode:true
    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_) :

    ```decode:true
    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_) :

    ```decode:true
    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以上版本已经不支持
在配置文件中指定主服务器相关选项。
_

解锁服务器:

    ```decode:true
    mysql>unlock tables;
    ```

—————————————————————————————

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

    ```decode:true
    mysql>start slave;
    ```

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

    ```decode:true
    mysql>show slave status\G;

    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
    ```

查看以上两项的值，均为Yes则表示状态正常。

不得不提一个很严重的地方:

第一次设置好之后不管怎么查看 从服务器状态都是:

Slave_IO_Running: No
 Slave_SQL_Running: Yes

这真是让哥百撕不得骑姐,纠结了一番去看自己去年那篇同步的日志才发现又出现了相同的问题,
这里着重记录一下:

    ```decode:true
     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相同这个傻逼错误的解决办法:**

用如下命令查看:

    ```decode:true
    show variables like 'server_id';
    ```

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

    ```decode:true
    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 这类旧配置项不再适用。

📚 【[CentOS 学习](https://yjyj.net/category/learn/centos)】专栏延伸阅读与热门精选（
[全站文章归档](https://yjyj.net/archive)）：

 * [Vps时间同步](https://yjyj.net/learn/centos/1753.html)
 * [linux中.zip的命令记录](https://yjyj.net/learn/centos/1668.html)
 * [MySql主主(主从)同步配置补遗](https://yjyj.net/learn/centos/1822.html)
 * [vps双机热备](https://yjyj.net/learn/centos/1834.html)
 * 热门必读[Shopify Sessions 数据维度表](https://yjyj.net/learn/shopify/7764.html)
 * 热门必读[解决: windows 环境下 无法将“git”项识别为 cmdlet、函数、脚本文件或可运行程序的名称](https://yjyj.net/learn/7776.html)

## Comments

### By: 拆哪

- Link: https://yjyj.net/learn/centos/1756.html/comment-page-1#comment-12410
- Published: 2013-09-09T13:54:39+08:00

作者，为什么高版本的mysql不需要
 \#以下内容在高版本的Mysql上已经没用了,可以删掉:\#
master-host=192.168.1.10 \#master-user=datauser \#master-password=123456789 \#master-
port=3306 如果不要这个，我从服务器，怎么知道去哪里同步啊？

### By: 小虾

- Link: https://yjyj.net/learn/centos/1756.html/comment-page-1#comment-12414
- Published: 2013-09-09T20:25:38+08:00

这个 在配置同步位置的时候 已经做了设置哈 请看步骤 e)
