Setup Hot Backups of MySQL with Percona Xtrabackup on Ubuntu

When backing up a database, you generally don't want to stop the database. Because if you stop it, your application will become unavailable. But if you do not stop it, your backup might end up inconsistent and useless, due to half done transactions being backed up. Percona have come up with good solution for incremental backup of running MySQL instance. Their hot backup solution is called Percona XtraBackup and can be used to backup both MySQL, MariaDB and PerconaServer. I requires running InnoDB storage engine though. In this article we will show how to install MySQL (current version 5.7) on Ubuntu 16.o4 and use XtraBackup to backup it while running. For restoring the backup, the database server must be stopped and data dir evacuated.

Installing MySQL

MySQL is present in Ubuntu repositories and we can install it with simple apt command:

sudo apt install mysql-server mysql-client

It will prompt you for root password from ncurses environment. Enter it and complete the installation process.

After that is done, we need to run a script to secure MySQL installation.

sudo mysql_secure_installation

Here you could change the password you entered previously, you can disable the remote login and you can delete the test database.

We need as well to add your user to the mysql grup, this command will do it

sudo gpasswd -a ubuntu mysql

The user name is ubuntu, you change it for your own. To check if install is successful, you can try to log in

mysql -u root -p

Lets then create a database that we will work with latter

CREATE DATABASE dbtobackup;

Lets create user and grant privileges to this database

GRANT ALL ON dbtobackup.* TO 'miki' IDENTIFIED BY 'password';

We need to populate database so first thing we create is a table like this:

USE dbtobackup;

CREATE TABLE linoxide_staff (id INT, name VARCHAR(12), nickname VARCHAR(28));

Then we put data into table

INSERT INTO linoxide_staff (id,name,nickname) VALUES(1,"Bobin","Bobby");

INSERT INTO linoxide_staff (id,name,nickname) VALUES(2,"Mihajlo","Miki");

And check what we created

mysql> SELECT * FROM linoxide_staff;

Next for backup to work, we need to give this user some privileges also to other databases that we might create later

GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'miki'@'localhost';

We also need to grant process:

GRANT SELECT, PROCESS ON *.* TO 'miki'@'localhost';

Next we can exit mysql and move to setup of Percona xtrabackup.

Using Percona Xtrabackup to backup your database

Lets get deb file for percona repository

wget https://www.percona.com/redir/downloads/percona-release/ubuntu/latest/percona-release_0.1-4.xenial_all.deb

Next we will install it

sudo dpkg -i percona-release_0.1-4.xenial_all.deb

Update sources list:

sudo apt update

And install Percona xtrabackup

sudo apt install percona-xtrabackup-24

Note that older version than 2.4 wont work.

We need to set permissions to MySQL data dir, which is where the databases are stored

sudo chown -R mysql: /var/lib/mysql
sudo find /var/lib/mysql -type d -exec chmod 775 "{}" \;

And we need to create backup dir, that is where we will store the backups

sudo mkdir -p /data/backups
sudo chown -R ubuntu: /data

Next we can backup the database:

innobackupex --user=miki --password=password --no-timestamp /data/backups/first_backup

This command will give you pretty verbose output and when it finishes execution, you should get something like this:

161109 17:04:51 [00] ...done
xtrabackup: Transaction log of lsn (2540757) to (2540766) was copied.
161109 17:04:51 completed OK!

For your backup to always be consistent and ready for restore, you would need to apply transaction log to it. That will help with consistency and with avoiding corruption.

innobackupex --apply-log /data/backups/

This command should also finish with completed OK! output if completed successfully.

Restoring the database from Xtrabackup

In order to restore backup you must stop the the database server and empty the data dir.

sudo systemctl stop mysql.service

After service have stopped, what we going to do is somewhat of a paradox. We backup the data dir for the case that backup doesn't succeed. You can't skip this process, as either way data dir needs to be empty. So if you don't want to backup, just delete. We will move/backup now:

mkdir /tmp/mysql
mv /var/lib/mysql/* /tmp/mysql/

The backup is restored with copy-back command

innobackupex --copy-back /data/backups/first_backup

When backup is restored we  need to change permissions to new copy of lib directory

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

Next we can start again the database server

 sudo systemctl start mysql.service

Conclusion

We have successfully done a backup and restore of MySQL on Ubuntu using Percona Xtrabackup. Using this article as a string point you might want to automate this process with a bash script. Another useful guide that we recently published is one about lsyncd. You could use lysncd to sync your backup dir (/data/backups in this case) to remote server and in such way safeguard your database in case your server becomes totally inaccessible for some reason. This would be all for today's article, thank you for reading and hopefully this helps you guard your data.