MySQL 自動故障轉移工具–mysqlfailover

    持續監控主從主從拓撲結構健康狀況，當主節點不可用時，觸發自動故障轉移      支持GTID全局事務標識符，傳統主從模式不支持      支持設置故障轉移首選及備選節點，支持投票選舉方式選擇新的主節點以及僅監測模式(不切換主從)      支持自定義時間監測間隔      支持交互模式以及守護進程的模式開啟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>

#?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

======================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

--創建主從復制(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?|  +------+---------+

[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)

[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

###主庫端創建對象并不停插入記錄實現初步模擬真實環境[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

###從節點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)

#?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';

#?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;

[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.    應對方案，需要在配置文件中增加上述參數，如本文之前描述

[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;??--該表記錄了主節點及端口號