使用mysql5.7 增強(qiáng)半同步温学，GTID復(fù)制，設(shè)置master wait永不超時(shí)钙畔，外加mysqlfailover自動(dòng)切換茫陆。可實(shí)現(xiàn)新一代高可用架構(gòu)擎析，零事務(wù)丟失自動(dòng)切換簿盅！
搭建步驟
- 搭建一主兩從（至少兩從）增強(qiáng)半同步架構(gòu)（安裝略）挥下。
master 半同步配置用不超時(shí)：
salve 的my.cnf配置(主庫也要配置，防止主庫切換后桨醋，再次以從庫的角色加入集群棚瘟，無法被自動(dòng)發(fā)現(xiàn))：rpl_semi_sync_master_wait_for_slave_count=1 rpl_semi_sync_master_wait_no_slave=ON rpl_semi_sync_master_timeout=1000000000000000000 rpl_semi_sync_master_wait_point=AFTER_SYNC
注意從庫不能配置super_read_only=ONreport_host='172.40.0.164' # the ip of the slave report_port=3306 master_info_repository=TABLE - 在一臺單獨(dú)的監(jiān)控機(jī)上安裝mysqlfailover.
wget -c https://cdn.mysql.com/archives/mysql-utilities/mysql-utilities-1.6.5-1.el7.noarch.rpm #mysqlfailover腳本依賴依賴mysql python connector wget -c https://dev.mysql.com/get/Downloads/Connector-Python/mysql-connector-python-2.1.7-1.el7.x86_64.rpm #安裝： rpm -ivh mysql-connector-python-2.1.7-1.el7.x86_64.rpm rpm -ivh mysql-utilities-1.6.5-1.el7.noarch.rpm - failover監(jiān)控腳本賬號創(chuàng)建。mysqlfailover腳本使用一個(gè)數(shù)據(jù)庫賬號去監(jiān)控集群中各節(jié)點(diǎn)狀態(tài)喜最，如果主庫故障偎蘸，會啟用自動(dòng)切換，切換時(shí)腳本會執(zhí)行change master 等操作,且所有節(jié)點(diǎn)需要此賬號瞬内，因此需要在主庫上創(chuàng)建迷雪。此賬號權(quán)限較大，為安全起見虫蝶，可使用login-path.
對于mysql5.7還需要授權(quán)：create user fo@'192.168.216.146' identified by 'fo'; grant SUPER, REPLICATION SLAVE, SELECT, RELOAD, DROP, CREATE, INSERT on *.* to fo@'192.168.216.146' with grant option;
因?yàn)閙ysql 5.7中對于已經(jīng)存在的賬號章咧，再次使用grant ... identified by 需要修改mysql庫中的相關(guān)表。grant update,delete on mysql.* to 'fo'@'192.168.216.146'; - 在監(jiān)控節(jié)點(diǎn)配置login-path
mysql_config_editor set --login-path=fo --user=fo --host=192.168.216.144 --port=3306 --password
# test login
mysql --login-path=fo
- 啟動(dòng)mysqlfailover
mysqlfailover --master=fo --discover-slaves-login=fo --log=/tmp/fo.log --rpl-user=repl:repl --force
mysqlfailover在第一次連接的時(shí)候能真，會在mysql庫下面創(chuàng)建failover_console表赁严，里面記錄了所監(jiān)控master的ip和端口。正常退出粉铐，這個(gè)表會被drop掉疼约。但對于非正常退出，這個(gè)表會一直存在蝙泼。如果下次再嘗試連接程剥，則會警告：
Multiple instances of failover console found for master 192.168.216.144:3306.
If this is an error, restart the console with --force.
Failover mode changed to 'FAIL' for this instance.
Console will start in 10 seconds..........starting Console.
上述信息是說mysqlfailover檢查到已經(jīng)有一個(gè)進(jìn)程在運(yùn)行了，因此這個(gè)進(jìn)程的mode被修改為FAIL, FAIL mode的意思是該進(jìn)程只監(jiān)控踱承，不會執(zhí)行切換倡缠。如果確認(rèn)只有一個(gè)mysqlfailover在運(yùn)行哨免，可以使用--force參數(shù)茎活。這里需要說一下failover的三種模式：
- auto: (默認(rèn))。 自動(dòng)切換模式琢唾。該模式在切換時(shí)载荔，首先從candicate列表中選擇，如果candicate列表的主機(jī)不可用采桃，則會從其它的從節(jié)點(diǎn)選擇一個(gè)作為新的master. 然后把新master配置為其它從節(jié)點(diǎn)的從庫懒熙，復(fù)制缺少的事務(wù)，直到該master包含所有的事務(wù)（most update slave）
- elect: 該模式和auto模式類似普办，只是它只從candidate列表中選擇工扎。如果列表不可用則報(bào)錯(cuò)退出。使用該模式必須用--candidates選項(xiàng)衔蹲。
- fail:該模式只監(jiān)控肢娘，不執(zhí)行切換。
測試
- 停止主庫，等待約10s左右橱健，可以看到切換動(dòng)作
Failed to reconnect to the master after 3 attemps.
Failover starting in 'auto' mode...
# Candidate slave 192.168.216.145:3306 will become the new master.
# Checking slaves status (before failover).
# Preparing candidate for failover.
# Creating replication user if it does not exist.
# Stopping slaves.
# Performing STOP on all slaves.
# Switching slaves to new master.
# Disconnecting new master as slave.
# Starting slaves.
# Performing START on all slaves.
# Checking slaves for errors.
# Failover complete.
# Discovering slaves for master at 192.168.216.145:3306
Failover console will restart in 5 seconds.
- 重新啟動(dòng)原來的主節(jié)點(diǎn)而钞，然后將其作為從庫加入集群：
mysqlreplicate --master=fo:fo@192.168.216.145:3306 --slave=fo:fo@192.168.216.144:3306 --rpl-user=repl:repl
加入后，failover腳本很快就發(fā)現(xiàn)新節(jié)點(diǎn)拘荡，顯示一主兩從的架構(gòu)臼节。
- 重新啟用原來的主節(jié)點(diǎn)為master
mysqlrpladmin --master=fo:fo@192.168.216.145:3306 \
> --slaves=fo:fo@192.168.216.144:3306,fo:fo@192.168.216.147:3306 \
> --rpl-user=repl:repl --new-master=fo:fo@192.168.216.144:3306 --demote-master switchover
WARNING: Using a password on the command line interface can be insecure.
# Checking privileges.
# Performing switchover from master at 192.168.216.145:3306 to slave at 192.168.216.144:3306.
# Checking candidate slave prerequisites.
# Checking slaves configuration to master.
# Waiting for slaves to catch up to old master.
# Stopping slaves.
# Performing STOP on all slaves.
# Demoting old master to be a slave to the new master.
# Switching slaves to new master.
# Starting all slaves.
# Performing START on all slaves.
# Checking slaves for errors.
# Switchover complete.
#
# Replication Topology Health:
+------------------+-------+---------+--------+------------+---------+
| host | port | role | state | gtid_mode | health |
+------------------+-------+---------+--------+------------+---------+
| 192.168.216.144 | 3306 | MASTER | UP | ON | OK |
| 192.168.216.145 | 3306 | SLAVE | UP | ON | OK |
| 192.168.216.147 | 3306 | SLAVE | UP | ON | OK |
+------------------+-------+---------+--------+------------+---------+
# ...done.
切換后發(fā)現(xiàn)，failover腳本監(jiān)控失斏好蟆：
Replication Health Status
+------------------+-------+---------+--------+------------+------------------------------------+
| host | port | role | state | gtid_mode | health |
+------------------+-------+---------+--------+------------+------------------------------------+
| 192.168.216.145 | 3306 | MASTER | UP | ON | OK |
| 192.168.216.144 | 3306 | SLAVE | WARN | | Slave is not connected to master. |
| 192.168.216.147 | 3306 | SLAVE | WARN | | Slave is not connected to master. |
+------------------+-------+---------+--------+------------+------------------------------------+
看來這種情下必須重啟mysqlfailover腳本网缝！
- 使用sysbench 對主庫壓測,監(jiān)控信息會顯示延遲情況。
Replication Health Status
+------------------+-------+---------+--------+------------+--------------------------------------------------------------------------------------------+
| host | port | role | state | gtid_mode | health |
+------------------+-------+---------+--------+------------+--------------------------------------------------------------------------------------------+
| 192.168.216.144 | 3306 | MASTER | UP | ON | OK |
| 192.168.216.145 | 3306 | SLAVE | UP | ON | Slave delay is 6 seconds behind master., No, Slave has 3916 transactions behind master. |
| 192.168.216.147 | 3306 | SLAVE | UP | ON | Slave delay is 59 seconds behind master., No, Slave has 33416 transactions behind master. |
+------------------+-------+---------+--------+------------+--------------------------------------------------------------------------------------------+
在主從復(fù)制延遲的情況下蟋定，停掉主庫途凫，查看failover腳本切換狀況。
Failed to reconnect to the master after 3 attemps.
Failover starting in 'auto' mode...
# Candidate slave 192.168.216.145:3306 will become the new master.
# Checking slaves status (before failover).
# Preparing candidate for failover.
發(fā)現(xiàn)切換時(shí)間明顯增長溢吻，在Preparing candidate for failover.等待维费。查看從庫狀態(tài)，發(fā)現(xiàn)有一個(gè)尚未應(yīng)用完binlog促王，當(dāng)所有從庫都應(yīng)用完binglog時(shí)犀盟，才正式切換。這對保證數(shù)據(jù)一致性很重要蝇狼！mysqlfailover已經(jīng)做到這一點(diǎn)兒了阅畴。另外如果切換時(shí)主從延遲嚴(yán)重，切換時(shí)間會較長迅耘，這可以通過啟用MTS來緩解贱枣。
問題
- 切換后，從庫被提升為master 颤专， 但只讀屬性沒有被取消纽哥。這會導(dǎo)致應(yīng)用依然不能做業(yè)務(wù)。還一個(gè)問題是切換后栖秕，應(yīng)該通知應(yīng)用春塌，連接新的master. (如修改DNS，或zookeeper中的masterIP,這取決于應(yīng)用數(shù)據(jù)源的配置方式) 這些都可以通過腳本來實(shí)現(xiàn)簇捍。
mysqlfailover 提供了兩個(gè)注入腳本的接口:- --exec-before=script ,在切換開始前執(zhí)行只壳。
- --exec-after=script ， 在切換完成后執(zhí)行暑塑。
這意味著我們可以在切換完成后寫個(gè)腳本去修改新master的只讀屬性吼句，已經(jīng)通知應(yīng)用修改數(shù)據(jù)源地址（使用vip來解決更為優(yōu)雅，但有些云環(huán)境不支持vip）事格。
- 使用--daemon=start 啟動(dòng)腳本時(shí)惕艳，不能使用--daemon=stop來終止况毅。會造成從庫sql_thread報(bào)錯(cuò)斷開。須使用kill -9 pid . 啟動(dòng)時(shí)尔艇，加--force參數(shù)尔许。我已向官方遞交此bug (91562)
參考資料：
https://dev.mysql.com/doc/mysql-utilities/1.6/en/mysqlfailover.html
