大家好,我是霸王卸甲,今天给大家带来的是数据库中的物理备份,物理备份又包括增量备份和差异备份,今天要讲的是增量备份!!!!!!!物理备份 + 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