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

概览

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

参考