XtraBackup is an open-source tools for performing hot backups of MariaDB, MySQL and Percona Server databases.
It can perform compressed, incremental and streaming backups. It was designed to backup XtraDB/InnoDB tables, but can also backup other storage engines

on this article i will explain step by step how to use Xtrabackup on mariadb-5.5.x

install xtrabackup

execute the following command as user root

# yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm

testing the repository

# yum list | grep percona-xtrabackup

you should see output similar the following :

percona-xtrabackup-21.x86_64                2.1.9-746.rhel6             @percona-release-x86_64
percona-xtrabackup.x86_64                   2.2.12-1.el6                percona-release-x86_64
percona-xtrabackup-20.x86_64                2.0.8-587.rhel6             percona-release-x86_64
percona-xtrabackup-20-debuginfo.x86_64      2.0.8-587.rhel6             percona-release-x86_64
percona-xtrabackup-20-test.x86_64           2.0.8-587.rhel6             percona-release-x86_64
percona-xtrabackup-21-debuginfo.x86_64      2.1.9-746.rhel6             percona-release-x86_64
percona-xtrabackup-debuginfo.x86_64         2.2.12-1.el6                percona-release-x86_64
percona-xtrabackup-test.x86_64              2.2.12-1.el6                percona-release-x86_64
percona-xtrabackup-test-21.x86_64           2.1.9-746.rhel6             percona-release-x86_64

if you using mariadb-5.5.x please install percona-xtrabackup-21.x86_64, because not compatible if you use percona-xtrabackup ver 2.2.12. it can’t perform to incrementall backup and i see error

DBD::mysql::db do failed: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘CHANGED_PAGE_BITMAPS’ at line 1 at /usr/bin/innobackupex line 3044.
innobackupex: got a fatal error with the following stacktrace: at /usr/bin/innobackupex line 3047
main::mysql_query(‘HASH(0x24ec2f8)’, ‘FLUSH NO_WRITE_TO_BINLOG CHANGED_PAGE_BITMAPS’) called at /usr/bin/innobackupex line 1970
main::backup() called at /usr/bin/innobackupex line 1601
innobackupex: Error:
Error executing ‘FLUSH NO_WRITE_TO_BINLOG CHANGED_PAGE_BITMAPS’: DBD::mysql::db do failed: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘CHANGED_PAGE_BITMAPS’ at line 1 at /usr/bin/innobackupex line 3044

percona-xtrabackup-2.2.1 is compatible with MySQL-5.6.x and MariaDB-10.20.x

install percona-backup

# yum install percona-xtrabackup-21.x86_64

creating backup with percona xtrabackup

Percona XtraBackup needs to be able to connect to the database server, so you must create user and privileges to do this actions.
The database user needs the following privileges on the tables / databases to be backed up:

RELOAD and LOCK TABLES (unless the –no-lock option is specified) in order to FLUSH TABLES WITH READ LOCK and FLUSH ENGINE LOGS prior to start copying the files, REPLICATION CLIENT in order to obtain the binary log position, CREATE TABLESPACE in order to import tables (see Restoring Individual Tables), PROCESS in order to see all threads which are running on the server (see Improved FLUSH TABLES WITH READ LOCK handling), SUPER in order to start/stop the slave threads in a replication environment, use XtraDB Changed Page Tracking for Incremental Backups and for Improved FLUSH TABLES WITH READ LOCK handling.

An SQL example of creating a database user with the privileges required to full backups would be:

mysql> CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY 's3cret';
mysql> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT, SUPER, CREATE TABLESPACE, PROSES ON *.* TO 'bkpuser'@'localhost';
mysql> FLUSH PRIVILEGES;

Full backup

before you create full backup, please create directory to save the backup file

# mkdir -p /home/backup/full_back

execute the following command to create full backup of your database

# innobackupex --user=bkpuser --password=s3cret /home/backup/full_back

now backup file was created and store to the folder /home/backup/full_back
Alternatively, you may omit the –no-timestamp to have XtraBackup create a backup directory based on the current timestamp, like so:

# innobackupex --user=bkpuser  --password=s3cret /home/backup/full_back

Prepare Backup

The last step in creating a hot backup with XtraBackup is to prepare it. This involves “replaying” the transaction log to apply any uncommitted transaction to the backup. Preparing the backup will make its data consistent, and usable for a restore.

Following our example, we will prepare the backup that was created in /data/backups/new_backup. Substitute this with the path to your actual backup:

# innobackupex --apply-log /home/backup/full_back/

Again, you should see “innobackupex: completed OK!” as the last line of output.

Restore Full Backup

before restoring database backup, stop mariadb service and remove all file on directory /var/lib/mysql/

# /etc/init.d/mysql stop
# rm -rfv /var/lib/mysql/*

or you can move/rename folder mysql to another name

use the following command to restore full backup

# innobackupex --user=backup –-password=s3cret --copy-back /home/backup/full_back/

or you can use mv “linux command” to move all content backup file to mysql datadir

# mv /home/backup/full_back /var/lib/mysql

change owner on /var/lib/mysql/ to user mysql before starting service mariadb

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

start service mariadb to check your database

# /etc/init.d/mysql start

Inremental Backup

To perform an incremental backup, we should first perform a full backup – the same like we did in the previous section – to be the base backup of the upcoming incremental backups.

create first incremental backup, just add command –incremental on the command of innobackupex, do as follow

# innobackupex --user=backup --password=s3cret --incremental /home/backup/incr --incremental-basedir=/home/backup/full_back

Prepare Incremental Backup

As mentioned earlier in the article, the preparation process consists of two steps (replaying the committed transactions and rolling back the uncommitted transactions) and using the –apply-log option only will do both of them (like we did in the full backup) but in the incremental backups, we MUST do them separately as follows:

Replay the committed transactions on the base backup (by adding the option “–redo-only”)

# innobackupex --user=backup --password=s3cret --apply-log --redo-only /home/backup/full_back

Replay the committed transactions on the 1st incremental backup

# innobackupex --user=backup --password=s3cret --apply-log --redo-only /home/backup/full_back --incremental-dir=/home/backup/incr

Finally, roll back all uncommitted transactions

# innobackupex --user=backup --password=s3cret --apply-log /home/backup/full_back

now incremental backup ready to restore with way like restore full backup

source :
http://fromdual.com/xtrabackup_in_a_nutshell
https://www.digitalocean.com/community/tutorials/how-to-create-hot-backups-of-mysql-databases-with-percona-xtrabackup-on-centos-7
https://www.percona.com/doc/percona-xtrabackup/2.1

Advertisements