Get the App
SLTechnology News&Howtos  ›  Database  › 

Xtrabackup use case

Shulou Source: shulou.com Published: 2022-06-01 09:51:35 11月19日 Update

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

Tags: Backup increment foundation data directory complete files folders time permissions that is users cases Apple Docker Huawei Linux macOS MariaDB Microsoft MySQL NVidia OPPO Reno macOS Huawei vpn Shulou Information Shulou Tech Info