MySQL utility mysqlslavetrx cheatsheet
mysqlslavetrx - skip transactions on slaves
Usage: mysqlslavetrx --gtid-set=gtid_set --slaves=user:pass@host:port
mysqlslavetrx utility used to skip multiple transactions on slaves in a single step for GTID enabled MySQL servers, by injecting empty transactions on all specified slaves for each GTID in the specified GTID set.
mysqlslavetrx --help
mysqlslavetrx --version
mysqlslavetrx --gtid-set=2e84a6ae-b8dc-11e6-884d-005056a12ebe:7-9 --slaves=user:pass@localhost:3311,user:pass@localhost:3312
mysqlslavetrx --gtid-set=0b6e2f7a-2888-11e4-907e-005056855cdb:6-12 --slaves=user:pass@localhost:3311,user:pass@localhost:3312 --dryrun
mysqlslavetrx --gtid-set=1e8f69aa-48e7-11e8-b8e8-005056a13b37:6-12 --slaves=user:pass@localhost:3311,user:pass@localhost:3312 --verbose
mysqlslavetrx --gtid-set=2e84a6ae-b8dc-11e6-884d-005056a12ebe:5-11 --slaves=slave-admin -vv
mysqlslavetrx Options:
--version show program's version number and exit
--help display a help message and exit
--license display program's license and exit
--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).
--gtid-set=GTID_SET set of Global Transaction Identifiers (GTID) to skip.
--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.
--dryrun determine the transactions (GTID) to be skipped for each slave but without effectively skipping them (injecting empty transactions) - useful to test the transactions that would be skipped.
-v, --verbose control how much information is displayed. e.g., -v = verbose, -vv = more verbose, -vvv = debug
Introduction
------------
The mysqlslavetrx utility is designed to skip multiple transactions on slaves in a quick and easy way. More specifically, it injects empty transactions on the slaves for each GTID that will be skipped.
The utility requires GTIDs to be enabled on all slaves. It does not require replication to be stopped. However, in some situation it is recommended.
For example, in order to skip a transaction from the master on a slave, that slave should be stopped otherwise the target transaction might still be replicated (and not skipped).
Note: Only transactions (GTIDs) that were not committed can be skipped, since two transactions cannot be applied with the same GTID. GTIDs already in the GTID_EXECUTED set of a slave will be ignored.
The utility requires the specification of the GTID set to skip and the list of target slaves as shown in the following example.
# Skip the specified GTID set (three transaction: 10, 11, 12) on two slaves.
$ mysqlslavetrx --gtid-set=ee2655ae-2e88-11e4-b7a3-606720440b68:10-12 --slaves=rpl:pass@host2:3306,rpl:pass@host3:3306
Helpful Hints
-------------
- Use the --dryrun option to execute the utility in dry run mode and confirm which transactions would be skipped with the provided input values without effectively skipping them.
WARNING: Skipping transactions is a useful technique to recover from erroneous situations with replication. However, it must be applied with extreme caution and with full knowledge of its consequences as it might lead to data
inconsistencies between the replication servers. For example, if a transaction that insert some data 'row1' in table 't1' fails on one slave and that transaction is skipped to solve the issue, then that data will be missing from the slave (and no longer replicated). As a consequence the data for table 't1' will be inconsistent with the one on the master and the other slaves because 'row1' will be missing.
Related MySQL Articles:
No comments:
Post a Comment