MySQL 自动故障转移工具--mysqlfailover

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>[<[group]>]    

    --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线程错误)阻止故障转移。 

四、搭建GTID主从

1、演示环境

# more /etc/redhat-release 
CentOS release 6.7 (Final)

# more /etc/hosts
192.168.1.233 node233.edq.com node233  ###用作主节点
192.168.1.245 node245.edq.com node245  ###用作从节点Slave1
192.168.1.247 node247.edq.com node247  ###用作从节点Slave2

# mysql -V
mysql  Ver 14.14 Distrib 5.6.30, for linux-glibc2.5 (x86_64) using  EditLine wrapper

# mysqlfailover --version
MySQL 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=root
password=pass

[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

basedir = /usr/local/mysql
datadir = /data

server_id=233
gtid_mode=on
enforce_gtid_consistency=on

#binlog
log_bin=node233-binlog
log-slave-updates=1
binlog_format=row
report_host=192.168.1.233
report_port=3306
master-info-repository = TABLE
relay-log-info-repository = TABLE
#relay log
skip_slave_start=1

======================192.168.1.245 Slave1========================
[mysql]
prompt= (\u@192.168.1.245)[\d]>
user=root
password=pass

[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
basedir = /usr/local/mysql
datadir = /data

server_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.245
report_port=3306
master-info-repository = TABLE
relay-log-info-repository = TABLE
skip_slave_start=1     

======================192.168.1.247 Slave2========================
[mysql]
prompt= (\u@192.168.1.247)[\d]>
user=root
password=pass

[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
basedir = /usr/local/mysql
datadir = /data
user = mysql 

#Author : Leshami
#Blog   : http://blog.csdn.net/leshami

server_id=247
gtid_mode=on
enforce_gtid_consistency=on

log_bin=node247-binlog
log-slave-updates=1
binlog_format=row
report_host=192.168.1.247
report_port=3306
master-info-repository = TABLE
relay-log-info-repository = TABLE
skip_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:pass
WARNING: 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模式为auto

Master 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-403

Replication 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,查看GTID
Master GTID Executed Set
+---------------------------------------------+
| gtid                                        |
+---------------------------------------------+
| 8dc97c98-9439-11e6-9968-000c29b82d0d:1-403  |
+---------------------------------------------+

--大写U,查看UUID
UUIDs
+----------------+-------+---------+---------------------------------------+
| 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/sh
cnt=1

while [ $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.sh
Warning: Using a password on the command line interface can be insecure.
Insert 2
Warning: Using a password on the command line interface can be insecure.
Insert 3
Warning: 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 164
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)

###以守护进程方式启动mysalfailover                  
[root@node233 ~]# mysqlfailover --master=failover:pass@'192.168.1.233':3306 \
> --discover-slaves-login=failover:pass --log=/tmp/failover.log --daemon=start
WARNING: 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.log
2016-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:3306
2016-10-17 17:47:54 PM INFO Discovering slave at 192.168.1.245:3306
2016-10-17 17:47:54 PM INFO Found slave: 192.168.1.245:3306
2016-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:3306
2016-10-17 17:47:54 PM INFO Found slave: 192.168.1.247:3306
2016-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 Information
2016-10-17 17:47:57 PM INFO Binary Log File: node233-binlog.000003, Position: 25463, Binlog_Do_DB: N/A, Binlog_Ignore_DB: N/A
2016-10-17 17:47:57 PM INFO GTID Executed Set: 8dc97c98-9439-11e6-9968-000c29b82d0d:1-511
2016-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: OK
2016-10-17 17:47:57 PM INFO host: 192.168.1.245, port: 3306, role: SLAVE, state: UP, gtid_mode: ON, health: OK
2016-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:3306
2016-10-17 17:48:15 PM INFO Discovering slave at 192.168.1.245:3306
2016-10-17 17:48:15 PM INFO Discovering slave at 192.168.1.247:3306
2016-10-17 17:48:15 PM INFO Master Information
2016-10-17 17:48:15 PM INFO Binary Log File: node233-binlog.000003, Position: 33887, Binlog_Do_DB: N/A, Binlog_Ignore_DB: N/A
2016-10-17 17:48:15 PM INFO GTID Executed Set: 8dc97c98-9439-11e6-9968-000c29b82d0d:1-547
2016-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: OK
2016-10-17 17:48:15 PM INFO host: 192.168.1.245, port: 3306, role: SLAVE, state: UP, gtid_mode: ON, health: OK
2016-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 stop

2016-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用于failover
2016-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:3306
2016-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/A
2016-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: OK
2016-10-17 17:49:06 PM INFO Discovering slaves for master at 192.168.1.245:3306
2016-10-17 17:49:06 PM INFO Discovering slave at 192.168.1.247:3306
2016-10-17 17:49:06 PM INFO Found slave: 192.168.1.247:3306
2016-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 Information
2016-10-17 17:49:06 PM INFO Binary Log File: node245-binlog.000002, Position: 41173, Binlog_Do_DB: N/A, Binlog_Ignore_DB: N/A
2016-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: OK
2016-10-17 17:49:06 PM INFO host: 192.168.1.247, port: 3306, role: SLAVE, state: UP, gtid_mode: ON, health: OK
2016-10-17 17:49:24 PM INFO Discovering slaves for master at 192.168.1.245:3306
2016-10-17 17:49:24 PM INFO Discovering slave at 192.168.1.247:3306
2016-10-17 17:49:24 PM INFO Master Information
2016-10-17 17:49:24 PM INFO Binary Log File: node245-binlog.000002, Position: 41173, Binlog_Do_DB: N/A, Binlog_Ignore_DB: N/A
2016-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: OK
2016-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:pass
WARNING: 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:pass
WARNING: 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;  --该表记录了主节点及端口号      

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Hadoop实操

如何使用Sentry管理Hive外部表权限

使用如下命令在HDFS的根目录下创建Hive外部表的数据目录/extwarehouse

2K80
来自专栏码农二狗

php持久化连接数据库

23330
来自专栏乐沙弥的世界

基于mysqldump快速搭建从库

    mysql主从搭建总的来说大致分为3个步骤,一是为主从实例添加复制所需参数以及创建复制用的账户,二在是需要在主库建立快照,三是在从库上添加指向主库IP,...

8110
来自专栏JAVA技术站

Spring整合Rabbitmq

没有找到一篇完整的文章介绍Spring如何整合Rabbitmq应用,琢磨一天搞出的一个入门的demo与伙伴们分享.

7520
来自专栏杨建荣的学习笔记

dg broker校验失败的一个奇怪问题(二) (r8笔记第51天)

对昨天提出的问题做了一个简单的分析和排查,也算是有了一个交代,上一篇文章在 dg broker校验失败的一个奇怪问题 我查看了最近的日志,发现在半个月以前有一...

36450
来自专栏dalaoyang

SpringBoot使用Mybatis-Generator

Mybatis-Generator是Mybatis提供的一个便捷型插件,自动可以为项目生产对应的实体类,Mapper,dao层。

27710
来自专栏Laoqi's Linux运维专列

zabbix 2.4.7 proxy代理端升级到zabbix 3.2.3

40920
来自专栏yukong的小专栏

【ssm个人博客项目实战01】SSM环境搭建1、ssm系统架构2、项目整合

maven是一个优秀的项目对象管理器工具 我可以通过在pom.xml中添加需要的jar包的依赖就可以导入对应的jar包了,非常的方便。 下面就列出整合ssm所...

65320
来自专栏乐沙弥的世界

Linux下基于源码方式安装MySQL 5.6

    MySQL为开源数据库,因此可以基于源码实现安装。基于源码安装有更多的灵活性。也就是说我们可以针对自己的硬件平台选用合适的编译器来优化编译后的二进制代码...

11510
来自专栏bboysoul

linux 格式化硬盘

fdisk /dev/sda 输入o建立dos分区表 输入n建立一个新的分区 输入p建立一个主分区,或者输入e建立一个逻辑分区 之后输入分区号码mbr只...

25750

扫码关注云+社区

领取腾讯云代金券