mysqlfailover 是mysql utilities工具包中包含的一個重要的高可用命令,用于對主從復制架構進行健康檢測以及實現故障自動轉移。它會定期按指定的時間間隔探測各節點的健康狀態,一旦在捕獲到主節點不可用時,將觸發故障轉移相關動作,自動執行故障切換到當前最佳的從服務器上。同時整個主從架構內的其他從節點將指向新的主節點,自動完成主從拓撲結構更新。
相關知識點熱身
基于mysqldump搭建gtid主從 ?
MySQL GTID 錯誤處理匯總 ?
配置MySQL GTID 主從復制 ?
使用mysqldump導出數據庫
一、mysqlfailover特點
持續監控主從主從拓撲結構健康狀況,當主節點不可用時,觸發自動故障轉移 支持GTID全局事務標識符,傳統主從模式不支持 支持設置故障轉移首選及備選節點,支持投票選舉方式選擇新的主節點以及僅監測模式(不切換主從) 支持自定義時間監測間隔 支持交互模式以及守護進程的模式開啟mysqlfailover 支持在切換前或切換后執行指定的腳本 支持操作記錄到日志不同的粒度以及日志老化
二、mysqlfailover需求
????主從需要開啟GTID模式(mysql?應使用5.6.5以上版本)???? ????所有的slave端需要配置以下參數,建議主庫也添加(切換后主從模式變化) ????????????report-host ????????????report-port ????????????master-info-repository=TABLE ????????????relay-log-info-repository=TABLE ????權限(mysqlfailover工具檢測及切換期間需要,主從都需要) ????????????SHOW?SLAVE?STATUS ????????????SHOW?MASTER?STATUS ????????????STOP?SLAVE,?START?SLAVE,?WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS,?CHANGE?MASTER?TO ????????????REPLICATE?SLAVE ????????????SUPER,?GRANT?OPTION,?RELOAD,?DROP,?CREATE,?INSERT?,SELECT權限
三、一些重要參數
????--failover-mode(轉移模式) ????????auto:執行故障自動轉移到第一候選人。如果沒有從可行的,繼續從從列表中找到一個可行的候選者。 ??????????如果沒有從被認為是一個可行的候選者,該程序將生成錯誤并退出。一旦候選者被發現,該程序將進行故障切換到最佳從上。 ??????????該命令測試每個候選從的先決條件。一旦候選從被選中,其他從作為它的從,收集其他從的任何事務交易。該方式,確保候選者是最新的從 ????????elect:這種模式與aoto一樣的,除了如果在指定的候選從列表中沒有可行的,不檢測剩余的從和產生錯誤并退出。 ????????fail:?這種模式會產生一個錯誤,當主失敗后不會進行故障轉移。這種模式被用來只進行定期健康監測不進行故障切換。 ????--interval ????????????選項來定義檢測主狀態和產生健康報告的時間間隔,缺省為15s,最小間隔為5s ????--master=MASTER ????????????主服務器連接配置 ????--slaves=SLAVES ????????????從服務器連接配置 ????--candidates=CANDIDATES ????????????候選服務器連接配置 ????????????以上3個連接配置值支持是使用下列方式,多個值以逗號分割 ????????????<user>[:<password>]@<host>[:<port>][:<socket>]? ????????????<login-path>[:<port>][:<socket>] ????????????<config-path>[]???? ????--discover-slaves-login=DISCOVER ????????????基于主服務器用戶密碼查詢當前所有注冊到主服務器的從庫端 ????????????使用<user>[:<password>]?or?<login-path> ????--ping=PING?? ?????????????Number?of?ping?attempts?for?detecting?downed?server.????? ????????偵測服務器宕機檢測,缺省為3s ????--force???override?the?registration?check?on?master?for?multiple ??????????instances?of?the?console?monitoring?the?same?master. ????????當控制臺啟動時,主的主機名和端口將被插入一個特殊的表來跟蹤記錄哪些實例與主聯系? ????????在啟動時,如果行匹配這些值,控制臺無法啟動。如果使用--force選項,該行將被刪除?????????????????????????????????? ????--daemon(使用守護進程方式,如未指定,則為交互方式) ????????????start????啟動守護進程。需要--log選項。 ????????????stop?????停止守護進程。如果有指定--pidfile選項,該值需要與start指定的一致。 ????????????restart??重新啟動守護進程。如果有指定--pidfile選項,該值需要與start指定的一致。 ????????????nodetach?啟動守護進程,但是不會從控制臺分離進程。需要--log選項。 ????--log=<log_file>? ????????????指定日志文件 ????--log-age? ????????????選項用于指定日志存放天數,默認是7天。舊的日志自動輪滾掉 ????--exec-before ????????????故障轉移命令之前執行外部腳本 ????????????腳本的返回碼來確定是否成功執行,0表示成功,非0表示失敗并返回錯誤信息 ????--exec-after ????????????故障轉移命令之后執行外部腳本 ????????????腳本的返回碼來確定是否成功執行,0表示成功,非0表示失敗并返回錯誤信息 ????-p,?--pedantic????????fail?if?some?inconsistencies?are?found?(e.g.?errant ????????????????????????????transactions?on?slaves).???????????? ????????????在檢查期間如果發現有不一致的情況(從錯誤的事務或SQL線程錯誤)阻止故障轉移。</log_file></login-path></password></user></config-path></socket></port></login-path></socket></port></host></password></user>
四、搭建GTID主從
1、演示環境
#?more?/etc/redhat-release?CentOS?release?6.7?(Final)#?more?/etc/hosts192.168.1.233?node233.edq.com?node233??###用作主節點192.168.1.245?node245.edq.com?node245??###用作從節點Slave1192.168.1.247?node247.edq.com?node247??###用作從節點Slave2#?mysql?-Vmysql??Ver?14.14?Distrib?5.6.30,?for?linux-glibc2.5?(x86_64)?using??EditLine?wrapper#?mysqlfailover?--versionMySQL?Utilities?mysqlfailover?version?1.6.4?License?type:?GPLv2
2、主從節點參數配置
======================192.168.1.233?Master======================== [mysql]#prompt=?(u@h)[d]>prompt=?(u@192.168.1.233)[d]>user=rootpassword=pass [mysqld]sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLESbasedir?=?/usr/local/mysqldatadir?=?/dataserver_id=233gtid_mode=onenforce_gtid_consistency=on#binloglog_bin=node233-binlog log-slave-updates=1binlog_format=rowreport_host=192.168.1.233report_port=3306 master-info-repository?=?TABLE relay-log-info-repository?=?TABLE#relay?logskip_slave_start=1 ======================192.168.1.245?Slave1======================== [mysql]prompt=?(u@192.168.1.245)[d]>user=rootpassword=pass [mysqld]sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLESbasedir?=?/usr/local/mysqldatadir?=?/dataserver_id=245???????????????? gtid_mode=on????????????????? enforce_gtid_consistency=on?? log_bin=node245-binlog log-slave-updates=1???? binlog_format=row???????????? report_host=192.168.1.245report_port=3306 master-info-repository?=?TABLE relay-log-info-repository?=?TABLEskip_slave_start=1????? ======================192.168.1.247?Slave2======================== [mysql]prompt=?(u@192.168.1.247)[d]>user=rootpassword=pass [mysqld]sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLESbasedir?=?/usr/local/mysqldatadir?=?/datauser?=?mysql? #Author?:?Leshami#Blog?:??http://www.php.cn/server_id=247gtid_mode=onenforce_gtid_consistency=onlog_bin=node247-binlog???? log-slave-updates=1binlog_format=rowreport_host=192.168.1.247report_port=3306 master-info-repository?=?TABLE relay-log-info-repository?=?TABLEskip_slave_start=1
3、開啟主從節點
--創建主從復制(repl)及切換賬戶(failover) --以下mysql提示符下Master即代表1.233節點執行,Slave1代表1.245上執行,Slave2代表1.247上執行 (root@Master)[mysql]>grant?all?privileges?on?*.*?to?'failover'@'%'?identified?by?'pass'?with?grant?option; (root@Master)[mysql]>grant?replication?slave?on?*.*?to?'repl'@'%'?identified?by?'123456'; (root@Slave1)[(none)]>grant?all?privileges?on?*.*?to?'failover'@'%'?identified?by?'pass'?with?grant?option; (root@Slave1)[(none)]>grant?replication?slave?on?*.*?to?'repl'@'%'?identified?by?'123456'; (root@Slave2)[(none)]>grant?all?privileges?on?*.*?to?'failover'@'%'?identified?by?'pass'?with?grant?option; (root@Slave2)[(none)]>grant?replication?slave?on?*.*?to?'repl'@'%'?identified?by?'123456'; --主節點上執行相應的操作 [root@node233?~]#?mysql?-uroot?-ppass?-e?"create?database?testdb;create?database?tempdb"[root@node233?~]#?mysql?-uroot?-ppass?-e?"create?table?testdb.repl(id?int,ename?varchar(50))"[root@node233?~]#?mysql?-uroot?-ppass?-e?"insert?into?testdb.repl?values(1,'leshami')"[root@node233?~]#?mysql?-uroot?-ppass?-e?"select?*?from?testdb.repl"??????????????????+------+---------+ |?id???|?ename???| +------+---------+ |????1?|?leshami?| +------+---------+
4、將數據同步到從節點并開啟同步
[root@node233?~]#?mysqldump?--all-databases?--single-transaction?--triggers?--routines?--events? >?--host=localhost?--port=3306?--user=root?--password=pass?>/tmp/alldb.sql? [root@node233?~]#?scp?/tmp/alldb.sql?192.168.1.245:/tmp [root@node233?~]#?scp?/tmp/alldb.sql?192.168.1.247:/tmp (root@Slave1)[testdb]>reset?master; (root@Slave1)[(none)]>source?/tmp/alldb.sql (root@Slave1)[testdb]>CHANGE?MASTER?TO?? ????->?MASTER_HOST='192.168.1.233',????????->?MASTER_USER='rpl',????????->?MASTER_PASSWORD='rpl',????????->?MASTER_PORT=3306,????????->?MASTER_AUTO_POSITION?=?1;Query?OK,?0?rows?affected,?2?warnings?(0.06?sec) (root@Slave1)[testdb]>start?slave; (root@Slave2)[(none)]>reset?master; (root@Slave2)[(none)]>source?/tmp/alldb.sql (root@Slave2)[testdb]>CHANGE?MASTER?TO?? ????->?MASTER_HOST='192.168.1.233',????????->?MASTER_USER='rpl',????????->?MASTER_PASSWORD='rpl',????????->?MASTER_PORT=3306,????????->?MASTER_AUTO_POSITION?=?1;Query?OK,?0?rows?affected,?2?warnings?(0.02?sec) (root@Slave2)[testdb]>start?slave; --分別在245及247節點上驗證 [root@node245?mysql]#?mysql?-uroot?-p?-e?"select?*?from?testdb.repl"Enter?password:? +------+---------+|?id???|?ename???| +------+---------+|????1?|?leshami?| +------+---------+[root@node247?mysql]#?mysql?-uroot?-p?-e?"select?*?from?testdb.repl"Enter?password:? +------+---------+|?id???|?ename???| +------+---------+|????1?|?leshami?| +------+---------+--使用mysqlrplshow查看主從結構 [root@node233?~]#?mysqlrplshow?--master=failover:pass@'192.168.1.233':3306?--discover-slaves-login=failover:pass?--verbose???? WARNING:?Using?a?password?on?the?command?line?interface?can?be?insecure. #?master?on?192.168.1.233:?...?connected. #?Finding?slaves?for?master:?192.168.1.233:3306 #?Replication?Topology?Graph 192.168.1.233:3306?(MASTER)???|???+---?192.168.1.245:3306?[IO:?Yes,?SQL:?Yes]?-?(SLAVE)???|???+---?192.168.1.247:3306?[IO:?Yes,?SQL:?Yes]?-?(SLAVE)
五、mysqlfailover切換
1、非守護進程方式啟動mysqlfailover查看主從
[root@node233?~]#?mysqlfailover?--master=failover:pass@'192.168.1.233':3306?--discover-slaves-login=failover:passWARNING:?Using?a?password?on?the?command?line?interface?can?be?insecure. #?Discovering?slaves?for?master?at?192.168.1.233:3306 #?Discovering?slave?at?192.168.1.245:3306 #?Found?slave:?192.168.1.245:3306 #?Discovering?slave?at?192.168.1.247:3306 #?Found?slave:?192.168.1.247:3306 #?Checking?privileges. MySQL?Replication?Failover?Utility Failover?Mode?=?auto?????Next?Interval?=?Mon?Oct?17?17:42:36?2016 --如上行,此時failover模式為autoMaster?Information ------------------Binary?Log?File???????Position??Binlog_Do_DB??Binlog_Ignore_DB?? node233-binlog.00000??191??????????????????????????????????????? GTID?Executed?Set?????--已經執行的GTID 8dc97c98-9439-11e6-9968-000c29b82d0d:1-403Replication?Health?Status???--主從復制的健康狀態 +----------------+-------+---------+--------+------------+---------+|?host???????????|?port??|?role????|?state??|?gtid_mode??|?health??| +----------------+-------+---------+--------+------------+---------+|?192.168.1.233??|?3306??|?MASTER??|?UP?????|?ON?????????|?OK??????| |?192.168.1.245??|?3306??|?SLAVE???|?UP?????|?ON?????????|?OK??????||?192.168.1.247??|?3306??|?SLAVE???|?UP?????|?ON?????????|?OK??????| +----------------+-------+---------+--------+------------+---------+--輸入大寫G,查看GTIDMaster?GTID?Executed?Set +---------------------------------------------+|?gtid????????????????????????????????????????| +---------------------------------------------+|?8dc97c98-9439-11e6-9968-000c29b82d0d:1-403??| +---------------------------------------------+--大寫U,查看UUIDUUIDs +----------------+-------+---------+---------------------------------------+|?host???????????|?port??|?role????|?uuid??????????????????????????????????| +----------------+-------+---------+---------------------------------------+|?192.168.1.233??|?3306??|?MASTER??|?8dc97c98-9439-11e6-9968-000c29b82d0d??| |?192.168.1.245??|?3306??|?SLAVE???|?5dacc005-943a-11e6-996d-000c29328504??||?192.168.1.247??|?3306??|?SLAVE???|?eca3bd57-943a-11e6-9971-000c292e1642??| +----------------+-------+---------+---------------------------------------+--輸入大寫Q,退出mysqlfailover
2、守護進程方式啟動failover
###主庫端創建對象并不停插入記錄實現初步模擬真實環境[root@node233?~]#?mysql?-uroot?-ppass?-e?"create?table?testdb.tb(userId?int)"????[root@node233?~]#?mysql?-uroot?-ppass?-e?"create?table?tempdb.tb(userId?int)"??###使用下面的腳本分別將記錄插入到tempdb以及testdb對應得表中#?more?insert_id.sh?#/bin/shcnt=1while?[?$cnt?-le?10000?] do ????????mysql?-uroot?-ppass?-e?"insert?into?tempdb.tb(userId)?values($cnt); ??????????insert?into?testdb.tb(userId)?values($cnt)"???????? ????????let?cnt=$cnt+1 ???????????sleep?1? ????????echo?"Insert?$cnt"done###執行shell腳本,初步模擬真實環境[root@node233?~]#?./insert_id.shWarning:?Using?a?password?on?the?command?line?interface?can?be?insecure. Insert?2Warning:?Using?a?password?on?the?command?line?interface?can?be?insecure. Insert?3Warning:?Using?a?password?on?the?command?line?interface?can?be?insecure. Insert?4 ??????????????????.........??????????????????###一下錯誤部分為主節點掛掉后拋出的錯誤Warning:?Using?a?password?on?the?command?line?interface?can?be?insecure. ERROR?2002?(HY000):?Can't?connect?to?local?MySQL?server?through?socket?'/tmp/mysql.sock'?(2) Insert?164Warning:?Using?a?password?on?the?command?line?interface?can?be?insecure. ERROR?2002?(HY000):?Can't?connect?to?local?MySQL?server?through?socket?'/tmp/mysql.sock'?(2)###以守護進程方式啟動mysalfailover??????????????????[root@node233?~]#?mysqlfailover?--master=failover:pass@'192.168.1.233':3306?>?--discover-slaves-login=failover:pass?--log=/tmp/failover.log?--daemon=startWARNING:?Using?a?password?on?the?command?line?interface?can?be?insecure. NOTE:?Log?file?'/tmp/failover.log'?does?not?exist.?Will?be?created. Starting?failover?daemon... [root@node233?~]#?tail?-fn?50?/tmp/failover.log2016-10-17?17:47:54?PM?INFO?MySQL?Utilities?mysqlfailover?version?1.6.4.2016-10-17?17:47:54?PM?INFO?Server?'192.168.1.233:3306'?is?using?MySQL?version?5.6.30-log.2016-10-17?17:47:54?PM?INFO?Discovering?slaves?for?master?at?192.168.1.233:33062016-10-17?17:47:54?PM?INFO?Discovering?slave?at?192.168.1.245:33062016-10-17?17:47:54?PM?INFO?Found?slave:?192.168.1.245:33062016-10-17?17:47:54?PM?INFO?Server?'192.168.1.245:3306'?is?using?MySQL?version?5.6.30-log.2016-10-17?17:47:54?PM?INFO?Discovering?slave?at?192.168.1.247:33062016-10-17?17:47:54?PM?INFO?Found?slave:?192.168.1.247:33062016-10-17?17:47:54?PM?INFO?Server?'192.168.1.247:3306'?is?using?MySQL?version?5.6.30-log.2016-10-17?17:47:54?PM?INFO?Checking?privileges.2016-10-17?17:47:54?PM?INFO?Unregistering?existing?instances?from?slaves.2016-10-17?17:47:54?PM?INFO?Registering?instance?on?master.2016-10-17?17:47:54?PM?INFO?Failover?daemon?started.2016-10-17?17:47:54?PM?INFO?Failover?mode?=?auto.2016-10-17?17:47:57?PM?INFO?Master?Information2016-10-17?17:47:57?PM?INFO?Binary?Log?File:?node233-binlog.000003,?Position:?25463,?Binlog_Do_DB:?N/A,?Binlog_Ignore_DB:?N/A2016-10-17?17:47:57?PM?INFO?GTID?Executed?Set:?8dc97c98-9439-11e6-9968-000c29b82d0d:1-5112016-10-17?17:47:57?PM?INFO?Getting?health?for?master:?192.168.1.233:3306.2016-10-17?17:47:57?PM?INFO?Health?Status:2016-10-17?17:47:57?PM?INFO?host:?192.168.1.233,?port:?3306,?role:?MASTER,?state:?UP,?gtid_mode:?ON,?health:?OK2016-10-17?17:47:57?PM?INFO?host:?192.168.1.245,?port:?3306,?role:?SLAVE,?state:?UP,?gtid_mode:?ON,?health:?OK2016-10-17?17:47:57?PM?INFO?host:?192.168.1.247,?port:?3306,?role:?SLAVE,?state:?UP,?gtid_mode:?ON,?health:?OK###上述部分完成主從、權限檢查,成功啟動mysqlfailover后輸出主從健康狀態,后會間隔1s持續檢查主從健康狀態2016-10-17?17:48:15?PM?INFO?Discovering?slaves?for?master?at?192.168.1.233:33062016-10-17?17:48:15?PM?INFO?Discovering?slave?at?192.168.1.245:33062016-10-17?17:48:15?PM?INFO?Discovering?slave?at?192.168.1.247:33062016-10-17?17:48:15?PM?INFO?Master?Information2016-10-17?17:48:15?PM?INFO?Binary?Log?File:?node233-binlog.000003,?Position:?33887,?Binlog_Do_DB:?N/A,?Binlog_Ignore_DB:?N/A2016-10-17?17:48:15?PM?INFO?GTID?Executed?Set:?8dc97c98-9439-11e6-9968-000c29b82d0d:1-5472016-10-17?17:48:15?PM?INFO?Getting?health?for?master:?192.168.1.233:3306.2016-10-17?17:48:15?PM?INFO?Health?Status:2016-10-17?17:48:15?PM?INFO?host:?192.168.1.233,?port:?3306,?role:?MASTER,?state:?UP,?gtid_mode:?ON,?health:?OK2016-10-17?17:48:15?PM?INFO?host:?192.168.1.245,?port:?3306,?role:?SLAVE,?state:?UP,?gtid_mode:?ON,?health:?OK2016-10-17?17:48:15?PM?INFO?host:?192.168.1.247,?port:?3306,?role:?SLAVE,?state:?UP,?gtid_mode:?ON,?health:?OK###此時將主節點mysql關閉,發布命令?[root@node233?~]#?service?mysqld?stop2016-10-17?17:48:42?PM?INFO?Failed?to?reconnect?to?the?master?after?3?attemps.?###3次檢測失敗2016-10-17?17:48:42?PM?CRITICAL?Master?is?confirmed?to?be?down?or?unreachable.2016-10-17?17:48:42?PM?INFO?Failover?starting?in?'auto'?mode...2016-10-17?17:48:42?PM?INFO?Candidate?slave?192.168.1.245:3306?will?become?the?new?master.2016-10-17?17:48:42?PM?INFO?Checking?slaves?status?(before?failover).2016-10-17?17:48:42?PM?INFO?Preparing?candidate?for?failover.???###尋找candidate用于failover2016-10-17?17:48:42?PM?INFO?Creating?replication?user?if?it?does?not?exist.2016-10-17?17:48:42?PM?INFO?Stopping?slaves.2016-10-17?17:48:42?PM?INFO?Performing?STOP?on?all?slaves.2016-10-17?17:48:42?PM?INFO?Switching?slaves?to?new?master.2016-10-17?17:48:42?PM?INFO?Disconnecting?new?master?as?slave.2016-10-17?17:48:42?PM?INFO?Starting?slaves.2016-10-17?17:48:42?PM?INFO?Performing?START?on?all?slaves.2016-10-17?17:48:42?PM?INFO?Checking?slaves?for?errors.2016-10-17?17:48:42?PM?INFO?Failover?complete.?????????????????###此處failover完成2016-10-17?17:48:42?PM?INFO?Discovering?slaves?for?master?at?192.168.1.245:33062016-10-17?17:48:47?PM?INFO?Unregistering?existing?instances?from?slaves.2016-10-17?17:48:47?PM?INFO?Registering?instance?on?new?master?192.168.1.245:3306.2016-10-17?17:48:48?PM?INFO?Master?Information?????????????????###后續部分為持續的主從狀態監測2016-10-17?17:48:48?PM?INFO?Binary?Log?File:?node245-binlog.000002,?Position:?41173,?Binlog_Do_DB:?N/A,?Binlog_Ignore_DB:?N/A2016-10-17?17:48:48?PM?INFO?GTID?Executed?Set:?5dacc005-943a-11e6-996d-000c29328504:1[...]2016-10-17?17:48:48?PM?INFO?Getting?health?for?master:?192.168.1.245:3306.2016-10-17?17:48:48?PM?INFO?Health?Status:2016-10-17?17:48:48?PM?INFO?host:?192.168.1.245,?port:?3306,?role:?MASTER,?state:?UP,?gtid_mode:?ON,?health:?OK2016-10-17?17:49:06?PM?INFO?Discovering?slaves?for?master?at?192.168.1.245:33062016-10-17?17:49:06?PM?INFO?Discovering?slave?at?192.168.1.247:33062016-10-17?17:49:06?PM?INFO?Found?slave:?192.168.1.247:33062016-10-17?17:49:06?PM?INFO?Server?'192.168.1.247:3306'?is?using?MySQL?version?5.6.30-log.2016-10-17?17:49:06?PM?INFO?Master?Information2016-10-17?17:49:06?PM?INFO?Binary?Log?File:?node245-binlog.000002,?Position:?41173,?Binlog_Do_DB:?N/A,?Binlog_Ignore_DB:?N/A2016-10-17?17:49:06?PM?INFO?GTID?Executed?Set:?5dacc005-943a-11e6-996d-000c29328504:1[...]2016-10-17?17:49:06?PM?INFO?Getting?health?for?master:?192.168.1.245:3306.2016-10-17?17:49:06?PM?INFO?Health?Status:2016-10-17?17:49:06?PM?INFO?host:?192.168.1.245,?port:?3306,?role:?MASTER,?state:?UP,?gtid_mode:?ON,?health:?OK2016-10-17?17:49:06?PM?INFO?host:?192.168.1.247,?port:?3306,?role:?SLAVE,?state:?UP,?gtid_mode:?ON,?health:?OK2016-10-17?17:49:24?PM?INFO?Discovering?slaves?for?master?at?192.168.1.245:33062016-10-17?17:49:24?PM?INFO?Discovering?slave?at?192.168.1.247:33062016-10-17?17:49:24?PM?INFO?Master?Information2016-10-17?17:49:24?PM?INFO?Binary?Log?File:?node245-binlog.000002,?Position:?41173,?Binlog_Do_DB:?N/A,?Binlog_Ignore_DB:?N/A2016-10-17?17:49:24?PM?INFO?GTID?Executed?Set:?5dacc005-943a-11e6-996d-000c29328504:1[...]2016-10-17?17:49:24?PM?INFO?Getting?health?for?master:?192.168.1.245:3306.2016-10-17?17:49:24?PM?INFO?Health?Status:2016-10-17?17:49:24?PM?INFO?host:?192.168.1.245,?port:?3306,?role:?MASTER,?state:?UP,?gtid_mode:?ON,?health:?OK2016-10-17?17:49:24?PM?INFO?host:?192.168.1.247,?port:?3306,?role:?SLAVE,?state:?UP,?gtid_mode:?ON,?health:?OK
3、驗證切換后的結果
###從節點Slave2上的記錄數 [root@node247?mysql]#?mysql?-uroot?-p?-e?"select?count(*)?from?testdb.tb"? Enter?password:? +----------+ |?count(*)?|+----------+|???????89?| +----------+[root@node247?mysql]#?mysql?-uroot?-p?-e?"select?count(*)?from?tempdb.tb" Enter?password:? +----------+ |?count(*)?|+----------+|???????89?| +----------+###驗證從節點復制狀態 (root@192.168.1.247)[(none)]>show?slave?status?G***************************?1.?row?***************************???????????????Slave_IO_State:?Waiting?for?master?to?send?event??????????????????Master_Host:?192.168.1.245??????????????????Master_User:?repl??????????????????Master_Port:?3306????????????????Connect_Retry:?60??????????????Master_Log_File:?node245-binlog.000002??????????Read_Master_Log_Pos:?41173???????????????Relay_Log_File:?node247-relay-bin.000002????????????????Relay_Log_Pos:?643????????Relay_Master_Log_File:?node245-binlog.000002?????????????Slave_IO_Running:?Yes????????????Slave_SQL_Running:?Yes###新主節點(原來為Slave1)上的記錄數 [root@node245?mysql]#?mysql?-uroot?-p?-e?"select?count(*)?from?testdb.tb"???? Enter?password:? +----------+ |?count(*)?|+----------+|???????89?| +----------+[root@node245?mysql]#?mysql?-uroot?-p?-e?"select?count(*)?from?tempdb.tb" Enter?password:? +----------+ |?count(*)?|+----------+|???????89?| +----------+###查看新主節點上slave主機 (root@192.168.1.245)[(none)]>show?slave?hosts;+-----------+---------------+------+-----------+--------------------------------------+|?Server_id?|?Host??????????|?Port?|?Master_id?|?Slave_UUID???????????????????????????| +-----------+---------------+------+-----------+--------------------------------------+|???????247?|?192.168.1.247?|?3306?|???????245?|?eca3bd57-943a-11e6-9971-000c292e1642?| +-----------+---------------+------+-----------+--------------------------------------+(root@192.168.1.245)[(none)]>show?slave?status?G Empty?set?(0.00?sec)
六、幾類常見的錯誤
1、訪問拒絕權限問題
#?mysqlrplshow?--master=failover:pass@192.168.1.233:3306?--discover-slaves-login=failover:pass??WARNING:?Using?a?password?on?the?command?line?interface?can?be?insecure.#?master?on?192.168.1.233:?...?FAILED. ERROR:?Access?denied?for?user?'failover'@'node233.edq.com'?(using?password:?YES) 應對方案,用戶需要授予基于主機名的訪問權限 mysql>?grant?all?privileges?on?*.*?to?'failover'@'node233.edq.com'?identified?by?'pass';
2、權限不足的問題
#?ERROR:?User?root?on?192.168.1.233@3306?does?not?have?sufficient?privileges?to?execute?the?failover?command? ????(required:?SUPER,?GRANT?OPTION,?REPLICATION?SLAVE,?SELECT,?RELOAD,?DROP,?CREATE,?INSERT).2016-10-08?16:18:20?PM?CRITICAL?Not?enough?privileges?to?execute?command. 應對方案,用戶需要授予with?grant?option權限 mysql>?grant?all?privileges?on?*.*?to?'root'@'node233.edq.com'?identified?by?'pass'?with?grant?option;
3、配置參數問題
[root@node233?~]#?mysqlfailover?--master=root:pass@192.168.1.233:3306?--discover-slaves-login=root:passWARNING:?Using?a?password?on?the?command?line?interface?can?be?insecure.#?Discovering?slaves?for?master?at?192.168.1.233:3306#?Discovering?slave?at?192.168.1.245:3306#?Found?slave:?192.168.1.245:3306#?Discovering?slave?at?192.168.1.247:3306#?Found?slave:?192.168.1.247:3306#?Checking?privileges.2016-10-08?16:21:40?PM?CRITICAL?Failover?requires?--master-info-repository=TABLE?for?all?slaves.ERROR:?Failover?requires?--master-info-repository=TABLE?for?all?slaves. 應對方案,需要在配置文件中增加上述參數,如本文之前描述
4、多個mysqlfailover進程啟動問題
[root@node233?~]#?mysqlfailover?--master=failover:pass@'192.168.1.233':3306?--discover-slaves-login=failover:passWARNING:?Using?a?password?on?the?command?line?interface?can?be?insecure.#?Discovering?slaves?for?master?at?192.168.1.233:3306#?Discovering?slave?at?192.168.1.245:3306#?Found?slave:?192.168.1.245:3306#?Discovering?slave?at?192.168.1.247:3306#?Found?slave:?192.168.1.247:3306#?Checking?privileges.Multiple?instances?of?failover?console?found?for?master?192.168.1.233: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. MySQL?Replication?Failover?Utility Failover?Mode?=?fail?????Next?Interval?=?Mon?Oct?17?17:02:17?2016如上,如果已經有啟動的mysqlfailover,則出現上述failover模式 如果無啟動的mysqlfailover,也出現上述情形,建議執行以下命令 mysql?>?truncate?table?mysql.failover_console;??--該表記錄了主節點及端口號
? 版權聲明
文章版權歸作者所有,未經允許請勿轉載。
THE END