概覽
- nodeA和nodeB互為主備藤韵，即雙主架構(gòu)Master-Master狮腿。
- 使用semisync半同步機(jī)制，保證雙主數(shù)據(jù)一致雁刷。
- 只有一個(gè)Master承擔(dān)寫操作覆劈，另一個(gè)備用，可承擔(dān)讀操作沛励，配合應(yīng)用實(shí)現(xiàn)讀寫分離责语。
- 雙主加上Keepavlied可搭建高可用MySQL集群，實(shí)現(xiàn)單點(diǎn)故障切換目派。（本文不涉及）
準(zhǔn)備工作
- 安裝mysql5.7
（MySQL5.7改善了半同步復(fù)制坤候，降低了主從數(shù)據(jù)不一致的風(fēng)險(xiǎn)。）
- 修改mysql配置文件
啟動(dòng)MySQL之前先修改/etc/my.cnf企蹭，增加下面的配置白筹。
nodeA的配置文件
server-id = 1
log-bin=mysql-bin # 打開二進(jìn)制日志功能徘键，作為主庫(kù)時(shí)必須設(shè)置
log-slave-updates # 做為從庫(kù)時(shí)，數(shù)據(jù)庫(kù)的修改也會(huì)寫到bin-log里
binlog-ignore-db = mysql #二進(jìn)制日志忽略的數(shù)據(jù)庫(kù)
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 # 表示自動(dòng)刪除5天以前的binlog遍蟋，可選
nodeB的配置文件
server-id = 2 #確保節(jié)點(diǎn)id不同
log-bin=mysql-bin # 打開二進(jìn)制日志功能，作為主庫(kù)時(shí)必須設(shè)置
log-slave-updates # 做為從庫(kù)時(shí)螟凭，數(shù)據(jù)庫(kù)的修改也會(huì)寫到bin-log里
binlog-ignore-db = mysql #二進(jìn)制日志忽略的數(shù)據(jù)庫(kù)
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 # 表示自動(dòng)刪除5天以前的binlog虚青，可選
- 在nodeA和nodeB上創(chuàng)建專門用于Replication的賬戶
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%' IDENTIFIED BY '123456';
mysql> FLUSH PRIVILEGES;
Master-Master配置
- 配置nodeA為主庫(kù)
在nodeA上配置：記住 File 和Position字段（File對(duì)應(yīng)MASTER_LOG_FILE，Position對(duì)應(yīng)MASTER_LOG_POS）
mysql> reset master; #清空master的binlog螺男，平時(shí)慎用棒厘，可選
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的從庫(kù)
在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設(shè)為主庫(kù)
在nodeB上設(shè)置：同樣記住File和Position
mysql> reset master;(清空master的binlog奢人，平時(shí)慎用，可選)
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的從庫(kù)
在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上執(zhí)行
mysql> unlock tables; #只讀鎖解除
雙Master配置完成
Semisync半同步配置
- 加載semisync_master和semisync_slave插件
nodeA和nodeB上執(zhí)行：
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
表示主庫(kù)在某次事務(wù)中，如果等待時(shí)間超過10秒土辩，則降級(jí)為普通模式支救，不再等待備庫(kù)。如果主庫(kù)再次探測(cè)到備庫(kù)恢復(fù)了拷淘，則會(huì)自動(dòng)再次回到semisync模式各墨。
rpl_semi_sync_master_wait_point=AFTER_SYNC
AFTER_SYNC工作流程：
- 客戶端提交一個(gè)事務(wù)，master將事務(wù)寫入binlog并刷新到磁盤启涯，發(fā)送到slave贬堵，master等待slave反饋。
- slave接收master的binlog结洼，寫到本地的relaylog里黎做。發(fā)送確認(rèn)信息給master。
- 當(dāng)接收到slave反饋补君，master提交事務(wù)并返回結(jié)果給客戶端引几。這樣就保證了主從數(shù)據(jù)一致。
- 開啟semisync master和slave
nodeA和nodeB上執(zhí)行：
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+半同步配置完成
可能出現(xiàn)的錯(cuò)誤
- Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs;
mysql 5.7的復(fù)制引入了uuid的概念伟桅，各個(gè)復(fù)制結(jié)構(gòu)中的server_uuid得保證不一樣，找到/etc/my.cnf文件中的datadir目錄叽掘，找到auto.cnf 中的server_uuid更改后重啟mysql服務(wù) - 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那邊楣铁，執(zhí)行：
flush logs;
show master status;
記下File, Position。
在slave端更扁，執(zhí)行：
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
