We are very happy to introduce a new MySQL utility named “mysqlslavetrx“, which allows users to easily skip multiple transactions on multiple servers in a single step. This utility is one of three new utilities included in MySQL Utilities release-1.6.1 Alpha. The other utilities are “mysqlbinlogpurge” and “mysqlbinlogrotate“, which can be used to purge and rotate binary logs.
The mysqlslavetrx utility allows you to skip multiple transactions on several slaves. More precisely, it injects empty transactions for the specified Global Transaction Identifier (GTID) set and list of target slaves. Skipping transactions can be useful to quickly recover from erroneous situations that can occur during the replication process, or to handle errant transactions. Check out the following blog posts for more details about concrete situations where you might need to inject empty transactions:
- Repair MySQL 5.6 GTID replication by injecting empty transactions
- Errant transactions: Major hurdle for GTID-based failover in MySQL 5.6
- Percona XtraDB Cluster 5.6: a tale of 2 GTIDs
- MySQL Replication and GTID-based failover – A Deep Dive into Errant Transactions
The mysqlslavetrx utility can assist you in the above situations, making the process of injecting empty transactions less tedious. However, misuse of this technique may cause the slaves to skip the wrong transactions. Thus, take care to ensure the transactions skipped do not introduce data inconsistencies.
Note: You can use the mysqlrplsync utility to check the data consistency of a replication topology.
Main Features
Here is a summary of the main features of the mysqlslavetrx utility:
- Skips multiple transactions (GTIDs), i.e. inject empty transactions, on multiple slaves.
- Provides a dry run mode to allow users to confirm which transaction would be skipped with the specified input without actually injecting any empty transactions on the target slaves.
- Checks existing transactions (GTIDs) and ignore them, since an empty transaction cannot be injected for an already executed GTID.
Requirements
The mysqlslavetrx utility requires all target servers to have GTID enabled.
Example
Here is an example of the execution of the utility:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
shell> mysqlslavetrx --gtid-set=ce969d18-7b10-11e4-aaae-606720440b68:1-5 \ --slaves=dba:pass@slave2:3312,dba:pass@slave3:3313 WARNING: Using a password on the command line interface can be insecure. # # GTID set to be skipped for each server: # - slave2@3312: ce969d18-7b10-11e4-aaae-606720440b68:1-5 # - slave3@3313: ce969d18-7b10-11e4-aaae-606720440b68:1-5 # # Injecting empty transactions for 'slave2:3312'... # Injecting empty transactions for 'slave3:3313'... # #...done. # |
As expected the only options that need to be specified is the GTID set representing the transactions to skip and the list of target slaves (more precisely their connection strings).
Now with the mysqlslavetrx utility, you no longer need to manually inject an empty transaction one by one on each required slave. Isn’t that cool?
Try it now and send us your feedback
MySQL Utilities release-1.6.1 alpha is available for download from the following links.
MySQL developers website: http://dev.mysql.com/downloads/tools/utilities/
Launchpad project: https://launchpad.net/mysql-utilities
The documentation of MySQL Utilities can be obtained from the following link: http://dev.mysql.com/doc/index-utils-fabric.html
Contributing Ideas:
- Community users: http://bugs.mysql.com (MySQL Utilities);
- Fabric, Sharding, HA, Utilities Forum: http://forums.mysql.com/list.php?144