概览
- nodeA和nodeB互为主备，即双主架构Master-Master。
- 使用semisync半同步机制，保证双主数据一致。
- 只有一个Master承担写操作，另一个备用，可承担读操作，配合应用实现读写分离。
- 双主加上Keepavlied可搭建高可用MySQL集群，实现单点故障切换。（本文不涉及）
准备工作
- 安装mysql5.7
（MySQL5.7改善了半同步复制，降低了主从数据不一致的风险。）
- 修改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，可选
- 在nodeA和nodeB上创建专门用于Replication的账户
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%' IDENTIFIED BY '123456';
mysql> FLUSH PRIVILEGES;
Master-Master配置
- 配置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)
- 配置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:
- 将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)
- 配置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半同步配置
- 加载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提交事务并返回结果给客户端。这样就保证了主从数据一致。
- 开启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+半同步配置完成
可能出现的错误
- 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服务 - 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
