Introduction to the use of MySQL pt-slave-restart tool

When setting up a MySQL master-slave replication environment, you often encounter a scenario where both the master and slave databases need to initialize the user's account password. Under normal circumstances, you need to turn off session-level binlog in both the master and slave databases before importing user information.

However, sometimes the pre-action of closing the session-level binlog is ignored. For example, in the GTID-based replication mode, the master and slave libraries directly import the user's account and password information. The master and slave libraries record the account and password transactions in their own GTIDs. At this time, if we use the GTID method to build replication, an error will occur. Because the slave library already has an account, the user information synchronized from the master library to the slave library will have an error, indicating that the user already exists. In this case, we need to skip the SQL statement for creating the user copied from the master library in the slave library.

In GTID mode, you can skip a transaction by:

stop slave;
set gtid_next=xxxxx;
begin;
commit;
set gtid_next=automatic;
start slave;

However, when there are many transactions, we cannot skip them one by one. We need to skip the same error in batches. Here are several ways to handle it;

Introduction to three parameters of MySQL replication problem

Adjust the slave_skip_errors parameter or the slave_exec_mode parameter respectively.

Today we will look at another method, which is to use the pt-slave-restart tool to skip transactions. The command is as follows:

./pt-slave-restart -uroot -proot123 --error-numbers=1062

The command is easy to understand. Just write the error code clearly and skip it. It will print out a lot of information. By default, the printed content is: timestamp, connection information, relay log file, relay log position and the last error number. For example:

2020-09-22T00:59:09 P=3306,h=192.168.7.194,p=...,u=root relay-bin.000005 369 1032
2020-09-22T00:59:09 P=3306,h=192.168.7.194,p=...,u=root relay-bin.000005 726 1032
2020-09-22T00:59:09 P=3306,h=192.168.7.194,p=...,u=root relay-bin.000005 1085 1032
2020-09-22T00:59:09 P=3306,h=192.168.7.194,p=...,u=root relay-bin.000005 1444 1032
2020-09-22T00:59:09 P=3306,h=192.168.7.194,p=...,u=root relay-bin.000005 1800 1032

You can add more information using the --verbose option, or suppress all output using the --quiet option.

Note:

When using multithreaded replication (slave_parallel_workers > 0), pt-slave-restart cannot skip transactions. pt-slave-restart cannot determine which specific slave thread executed the failed transaction for the GTID event.

If you need to use it, please temporarily shut down the workers of multi-threaded replication

For other parameters of pt-slave-restart, you can use the pt-slave-restart --help command to view them, or refer to the following common parameter list:

--always : never stop the slave thread, even if you stop it manually --ask-pass : replace the -p command to prevent the password input from being peeped at by the developer behind you --error-numbers : specify which errors to skip, which can be separated by , --error-text : match and skip based on the error message --log : output to a file --recurse : execute on the master side and monitor the slave side --runtime : how long the tool will run before exiting: default seconds, m=minute, h=hours, d=days

--slave-user --slave-password : The account and password of the slave database, used when running from the master end --skip-count : The number of errors to be skipped at one time. If you are bold, you can set a larger number. If not specified, the default is 1 --master-uuid : When cascading replication, specify to skip errors of the superior or superior transaction --until-master : Stop after reaching the specified master_log_pos, file position, format: "file:pos"

--until-relay : Same as above, but stops based on the position of relay_log

The above is the detailed content of the introduction to the use of the MySQL pt-slave-restart tool. For more information about the use of the MySQL pt-slave-restart tool, please pay attention to other related articles on 123WORDPRESS.COM!