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.

Check Replica Status – Before deleting it – Part 1

Last month, a partner had to face up a strange VBR behavior.

From the VBR console he deleted a VM’s replica (Picture 1), and suddenly the production VM has been also erased (don’t worry, before doing any activity he tested the backups using sure backup technology).

Picture 1

The reason why it happened become clear to me once I read the logs.

To do it shortly, some weeks before someone started a Failover directly from vCENTER console without doing any communication to the internal IT team.

This article wants to explain how to avoid this common mistake.

The first step is understanding some basic concepts:

a) VMware identify any single VM with a number named MorefID and a UUID.

b) Any single operating system has an identifier named Instance UUID (Universal Unique IDentifier); in my lab, I set-up  more than one replica job for a single VM

Table 1. row 2. shows the name of production VM (Ubuntu-02), its morefID  (vm2270), where it is running (Milan), the UUID (…bcc12) and its VM UUID … f58b.

Table 1. row 3-4 shows the name of  VMs replicas,  morefID, instance UUID and its UUID.

All tables shown in these articles have been created using Veeam One

Name PCName morefID DataCenter Instance UUID UUID
Ubuntu-02 ubuntu-02 vm-2270 Milan 502d2405-cc8f-de73-1a19-57e8496bcc12 564d013a-7835-9d1b-841e-32855790f58b
Ubuntu-02_Rep_VC01 ubuntu-02 vm-2694 Milan 502d2d90-d08f-08aa-efcf-d9feaa1d13f8 564d013a-7835-9d1b-841e-32855790f58b
Ubuntu-02_Rep_VCDR ubuntu-02 vm-399 Venice 501d517b-672e-30e0-665a-fd4b4af7dcb6 564d013a-7835-9d1b-841e-32855790f58b

Table-1

Picture 2 shows the VM  source (highlighted in yellow) from vCENTER console.

Picture 2

After checking up that the VM source is switched off, it’s possible to start a Failover (Picture 3).

Picture 3

The next five pictures show the step-by-step wizard to complete the procedure correctly.  As you can see from picture 4 the VM that has been replicated with two different jobs (Picture 5) is always Ubuntu-02.

Picture 4

Picture 5

Pictures 6-8 show the result of the failover.

Picture 6

Picture 7

Picture 8

What happens when you complete the task with the Permanent failover? (Picture 9/10/11)

Picture 9

Picture 10

Picture 11

First of all, comparing picture 3 with 12 it is possible to see that one of the Replica Ready VM, and precisely the VM in permanent failover, has been deleted.

Picture 12

Picture 13 shows that now the replica job contains 0 objects. The right behavior is confirmed by pictures 14,15,16 and 17  where it is shown that the replica is not available anymore.

Picture 13

Picture 14

Picture 15

Picture 16

Picture 17

The cloning job option didn’t change the correct behavior (Pictures 18 and 19)

Picture 18

Picture 19

Let’s sum up. Following the right procedure, the Failover works as aspected

Now …. why the VM has been deleted? The next article will explain it in detail.

How to find strings with PowerShell

An article to explain how easy it is to answer some working needs using Microsoft Powershell.

In my job, I happen to have the need to search some data written inside files.

Three classic requests:

1) I need to remember some info about a meeting (I take always notes during meetings)

2) I need to get a statistic about how many customers asked a particular feature

3) I need to search for some errors in application logs

In this short article, I show you how to answer.

In my example, I need to find  a string with the content “find me” in my Documents folder

The PowerShell command is:

Get-ChildItem -Recurse -Path “C:\Users\VBR\Documents\” | Select-String Pattern “find me”

it is composed of two parts separated by a vertical bar (|)

In the first part, the command will search all files into the path C:\Users\VBR\Documents\ (Recurse)

In the second will search the type (string) and the object (pattern)

I like the idea of saving the results of the command in a file and also having just the path of the string I searched.

The command is changed as you can see below:

Get-ChildItem -Recurse -Path “C:\Users\VBR\Documents\Test-Find” | Select-String -Pattern “find me” | select path | Out-File C:\Scripts\Results\search_script_out.txt

To remember:
All PowerShell commands support wild card (*, ?, [ ]), which means you can search any string in your environment.