MySQL Backup and Veeam Backup & Replication – Part 1

This article will show you how to implement a data protection strategy in MySQL environments.

Let’s start with a consideration.

To create consistent backups from an application point of view, it is necessary that before the copy process is started, the application has written all the data in memory to disk ( flush ).

For example, Microsoft® applications use a technology called Shadow Copy which, through the coordination of VSS drivers , achieves application consistency.

A similar technology is not available on Linux and in addition MySQL does not support it in the Microsoft® environment.

How to remedy?

Through the creation of scripts that automate application consistency before starting the creation of the Snapshot .

Having understood this aspect, let’s return to the scope of the article, introducing the options available for MySQL .

Note 1 : Application consistency occurs before snapshot creation.

  • 1. Logical Backup : The script creates a file with the .sql extension which in case of restore allows the re-creation of the database and its data.

The file . sql is created through the native MySQL command ” mysqldump “ .

The advantages of logical backup can be summarized in:

  • There are no dependencies on third-party software.
  • Backups can be restored to other servers.
  • 2. Physical / Cold Backup : Cold copies of the DB files are created (for example: ibdata, .ibd, .frm, ib_logfile, my.cnf).

To be sure that the backups are made in ” application consistency ” mode, before taking the snapshot, it is essential to stop the MySQL services.

It is a backup strategy typically implemented in environments that do not require 24×7 operations.

Note 2 : The service is stopped only for the time necessary to create the snapshot and not for the entire duration of the backup.

  • 3. Physical / Hot Backup : If the InnoDB engine is running, the script allows the creation of consistent copies without stopping the services (using for example the command mysqlbackup component of the MySQL Enterprise suite ( MySQL Product) ).

Now that we know the scripting options available, let’s see how Veeam solutions can natively integrate with MySQL environments.

The first available option is the Veeam Agent for Linux ( VAL ) which automates the following four steps:

  1. Flush data from memory to disk (application consistency).
  2. Creation of the snasphot.
  3. Release of tables.
  4. Start the Backup process.

Note 3 : As indicated in the first part of the article, if the DB is of the MyISAM type, it is possible to backup with the blocking of all the tables.

The pre-requisites of the VAL are:

  • MySQL version is greater than or equal to 5.8.
  • The operating system is Linux.

Question: Is it possible to backup in Windows environments where the MySQL version is lower than version 5.8?

The answer is yes and the available scenarios are:

Logical Backup -> Hot-Backup Database Online Dump -> Mysqldump command.

Physical / Cold Backup –> Cold-Backup Database Shutdown -> Temporary stop of the Services.

Physical / Hot Backup –> Hot-Backup Database Freeze -> Native mysql commands.

Note4 : There is also the possibility of making Partial Backups . In this scenario, specific tables and databases are backed up. It is useful when different protection strategies have to be implemented on the same Server.

In the next article, we will find out how to create scripts and how to integrate them into Veeam Backup & Replication.