MySQL双主(master-master)+半同步(Semisync Replication)

小灰灰 2023-02-21 06:59 178阅读 0赞

## 思路 ##

1.  准备两个库
2.  设置库A主库，库B从库
3.  设置库B主库，库A从库
4.  开启半同步复制

## 安装mysql5.7 ##

## 修改mysql配置文件 ##

**node-A的配置文件**

启动MySQL之前先修改/etc/my.cnf，增加下面的配置

server-id = 1
    log-bin=mysql-bin    # 打开二进制日志功能，作为主库时必须设置
    log-slave-updates    # 做为从库时，数据库的修改也会写到bin-log里
    binlog-ignore-db = mysql #二进制日志忽略的数据库
    binlog-ignore-db = information_schema 
    binlog-ignore-db = performance_schema
    replicate-wild-ignore-table = mysql.% #忽略备份的表
    replicate-wild-ignore-table = information_schema.%
    replicate-wild-ignore-table = performance_schema.%
    expire_logs_days=5   # 表示自动删除5天以前的binlog，可选
    
    #开启半同步，也可以在命令行开启；其他参数后面会说明
    plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
    loose_rpl_semi_sync_master_enabled = 1
    loose_rpl_semi_sync_slave_enabled = 1
    loose_rpl_semi_sync_master_timeout = 5000

**node-B的配置文件**

server-id = 2  #确保节点id不同
    log-bin=mysql-bin    # 打开二进制日志功能，作为主库时必须设置
    log-slave-updates    # 做为从库时，数据库的修改也会写到bin-log里
    binlog-ignore-db = mysql #二进制日志忽略的数据库
    binlog-ignore-db = information_schema 
    binlog-ignore-db = performance_schema
    replicate-wild-ignore-table = mysql.% #忽略备份的表
    replicate-wild-ignore-table = information_schema.%
    replicate-wild-ignore-table = performance_schema.%
    expire_logs_days=5   # 表示自动删除5天以前的binlog，可选
    
    #开启半同步，也可以在命令行开启；其他参数后面会说明
    plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
    loose_rpl_semi_sync_master_enabled = 1
    loose_rpl_semi_sync_slave_enabled = 1
    loose_rpl_semi_sync_master_timeout = 5000

## 创建用于replication的账户 ##

在node-A和node-B上分别创建专门用于Replication的账户

登陆并使用如下命令

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%' IDENTIFIED BY '123456';
    mysql> FLUSH PRIVILEGES;

## Master-Master配置 ##

### 配置node-A为主库 ###

**在node-A上配置**

记住 File 和Position字段（File对应MASTER\_LOG\_FILE，Position对应MASTER\_LOG\_POS）

mysql> reset master; #清空master的binlog，平时慎用，可选
    mysql> flush tables with read lock; #只读
    mysql> flush logs;
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> show master status;
    +------------------+----------+--------------+---------------------------------------------+-------------------+
    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                            | Executed_Gtid_Set |
    +------------------+----------+--------------+---------------------------------------------+-------------------+
    | mysql-bin.000002 |      154 |              | mysql,information_schema,performance_schema |                   |
    +------------------+----------+--------------+---------------------------------------------+-------------------+
    1 row in set (0.00 sec)

这里的Position和File内容需要。

### 配置node-B为node-A的从库 ###

**在bode-B上配置**

mysql>stop slave;
    mysql> CHANGE MASTER TO MASTER_HOST='{nodeA-server-ip}', MASTER_USER='repl_user', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=154;
    mysql> start slave;
    mysql> show slave status\G
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 此处为nodeA_ip地址
                      Master_User: repl_user
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000012
              Read_Master_Log_Pos: 154
                   Relay_Log_File: izj6c1rl2qyewsyu87vth0z-relay-bin.000012
                    Relay_Log_Pos: 367
            Relay_Master_Log_File: mysql-bin.000012
                 Slave_IO_Running: Yes （为Yes表示成功，为No查看Last_IO_Error信息）
                Slave_SQL_Running: Yes （为Yes表示成功，为No查看Last_SQL_Error信息）
                  Replicate_Do_DB:
              Replicate_Ignore_DB:
               Replicate_Do_Table:
           Replicate_Ignore_Table:
          Replicate_Wild_Do_Table:
      Replicate_Wild_Ignore_Table: mysql.%,information_schema.%,performance_schema.%
    ....
                    Last_IO_Errno: 0
                    Last_IO_Error:
                   Last_SQL_Errno: 0
                   Last_SQL_Error:

这里的MASTER\_HOST为node-A的服务器IP，MASTER\_USER为刚才创建的用户同步使用的用户信息。MASTER\_LOG\_FILE为node-A刚才显示的信息，MASTER\_LOG\_POS类似。

如果报错，按照对应错误百度即可。

我遇到的问题：

1.由于懒，直接拷贝的mysql到另一个服务器，导致service-uuid一样，需要删除mysql/data/auto.conf文件，重启mysql即可。

2.配置文件时service-id一样，这个不能一样，my.conf改一下就好。

同理：

