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.

A flexible file backup Strategy – Part 3

This article’s topic is how to set up and work VBR when it is combined with the NDMP protocol.

This type of configuration is part of the category “file to tape” I treated in my last article “A flexible file backup Strategy – Part 2”.

One more note before starting: VBR requires NDMP version 4 and later.

  • How does it work?

Picture 1

The architecture is quite easy.

Files are gathered from the File Server through the data mover present on the Gateway server. Then they are sent to the Tape Server that performs the write on Tape.

The Datamover installed on Tape Server has the ownership of managing the data traffic to Tape Device.

VBR has the task of enumerating the Volumes and launch the command to Tape Devices.

Point-1: VBR works with a 3 layer backup architecture. It means there is no direct connection between File Servers and Tape Devices. All Data have to pass through the tape Sever.

In this article, I’m not covering the procedure to set up the NAS correctly. Please ask your storage vendor specialist to get all details needed.

How to add the NDMP server to VBR and set up a backup job?

The next video will answer the question.

Video 1

After the backup is completed how to perform a restore?

NDMP backup files are available to perform restores from the FILE menu at the voice Tape and then NDMP as shown in picture 2

Picture 2

 

Please have a look at the next short video (video 2) to see the easy step to perform a restore.

Video 2

Point-2: The backup chain stored on tapes will consist of 10 restore points maximum. On the 11th run, VBR will force an active full.

  • Common scenarios

One of the main characteristics of NDMP backup is that it allows to perform backup and restore of entire volumes.

The most common scenario is the Disaster Recovery of the entire Filer. Imagine the case in which the customer NAS filer is completely out of order and the only way to restart the file sharing service is formatting all the disks and then restoring the volumes (or the worst case is getting a new NAS).

In this case, NDMP volume restores from tape is an excellent low-cost solution.

Main Pro:

NDMP servers backup to tape is available in the Enterprise Plus Edition of VBR.

It means that there is no limit on the amount of data that can be saved. Could be 1 TB or 100 PB it doesn’t matter.

Veeam Universal License (VUL) has the Enterprise Plus Edition available. Is it time to move your old sockets license to the subscription model (VUL)?

Cons:

The granular restore (files and folders) it’s not an available option with VBR and NDMP backup.

Please note that NAS backup is now completely supported by VBR (please refer to article 1 of this series) and it’s the fastest way to perform the granular restores.

Point-3: Even if the NAS device supporting NDMP protocol is already added to VBR, you need to add the NDMP server as a separate procedure. Otherwise, you will not be able to perform file backup to tape.

The next article will cover a recap of the different techniques and show a hidden gem of VBR.

See you next week and take care