Linux MySQL physical backup-incremental backup

Source: Internet
Author: User
Tags percona percona server

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.