Use Xtrabackup to back up and restore the database,

Use Xtrabackup to back up and restore the database,

Install Xtrabackup

wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.9/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.9-1.el7.x86_64.rpmyum install percona-xtrabackup-24-2.4.9-1.el7.x86_64.rpm

Note: backup source files cannot be directly used for restoration and recovery in the production environment. You must copy one copy separately for restoration. 1. Full backup and restoration.

1. Back up the database

innobackupex --user=root --password=li123456 /backups

2. Database destruction

rm -rf /app/mysqldb/*

3. Restore the database

Systemctl stop mysqld # stop the service
Innobackupex -- apply-log/backups/2018-03-05_18-11-28/# sort out the backup
Innobackupex -- copy-back/backups/2018-03-05_18-11-28/# restore the database. Make sure that the database directory is empty before restoration.
Chown-R mysql. mysql/app/mysqldb/# Fix owner Group
Systemctl start mysqld # start the service

 

 

Ii. Full and Incremental backup and Restoration

1. Full backup of the database

innobackupex --user=root --password=li123456 /backups/

 

2. Modify the database and perform Incremental Backup

MariaDB [(none)]> create database ceshi;

 

Mkdir/backups/inc1 # create a directory for storing the first incremental backup to facilitate innobackupex -- user = root -- password = li123456 -- incremental/backups/inc1 -- incremental-basedir =/backups/2018-03-07_15-23-30

 

 

MariaDB [(none)]> drop database test ;

 

Mkdir/backups/inc2 # create a directory for storing the second Incremental Backup
Innobackupex -- user = root -- password = li123456 -- incremental/backups/inc2 -- incremental-basedir =/backups/inc1/2018-03-07_15-42-56

 

3. Database destruction

rm -rf /app/mysqldb/*

4. Restore the database

Systemctl stop mysqld # stop the service
Innobackupex -- apply-log -- redo-only/backups/2018-03-07_15-23-30/# complete backup
Innobackupex -- apply-log -- redo-only/backups/2018-03-07_15-23-30/-- incremental-dir =/backups/inc1/2018-03-07_15-42-56 # sort out the first incremental Backup
Innobackupex -- apply-log -- redo-only/backups/2018-03-07_15-23-30/-- incremental-dir =/backups/inc2/2018-03-07_16-00-17 # sort out the second incremental Backup
Innobackupex -- copy-back/backups/2018-03-07_15-23-30/# restore the database. Make sure that the database directory is empty before restoration.
Chown-R mysql. mysql/app/mysqldb/# Fix owner Group
Systemctl start mysqld # start the service