ProHoster > Blog > Administration > Creating a MySQL Backup Using the XtraBackup Utility

Creating a MySQL Backup Using the XtraBackup Utility

Contact XtraBackup is a utility for hot backup of databases MySQL.

While backing up data, there are no table locks, your system continues to work without any restrictions.

XtraBackup 2.4 can back up tables InnoDB, XtraDB ΠΈ MyISAM on servers MySQL 5.11, 5.5, 5.6 and 5.7and also on the server percona for MySQL Ρ XtraDB.

To work with MySQL 8.x version should be used XtraBackup 8.x. In this article, we will only talk about XtraBackup 2.4.

The main advantage XtraBackup is that this utility is suitable for creating backup copies of highly loaded servers, as well as for systems with a low number of transactions.

If the total size of your MySQL databases is significant (tens of gigabytes), then the standard utility mysqldump will not allow you to quickly create a backup, and restoring a dump will take a long time.

Installation

Installation XtraBackup from the repository apt Percona.

Run the following commands in sequence:

wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb

sudo dpkg -i percona-release_latest.$(lsb_release -sc)_all.deb

sudo apt-get update 

sudo apt-get install percona-xtrabackup-24

2. After installation run the command xtrabackup -v. Since, it is important to make sure that the utility works correctly on the server. As a result, something like this will be displayed on the screen:

xtrabackup: recognized server arguments: - datadir=/var/lib/mysql - tmpdir=/tmp - server-id=1 - logbin=/var/log/mysql/mysql-bin.log - innodbbufferpoolsize=16384M - innodbfilepertable=1 - innodbflushmethod=Odirect β€” innodbflushlogattrxcommit=0xtrabackup version 2.4.20 based on MySQL server 5.7.26 Linux (x8664) (revision id: c8b4056)

Creating a MySQL Backup Using the XtraBackup Utility

Access rights, permissions and privileges 

XtraBackup should be able to:

  • Connect to your MySQL server.

  • Have access rights to the directory datadir. 

  • During the creation of a backup, have write permissions to the specified via the parameter target-dir catalog.

What is datadir?

datadir is the directory where the database server MySQL stores data. All databases, all tables are there. On most Linux distributions, the default directory is / Var / lib / MySQL.

What is the target-dir directory?

target-dir is the directory where the backup will be saved.

The database user needs the following access rights to the tables and databases to be backed up:

  • RELOAD and LOCK TABLES

  • REPLICATION CLIENT

  • CREATE TABLESPACE

  • PROCESS

  • SUPER

  • CREATE

  • INSERT

  • SELECT

Configuration 

Configuration XtraBackup is done using options, which behave the same as standard MySQL options.

What does this mean?

Configuration parameters can be specified either on the command line or in the DBMS configuration file, for example in /etc/my.cnf.

XtraBackup utility reads partitions after launch [mysqld] ΠΈ [xtrabackup] from MySQL config files. This is done so that the utility can use the settings of your DBMS without having to specify the parameters by hand during each backup.

For example, the value datadir and some parameters InnoDB XtraBackup is obtained from the configuration of your DBMS.

If for XtraBackup to work you want to override the parameters that are in the section [mysqld], then just specify them in the configuration file in the section [xtrabackup]. Since they will be read later, their priority will be higher.

You don't have to add any parameters to my.cnf. All required parameters can be specified on the command line. Usually the only thing that can be conveniently placed in a section [xtrabackup] your my.cnf is a parameter target_dir, which by default defines the directory where the backups will be placed. But this is optional.

An example of specifying the path to the backup directory in my.cnf:

[xtrabackup]
target_dir = /data/backups/mysql/

Backup script

You can use the following script to create a backup:

#!/bin/bash

# УдаляСм Π΄Π°Π½Π½Ρ‹Π΅ Π² ΠΊΠ°Ρ‚Π°Π»ΠΎΠ³Π΅ Π±Π΅ΠΊΠ°ΠΏΠ°
rm -rf /mysql/backup

# CΠΎΠ·Π΄Π°Ρ‘ΠΌ Π±Π΅ΠΊΠ°ΠΏ
xtrabackup --user=xtrabackup 
 --password=xxxx_SECRET_xxxx 
 --backup 
 --target-dir=/mysql/backup

# ВыполняСм ΠΏΠΎΠ΄Π³ΠΎΡ‚ΠΎΠ²ΠΊΡƒ Π±Π΅ΠΊΠ°ΠΏΠ° для развёртывания
xtrabackup --prepare --target-dir=/mysql/backup

# Π‘ΠΎΠ·Π΄Π°Ρ‘ΠΌ Π°Ρ€Ρ…ΠΈΠ²
tar -zcvf /home/developer/dumps/xtrabackup-all-dbs-"$(date +%F-%H:%M:%S)".gz /mysql/backup

What happens during script execution?

First of all, we clean (delete) the directory in which we will save the backup:

rm -rf /mysql/backup.

Then, using the utility XtraBackup create a backup and save it to /mysql/backup/:

xtrabackup --user=xtrabackup --password=xxxxz1cYf95550Gc6xxxxxxxpE3rB03xxxx --backup --target-dir=/mysql/backup

Previously, in MySQL, we created a user xtrabackup with the required privileges. Using the parameter target-dir we specify the directory where the backup should be saved.

An important point!

Notice the script line:

xtrabackup --prepare --target-dir=/mysql/backup

Catalog data /mysql/backup not consistent until they are prepared. 

The fact is that during the copying of files, changes could occur. Operation xtrabackup --prepare --target-dir=/mysql/backup makes the backup data perfectly aligned in time.

You can run the data preparation operation on any machine. There is no need to do this on the server where the original DBMS resides. You can copy the backup to the target server and prepare it there.

The last thing we do is create an archive in which we place our backup:

tar -zcvf /home/developer/dumps/xtrabackup-all-dbs-Β«$(date % F% H% M% S)Β».gz /mysql/backup

Restoring a backup 

Before the backup can be restored to the target server, the data must go through a preparation phase. How to do this, see above.

The data recovery process is very simple. You need to extract the backup from the archive and replace the data in datadir.

How to replace data in datadir?

Let's consider two options.

Option 1

Use the utility XtraBackup. You need to specify an option --copy-back. 

The command below will transfer the backup to datadir target server:

xtrabackup --copy-back --target-dir=/mysql/backup

Option 2

You can do otherwise, do without the utility XtraBackup.

All you need is to copy the backup to datadir. You can do this with cp or Rsync.

It is important to understand that the procedure for restoring a backup is only to replace the contents of the directory datadir.

Before you start restoring a backup on the target server, you must:

  • Stop MySQL server.

  • Empty folder datadir or move its contents to another location. Catalog datadir must be empty.

After the data has been transferred to datadir the MySQL server can be started.

Materials used

Official documentation percona XtraBackup.

Source: habr.com

Add a comment