Community

Blog Events Webinars Tutorials Forum
  1. Blog
  2. Events
  3. Webinars
  4. Tutorials
  5. Forum
Create Account
×
Community Blog Back Up MySQL with Percona Xtrabackup

Back Up MySQL with Percona Xtrabackup

In this tutorial, you will learn how to install and configure Percona Xtrabackup to backup MariaDB on an Alibaba Cloud ECS instance.

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:

apt-get install mariadb-server mariadb-client -y

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:

    Enter current password for root (enter for none):
    Set root password? [Y/n]: N
    Remove anonymous users? [Y/n]: Y
    Disallow root login remotely? [Y/n]: Y
    Remove test database and access to it? [Y/n]:  Y
    Reload privilege tables now? [Y/n]:  Y

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:

wget https://repo.percona.com/apt/percona-release_latest.xenial_all.deb
dpkg -i percona-release_latest.xenial_all.deb

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

apt-get update -y
apt-get install percona-xtrabackup-24 -y

Configure MariaDB

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

mysql -u root -p

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

MariaDB [(none)]> CREATE USER 'bkuser'@'localhost' IDENTIFIED BY 'password';

Next, grant special privileges with the following command:

MariaDB [(none)]> GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'bkuser'@'localhost';

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

MariaDB [(none)]> FLUSH PRIVILEGES;
MariaDB [(none)]> EXIT;

Next, create a new directory to store MariaDB backup:

mkdir -p /opt/backup/mysql

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:

innobackupex --user=bkuser --password=password /opt/backup/mysql/fullbackup --no-timestamp

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

xtrabackup: recognized server arguments: --datadir=/var/lib/mysql --tmpdir=/tmp 
xtrabackup: recognized client arguments: --datadir=/var/lib/mysql --tmpdir=/tmp 
encryption: using gcrypt 1.6.5
181112 15:46:02 innobackupex: Starting the backup operation

IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackupex
           prints "completed OK!".

181112 15:46:02  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'bkuser'  (using password: YES).
181112 15:46:02  version_check Connected to MySQL server
181112 15:46:02  version_check Executing a version check against the server...
181112 15:46:02  version_check Done.
181112 15:46:02 Connecting to MySQL server host: localhost, user: bkuser, password: set, port: not set, socket: not set
Using server version 10.0.36-MariaDB-0ubuntu0.16.04.1
innobackupex version 2.4.12 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 170eb8c)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
.
.
181112 15:46:06 Finished backing up non-InnoDB tables and files
181112 15:46:06 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '137240123'
xtrabackup: Stopping log copying thread.
.181112 15:46:06 >> log scanned up to (137240123)

181112 15:46:06 Executing UNLOCK TABLES
181112 15:46:06 All tables unlocked
181112 15:46:06 Backup created in directory '/opt/backup/mysql/fullbackup/'
181112 15:46:06 [00] Writing /opt/backup/mysql/fullbackup/backup-my.cnf
181112 15:46:06 [00]        ...done
181112 15:46:06 [00] Writing /opt/backup/mysql/fullbackup/xtrabackup_info
181112 15:46:06 [00]        ...done
xtrabackup: Transaction log of lsn (137240123) to (137240123) was copied.
181112 15:46:06 completed OK!

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.

innobackupex --apply-log /opt/backup/mysql/fullbackup

You should see the following output:

