What does it take to back up MySQL on a Linux VM? This is a riddle we sometimes hear at Veeam: When running on a Linux VM, how does one quiesce MySQL databases? Unfortunately, there are not many new ways to answer this riddle, and the answers we currently have are already tried and tested!
The answers can be found in our popular white paper Consistent protection of MySQL/MariaDB with Veeam, written by Solutions Architect Pascal Di Marco. The paper is available for download on our website and describes three different methods for MySQL/MariaDB backup on a Linux VM. Two hot backup methods running pre- and post-snapshot scripts, and cold backup using database shutdown. This makes use of VMware tools installed on the Linux VM. It is not straightforward like a Microsoft SQL quiescence, because Linux doesn’t have a VSS mechanism like Windows does.
Described are methods of creating a MySQL/MariaDB backup on a Linux VM using activated scripts local to the database. VMware can run a script to act before the snapshot is created, known as the pre-freeze script and can run a script to do things after the snapshot is created, known as the post-thaw script.
Here’s a quick summary:
Option 1: Hot MySQL/MariaDB backup — Database online dump
The mysqldump command copies a database to storage accessible from the MySQL server, taking an online dump of each database without disrupting the MySQL service. This method lets you take a transaction consistent backup of databases but more steps are needed to perform a restore. As with Option 1, the pre-freeze-script will only run if you have the VMware tools running.
Advantage: This allows for 100% uptime; the MySQL service does not stop and the dumped databases are in a transaction-consistent state.
Disadvantage: Depending on the size of your databases, the process may take a considerable amount of time to achieve. A second copy of the database means extra storage space is required to maintain it.
Option 2: Hot MySQL/MariaDB backup — Database freezing
Stop the MySQL service for a few moments while the snapshot is created, then start it again. The post-thaw script will not run until the snapshot is created. The pre-freeze script and post-thaw script will only be able to run if you have the VMware tools running in your MySQL server.
Advantage: This is quick and simple, allowing you to take a transaction of all databases with no additional disk usage local to the MySQL server.
Disadvantage: Databases running on the MySQL server will briefly be unavailable, and applications that need 100% uptime may not find this suitable.
Option 3: Cold MySQL/MariaDB Backup — Database shutdown
In this method, the application service will be stopped during snapshot creation and restarted once the VM snapshot has been created. It requires permission to start and stop application services but does not require MySQL user permissions. You can authenticate by either using the MySQL default configuration file or hardcoding the username and password in the script.
Advantage: This is easy to set up and doesn’t take extra space. It provides a short RTO, since no further action is required aside from booting the restored guest.
Disadvantage: The databases will be totally unavailable while the guest snapshot is created.
Recovery
Guest recovery: The cold MySQL/MariaDB backup and freeze method will leave the database consistent and able to start up without additional operation, so restoring the VM from the backup files is the only operation to perform. The guest recovery may benefit from Veeam’s Instant VM Recovery feature, which lets you boot up the guest directly from the Veeam Backup Repository in minutes.
Additional dump restoration: The extra task of injecting the dump file into the database using file redirection is necessary if the following is true: The issue is not limited to a database outage, the entire VM must be recovered from the Veeam Backup file and the database dump method has been used.
Veeam U-AIR database restoration: Whether it is a granular or a full database restoration, Veeam U-AIR wizard can be used in conjunction with any relevant database management tool such as MySQL Workbench to recover a database item.