MYSQLFAILOVER(1) | MySQL Utilities | MYSQLFAILOVER(1) |
NAME¶
mysqlfailover - Automatic replication failoverSYNOPSIS¶
mysqlfailover [options]
DESCRIPTION¶
This utility permits users to perform replication health monitoring and automatic failover on a replication topology consisting of a master and its slaves. The utility is designed to run interactively or continuously refreshing the health information at periodic intervals. Its primary mission is to monitor the master for failure and when a failure occurs, execute failover to the best slave available. The utility accepts a list of slaves to be considered the candidate slave. This utility is designed to work exclusively for servers that support global transaction identifiers (GTIDs) and have GTID_MODE=ON. MySQL server versions 5.6.9 and higher support GTIDs. See the MySQL server online reference manual for more information about setting up replication with GTIDs enabled. The user can specify the interval in seconds to use for detecting the master status and generating the health report using the --interval option. At each interval, the utility will check to see if the server is alive via a ping operation followed by a check of the connector to detect if the server is still reachable. The ping operation can be controlled with the --ping option (see below). If the master is found to be offline or unreachable, the utility will execute one of the following actions based on the value of the --failover-mode option. auto Execute automatic failover to the list of candidates first and if no slaves are viable, continue to locate a viable candidate from the list of slaves. If no slaves are found to be a viable candidate, the utility will generate and error and exit. Once a candidate is found, the utility will conduct failover to the best slave. The command will test each candidate slave listed for the prerequisites. Once a candidate slave is elected, it is made a slave of each of the other slaves thereby collecting any transactions executed on other slaves but not the candidate. In this way, the candidate becomes the most up-to-date slave. elect This mode is the same as auto except if no candidates specified in the list of candidate slaves are viable, it does not check the remaining slaves and generates and error and exits. fail This mode produces an error and does not failover when the master is downed. This mode is used to provide periodic health monitoring without the failover action taken. For all options that permit specifying multiple servers, the options require a comma-separated list of connection parameters in the following form (where the password, port, and socket are optional).:<*user*>[:<*passwd*>]@<*host*>[:<*port*>][:<*socket*>] or <*login-path*>[:<*port*>][:<*socket*>]
•start
Starts the daemon. The --log option is required.
•stop
Stops the daemon. If you used the option --pidfile, the value must be the
same when starting the daemon.
•restart
Restarts the daemon. If you used the option --pidfile, the value must be
the same when starting the daemon.
•nodetach
Starts the daemon, but it will not detach the process from the console. The
--log option is required.
The utility supports two modes of operation. The default mode, running as a
console, works as described above. An additional mode that permits you to run
the utility as a daemon is provided for POSIX platforms.
When run as a daemon, the utility does not have interactivity. However, all
events are written to the log file. You can control what is written to the log
by using the --report-values option.
To run the utility as a daemon, use the --daemon option. There are three
commands that can by used in --daemon option. These include:
•start
Starts the daemon. The --log option is required.
•stop
Stops the daemon. The option --pidfile must be the same when starting the
daemon.
•restart
Restarts the daemon. The option --pidfile must be the same when starting
the daemon.
•nodetach
Starts the daemon, but it will not detach the process from the console. The
--log option is required.
OPTIONS mysqlfailover accepts the following command-line options:
•--help
Display a help message and exit.
•--candidates=<candidate slave connections>
Connection information for candidate slave servers for failover in the form:
<
user>[:<passwd>]@<host>[:<port>][:<
socket>] or
<login-path>[:<port>][:<socket>]. Valid
only with failover command. List multiple slaves in comma-separated
list.
•--daemon=<command>
Run as a daemon. It can be start (start daemon), stop (stop
daemon), restart (stop then start the daemon) or nodetach (start
but do not detach the process). This option is only available for POSIX
systems.
•--discover-slaves-login=<user:password>
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 <
user>[:<passwd>] or <login-path>. For
example, --discover=joe:secret will use 'joe' as the user and 'secret' as the
password for each discovered slave.
•--exec-after=<script>
Name of script to execute after failover or switchover. Script name may include
the path.
•--exec-before=<script>
Name of script to execute before failover or switchover. Script name may include
the path.
•--exec-fail-check=<script>
Name of script to execute on each interval to invoke failover.
•--exec-post-failover=<script>
Name of script to execute after failover is complete and the utility has
refreshed the health report.
•--failover-mode=<mode>, -f <mode>
Action to take when the master fails. 'auto' = automatically fail to best slave,
'elect' = fail to candidate list or if no candidate meets criteria fail,
'fail' = take no action and stop when master fails. Default = 'auto'.
•--force
Override the registration check on master for multiple instances of the console
monitoring the same master. See notes.
•--interval=<seconds>, -i <seconds>
Interval in seconds for polling the master for failure and reporting health.
Default = 15 seconds. Minimum is 5 seconds.
•--log=<log_file>
Specify a log file to use for logging messages
•--log-age=<days>
Specify maximum age of log entries in days. Entries older than this will be
purged on startup. Default = 7 days.
•--master=<connection>
Connection information for the master server in the format: <
user>[:<
passwd>]@<host>[:<port>][:<
socket>] or <
login-path>[:<port>][:<socket>].
•--max-position=<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 = 0.
•--pedantic, -p
Used to stop failover if some inconsistencies are found (e.g. errant
transactions on slaves or SQL thread errors) during servers checks. By
default, the utility will only issue warnings if issues are found when
checking slaves status during failover and will continue its execution unless
this option is specified.
•--pidfile=<pidfile>
Pidfile for running mysqlfailover as a daemon. This file contains the PID
(process identifier), that uniquely identify a process. It is needed to
identify and control the process forked by mysqlfailover.
•--ping=<number>
Number of ping attempts for detecting downed server. Note: on some platforms
this is the same as number of seconds to wait for ping to return. Default is 3
seconds.
•--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.
•health
Display the replication health of the topology.
•gtid
Display the master's list of executed GTIDs, contents of the GTID variables;
@@GLOBAL.GTID_EXECUTED, @@GLOBAL.GTID_PURGED and @@GLOBAL.GTID_OWNED.
•uuid
Display universally unique identifiers (UUIDs) for all servers.
Default = health.•--rpl-user=:<replication_user>
The user and password for the replication user requirement , in the form: <
user>[:<password>] or <login-path>. E.g.
rpl:passwd
Default = None.
•--script-threshold=<return_code>
Value for external scripts to trigger aborting the operation if result is
greater than or equal to the threshold.
Default = None (no threshold checking).
•--seconds-behind=<seconds>
Used to detect slave delay. The maximum number of seconds behind the master
permitted before slave is considered behind the master. Default = 0.
•--slaves=<slave connections>
Connection information for slave servers in the form: <
user>[:<passwd>]@<host>[:<port>][:<socket>]
or < login-path>[:<port>][:<socket>].
List multiple slaves in comma-separated list. The list will be evaluated
literally whereby each server is considered a slave to the master listed
regardless if they are a slave of the master.
•--timeout=<seconds>
Maximum timeout in seconds to wait for each replication command to complete. For
example, timeout for slave waiting to catch up to master.
Default = 3.
•--verbose, -v
Specify how much information to display. Use this option multiple times to
increase the amount of information. For example, -v = verbose,
-vv = more verbose, -vvv = debug.
•--version
Display version information and exit.
NOTES The login user must have the appropriate permissions to execute SHOW SLAVE STATUS, SHOW MASTER STATUS, and SHOW VARIABLES on the appropriate servers as well as grant the REPLICATE SLAVE privilege. Different permission are required by the failover utility to run successfully for master and slaves. In particular, users connectioned to slaves and candidates require SUPER, GRANT OPTION, REPLICATION SLAVE, and RELOAD privileges. In addition, the user connected to the master requires DROP, CREATE, INSERT and SELECT privileges to register the failover console. The utility checks permissions for the master, slaves, and candidates at startup. At startup, the console will attempt to register itself with the master. If another console is already registered, and the failover mode is auto or elect, the console will be blocked from running failover. When a console quits, it deregisters itself from the master. If this process is broken, the user may override the registration check by using the --force option. Mixing IP and hostnames is not recommended. The replication-specific utilities will attempt to compare hostnames and IP addresses as aliases for checking slave connectivity to the master. However, if your installation does not support reverse name lookup, the comparison could fail. Without the ability to do a reverse name lookup, the replication utilities could report a false negative that the slave is (not) connected to the master. For example, if you setup replication using MASTER_HOST=ubuntu.net on the slave and later connect to the slave with mysqlrplcheck and have the master specified as --master=192.168.0.6 using the valid IP address for ubuntu.net, you must have the ability to do a reverse name lookup to compare the IP (192.168.0.6) and the hostname (ubuntu.net) to determine if they are the same machine. Similarly, in order to avoid issues mixing local IP '127.0.0.1' with 'localhost', all the addresses '127.0.0.1' will be internally converted to 'localhost' by the utility. Nevertheless, It is best to use the actual hostname of the master when connecting or setting up replication. The utility will check to see if the slaves are using the option --master-info-repository=TABLE. If they are not, the utility will stop with an error. The path to the MySQL client tools should be included in the PATH environment variable in order to use the authentication mechanism with login-paths. This will allow the utility to use the my_print_defaults tools which is required to read the login-path values from the login configuration file (.mylogin.cnf). The console creates a special table in the mysql database that is used to keep track of which instance is communicating with the master. If you use the --force option, the console will remove the rows in this table. The table is constructed with:
CREATE TABLE IF NOT EXISTS mysql.failover_console (host char(30), port char(10))
EXAMPLES To launch the utility, you must specify at a minimum the --master option and either the --discover-slaves-login option or the --slaves option. The option: option can be used in conjunction with the --slaves option to specify a list of known slaves (or slaves that do not report their host and ip) and to discover any other slaves connected to the master. An example of the user interface and some of the report views are shown in the following examples.
$ mysqlfailover --master=root@localhost:3331 --discover-slaves-login=root --log=log.txt MySQL Replication Monitor and Failover Utility Failover Mode = auto Next Interval = Mon Mar 19 15:56:03 2012 Master Information ------------------ Binary Log File Position Binlog_Do_DB Binlog_Ignore_DB mysql-bin.000001 571 GTID Executed Set 2A67DE00-2DA1-11E2-A711-00764F2BE90F:1-7 [...] Replication Health Status +------------+-------+---------+--------+------------+---------+ | host | port | role | state | gtid_mode | health | +------------+-------+---------+--------+------------+---------+ | localhost | 3331 | MASTER | UP | ON | OK | | localhost | 3332 | SLAVE | UP | ON | OK | | localhost | 3333 | SLAVE | UP | ON | OK | | localhost | 3334 | SLAVE | UP | ON | OK | +------------+-------+---------+--------+------------+---------+ Q-quit R-refresh H-health G-GTID Lists U-UUIDs L-log entries
MySQL Replication Monitor and Failover Utility Failover Mode = auto Next Interval = Mon Mar 19 15:59:33 2012 Master Information ------------------ Binary Log File Position Binlog_Do_DB Binlog_Ignore_DB mysql-bin.000001 571 GTID Executed Set 2A67DE00-2DA1-11E2-A711-00764F2BE90F:1-7 [...] Master GTID Executed Set +-------------------------------------------+ | gtid | +-------------------------------------------+ | 2A67DE00-2DA1-11E2-A711-00764F2BE90F:1-7 | | 5503D37E-2DB2-11E2-A781-8077D4C14B33:1-3 | +-------------------------------------------+ Q-quit R-refresh H-health G-GTID Lists U-UUIDs L-log entries Up|Down-scroll
MySQL Replication Monitor and Failover Utility Failover Mode = auto Next Interval = Mon Mar 19 16:02:34 2012 Master Information ------------------ Binary Log File Position Binlog_Do_DB Binlog_Ignore_DB mysql-bin.000001 571 GTID Executed Set 2A67DE00-2DA1-11E2-A711-00764F2BE90F:1-7 [...] UUIDs +------------+-------+---------+---------------------------------------+ | host | port | role | uuid | +------------+-------+---------+---------------------------------------+ | localhost | 3331 | MASTER | 55c65a00-71fd-11e1-9f80-ac64ef85c961 | | localhost | 3332 | SLAVE | 5dd30888-71fd-11e1-9f80-dc242138b7ec | | localhost | 3333 | SLAVE | 65ccbb38-71fd-11e1-9f80-bda8146bdb0a | | localhost | 3334 | SLAVE | 6dd6abf4-71fd-11e1-9f80-d406a0117519 | +------------+-------+---------+---------------------------------------+ Q-quit R-refresh H-health G-GTID Lists U-UUIDs L-log entries
Failover starting... # Candidate slave localhost:3332 will become the new master. # Preparing candidate for failover. # Creating replication user if it does not exist. # Stopping slaves. # Performing STOP on all slaves. # Switching slaves to new master. # Starting slaves. # Performing START on all slaves. # Checking slaves for errors. # Failover complete. # Discovering slaves for master at localhost:3332 Failover console will restart in 5 seconds.
MySQL Replication Monitor and Failover Utility Failover Mode = auto Next Interval = Mon Mar 19 16:05:12 2012 Master Information ------------------ Binary Log File Position Binlog_Do_DB Binlog_Ignore_DB mysql-bin.000001 1117 GTID Executed Set 2A67DE00-2DA1-11E2-A711-00764F2BE90F:1-7 [...] UUIDs +------------+-------+---------+--------+------------+---------+ | host | port | role | state | gtid_mode | health | +------------+-------+---------+--------+------------+---------+ | localhost | 3332 | MASTER | UP | ON | OK | | localhost | 3333 | SLAVE | UP | ON | OK | | localhost | 3334 | SLAVE | UP | ON | OK | +------------+-------+---------+--------+------------+---------+ Q-quit R-refresh H-health G-GTID Lists U-UUIDs L-log entries
MySQL Replication Monitor and Failover Utility Failover Mode = auto Next Interval = Mon Mar 19 16:06:13 2012 Master Information ------------------ Binary Log File Position Binlog_Do_DB Binlog_Ignore_DB mysql-bin.000001 1117 GTID Executed Set 2A67DE00-2DA1-11E2-A711-00764F2BE90F:1-7 [...] Log File +-------------------------+----------------------------------------- ... --+ | Date | Entry ... | +-------------------------+----------------------------------------- ... --+ | 2012-03-19 15:55:33 PM | INFO Failover console started. ... | | 2012-03-19 15:55:33 PM | INFO Failover mode = auto. ... | | 2012-03-19 15:55:33 PM | INFO Getting health for master: localhos ... | | 2012-03-19 15:55:33 PM | INFO Master status: binlog: mysql-bin.00 ... | +-------------------------+----------------------------------------- ... --+ Q-quit R-refresh H-health G-GTID Lists U-UUIDs L-log entries Up|Down-scroll\
COPYRIGHT¶
SEE ALSO¶
For more information, please refer to the MySQL Utilities section of the MySQL Workbench Reference Manual, which is available online at http://dev.mysql.com/doc/workbench/en/.AUTHOR¶
Oracle Corporation (http://dev.mysql.com/).08/30/2013 | MySQL 1.3.4 |