摘要:雙主是一個比較簡單的高可用架構(gòu),適用于中小集群,今天就說說怎么用做的高可用。缺點也比較明顯,就是增加從節(jié)點的情況下,從節(jié)點不會主動切換同步對象,而且腳本需要自己實現(xiàn),有一定風(fēng)險。
雙主 + keepalived 是一個比較簡單的 MySQL 高可用架構(gòu),適用于中小 MySQL 集群,今天就說說怎么用 keepalived 做 MySQL 的高可用。
1 概述 1.1 keepalived 簡介簡單地說,keepalived 就是通過管理 VIP 來實現(xiàn)機器的高可用的,在使用 keepalived 的情況下,只有一臺服務(wù)器能夠提供服務(wù)(通過 VIP 來實現(xiàn)),當(dāng) Master 主機宕機后,VIP 會自動飄移到另一臺服務(wù)器
keepalived 采用 Master/Slave 模式, 在 Master 上設(shè)置配置文件的 VIP,當(dāng) Master 宕機后,VIP 自動漂移到另一臺 keepalived 服務(wù)器上
keepalived 可以用來做各種軟件的高可用集群,它會一直檢測服務(wù)器的狀態(tài),如果有一臺服務(wù)器宕機,或工作出現(xiàn)故障,keepalived 將檢測到,并將有故障的服務(wù)器從系統(tǒng)中剔除,同時使用其他服務(wù)器代替該服務(wù)器的工作,當(dāng)服務(wù)器工作正常后 keepalived 自動將服務(wù)器加入到服務(wù)器群中。
1.2 keepalived 配合雙主keepalived 使用默認(rèn)配置只能做到主機級別的高可用,但是我們的 MySQL 要做高可用至少要增加以下功能
能夠檢測 MySQL 服務(wù)狀態(tài)
主節(jié)點 read_only=0,備節(jié)點 read_only=1
切換時,備節(jié)點要等待主節(jié)點同步完成
所以,keepalived 實現(xiàn) MySQL 高可用需要使用自定義腳本來進行擴展
2 環(huán)境準(zhǔn)備 2.1 數(shù)據(jù)庫環(huán)境操作前已經(jīng)準(zhǔn)備好了一套主主架構(gòu)數(shù)據(jù)庫,搭建方法參考 MySQL集群搭建(2)-主主從模式
節(jié)點信息IP | 系統(tǒng) | 端口 | MySQL版本 | 節(jié)點 | 讀寫 | 說明 |
---|---|---|---|---|---|---|
10.0.0.247 | Centos6.5 | 3306 | 5.7.9 | Master | 讀寫 | 主節(jié)點 |
10.0.0.248 | Centos6.5 | 3306 | 5.7.9 | Standby | 只讀,可切換為讀寫 | 備主節(jié)點 |
簡稱 | VIP | 類型 |
---|---|---|
RW-VIP | 10.0.0.237 | 讀寫VIP |
[client] port = 3306 default-character-set=utf8mb4 socket = /data/mysql_db/test_db/mysql.sock [mysqld] datadir = /data/mysql_db/test_db basedir = /usr/local/mysql57 tmpdir = /tmp socket = /data/mysql_db/test_db/mysql.sock pid-file = /data/mysql_db/test_db/mysql.pid skip-external-locking = 1 skip-name-resolve = 1 port = 3306 server_id = 2473306 default-storage-engine = InnoDB character-set-server = utf8mb4 default_password_lifetime=0 auto_increment_offset = 1 auto_increment_increment = 2 #### log #### log_timestamps=system log_bin = /data/mysql_log/test_db/mysql-bin log_bin_index = /data/mysql_log/test_db/mysql-bin.index binlog_format = row relay_log_recovery=ON relay_log=/data/mysql_log/test_db/mysql-relay-bin relay_log_index=/data/mysql_log/test_db/mysql-relay-bin.index log_error = /data/mysql_log/test_db/mysql-error.log #### replication #### log_slave_updates = 1 replicate_wild_ignore_table = information_schema.%,performance_schema.%,sys.% #### semi sync replication settings ##### plugin_dir=/usr/local/mysql57/lib/plugin plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so" loose_rpl_semi_sync_master_enabled = 1 loose_rpl_semi_sync_slave_enabled = 1Slave 參考配置
[client] port = 3306 default-character-set=utf8mb4 socket = /data/mysql_db/test_db/mysql.sock [mysqld] datadir = /data/mysql_db/test_db basedir = /usr/local/mysql57 tmpdir = /tmp socket = /data/mysql_db/test_db/mysql.sock pid-file = /data/mysql_db/test_db/mysql.pid skip-external-locking = 1 skip-name-resolve = 1 port = 3306 server_id = 2483306 default-storage-engine = InnoDB character-set-server = utf8mb4 default_password_lifetime=0 auto_increment_offset = 2 auto_increment_increment = 2 #### log #### log_timestamps=system log_bin = /data/mysql_log/test_db/mysql-bin log_bin_index = /data/mysql_log/test_db/mysql-bin.index binlog_format = row relay_log_recovery=ON relay_log=/data/mysql_log/test_db/mysql-relay-bin relay_log_index=/data/mysql_log/test_db/mysql-relay-bin.index log_error = /data/mysql_log/test_db/mysql-error.log #### replication #### log_slave_updates = 1 replicate_wild_ignore_table = information_schema.%,performance_schema.%,sys.% #### semi sync replication settings ##### plugin_dir=/usr/local/mysql57/lib/plugin plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so" loose_rpl_semi_sync_master_enabled = 1 loose_rpl_semi_sync_slave_enabled = 12.2 創(chuàng)建監(jiān)控用的賬號
- 由于是測試環(huán)境,賬號密碼設(shè)置比較隨便 create user monitor@"localhost" identified by "monitor"; grant all on *.* to monitor@"localhost"; flush privileges;2.3 安裝 keepalived
我們在 Master 和 Slave 上部署 keepalived
1). yum 安裝如果有對應(yīng)的 yum 源,直接安裝就可以了
yum install -y keepalived2). 源碼安裝
下載安裝包, 下載地址 keepalived, 使用 1.2.24 版本舉例
# 安裝依賴 yum install -y gcc popt-devel openssl openssl-devel libssl-dev libnl-devel popt-devel libnfnetlink-devel # 下載包 wget http://www.keepalived.org/software/keepalived-1.2.24.tar.gz # 解壓安裝 tar -xvz -f keepalived-1.2.24.tar.gz cd keepalived-1.2.24 ./configure --prefix=/usr/local/keepalived make && make install cp /usr/local/keepalived/sbin/keepalived /usr/sbin/ cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/init.d/ cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/ mkdir /etc/keepalived/ cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/3 配置高可用 3.1 keepalived 配置
打開 /etc/keepalived/keepalived.conf 文件, 按照實際情況加上下面的配置
global_defs { router_id MYSQL_MM # 標(biāo)識 vrrp_skip_check_adv_addr vrrp_strict # 嚴(yán)格執(zhí)行 VRRP 協(xié)議規(guī)范 vrrp_garp_interval 0 vrrp_gna_interval 0 } vrrp_script check_mysql { script "/bin/sh /etc/keepalived/keepalived_mysql_check.sh" # 檢查腳本 interval 10 # 檢查周期 } vrrp_instance MYSQL_MM { state BACKUP # 都設(shè)為 BACKUP,避免起來后搶占 interface eth0 # 網(wǎng)卡名稱,根據(jù)實際情況填寫 virtual_router_id 243 # 用來區(qū)分 VRRP 組播的標(biāo)記,取值 0-255 priority 100 advert_int 1 nopreempt # 設(shè)為非搶占 authentication { auth_type PASS auth_pass 1111 } # Master 節(jié)點可以注釋掉下面語句,防止啟動 keepalived 的時候執(zhí)行腳本 notify_master "/bin/sh /etc/keepalived/keepalived_mysql_start.sh" # 變?yōu)?MASTER 時執(zhí)行 virtual_ipaddress { 10.0.0.237 } # Slave 節(jié)點可以注釋下面檢查腳本,Slave 沒有必要一直檢查 track_script { check_mysql } }3.2 配置檢查腳本
打開 /etc/keepalived/keepalived_mysql_check.sh, 寫入檢測腳本
#!/bin/sh # @Author: chengqm # MySQL 檢測腳本 MyPath=$(cd $(dirname $0); pwd) cd $MyPath ThisTime=`date "+%F %T"` log_file="/var/log/keepalived_mysql.log" # MySQL 連接方式,根據(jù)實際情況調(diào)整 export MYSQL_PWD="monitor" MYSQL_USER="monitor" MYSQL_SOCKET="/data/mysql_db/test_db/mysql.sock" mysql_connect="mysql -u${MYSQL_USER} -S${MYSQL_SOCKET} " # 美化輸出 function techo() { message=$1 message_level=$2 if [ -e $message_level ];then message_level="info" fi echo "`date "+%F %T"` - [${message_level}] $message" >> $log_file } # 檢查函數(shù), 正常返回 0 function check { ret=`$mysql_connect -N -e "select 1 as value"` if [ $? -ne 0 ] || [ $ret -ne "1" ];then return 1 else return 0 fi } function read_only { param=$1 $mysql_connect -e "set global read_only = ${param}" techo "設(shè)置是否只讀 read_only ${param}" } # 失效轉(zhuǎn)移 function failover { techo "開始執(zhí)行失效轉(zhuǎn)移" # 1. 停止 keepalived killall keepalived # 2. 如果還能執(zhí)行的話,設(shè)為 read_only read_only 1 if [ $? -eq 0 ];then # 3. 如果還能執(zhí)行,kill 所有的連接 $mysql_connect -e "select concat("KILL ",id,";") from information_schema.processlist where user!="root" AND db is not null into outfile "/tmp/kill.txt.${ThisTime}";" if [ $? -eq 0 ];then $mysql_connect -e "source /tmp/kill.txt.${ThisTime};" fi fi # 4. 其他操作,比如說自動關(guān)機 techo "失效轉(zhuǎn)移執(zhí)行成功,當(dāng)前數(shù)據(jù)庫關(guān)閉訪問" } # 有問題檢查 4 次 for ((i=1; i<=4; i ++)) do check if [ $? -eq 0 ];then techo "MySQL is ok" # 正常退出腳本 exit 0 else techo "Connection failed $i time(s)" sleep 1 fi done techo "無法連接當(dāng)前數(shù)據(jù)庫" # 失效轉(zhuǎn)移 failover
注意:腳本沒有經(jīng)過嚴(yán)格測試,需要根據(jù)實際情況調(diào)整
3.3 配置提升為 Master 時執(zhí)行的腳本打開 /bin/sh /etc/keepalived/keepalived_mysql_start.sh", 寫入腳本內(nèi)容
#!/bin/sh # @Author: chengqm # keepalived 變?yōu)?Master 時執(zhí)行 MyPath=$(cd $(dirname $0); pwd) cd $MyPath ThisTime=`date "+%F %T"` log_file="/var/log/keepalived_mysql.log" # MySQL 連接方式,根據(jù)實際情況調(diào)整 export MYSQL_PWD="monitor" MYSQL_USER="monitor" MYSQL_SOCKET="/data/mysql_db/test_db/mysql.sock" mysql_connect="mysql -u${MYSQL_USER} -S${MYSQL_SOCKET} " # 美化輸出 function techo() { message=$1 message_level=$2 if [ -e $message_level ];then message_level="info" fi echo "`date "+%F %T"` - [${message_level}] $message" >> $log_file } # 檢查函數(shù), 正常返回 0 function check { ret=`$mysql_connect -N -e "select 1 as value"` if [ $? -ne 0 ] || [ $ret -ne "1" ];then return 1 else return 0 fi } # 獲取 slave status 的信息 function slave_info() { tmp_file=/tmp/slave_info.tmp $mysql_connect -e "show slave statusG" > /tmp/slave_info.tmp slave_sql=`grep "Slave_SQL_Running:" $tmp_file | sed "s/s*//g" | tr "A-Z" "a-z" | awk -F":" "{print $2}"` seconds_behind_master=`grep "Seconds_Behind_Master:" $tmp_file | sed "s/s*//g" | tr "A-Z" "a-z" | awk -F":" "{print $2}"` master_log_file=`grep "Master_Log_File:" $tmp_file | head -1 | sed "s/s*//g" | tr "A-Z" "a-z" | awk -F":" "{print $2}"` master_log_pos=`grep "Read_Master_Log_Pos:" $tmp_file | sed "s/s*//g" | tr "A-Z" "a-z" | awk -F":" "{print $2}"` relay_master_log_file=`grep "Relay_Master_Log_File:" $tmp_file | sed "s/s*//g" | tr "A-Z" "a-z" | awk -F":" "{print $2}"` exec_master_log_pos=`grep "Exec_Master_Log_Pos:" $tmp_file | sed "s/s*//g" | tr "A-Z" "a-z" | awk -F":" "{print $2}"` } # 設(shè)置是否可讀 function read_only { param=$1 $mysql_connect -e "set global read_only = ${param}" techo "設(shè)置是否只讀 read_only ${param}" } # 處理數(shù)據(jù)同步 function sync_master_log() { # 如果是數(shù)據(jù)一致性優(yōu)先,等待同步完畢。如果是服務(wù)可用性優(yōu)先,可以注銷下面的代碼 slave_info if [ $slave_sql == "yes" ];then techo "當(dāng)前同步位置 Master ${master_log_file} ${master_log_pos}" techo "等待同步到 Master ${master_log_file} ${master_log_pos}" $mysql_connect -e "select master_pos_wait("$master_log_file", $master_log_pos);" > /dev/null techo "同步完畢" fi } techo "當(dāng)前數(shù)據(jù)庫提升為主庫" check if [ $? -ne 0 ];then techo "無法連接當(dāng)前數(shù)據(jù)庫" exit 1 fi # 等待同步 sync_master_log # 設(shè)為可寫 read_only 0
注意:腳本沒有經(jīng)過嚴(yán)格測試,需要根據(jù)實際情況調(diào)整
3.4 啟動 keepalived由于配置了 BACKUP 模式,所以兩個 keepalived 先起來的是主,先后在主備節(jié)點執(zhí)行
/etc/init.d/keepalived start
檢查 /var/log/message 日志,確認(rèn) keepalived 沒有報錯
檢查 Master IP 狀態(tài), 確認(rèn)設(shè)置了 VIP
[root@cluster01 shell]# ip addr 1: lo:mtu 16436 qdisc noqueue state UNKNOWN link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00 inet 127.0.0.1/8 scope host lo inet6 ::1/128 scope host valid_lft forever preferred_lft forever 2: eth0: mtu 1500 qdisc pfifo_fast state UP qlen 1000 link/ether fa:16:3e:de:80:33 brd ff:ff:ff:ff:ff:ff inet 10.0.0.247/16 brd 10.0.255.255 scope global eth0 inet 10.0.0.237/32 scope global eth0 inet6 fe80::f816:3eff:fede:8033/64 scope link valid_lft forever preferred_lft forever
檢查 MySQL 檢測腳本執(zhí)行情況,確認(rèn)正常運行
[root@cluster01 ~]# tail -f /var/log/keepalived_mysql.log ... 2019-01-28 15:04:18 - [info] MySQL is ok 2019-01-28 15:04:28 - [info] MySQL is ok4 失效轉(zhuǎn)移測試
在 mytest 庫里新建 nowdate 測試表,只有 id 和 ctime 字段,然后每秒插入一條數(shù)據(jù)
[root@cluster03 ~]# while true; do date;mysql -h10.0.0.237 -P3306 -umytest -e "use mytest;insert into nowdate values (null, now());"; sleep 1;done Mon Jan 28 15:04:26 CST 2019 Mon Jan 28 15:04:27 CST 2019 ...
kill 掉 Master 進程
killall mysqld
查看舊 Master 日志
2019-01-28 15:04:48 - [info] MySQL is ok 2019-01-28 15:04:58 - [info] Connection failed 1 time(s) 2019-01-28 15:04:59 - [info] Connection failed 2 time(s) 2019-01-28 15:05:00 - [info] Connection failed 3 time(s) 2019-01-28 15:05:01 - [info] Connection failed 4 time(s) 2019-01-28 15:05:02 - [info] 無法連接當(dāng)前數(shù)據(jù)庫 2019-01-28 15:05:02 - [info] 開始執(zhí)行失效轉(zhuǎn)移 2019-01-28 15:05:02 - [info] 設(shè)置是否只讀 read_only 1 2019-01-28 15:05:02 - [info] 失效轉(zhuǎn)移執(zhí)行成功,當(dāng)前數(shù)據(jù)庫關(guān)閉訪問
查看新 Master 日志
2019-01-28 15:05:04 - [info] 當(dāng)前數(shù)據(jù)庫提升為主庫 2019-01-28 15:05:04 - [info] 當(dāng)前同步位置 Master mysql-bin.000015 32338 2019-01-28 15:05:04 - [info] 等待同步到 Master mysql-bin.000015 32338 2019-01-28 15:05:04 - [info] 同步完畢 2019-01-28 15:05:04 - [info] 設(shè)置是否只讀 read_only 0 2019-01-28 15:05:05 - [info] MySQL is ok
查看新 Master IP,確認(rèn) VIP 已經(jīng)飄過來了
[root@cluster02 ~]# ip addr 1: lo:mtu 16436 qdisc noqueue state UNKNOWN link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00 inet 127.0.0.1/8 scope host lo inet6 ::1/128 scope host valid_lft forever preferred_lft forever 2: eth0: mtu 1500 qdisc pfifo_fast state UP qlen 1000 link/ether fa:16:3e:66:7e:e8 brd ff:ff:ff:ff:ff:ff inet 10.0.0.248/16 brd 10.0.255.255 scope global eth0 inet 10.0.0.237/32 scope global eth0 inet6 fe80::f816:3eff:fe66:7ee8/64 scope link valid_lft forever preferred_lft forever
查看插入數(shù)據(jù)執(zhí)行情況,大概有 12 秒是不可用的
Mon Jan 28 15:04:51 CST 2019 ERROR 2003 (HY000): Can"t connect to MySQL server on "10.0.0.237" (111) Mon Jan 28 15:04:52 CST 2019 ERROR 2003 (HY000): Can"t connect to MySQL server on "10.0.0.237" (111) Mon Jan 28 15:04:53 CST 2019 ERROR 2003 (HY000): Can"t connect to MySQL server on "10.0.0.237" (111) Mon Jan 28 15:04:54 CST 2019 ERROR 2003 (HY000): Can"t connect to MySQL server on "10.0.0.237" (111) Mon Jan 28 15:04:55 CST 2019 ERROR 2003 (HY000): Can"t connect to MySQL server on "10.0.0.237" (111) Mon Jan 28 15:04:56 CST 2019 ERROR 2003 (HY000): Can"t connect to MySQL server on "10.0.0.237" (111) Mon Jan 28 15:04:57 CST 2019 ERROR 2003 (HY000): Can"t connect to MySQL server on "10.0.0.237" (111) Mon Jan 28 15:04:58 CST 2019 ERROR 2003 (HY000): Can"t connect to MySQL server on "10.0.0.237" (111) Mon Jan 28 15:05:00 CST 2019 ERROR 2003 (HY000): Can"t connect to MySQL server on "10.0.0.237" (111) Mon Jan 28 15:05:01 CST 2019 ERROR 2003 (HY000): Can"t connect to MySQL server on "10.0.0.237" (111) Mon Jan 28 15:05:02 CST 2019 ERROR 2003 (HY000): Can"t connect to MySQL server on "10.0.0.237" (111) Mon Jan 28 15:05:03 CST 2019
失效切換成功
5 總結(jié)使用雙主 + keepalived 的優(yōu)點是部署簡單,雙主加半同步情況下,理論上不會丟數(shù)據(jù),適用于中小型 MySQL 集群。缺點也比較明顯,就是增加從節(jié)點的情況下,從節(jié)點不會主動切換同步對象,而且腳本需要自己實現(xiàn),有一定風(fēng)險。
文章版權(quán)歸作者所有,未經(jīng)允許請勿轉(zhuǎn)載,若此文章存在違規(guī)行為,您可以聯(lián)系管理員刪除。
轉(zhuǎn)載請注明本文地址:http://specialneedsforspecialkids.com/yun/8086.html
摘要:雙主是一個比較簡單的高可用架構(gòu),適用于中小集群,今天就說說怎么用做的高可用。缺點也比較明顯,就是增加從節(jié)點的情況下,從節(jié)點不會主動切換同步對象,而且腳本需要自己實現(xiàn),有一定風(fēng)險。 雙主 + keepalived 是一個比較簡單的 MySQL 高可用架構(gòu),適用于中小 MySQL 集群,今天就說說怎么用 keepalived 做 MySQL 的高可用。 1 概述 1.1 keepalive...
MySQL高可用方案測試 img{ display:block; margin:0 auto !important; width:100%; } body{ width:75%; margin...
閱讀 2847·2021-11-22 15:22
閱讀 19015·2021-09-22 15:00
閱讀 1433·2021-09-07 09:58
閱讀 1236·2019-08-30 13:01
閱讀 2408·2019-08-29 16:27
閱讀 2344·2019-08-26 13:25
閱讀 1618·2019-08-26 12:13
閱讀 934·2019-08-26 11:53