MySQL mysqlfailover utility commands

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 --version
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 [:] or. For example, --discover-slaves-login=joe:secret will use 'joe' as the user and 'secret' as the password for each discovered slave.

  --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]]. List multiple slaves in comma-separated list.
  --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: [:] or  . e.g. rpl:passwd
  --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