The use of xtarback backup tool

One: what is xtrabackup

XtraBackup is a tool for backing up MySQL. Only full backup is supported for mysql's MYISAM engine, and not only full backup but also incremental backup is supported for innodb. It is a fully automatic tool.

Two: install xtrabackup

Download the xtrabackup backup tool on the official website of percona

Percona-xtrabackup-2.0.0-417 / / supports mysql-5.5

Rpm package for installation

Dependencies during installation

Solve the three dependencies

1,libaio.1

2.perl-DBD-Mysql

3.linev.so.4 / / need to download the yum feed from the Internet.

Third: the use of xtrabackup

Innobackupex is a perl script for xtrabackup.

Actual combat one:

Full backup of the database

Innobackupex-- user=root-- password=*** / backup/ backup path

In general, after the backup is completed, it is necessary to do a rollback to prevent some transactions from not being committed and solve the problem of non-synchronization.

Innobackupex-apply-log / backup/2015....

Connection database refresh log file

Flush logs

Backup binaries

Cp / mysql-bin-00003 / backup/

Mysqlbinlog mysql-bin-00004 > abc.sql

Simulate database corruption

Data file for rm-fr / mydata/data / / mysql

An exception occurred when the service was turned off and enabled.

Killall mysqld

Restore database

The normal state of the database service is not required during restore, but backup is required.

Innobackup-copy-back / backup/2015....

Chown mysql.mysql / mydata/data-R

/ etc/init.d/mysqld start

The database is normal.

Use binaries to recover data (operate the database after a full backup)

Set sql_log_bin=0 / / does not record binary logs

Source / backup/abc.sql

Set sql_log_bin=1 / / returned to normal

Actual combat II:

Xtarbackup makes incremental backup

Innobackup-incremental / backup-incremental-basedir=/backup/2016... Fully backed up files

Innobackupex-- incremental / backup/-- incremental-basedir=/backup/2016-05-15 15-30-23 /

The first increment points to the fully backed up file

Specify the files for the first incremental backup after the second incremental backup

In case of damage, it can be completely + incremental + binary.

Restore:

Prepare to specify only redo operations

When there is an incremental backup, only specify the redo operation and incrementally commit the transaction because there is no commit operation at all

Innobackupex-- apply-log-- redo-only / backup/2016-05-15 15-30-23 /

Innobackupex-- apply-log-- redo-only / backup/2016-05-15-30-23 /-incremental-dir=/backup/2016-05-15-35-25 /

All submission actions are backed up to the full backup file

Restore only the fully backed up files-- copy-back

Specify the complete file for the first time when restoring. Append later, unlike when backing up

420 innobackupex-- apply-log-redo-only / backup/2016-05-15 years 16-05-22 /-incremental-dir=/backup/2016-05-15 years 16-13-46 /

421 ls

422 rm-fr *

423 innobackupex-- copy-back / backup/2016-05-15 16-05-22 /

424 ls

425 chown mysql.mysql

426 chown mysql.mysql. -R

427 mysqlls

428 ls

429 rm-fr *

430 innobackupex-copy-back / backup/2016-05-15 innobackupex 16-05-22 /

431 ls

432 chown mysql.mysql. -R

433 killall mysqld

434 / etc/init.d/mysqld start

435 mysql