InnoDB: Doing recovery: scanned up to log sequence number 137241109 (0%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: Removed temporary tablespace data file: "ibtmp1"
InnoDB: Creating shared tablespace for temporary tables
InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
InnoDB: File './ibtmp1' size is now 12 MB.
InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
InnoDB: 32 non-redo rollback segment(s) are active.
InnoDB: Waiting for purge to start
InnoDB: 5.7.19 started; log sequence number 137241109
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 137241128
181112 15:49:37 completed OK!

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

cat /opt/backup/mysql/fullbackup/xtrabackup_checkpoints

The output will look something like this:

backup_type = full-prepared
from_lsn = 0
to_lsn = 137240123
last_lsn = 137240123
compact = 0
recover_binlog_info = 0

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:

innobackupex --user=bkuser --password=password /opt/backup/mysql/fullbackup-base --no-timestamp

Next, create an incremental backup with the following command:

innobackupex --user=bkuser --password=password --incremental /opt/backup/mysql/increament_bk --incremental-basedir=/opt/backup/mysql/fullbackup-base

The output is as follows:

181112 15:51:51 Finished backing up non-InnoDB tables and files
181112 15:51:51 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '137240123'
xtrabackup: Stopping log copying thread.
.181112 15:51:51 >> log scanned up to (137240123)

181112 15:51:51 Executing UNLOCK TABLES
181112 15:51:51 All tables unlocked
181112 15:51:51 Backup created in directory '/opt/backup/mysql/increament_bk/
181112 15:51:51 [00] Writing /opt/backup/mysql/increament_bk/backup-my.cnf
181112 15:51:51 [00]        ...done
181112 15:51:51 [00] Writing /opt/backup/mysql/increament_bk/xtrabackup_info
181112 15:51:51 [00]        ...done
xtrabackup: Transaction log of lsn (137240123) to (137240123) was copied.
181112 15:51:51 completed OK!

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

cat /opt/backup/mysql/increament_bk/xtrabackup_checkpoints

The output is as follows:

backup_type = incremental
from_lsn = 137240123
to_lsn = 137240123
last_lsn = 137240123
compact = 0
recover_binlog_info = 0

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:

innobackupex --apply-log --redo-only /opt/backup/mysql/fullbackup-base

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

innobackupex --apply-log --redo-only BASE-DIR --incremental-dir=/opt/backup/mysql/increament_bk/

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:

innobackupex --apply-log /opt/backup/mysql/fullbackup-base

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:

mv /var/lib/mysql/* /mnt/

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

innobackupex --copy-back /opt/backup/mysql/fullbackup-base

You should see the following output:

xtrabackup: recognized server arguments: --datadir=/var/lib/mysql --tmpdir=/tmp 
xtrabackup: recognized client arguments: --datadir=/var/lib/mysql --tmpdir=/tmp 
encryption: using gcrypt 1.6.5
181112 15:04:15 innobackupex: Starting the copy-back operation

IMPORTANT: Please check that the copy-back run completes successfully.
           At the end of a successful copy-back run innobackupex
           prints "completed OK!".
.
.
.
181112 15:04:19 [01] Copying ./mysql/servers.MYI to /var/lib/mysql/mysql/servers.MYI
181112 15:04:19 [01]        ...done
181112 15:04:19 [01] Copying ./mysql/time_zone_name.frm to /var/lib/mysql/mysql/time_zone_name.frm
181112 15:04:19 [01]        ...done
181112 15:04:19 [01] Copying ./mysql/column_stats.frm to /var/lib/mysql/mysql/column_stats.frm
181112 15:04:19 [01]        ...done
181112 15:04:19 [01] Copying ./mysql/gtid_slave_pos.frm to /var/lib/mysql/mysql/gtid_slave_pos.frm
181112 15:04:19 [01]        ...done
181112 15:04:19 [01] Copying ./mysql/slow_log.frm to /var/lib/mysql/mysql/slow_log.frm
181112 15:04:19 [01]        ...done
181112 15:04:19 [01] Copying ./mysql/user.MYD to /var/lib/mysql/mysql/user.MYD
181112 15:04:19 [01]        ...done
181112 15:04:19 [01] Copying ./mysql/general_log.CSV to /var/lib/mysql/mysql/general_log.CSV
181112 15:04:19 [01]        ...done
181112 15:04:19 [01] Copying ./mysql/time_zone_transition.MYD to /var/lib/mysql/mysql/time_zone_transition.MYD
181112 15:04:19 [01]        ...done
181112 15:04:19 completed OK!

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

chown -R mysql:mysql /var/lib/mysql
systemctl start mysql

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

SQL Database Computing Data Backup Tech Share Percona XtraBackup Tutorials
0 0 0
Share on

Read previous post:

RAID and How to Set It Up on Alibaba Cloud

Read next post:

How to Install OpenVAS on Alibaba Cloud

Alibaba Clouder

2,599 posts | 764 followers

You may also like

Comments

Alibaba Clouder

2,599 posts | 764 followers

Related Products

More Posts by Alibaba Clouder

See All