Back Up MySQL with Percona Xtrabackup

Image for post
Image for post

By Hitesh Jethva, Alibaba Cloud Community Blog author.

Database backup is one of the most important tasks of any system administrator can do to protect against data loss in the event of an accident or hardware failure. Percona XtraBackup is a free, open-source, high-performance, low-profile MySQL hot backup software for InnoDB and XtraDB databases. It is used to backup both MySQL, MariaDB and PerconaServer. It doesn’t lock your database while performing backup.

In this tutorial, we will learn how to install and configure Percona Xtrabackup to backup MariaDB on an Alibaba Cloud Elastic Compute Service (ECS) instance installed with Ubuntu 16.04.

Prerequisites

For this tutorial, you will need the following items:

  • A newly created Alibaba Cloud Ubuntu 16.04 instance.
  • A root password is set up to your instance.

For reference, see create a new ECS instance and connect to your instance. Also, once you are logged into your instance running Ubuntu 16.04, run the apt-get update -y command to update your base system with the latest available packages.

Install MariaDB

By default, MariaDB is available in the Ubuntu 16.04 default repository. You can install it by just running the following command:

Then, once the MariaDB is installed, you will need to secure it first. You can do this by running the mysql_secure_installation command, and then answer all the questions as shown below:

Install Percona XtraBackup

By default, the latest version of Percona XtraBackup is not available in the Ubuntu 16.04 default repository. You can download and install Percona XtraBackup repository with the following command:

Next, update the repository and install Percona XtraBackup with the following command:

Configure MariaDB

Before using Percona XtraBackup, you will need to create a new MySQL user with special privileges. First, log in to MariaDB shell:

Enter your root password. Then, create a user with the following command:

Next, grant special privileges with the following command:

Next, flush the privileges and exit from the MariaDB shell with the following command:

Next, create a new directory to store MariaDB backup:

Perform Full Backup with Innobackupex

You can create a MariaDB backup with the innobackupex to new directory /opt/backup/mysql/fullbackup with the following command:

During the backup process, you should see the following output:

The above command will create a new backup directory fullbackup automatically during backup process. If the directory exists, you will get an error message. After creating a fullbackup, the data isn’t ready for a restore. You will need to run one more command to prepare stage so the data can be restored.

You should see the following output:

You can see the information about your backup with the following command:

The output will look something like this:

Incremental Backups with Innobackupex

As not all information changes between each backup, the incremental backup strategy uses this to reduce the storage needs and the duration of making a backup. So you’ll want to follow these steps:

First, you will need to take a full backup as the BASE for subsequent incremental backups:

Next, create an incremental backup with the following command:

The output is as follows:

You can check the information about your backup with the following command:

The output is as follows:

After creating increamental backup, you will need to prepare stage so the data can be restored.

You can start with the base backup using the following command:

Then, the first incremental backup can be applied to the base backup, by running the following command:

Similarly you can use this procedure to add more increments to the base.

Once you merge the base with all the increments, you can prepare it to roll back the uncommitted transactions:

Now, your backup is ready to use for restore.

Restore Full Backup with Innobackupex

Before restoring the backup, you will need to stop MariaDB service first. You can do this with the systemctl stop mysql command. Next, take a backup of old MySQL data directory:

Next, restore the mysql backup from the full backup with innobackupex:

You should see the following output:

Now, change the ownership of the MySQL data directory and start MySQL service with the following command:

You have now successfully restored the MySQL instance with Percona XtraBackup.

Original Source

Written by

Follow me to keep abreast with the latest technology news, industry insights, and developer trends.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store