Xtrabackup use case

Backup user creation

CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY' bkpuser'

GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON. TO 'bkpuser'@'localhost'

FLUSH PRIVILEGES

II. Backup

-- perform a backup (without timestamp)

/ usr/local/xtrabackup/bin/innobackupex-defaults-file=/data/mysql/db_test/conf/test.cnf-- user=bkpuser-- password=bkpuser-- socket=/data/mysql/db_test/mysql.sock-- parallel=8-- slave-info-- no-timestamp / data/backups/full

-- perform backup (with timestamp)

/ usr/local/xtrabackup/bin/innobackupex-defaults-file=/data/mysql/db_test/conf/test.cnf-user=bkpuser-password=bkpuser-socket=/data/mysql/db_test/mysql.sock-parallel=8-slave-info / data/backups/full

-- perform a backup (specific to a library)

/ usr/local/xtrabackup/bin/innobackupex-defaults-file=/data/mysql/db_test/conf/test.cnf-- user=bkpuser-- password=bkpuser-- socket=/data/mysql/db_test/mysql.sock-- databases=t1-- parallel=8-- slave-info / data/backups/full

-- perform backup (incremental backup, incremental backup on a full basis)

/ usr/local/xtrabackup/bin/innobackupex-- defaults-file=/data/mysql/db_test/conf/test.cnf-- user=bkpuser-- password=bkpuser-- socket=/data/mysql/db_test/mysql.sock-- incremental-basedir=/data/backups/full/2018-05-02 13-42-59 /-- incremental / data/backups/inc

-- perform backup (incremental backup, incremental backup on the basis of additional backup)

/ usr/local/xtrabackup/bin/innobackupex-- defaults-file=/data/mysql/db_test/conf/test.cnf-- user=bkpuser-- password=bkpuser-- socket=/data/mysql/db_test/mysql.sock-- incremental-basedir=/data/backups/inc/2018-05-02 13-44-24 /-- incremental / data/backups/inc

III. Recovery

-full recovery

Rm-rf / data/mysql/db_test/data/ # Delete the data directory

Rm-rf / data/mysql/db_test/ulog/ # Delete the data directory

/ usr/local/xtrabackup/bin/innobackupex-- defaults-file=/data/mysql/db_test/conf/test.cnf-- user=bkpuser-- password=bkpuser-- apply-log-- redo-only / data/backups/full/2018-05-02 December 13-42-59 /

/ usr/local/xtrabackup/bin/innobackupex-defaults-file=/data/mysql/db_test/conf/test.cnf-user=bkpuser-password=bkpuser-copy-back / data/backups/full/2018-05-02 13-42-59 /

-- modify folder permissions

Chown-R mysql:mysql / data/mysql/db_test/data

Chown-R mysql:mysql / data/mysql/db_test/ulog

-- incremental recovery (incremental recovery on a complete basis)

-- in fact, it is to combine the complete equipment with the additional equipment, and then restore it.

Rm-rf / data/mysql/db_test/data/ # Delete the data directory

Rm-rf / data/mysql/db_test/ulog/ # Delete the data directory

/ usr/local/xtrabackup/bin/innobackupex-- defaults-file=/data/mysql/db_test/conf/test.cnf-- user=bkpuser-- password=bkpuser-- apply-log-- redo-only / data/backups/full/2018-05-02 December 13-42-59 /

/ usr/local/xtrabackup/bin/innobackupex-- defaults-file=/data/mysql/db_test/conf/test.cnf-- user=bkpuser-- password=bkpuser-- apply-log-- redo-only / data/backups/full/2018-05-02 13-42-59 /-- incremental-dir=/data/backups/inc/2018-05-02 13-44-24

/ usr/local/xtrabackup/bin/innobackupex-- defaults-file=/data/mysql/db_test/conf/test.cnf-- user=bkpuser-- password=bkpuser-- apply-log-- redo-only / data/backups/full/2018-05-02 December 13-42-59 /

/ usr/local/xtrabackup/bin/innobackupex-defaults-file=/data/mysql/db_test/conf/test.cnf-user=bkpuser-password=bkpuser-copy-back / data/backups/full/2018-05-02 13-42-59 /

-- incremental recovery (incremental recovery based on additional equipment)

-- restore all the equipment + additional equipment 1 + additional equipment 2 together

/ usr/local/xtrabackup/bin/innobackupex-- defaults-file=/data/mysql/db_test/conf/test.cnf-- user=bkpuser-- password=bkpuser-- apply-log-- redo-only / data/backups/full/2018-05-02 December 13-42-59 /

/ usr/local/xtrabackup/bin/innobackupex-- defaults-file=/data/mysql/db_test/conf/test.cnf-- user=bkpuser-- password=bkpuser-- apply-log-- redo-only / data/backups/full/2018-05-02 13-42-59 /-- incremental-dir=/data/backups/inc/2018-05-02 13-44-24

/ usr/local/xtrabackup/bin/innobackupex-- defaults-file=/data/mysql/db_test/conf/test.cnf-- user=bkpuser-- password=bkpuser-- apply-log-- redo-only / data/backups/full/2018-05-02 13-42-59 /-- incremental-dir=/data/backups/inc/2018-05-02 13-45-45

/ usr/local/xtrabackup/bin/innobackupex-- defaults-file=/data/mysql/db_test/conf/test.cnf-- user=bkpuser-- password=bkpuser-- apply-log-- redo-only / data/backups/full/2018-05-02 December 13-42-59 /

/ usr/local/xtrabackup/bin/innobackupex-defaults-file=/data/mysql/db_test/conf/test.cnf-user=bkpuser-password=bkpuser-copy-back / data/backups/full/2018-05-02 13-42-59 /

-- modify folder permissions

Chown-R mysql:mysql / data/mysql/db_test/data

Chown-R mysql:mysql / data/mysql/db_test/ulog