MySQL Performance Blog
Guest
TokuDB Hotbackup is a solution that allows you to do backups on the fly. It works as a library that intercepts certain system calls that duplicate data written to already copied parts of files, so that at the end of the backup process the copied files contain the same content as the original files. There are several blog posts describing how TokuDB Hot Backup works in details:Replication often uses backups replication to create slaves. For this purpose, we need to know the last executed GTID or binary log position both for the slave and master configurations.
You can obtain the corresponding information with SHOW MASTER/SLAVE STATUS. But if we request this information after the backup, the corresponding binlog position or executed GTID can be changed and the backed-up data won’t correspond to the master/slave state.
It’s possible to lock tables with FLUSH TABLE WITH READ LOCK, or use more smart locks like LOCK TABLES/BINLOG FOR BACKUP. See this blog post for details: Introducing backup locks in Percona Server.
But the larger question is when can we use the corresponding locks? We could lock binlog or some table before getting a backup, and then release after it’s done. But if the data being backed-up is big enough the backup itself can take time — and all that time the data is locked for changes.
There must be a more suitable solution. Let’s recall how TokuDB Hotbackup works: it uses a special library that intercepts some system calls and it has an API. This API includes such commands as “start capturing” and “stop capturing”. “Capturing” means that if some part of a file gets copied to a backup location, and this part is changed (by the MySQL server in our case), then these changes are also applied to the backup location. TokuDB Hotbackup plugin uses the API, starts capturing, make copies and stops capturing.
There is no need to lock the binlog during the copy process, because when the binlog is flushed the changes are copied to backup by a “capturing” mechanism. After everything has been copied, and with the capturing still running, this is a good time for LOCK BINLOG FOR BACKUP execution. After this statement is executed, the binlog is flushed, the flushed changes are captured and applied to a backup location, and any queries that could change the binlog position or executed GTID are blocked. After this we can stop capturing, get the binlog position or the last executed GTID and unlock the binlog.
This is how it’s implemented in TokuDB Hotbackup. After a backup is taken, there are “tokubackup_slave_info” and “tokubackup_binlog_info” files in the backup directory that contain the corresponding information about the slave and master (in human-readable format). You can use this information to start a new slave from the master or slave. 5.7 supports a multisource replication, and “tokubackup_slave_info” contains information for all replication channels.
There could be a happy ending here, but the devil is in the details. For example, there are several binary logging formats: RBR, SBR, MBR (for details see https://dev.mysql.com/doc/refman/5.7/en/replication-formats.html). In the case of SBR or MBR, a binary log event can contain statements that produce temporary tables on the slave side, and the result of further statements can depend on the content of the temporary tables.
Usually, temporary tables are created in a separate directory that is out of a MySQL data directory, and aren’t backed up. That is why if we create a backup when temporary tables produced by binary log events exist, and then try to restore the backup, the temporary tables aren’t restored (as they were not backed up). If we try to restore the replication from the point saved during the backup, and after this point the binary log contains events that use the content of non-restored temporary tables, the data will be inconsistent.
That is why the so-called “safe-slave” mode is implemented in TokuDB Hotbackup. The same mode is implemented in Percona XtraBackup, and its name was also inherited from Percona XtraBackup. In this mode, along with LOCK BINLOG FOR BACKUP statement execution, the slave SQL thread is stopped. After this it is checked to see if temporary tables produced by slave SQL thread exist or not. If yes, then the slave SQL thread is restarted until there are no temporary tables (or the certain timeout is reached).
For those purposes, we introduced the following new system variables:
--tokudb-backup-safe-slave
– turn on/off safe-slave mode
--tokudb-backup-safe-slave-timeout
– maximum amount of time in seconds to wait until temp tables disappear
Note for the case of multisource replication, the simplified version of this feature is implemented. So if there are several replication channels, and for some of them the SQL thread is started while for others the thread is stopped, the TokuDB Hotbackup does not restore the channels state. It just restarts the SQL threads for all channels, and after the backup SQL threads for all channels will be started.
You can’t use this option for group-replication.
This could be the happy ending, but… well, you know, it’s complicated. Apart from replication features in the MySQL server, there are a lot of engine-specific features. One of them is how frequently the recovery log is synchronized.
For TokuDB, there are two system variables you can use to tune recovery log synchronization frequency:
tokudb_commit_sync
and
tokudb_fsync_log_period
. By playing with these variables, you can establish some tradeoff between durability and performance. Here is a good blogpost about these parameters: High Insertion Rates into a TokuDB Table with Durable Transactions.
But let’s imagine we have a certain recovery log synchronization period, and the backup finished somewhere in the middle of this period. It’s OK if we use the backup just to restore some data, because the data is restored using recovery and rollback logs during the server’s start.
But if we also want to start the slave using the information stored in the “tokubackup_slave_info” and “tokubackup_binlog_info” files, there might be a situation where after the recovery stage the data in the database is behind the replication position stored during backup. See this bug description https://bugs.launchpad.net/percona-server/+bug/1533174 for details. Now, when capturing is still active, TokuDB forcibly synchronizes the recovery log when the copy process is finished.
So this could be the happy ending but… Well, actually I hope this is a happy ending! At least we’ve implemented the general functionality for creating slaves using TokuDB Hotbackup. However, as “ the show must go on”, or “the spice must flow” etc., we are happy to discuss any concerns, ideas, proposals and bug reports.
Thanks to all who took part in development: George O. Lorch III, Sergey Glushchenko, Shahriyar Rzaev, Alexey Kopytov (as the author of “LOCK BINLOG FOR BACKUP” feature) and other people from Percona team and community.
Читать дальше...