MySQL utility mysqlfailover commands
Automatic replication health monitoring and failover.
MySQL utility mysqlfailover used to perform replication health monitoring and automatic failover on a replication topology consisting of a single master and its slaves.
If MySQL (5.6’s or later) GTID-replication is enabled, mysqlfailover can perform automatic failover. mysqlfailover will monitor the master for failure and when a failure occurs, execute failover to one of the slaves that is in a valid state.
mysqlfailover --help
Usage: mysqlfailover --master=root@localhost --discover-slaves-login=root --candidates=root@host123:3306,root@host456:3306
mysqlfailover --master=root --discover-slaves-login=dbamon
mysqlfailover --master=dba --discover-slaves-login=dba health
mysqlfailover --master=server1 --slaves=server2,server3 --candidates=server2 --daemon=start --log=failover.log
mysqlfailover --master=devhost --discover-slaves-login=sysdba auto --daemon=start --pidfile=/var/log/mysql-failover-daemon.pid --log=/var/log/mysql-failover-daemon.log
mysqlfailover --master=masterhost --discover-slaves-login=satya:secret auto --daemon=stop --pidfile=mysql-failover-daemon.pid --log=mysql-failover.log --verbose
mysqlfailover --master=host2 --slaves=host1,host3 --log=failover3.log --rpl-user=rpluser --candidates=host1 --exec-before=./pre-failover-code.ksh --exec-after=./post-failover-code.ksh
mysqlfailover --master=root@localhost:13001 --discover-slaves-login=root health
mysqlfailover --master=root@localhost:15001 --discover-slaves-login=admin auto
mysqlfailover --master=root@localhost:3306 --discover-slaves-login=dba auto --daemon=start --log=/var/log/mysql-failover.log
mysqlfailover --master=root@localhost:3310 --discover-slaves-login=root --pedantic auto
mysqlfailover –master=root:passwd@localhost:3306 –discover-slaves-login=root:passwd –failover-mode=auto --force
To run mysqlfailover commands, login user must have the following privileges: SUPER, GRANT OPTION, REPLICATION SLAVE, RELOAD, DROP, CREATE, INSERT, and SELECT.
mysql failover options:
--version show program's version number and exit
--help display this help message and exit
--license display program's license and exit
--candidates=CANDIDATES connection information for candidate slave servers for failover in the form: user[:password]@host[:port][:socket] or login-path[:port][:socket] or config-path[[group]] Valid only with failover command. List multiple slaves in comma-separated list.
--discover-slaves-login=DISCOVER at startup, query master for all registered slaves and use the user name and password specified to connect. Supply the user and password in the form
--exec-after=EXEC_AFTER name of script to execute after failover or switchover
--exec-before=EXEC_BEFORE name of script to execute before failover or switchover
--log=LOG_FILE specify a log file to use for logging messages
--log-age=LOG_AGE specify maximum age of log entries in days. Entries older than this will be purged on startup. Default = 7 days.
--master=MASTER connection information for master server in the form:
user[:password]@host[:port][:socket] or login-path[:port][:socket] or config-path[[group]]
--max-position=MAX_POSITION used to detect slave delay. The maximum difference between the master's log position and the slave's reported read position of the master. A value greater than this means the slave is too far behind the master. Default is 0.
--ping=PING Number of ping attempts for detecting downed server.
--seconds-behind=MAX_DELAY used to detect slave delay. The maximum number of seconds behind the master permitted before slave is considered behind the master. Default is 0.
--slaves=SLAVES connection information for slave servers in the form:
user[:password]@host[:port][:socket] or login-path[:port][:socket] or config-path[[group]]
--timeout=TIMEOUT maximum timeout in seconds to wait for each replication command to complete. For example, timeout for slave waiting to catch up to master. Default = 300.
--script-threshold=SCRIPT_THRESHOLD Value for external scripts to trigger aborting the operation if result is greater than or equal to the threshold. Default = None (no threshold checking).
-i INTERVAL, --interval=INTERVAL interval in seconds for polling the master for failure and reporting health. Default = 15 seconds. Lowest value is 5 seconds.
-f FAILOVER_MODE, --failover-mode=FAILOVER_MODE action to take when the master fails.
'auto' = automatically fail to best slave. Default = 'auto'.
'elect' = fail to candidate list or if no candidate meets criteria fail.
'fail' = take no action and stop when master fails.
--exec-fail-check=EXEC_FAIL name of script to execute on each interval to invoke failover
--force override the registration check on master for multiple instances of the console monitoring the same master.
--exec-post-failover=EXEC_POST_FAIL name of script to execute after failover is complete and the utility has refreshed the health report.
-p, --pedantic fail if some inconsistencies are found (e.g. errant transactions on slaves).
--no-keyboard start with no keyboard input support.
--daemon=DAEMON run on daemon mode. It can be start, stop, restart or nodetach.
--pidfile=PIDFILE pidfile for running mysqlfailover as a daemon.
--report-values=REPORT_VALUES report values used in mysqlfailover running as a daemon. It can be health, gtid or uuid. Multiple values can be used separated by commas. The default is health.
--connection-timeout=CONN_TIMEOUT set the connection timeout for TCP and Unix socket connections for all master, slaves, and candidate slaves specified. Default is 10 as provided in the Connector/Python module.
--master-fail-retry=FAIL_RETRY time in seconds to wait to determine master is down. The failover check will be run again when the retry delay expires. Can be used to introduce a longer period between when master is detected as unavailable to declaring it down. This option is not used with --exec-fail-check.
-v, --verbose control how much information is displayed. e.g., -v = verbose, -vv = more verbose, -vvv = debug
--rpl-user=RPL_USER the user and password for the replication user requirement, in the form:
--ssl-ca=SSL_CA path to a file that contains a list of trusted SSL CAs.
--ssl-cert=SSL_CERT name of the SSL certificate file to use for establishing a secure connection.
--ssl-key=SSL_KEY name of the SSL key file to use for establishing a secure connection.
--ssl=SSL specifies if the server connection requires use of SSL. If an encrypted connection cannot be established, the connection attempt fails. By default 0 (SSL not required).
Note that --master-info-repository=TABLE needs to be configured on all slaves or the tool will exit with an error message.
Related MySQL Articles: mysqlcheck utility commands MySQL utility - mysqlauditgrep commands
