MySQL Backup & Veeam Backup & Replication Parte 2

In questo secondo articolo è illustrato dove ricercare gli script per realizzare backup consistenti di DataBase MySQL con Veeam Backup & Replication.

Per scoprire perché sia necessario utilizzare script, vi raccomando di leggere il precedente articolo.

Hot Backup Database Online Dump (Linux)

L’opzione prevede di integrare negli script il comando mysqldump.

Due esempi sono consultabili al seguente sito:

HotBackup Database Freeze (Linux)

L’opzione prevede di effettuare a caldo il flush delle tabelle.

Due esempi sono consultabili al seguente sito:

Cold Backup Database Shutdown (Linux)

L’opzione prevede di fermare il servizion MySQL prima di realizzare il backup.

Due esempi sono consultabili al seguente sito:

Hot Backup Database Online Dump (Windows)

Il seguente esempio in poweshell è puramente dimostrativo. Il mio consiglio è quello di chiedere al vostro esperto in powershell di crearne uno che rispetti le politiche aziendali di gestione e sicurezza.

Pre command (avvia lo script mySQLdump.ps1 sul server YOURMYSQLSERVER)

$password = ConvertTo-SecureString “YOURPWD” -AsPlainText -Force

$Cred = New-Object System.Management.Automation.PSCredential (“DOMAIN\USER”, $password)

New-PSSession -ComputerName mySQL-WIN -Credential $Cred

#Enter-PSSession -ComputerName YOURMYSQLSERVER

#Invoke-Command -Session 6 -FilePath “C:\Script\script-7.ps1” -ComputerName mySQL-WIN

Invoke-Command -ComputerName mySQL-WIN -Credential $Cred -ScriptBlock { C:\Script\mySQLdump.ps1}

mySQLdump.ps1 (Crea il file .sql che viene memorizzato in una specifica cartella sul server YOURMYSQLSERVER)

# Declare variables

$path = “/backups”                      # path of backup folder

$logFile = “automate-mysqldump.log”     # path of log file

$configFile = “C:\ProgramData\MySQL\MySQL Server 5.6\my.ini”           # path of my.cnf file

# Navigate to the backups folder

Set-Location $path

# get today’s date to name today backup folder

$date = Get-Date -UFormat “%Y-%m-%d”

# Check for log file

# Create if not found

if (-NOT (Test-Path $logFile)) {

    New-Item -Path . -Name $logFile -ItemType “file”

    Add-Content $logFile “Created on: $date`n”

}

# enter directory

# create today’s backup directory if it does not exist

if (-NOT (Test-Path $date)) {

    New-Item -ItemType “directory” $date

    Add-Content $logFile “[$date]: New $date directory is created”

}

# Set-Location $date

Add-Content $logFile “[$date]: Starting mysqldump”

# invoke mysqldump – insert mysqldump statement

mysqldump –defaults-file=$configFile -r $date/database-backup.sql –all-databases

Add-Content $logFile “[$date]: Backup for databases are completed”

Add-Content $logFile “”

# pause

 Post command (chiude la sessione remota)

Remove-PSSession -ComputerName YOURMYSQLSERVER

Nel prossimo articolo sarà illustrato come integrare gli script in Veeam Backup & Replication.

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.

Veeam NAS Backup – Scripts for Demo live

BR v.10 introduced a great new feature. 

NAS Backup allows managing the protection of the environment where files and folders are a focus point for a company.

This article does not cover how a Nas Backup job works or how to set it up. Please refer to the official documentation to have all details.

What is the scope of the next lines?

It will show how easy it is to realize a script to create dummy files
before launching an incremental backup.
Why? Because the scope of this article is showing how fast a backup job can be.

1. The script is composed of four parts (in every box line 1 describes the actions that will be performed after).

#working path
$mypath=”C:\Users\VBR\Documents\NAS-Folder”
Set-Location -Path $mypath
#creating folder to copy to share
$folders_new = New-Item “$((get-date).toString(‘yyyyMMddhhmmss’))” -ItemType directory
Set-Location -Path $folders_new
# Creating 100 files
$baseFile = $rootDir + “base.txt”
$desiredFileSize = 2*10KB
$fileCount = 100
“start” | Out-File -Filepath $baseFile
While ($(Get-ChildItem -path $baseFile).Length -lt $desiredFileSize)
{
$(Get-ChildItem -path $baseFile).Length | Out-File $baseFile -APPEND
}
for($i=1;$i -lt $fileCount;$i++)
{
Copy-Item $baseFile “File-NAS$i.txt”
}
#copy to network share
Set-Location -Path $mypath
$pathtocopy= $folders_new.Name
copy-item -path $pathtocopy -Destination \\ip-address\cifs-destination -Recurse

2. Let’s see how to integrate the script in VBR.

The next pictures will show the Nas Backup job wizard pointing out the main point (picture 3, 4 and 5)

Pictures 1 and 2 show one of my favorite advance options: the possibility to select the file topology to save. For example, protecting the pdf files only. It supports wild-cat character so it’s very flexible and useful.

Picture 1

Picture 2

From picture 3 to 5, highlighted in yellow the step to set-up the script:

Picture 3

Picture 4

Picture 5

Pictures 6 and 7 show the result of the job modified in this way.
Please put your attention to the last yellow line where 195 MB of the source data is processed in 20 seconds. (In my small lab it’s a great result !!!)

Picture 6

Picture 7

Note: If you need to change the script time execution from default 15 mins (900 secs), just add a REG-DWORD named “PreJobScriptTimeoutSec”(Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Veeam\Veeam Backup and Replication). The amount has to be in seconds (Picture 8)

Picture 8

That’s all folks