Database Backup tool Xtrabackup

Xtrabackup is a MySQL database backup tool provided by Percona, officially introduced as an open source tool for hot provisioning of InnoDB and XTRADB databases.

1. Installation

Yum Install Percona-xtrabackup-y

2. Create a database

Create DATABASE test;

Use test;

CREATE table bak (id int, name char (20)); Create a table

Insert into BAK values (1, ' dog ');

3, the establishment of a less privileged user management database, try not to use root

Grant Reload,lock tables, replication client on * * to ' user ' @ ' localhost ' identified by ' CentOS ';

(1) Reload allowed execution of commands flush-hosts, Flush-logs, Flush-privileges, Flush-status, Flush-tables, flush-threads, refresh, reload

(2) You must have lock tables permission before you can use the lock tables

(3) Grant copy account replication client permission, copy user can use show master status, show slave status and show binary logs

4. Full backup

Innobackupex--user=user--password=centos/backup/

650) this.width=650; "src=" Https://s1.51cto.com/oss/201711/16/68fab3a4ac06ab14b4ebef7ea42b71af.png "title=" 12.png "Width=" "height=" 106 "border=" 0 "hspace=" 0 "vspace=" 0 "style=" width:700px;height:106px; "alt=" 68fab3a4ac06ab14b4ebef7ea42b71af.png "/>

5. Inserting data into the database

Use test;

Insert into BAK values (2, ' cat ');

Insert into BAK values (3, ' pig ');

6. Make Incremental backup

Innobackupex--incremental/backup/--incremental-basedir=/backup/2017-11-16_16-37-22

Explanation:/backup/Specifies the backup directory

--incremental-basedir Specifies the basis on which to base the incremental backup.

7. Preparation of data recovery

in general, data cannot be used for recovery operations after the backup is complete, because the data that is backed up may contain transactions that have not yet been committed or that have been committed but have not been synchronized to the data file. Therefore, the data file still handles the inconsistent state at this time. The primary role of "prepare" is to roll back uncommitted transactions and synchronize committed transactions to data files to make the data files consistent state "ready"(Prepare) incremental backups are a bit different from organizing a full backup, especially to note that:

(1) need to be in each backup ( including full and individual incremental backups ) , a transaction that has already been committed is "replayed". After replay, all backup data is merged into a full backup.

(2) "Roll Back" uncommitted transactions based on all backups.

For example, some data is not written to disk when the database is copied.

Cd/backup

First full backup Innobackupex--apply-log--redo-only 2017-11-16_16-37-22
then the incremental backup Innobackupex--apply-log--redo-only 2017-11-16_16-37-22--incrementaldir=2017-11-16_16-43-47 need to specify a full backup directory and Incremental Catalog

8. Test recovery data

(1) accidentally deleting database files

650) this.width=650; "src=" Https://s4.51cto.com/oss/201711/16/b8a1528b0eebcf5fe0e1e0dd8bc4e813.png "title=" 15.png "alt=" B8a1528b0eebcf5fe0e1e0dd8bc4e813.png "/>

or RM-RF *, if you just delete one of the libraries, you need to do the following

(2) Recovery

Innobackupex--copy-back/backup/2017-11-16_17-39-43/

650) this.width=650; "src=" Https://s2.51cto.com/oss/201711/16/a7a7cfb3a9d898b772e8e3a669abb6b6.png "title=" 16.png "Width=" "height=" 133 "border=" 0 "hspace=" 0 "vspace=" 0 "style=" width:700px;height:133px; "alt=" A7a7cfb3a9d898b772e8e3a669abb6b6.png "/>

(3) Cd/var/lib

Cp-a MySQL Mysql.bak

RM-RF MySQL Delete mysql folder

mkdir MySQL

CD MySQL

Innobackupex--copy-back/backup/2017-11-16_17-39-43/Recovering data

650) this.width=650; "src=" Https://s4.51cto.com/oss/201711/16/a3b1857654a6d24db40d2b1269839696.png "title=" 17.png "Width=" height= "border=" 0 "hspace=" 0 "vspace=" 0 "style=" width:700px;height:31px; "alt=" A3b1857654a6d24db40d2b1269839696.png "/>

chown-r mysql.mysql MySQL modify/var/lmysql

Systemctl Start mariadb

9. Connect to the database to view file recovery

650) this.width=650; "src=" Https://s5.51cto.com/oss/201711/16/b3df3e12d7067d58afaff9196d98535d.png "title=" 20.png "alt=" B3df3e12d7067d58afaff9196d98535d.png "/>

This article is from the "Linux Learning log" blog, so be sure to keep this source http://guanm.blog.51cto.com/13126952/1982599

Database Backup tool Xtrabackup