Linux MySQL physical backup-incremental backup

大家好，我是霸王卸甲，今天给大家带来的是数据库中的物理备份，物理备份又包括增量备份和差异备份，今天要讲的是增量备份！！！！！！！物理备份 + binlog

Preparatory work:
PERCONA Xtrabakuo
It is open source free of charge MySQL database hot backup software, it can be InnoDB and XTRADB storage Engine database non-blocking backup. It does not pause the service to create InnoDB hot backup;
Make incremental backups of MySQL, make online table migrations between MySQL servers, make it easier to create replication, and backup MySQL without increasing the load on the server.
Percona is a veteran MySQL technology consulting firm. It not only provides MySQL technical support, training, consulting, but also released the MySQL branch version of--percona Server. and around
Percona Server also released a series of MySQL tools.

Official site: https://www.percona.com/

Installation: wget http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm download Yum source

Yum-y Install percona-release-0.1-3.noarch.rpm installation yum source

Yum-y Install percona-xtrabackup-24.x86_64 installation Percona

RPM-QL percona-xtrabackup-24 Query installation Results installation files

Single full backup

Prepare the backup directory first
Mkdir/xtrabackup/full-p
Connect to the database and start the backup.
Innobackupex--user=root--password= ' [email protected] '/xtrabackup/full
Please note that the backup results are complete
Xtrabackup:transaction Log of LSN (74182721) to (74182730) was copied.
171127 15:04:12 completed ok!

View the backup directory. Database, configuration file, log file
ls/xtrabackup/full/
2018-08-01_00-00-02/

Binary Log Location
Cat/xtrabackup/full/2017-08-01_00-00-18/xtrabackup_binlog_info

Full recovery process

First stop the database
Systemctl Stop Mysqld

Clean up the environment (simulated damage)
rm-rf/var/lib/mysql/*

Rm-rf/var/log/mysqld.log

Rm-rf/var/log/mysql-slow/slow.log

Generate rollback Log
Innobackupex--apply-log/xtrabackup/full/2018-08-01_00-00-18/

Recover files
Innobackupex--copy-back/xtrabackup/full/2018-08-01_00-00-18/

Login Verification
Ls/var/lib/mysql

Give permission
Chown-r Mysql.mysql/var/lib/mysql
Restart
Systemctl Start mysqld

Mysql-uroot-p ' [email protected] '

Incremental backup process

Create DATABASE TestDB;
Use TestDB;
CREATE TABLE test (id int);
INSERT into test values (1);
SELECT * from Test;
Analog data

First full backup of the next Monday
rm-rf/xtrabackup/*
Set your own time to avoid chaos.
Date 09010000
Innobackupex--user=root--password= ' [email protected] '/xtrabackup
ll/xtrabackup/
2018-09-01_00-00-04
The records in the database are 1

Backup of the next Tuesday
Date 09020000
Mysql-uroot-p ' [email protected] '-e ' insert into testdb.test values (2) '
Basedir based on the Monday backup.
Will generate a today's.
Innobackupex--user=root--password= ' [email protected] '--incremental/xtrabackup/--incremental-basedir=/xtrabackup /2017-09-01_00-00-04

ls/xtrabackup/
2018-09-01_00-00-04
2018-09-02_00-00-58

Backup of the next Wednesday
Date 09030000
Mysql-uroot-p ' [email protected] '-e ' insert into testdb.test values (3) '
Innobackupex--user=root--password= ' [email protected] '--incremental/xtrabackup/--incremental-basedir=/xtrabackup /2017-09-02_00-00-26

ls/xtrabackup/
2018-09-01_00-00-04
2018-09-02_00-00-58
2018-09-03_00-00-36

This is the end of the backup.
Next: Incremental recovery process

Stopping the database
Systemctl Stop Mysqld
Clean Environment Simulation data loss
rm-rf/var/lib/mysql/*
Roll Back Week 1st log
Innobackupex--apply-log--redo-only/xtrabackup/2018-09-01_00-00-04
Roll Back Week 2nd log
Innobackupex--apply-log--redo-only/xtrabackup/2018-09-01_00-00-04--incremental-dir=/xtrabackup/2018-09-02_ 00-00-26
Start recovering data
Innobackupex--copy-back/xtrabackup/2017-09-01_00-00-06
Chown-r Mysql.mysql/var/lib/mysql
Systemctl Start mysqld

Log-in query record. The discovery was Tuesday.
You want to recover Wednesday, you have to roll back one log.
Innobackupex--apply-log--redo-only/xtrabackup/2017-09-01_00-00-04--incremental-dir=/xtrabackup/2018-09-03_ 00-00-27

Stopping the database
Systemctl Stop Mysqld
Restore Backup
Innobackupex--copy-back/xtrabackup/2018-09-01_00-00-04

Give the power.
Chown-r Mysql.mysql/var/lib/mysql

Systemctl Start mysqld

Enter the database to see if the data is restored
Mysql-uroot-p ' [email protected] '

Linux MySQL physical backup-incremental backup