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.
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
$ sudo yum install
- Install XtraBackup
$ 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
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,
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.
$ 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.
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.
$ 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.
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)
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.