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

概览

  • nodeA和nodeB互为主备,即双主架构Master-Master。
  • 使用semisync半同步机制,保证双主数据一致。
  • 只有一个Master承担写操作,另一个备用,可承担读操作,配合应用实现读写分离。
  • 双主加上Keepavlied可搭建高可用MySQL集群,实现单点故障切换。(本文不涉及)

准备工作

  1. 安装mysql5.7

(MySQL5.7改善了半同步复制,降低了主从数据不一致的风险。)

  1. 修改mysql配置文件

启动MySQL之前先修改/etc/my.cnf,增加下面的配置。
nodeA的配置文件

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,可选

nodeB的配置文件

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,可选
  1. 在nodeA和nodeB上创建专门用于Replication的账户
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%' IDENTIFIED BY '123456';
mysql> FLUSH PRIVILEGES;

Master-Master配置

  1. 配置nodeA为主库
    在nodeA上配置:记住 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)
  1. 配置nodeB为nodeA的从库
    在bodeB上配置:
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:
  1. 将nodeB设为主库
    在nodeB上设置:同样记住File和Position
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)
  1. 配置nodeA为nodeB的从库
    在bodeA上配置:
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:

最后在nodeB上执行

mysql> unlock tables; #只读锁解除

双Master配置完成

Semisync半同步配置

  1. 加载semisync_master和semisync_slave插件
    nodeA和nodeB上执行:
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
mysql> show variables like '%semi%';

rpl_semi_sync_master_timeout=10000
表示主库在某次事务中,如果等待时间超过10秒,则降级为普通模式,不再等待备库。如果主库再次探测到备库恢复了,则会自动再次回到semisync模式。
rpl_semi_sync_master_wait_point=AFTER_SYNC

AFTER_SYNC工作流程:
  • 客户端提交一个事务,master将事务写入binlog并刷新到磁盘,发送到slave,master等待slave反馈。
  • slave接收master的binlog,写到本地的relaylog里。发送确认信息给master。
  • 当接收到slave反馈,master提交事务并返回结果给客户端。这样就保证了主从数据一致。
  1. 开启semisync master和slave
    nodeA和nodeB上执行:
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;
mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;
mysql> stop slave;start slave;
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)

并修改my.cnf,添加下面两行:

rpl_semi_sync_master_enabled = 1
rpl_semi_sync_slave_enabled = 1

至此mysql Master-Master+半同步配置完成

可能出现的错误

  1. Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs;
    mysql 5.7的复制引入了uuid的概念,各个复制结构中的server_uuid得保证不一样,找到/etc/my.cnf文件中的datadir目录,找到auto.cnf 中的server_uuid更改后重启mysql服务
  2. Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file

在master那边,执行:

flush logs;
show master status;

记下File, Position。
在slave端,执行:

CHANGE MASTER TO MASTER_LOG_FILE='{File}',MASTER_LOG_POS={Position};
slave start;
show slave status \G

参考

https://dev.mysql.com/doc/refman/5.7/en/replication.html
https://blog.csdn.net/qq_16177481/article/details/70332004
https://blog.csdn.net/qq_16177481/article/details/70333978
http://blog.51cto.com/sqlercn/1975157

推荐阅读更多精彩内容