将库B设置为主库，库A设置为从库一样的方法。

### 将node-B设为主库 ###

**在node-B节点上**

mysql> reset master;(清空master的binlog，平时慎用，可选)
    mysql> flush tables with read lock;
    mysql> flush logs;
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> show master status;
    +------------------+----------+--------------+---------------------------------------------+-------------------+
    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                            | Executed_Gtid_Set |
    +------------------+----------+--------------+---------------------------------------------+-------------------+
    | mysql-bin.000002 |      154 |              | mysql,information_schema,performance_schema |                   |
    +------------------+----------+--------------+---------------------------------------------+-------------------+
    1 row in set (0.00 sec)

### 配置node-A为node-B的从库 ###

**在node-A节点上操作**

mysql>stop slave;
    mysql> CHANGE MASTER TO MASTER_HOST='{nodeB-server-ip}', MASTER_USER='repl_user', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=154;
    mysql> start slave;
    mysql> show slave status\G
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 此处为nodeB_ip地址
                      Master_User: repl_user
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000012
              Read_Master_Log_Pos: 154
                   Relay_Log_File: izj6c1rl2qyewsyu87vth0z-relay-bin.000012
                    Relay_Log_Pos: 367
            Relay_Master_Log_File: mysql-bin.000012
                 Slave_IO_Running: Yes （为Yes表示成功，为No查看Last_IO_Error信息）
                Slave_SQL_Running: Yes （为Yes表示成功，为No查看Last_SQL_Error信息）
                  Replicate_Do_DB:
              Replicate_Ignore_DB:
               Replicate_Do_Table:
           Replicate_Ignore_Table:
          Replicate_Wild_Do_Table:
      Replicate_Wild_Ignore_Table: mysql.%,information_schema.%,performance_schema.%
    ....
                    Last_IO_Errno: 0
                    Last_IO_Error:
                   Last_SQL_Errno: 0
                   Last_SQL_Error:

### 在node-A和node-B上解锁只读 ###

mysql> unlock tables; #只读锁解除

## 半同步说明 ##

### 查看同步状态是否开启 ###

mysql> show status like '%semi%';
    +--------------------------------------------+-------+
    | Variable_name                              | Value |
    +--------------------------------------------+-------+
    | Rpl_semi_sync_master_clients               | 1     |
    | Rpl_semi_sync_master_net_avg_wait_time     | 0     |
    | Rpl_semi_sync_master_net_wait_time         | 0     |
    | Rpl_semi_sync_master_net_waits             | 0     |
    | Rpl_semi_sync_master_no_times              | 0     |
    | Rpl_semi_sync_master_no_tx                 | 0     |
    | Rpl_semi_sync_master_status                | ON    | (master同步）
    | Rpl_semi_sync_master_timefunc_failures     | 0     |
    | Rpl_semi_sync_master_tx_avg_wait_time      | 0     |
    | Rpl_semi_sync_master_tx_wait_time          | 0     |
    | Rpl_semi_sync_master_tx_waits              | 0     |
    | Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
    | Rpl_semi_sync_master_wait_sessions         | 0     |
    | Rpl_semi_sync_master_yes_tx                | 0     |
    | Rpl_semi_sync_slave_status                 | ON    |（从同步）
    +--------------------------------------------+-------+
    15 rows in set (0.00 sec)

### rpl\_semi\_sync\_master\_wait\_point=AFTER\_SYNC ###

*  客户端提交一个事务，master将事务写入binlog并刷新到磁盘，发送到slave，master等待slave反馈。
 *  slave接收master的binlog，写到本地的relaylog里。发送确认信息给master。
 *  当接收到slave反馈，master提交事务并返回结果给客户端。这样就保证了主从数据一致。

## 总结 ##

### 双主同步优点 ###

1、主主模式能将读写请求分摊到两个主节点，有效提升服务器使用率。  
2、主节点发生故障后，能快速进行主从切换。  
3、当故障节点恢复后，故障节点能通过复制进行数据恢复(应用其他节点数据)和数据同步(将未同步数据发生给其他节点)。

### 缺点 ###

1、当主节点上MySQL实例发生故障后，可能会存在部分数据(Binlog)未同步到另外的主节点，导致数据丢失(直到故障节点恢复)。  
2、主主模式下，很容易因数据访问控制不当导致数据冲突。  
3、为提高系统高可用性，双主架构会被扩展成双主多从结构，同样存在主节点发生故障后多个从库选主和恢复复制的问题。

### 我为什么选择双主同步？ ###

由于以前的服务器使用单点数据库+应用+nginx模式，没有使用现在的微服务模式。所以数据库也没有集群，因为业务量不够大。但是为了提高可用性，需要对数据库实时备份，如果数据库或者应用或者系统挂掉之后，可以迅速将请求转移到可用服务上。

如果是实际互联网使用，可以使用MHA架构，自动故障点切换。

详情见我的另一篇文章：

https://blog.csdn.net/u013919153/article/details/106261865

参考说明：

本文核心内容来自：https://www.jianshu.com/p/dd15e6dd2e94

在此对原作者表示感谢。