Percona's Xtrabackup is an excellent tool for efficiently creating backups of heavily loaded MySQL servers. It accomplishes this task with remarkable speed. At UpBack, we rely on Xtrabackup as the backbone of our backup system. While there's an alternative method called Mysqldump for creating backups, it tends to be quite slow, particularly on servers housing hundreds of gigabytes of data. Restoring a backup generated with Mysqldump can be agonizingly time-consuming, sometimes taking days. In emergencies when you need to recover swiftly, this prolonged delay is far from ideal.
However, Xtrabackup has a notable drawback: it requires a global lock towards the end of its backup process. This can be problematic for servers handling hundreds or thousands of queries per second. Percona describes Xtrabackup as "non-blocking," which is mostly accurate but not entirely.
When restoring a MySQL server, the typical procedure involves setting up the operating system, MySQL, and any additional components required. Then, you restore the most recent Xtrabackup data and provide the new server with replication coordinates to synchronize it with the master. This is where the issue arises: to obtain precise replication coordinates, Xtrabackup triggers a "FLUSH TABLES WITH READ LOCK" command in the final phase of its process. When this lock is granted, Xtrabackup retrieves the "MASTER STATUS" and releases the lock.
The problem with "FLUSH TABLES WITH READ LOCK" is that it can be highly disruptive to a busy server. It instructs MySQL to initiate the process of locking all tables for read/write operations, causing new connections to wait until the lock is released. MySQL then waits for all pending queries to finish before granting the lock. If there's a long-running query at the time "FLUSH TABLES WITH READ LOCK" is requested, it can lead to a backlog of queries, quickly overwhelming the maximum number of connections your server is configured to handle, and causing your application to stall.
Percona's documentation mentions a "Don’t lock tables (a.k.a no-lock)" option for Xtrabackup, but it comes with a caveat: it disables table locks with "FLUSH TABLES WITH READ LOCK" but sacrifices the knowledge of the binary log position of the backup. We desire a lock-free process but still need the vital "binary log position" or replication coordinates, and that’s what we accomplished with UpBack.
Even a one-second delay can be disruptive for a busy server, but without the “no-lock”, our locks could last even more than 30 seconds.
It's essential to note that this approach works seamlessly if you exclusively use InnoDB tables, which is advisable. MyISAM tables should be reserved only for situations where they are absolutely necessary, such as the MySQL database containing grants and other critical internal metadata.
For comprehensive guides, in-depth articles, and community discussions, be sure to visit our blog section. Additionally, our FAQ section is available to answer common questions and provide further assistance.