mysqlfailover 是mysql utilities工具包中包含的一個重要的高可用命令，用於對主從複製架構進行健康檢測以及實現故障自動轉移。它會定期按指定的時間間隔探測各節點的健康狀態，一旦在捕獲到主節點不可用時，將觸發故障轉移相關動作，自動執行故障切換到當前最佳的從伺服器上。同時整個主從架構內的其他從節點將指向新的主節點，自動完成主從拓撲結構更新。
相關知識點熱身
基於mysqldump搭建gtid主從
MySQL GTID 錯誤處理匯總
配置MySQL GTID 主從複製
使用mysqldump導出重要參數
持续监控主从主从拓扑结构健康状况，当主节点不可用时，触发自动故障转移 支持GTID全局事务标识符，传统主从模式不支持 支持设置故障转移首选及备选节点，支持投票选举方式选择新的主节点以及仅监测模式(不切换主从) 支持自定义时间监测间隔 支持交互模式以及守护进程的模式开启mysqlfailover 支持在切换前或切换后执行指定的脚本 支持操作记录到日志不同的粒度以及日志老化
四、搭建GTID主從
1、演示環境
主从需要开启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权限2、主從節點參數配置
--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线程错误)阻止故障转移。3、開啟主從節點
# 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
4、將資料同步到從節點並同步開啟主
======================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
五、mysqlfailover切換
1、非守護程序方式啟動mysqlfailover查看主從
--创建主从复制(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 | +------+---------+
2、守護程序方式啟動failover
[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)3、切換後的結果
1、存取拒絕權限問題[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、權限不足的問題###主库端创建对象并不停插入记录实现初步模拟真实环境[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)
4、多個mysqlfailover進程啟動問題# 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';
4、多個mysqlfailover進程啟動問題
# 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;4、多mysqlfailover進程啟動問題[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; --该表记录了主节点及端口号🎜4、多mysqlfailover進程啟動問題🎜rrreee
陳述
本文內容由網友自願投稿，版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容，請聯絡admin@php.cn
如何使用MySQL的函數進行數據處理和計算Apr 29, 2025 pm 04:21 PMMySQL函數可用於數據處理和計算。 1.基本用法包括字符串處理、日期計算和數學運算。 2.高級用法涉及結合多個函數實現複雜操作。 3.性能優化需避免在WHERE子句中使用函數，並使用GROUPBY和臨時表。
MySQL批量插入數據的高效方法Apr 29, 2025 pm 04:18 PMMySQL批量插入数据的高效方法包括：1.使用INSERTINTO...VALUES语法，2.利用LOADDATAINFILE命令，3.使用事务处理，4.调整批量大小，5.禁用索引，6.使用INSERTIGNORE或INSERT...ONDUPLICATEKEYUPDATE，这些方法能显著提升数据库操作效率。
給MySQL表添加和刪除字段的操作步驟Apr 29, 2025 pm 04:15 PM在MySQL中，添加字段使用ALTERTABLEtable_nameADDCOLUMNnew_columnVARCHAR(255)AFTERexisting_column，刪除字段使用ALTERTABLEtable_nameDROPCOLUMNcolumn_to_drop。添加字段時，需指定位置以優化查詢性能和數據結構；刪除字段前需確認操作不可逆；使用在線DDL、備份數據、測試環境和低負載時間段修改表結構是性能優化和最佳實踐。
如何分析MySQL查詢的執行計劃Apr 29, 2025 pm 04:12 PM使用EXPLAIN命令可以分析MySQL查詢的執行計劃。 1.EXPLAIN命令顯示查詢的執行計劃，幫助找出性能瓶頸。 2.執行計劃包括id、select_type、table、type、possible_keys、key、key_len、ref、rows和Extra等字段。 3.根據執行計劃，可以通過添加索引、避免全表掃描、優化JOIN操作和使用覆蓋索引來優化查詢。
如何使用MySQL的子查詢提高查詢效率Apr 29, 2025 pm 04:09 PM子查詢可以提升MySQL查詢效率。 1)子查詢簡化複雜查詢邏輯，如篩選數據和計算聚合值。 2)MySQL優化器可能將子查詢轉換為JOIN操作以提高性能。 3)使用EXISTS代替IN可避免多行返回錯誤。 4)優化策略包括避免相關子查詢、使用EXISTS、索引優化和避免子查詢嵌套。
MySQL的字符集和排序規則如何配置Apr 29, 2025 pm 04:06 PM在MySQL中配置字符集和排序規則的方法包括：1.設置服務器級別的字符集和排序規則：SETNAMES'utf8';SETCHARACTERSETutf8;SETCOLLATION_CONNECTION='utf8_general_ci';2.創建使用特定字符集和排序規則的數據庫：CREATEDATABASEexample_dbCHARACTERSETutf8COLLATEutf8_general_ci;3.創建表時指定字符集和排序規則：CREATETABLEexample_table(idINT
怎樣卸載MySQL並清理殘留文件Apr 29, 2025 pm 04:03 PM要安全、徹底地卸載MySQL並清理所有殘留文件，需遵循以下步驟：1.停止MySQL服務；2.卸載MySQL軟件包；3.清理配置文件和數據目錄；4.驗證卸載是否徹底。
如何在MySQL中重命名數據庫Apr 29, 2025 pm 04:00 PMMySQL中重命名數據庫需要通過間接方法實現。步驟如下：1.創建新數據庫；2.使用mysqldump導出舊數據庫；3.將數據導入新數據庫；4.刪除舊數據庫。
熱AI工具
Undresser.AI Undress
人工智慧驅動的應用程序，用於創建逼真的裸體照片
AI Clothes Remover
用於從照片中去除衣服的線上人工智慧工具。
Undress AI Tool
免費脫衣圖片
Clothoff.io
AI脫衣器
Video Face Swap
使用我們完全免費的人工智慧換臉工具，輕鬆在任何影片中換臉！
熱門文章
Windows 11 KB5054979中的新功能以及如何解決更新問題
3 週前ByDDD
如何修復KB5055523無法在Windows 11中安裝？
2 週前ByDDD
Inzoi：如何申請學校和大學
3 週前ByDDD
如何修復KB5055518無法在Windows 10中安裝？
2 週前ByDDD
Roblox：Dead Rails - 如何召喚和擊敗Nikola Tesla
4 週前By尊渡假赌尊渡假赌尊渡假赌
熱工具
記事本++7.3.1
好用且免費的程式碼編輯器
SublimeText3 Linux新版
SublimeText3 Linux最新版
WebStorm Mac版
好用的JavaScript開發工具
MinGW - Minimalist GNU for Windows
這個專案正在遷移到osdn.net/projects/mingw的過程中，你可以繼續在那裡關注我們。 MinGW：GNU編譯器集合（GCC）的本機Windows移植版本，可自由分發的導入函式庫和用於建置本機Windows應用程式的頭檔；包括對MSVC執行時間的擴展，以支援C99功能。 MinGW的所有軟體都可以在64位元Windows平台上運作。
PhpStorm Mac 版本
最新（2018.2.1 ）專業的PHP整合開發工具
