The documentation about the ‘pt-slave-delay’ shell command of the Percona tookit package seems a little bit ambiguous to me. It says : " pt-slave-delay watches a slave and starts and stops its replication SQL thread as necessary to hold it at least as far behind the master as you request".
Does it mean that the command would only stop the replication SQL thread for a certain period of time at the precise moment when the slave receives new orders/commands from the master ?
The first tests I made on a MySQL server with the command within a linux terminal screen tend to indicate that it simply stops the replication SQL thread for the period of time specified in the parameter entered at the time of the call. It seems that it is not really trying to synchronize the stops with the coming of new orders from the master to the slave as it would a priori be supposed to.
It would be very helpful if you had any additional information on the way to eventually make pt-slave-delay work as a real lag generator between master and slave replication process, if it is possible to use it for this particular purpose.
Hello @pierre-olivier,
If you are running MySQL 5.6, 5.7 or 8.0 then please do not use this tool. Instead, use the native replication delay functionality:
I found the page to which the link you indicated in your message leads few days ago. I started to implement the feature in my replication configuration. I didn’t make all the tests yet to be quite sure the behaviour of the replication is the one I was looking for, but the contents of your message would tend to confirm it.
The exact effect I was looking for is a permanent and systematic lag of the slave over the incoming orders from the master. The goal is to allow the replica to be synchronized with other files that has to be updated every 15 minutes.
I guess the way to obtain such a result is rather tricky with the use of the ‘pt-slave-delay’ command, starting with the fact that the command doesn’t automatically engage the delay in a strictly synchronized manner with the incoming flow of orders from the master.
I first chose to use the percona toolkit command for the above purpose because I was working with a reference book of MySQL v5.5 in my language, which I found more convenient than the website on the documentation of the current version of MySQL in English. And the book was talking about “Maatkit” that was replaced by the previous “Percona toolkit”.
I don’t really understand the interest of the actual ‘pt-slave-delay’ command on the MySQL v5.6 or later versions, especially having tried to make it work in the desired way without success.
Have you ever tried and managed to make it work in any common use case, with the any version of MySQL, such as mine ?
The goal is to allow the replica to be synchronized with other files that has to be updated every 15 minutes.
I think you need to understand how replication works in MySQL. In MySQL, replication is always running. If you INSERT on the master, that transaction is immediately written to binlog and sent to replica and executed on replica. There is no delay. Everything processes as fast as possible. There is no need to force any synchronization.
pt-slave-delay and MASTER_DELAY are used to purposefully pause replication. These are NOT tools used to sync. They are, in fact, tools used to de-sync. These tools exist for advanced setups where you want to have a replica which is always delayed by some period of time. This creates a “safety net” in case someone accidentally drops a table, or deletes a critical row. If your replica is delayed by 10m, for example, then you have 10m from the time that table was dropped to fetch that data from the replica.
99% of MySQL users DO NOT need either of these tools. I suspect that you do not either. You just need to let replication run as normal and everything will sync automatically. The replica will maintain connectivity with the master and every time a write happens on the master, it will immediately be transfered to the replica. There is no need to use any tools to sync the data.
The files I was talking about (that needed to be synchronized) had nothing to do with replication process and MySQL. I am sorry if these details may have confused my message.
It is certainly due to the fact that I didn’t really explain the context of the use of the replication.
My ultimate goal was to have a distant replica of a whole information system (web app). That includes client/server scripts, work files and database. The information system works with files, it is to say that each time it writes files, or in files, it also writes some associated data in the database.
So, in order to have a relevant and operational replica of the original information system:
I needed to synchronized some files (such as pdf, pictures, .txt, etc.) every 15 minutes
I needed to delay the replication a little bit more to avoid generating some unwanted errors while using the replicated distant system.
One of the requirements of my project was to allow to switch the web server with a kind of mirror server, in the case the main server (the one of the data source for the database replication) would severely fail.
You really should not do this. You will only complicate matters by messing with MySQL’s replication. “delaying replication” is not a solution to fixing errors. Fix the reason why the errors are happening instead of using a feature in a wrong way.
This is known in the industry as “DR” or “disaster recovery”. You are trying to set up a DR site. Use MySQL replication as intended, and use rsync to synchronize files between the two servers. We at Percona set up DR sites all the time for our clients. Delayed-replica is NEVER a tool used to implement this.
Maybe the databases replication is not the best option in the case of the application I am willing to clone on a “Disaster recovery” distant server.
However, this point aside, and assuming that this option is still functional, would it be possible either on this version or one another (especially on one that doesn’t support the ‘MASTER_DELAY’ property/feature, v5.5 for instance) of MySQL to use ‘pt-slave-delay’ in a way (parameter values, options, etc.) that it could behave like the ‘MASTER_DELAY’ feature from the 5.6 version of MySQL ?
pt-slave-delay and MASTER_DELAY accomplish the exact same thing in the same exact way. They both pause the sql_thread on the replica in such a way so that the timestamp of the next transaction to process is at least X second old (where X is the value you configure).
Example: The current time is 2022-07-06 12:00:01. You have configured a 10 minute delay (doesn’t matter which tool was used). The timestamp of the next transaction is 12:00:03. Replication will be stopped/paused until 12:10:03 and then this transaction will be allowed to be executed on the replica. Then replication will be paused again and the next transaction’s timestamp is evaluated. Etc, etc.
And I’m sure you know, MySQL 5.5 and MySQL 5.6 are dead, dead, dead, completely dead in every way possible. You should upgrade to 5.7 when you get the chance.
MySQL v5.5 & 5.6 are probably outdated, but, although I was referring to the documentation of MySQL v5.6, I am, in fact, rather using the version 8 of MySQL on both servers (source and clone), after checking.
It might be the reason why the ‘pt-slave-delay’ doesn’t seem to behave the way you described it (according to the output messages on terminal, for instance), at least with the invocation syntax I previously quoted (first message).