MySQL Backups: What is XtraBackup?

The most challenging part of any database backup process is maintaining data consistency during the backup process. It becomes even harder when your database is being used in the production and you can’t stop the database to take the consistent backup as it will result in the downtime for the dependent applications. The process of taking the backup of an active database is called hot backups. If you try to take the backup of an active database, you may end up with the inconsistent snapshot of the database i.e all the transactions which were made during the backup process won’t be there in the backup.

If you are facing similar kinds of problems then you can use the open-source utility called Percona XtraBackup from Percona. You can either create incremental or full backups of your active MySQL/Percona Server. In this article, we will go through the process of installing Percona XtraBackup and some of its use cases like taking a backup or restoring a backup of your database.

Prerequisites

Make sure that you have already set up the following things:

Ubuntu/CentOS system with superuser permissions
A running MySQL/Percona Server
Root/Admin user access to the database

Installation on CentOS server

You can install the XtraBackup on CentOS server using yum command as following:

Install the Xtrabackup RPM
1. ```
$ sudo yum install
```
2. http://www.percona.com/downloads/percona-release/redhat/1.0-15/percona-release-1.0-15.noarch.rpm
Install XtraBackup
1. ```
$ sudo yum install percona-xtrabackup
```

Installation on Ubuntu server

You can install Xtrabackup on Ubuntu using apt-get command as following:

Add the Percona repository

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

Install the downloaded package

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

Update the apt sources
1. ```
sudo apt-get update
```

Install XtraBackup

sudo apt-get install percona-xtrabackup-24

Or run the command below if using MySQL/Percona Server >= 8.0,
1. ```
sudo apt-get install percona-xtrabackup-80
```
You can check whether XtraBackup got installed successfully or not by running the following command to check its version.
1. ```
$ xtrabackup --version
```

Before we start the actual process of backing up our database using XtraBackup, we need to create the new user and set the system user permissions and privileges appropriately so the backup process can be executed properly.

Initial Preparations

Add the system user to the mysql group.

$ sudo gpasswd -a <your_username> mysql

Create the directory structure for storing the backups and change the owner of the parent directory.

$ sudo mkdir -p /data/backups

$ sudo chown -R <your_username>: /data

Now to start the XtraBackup process, we will require a new user which will be used by XtraBackups to create the backups.

To create the new user, first, log in to MySQL shell using the following command and enter the password once it prompts.
1. ```
$ mysql -u root -p
```

Create a new user with some good password

mysql > CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'backup_password';

Grant all the required permissions like replication, reload or lock which all are required to take a database backup.

mysql > GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'backup_user'@'localhost';
mysql > FLUSH PRIVILEGES;

Exit from the MySQL shell once you are done.
1. ```
mysql > exit
```

Once you are done with all of the above steps, we are ready to start the backup process.

Backup Using XtraBackup

We will use innobackupex utility to take the backup of an already running database. innobackupex is a Perl script wrapper around the actual XtraBackup program.

Run the following command to start the backup process.

$ innobackupex --user=backup_user  --password=backup_password --no-timestamp /data/backups/mysql

The above command will create the backup of your database in the path we provided: /data/backups/mysql. If the provided directory already exists then the command will throw an error. If you want to avoid creating a new directory every time, remove the --no-timestamp option. This will create a new directory based on the current timestamp. For e.g. /data/backups/2020_03_14_12_00_00

Once the process is complete, you should be able to see the following message on your screen:

150163 12:05:03 innobackupex: completed OK!

Restore Using XtraBackup

Once you have created the backup, it isn’t ready for the restore yet. We need to prepare the data for the restoration by applying the transaction logs of any uncommitted transactions. Now run the following command

$ innobackupex --apply-log /data/backups/mysql

Once this process is completed, you should be able to see the same message on your screen.

150163 12:10:05 innobackupex: completed OK!

Now our data is ready to be restored. Before we start the restore process, ensure that your database is stopped and the data directory inside MySQL folder is empty.

To stop the MySQL service run the following command:

sudo service mysql stop (Ubuntu)
sudo systemctl stop mysql (CentOS)

Take a backup of your old data directory.

$ mkdir ~/mysql_old/
$ mv /var/lib/mysql/* ~/mysql_old/

Now, we can restore the database with the backup we created using the following command.

$ innobackupex --copy-back /data/backups/mysql

Again, you should see the same success message if the operation was successful. Ensure that the owner of the mysql data directory is mysql user by running the following command:

$ chown -R mysql:mysql /var/lib/mysql

Now you can start the MySQL service.

sudo service mysql start (Ubuntu)
sudo systemctl start mysql (CentOS)

Conclusion

It is easy to create backups of a running database and restore it to the existing database using the Percona XtraBackup utility. If you want to do this process regularly, it is advisable to automate this process using automation scripts and cronjobs. Also, you should consider rotating your backup folders regularly to avoid storage-related issues.

MySQL Backups: What is XtraBackup?