MySQL Series 6--------use third-party tool-percona to back up MySQL and restore

use third-party tools to back up MySQL-----percona

First, pre-preparation work:

1, install the dependency package: Yum-y Install PERL-DIGEST-MD5 Perl-dbd-mysql

RPM-IVH libev-4.15-1.el6.rf.x86_64.rpm

2. Installation Main package: percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm

3. Contains two components:

/usr/bin/xtrabackup: Supports InnoDB and XTRADB (incremental backup)

/usr/bin/innobackupex: Supports InnoDB and MyISAM (full standby only, incremental not supported)

4. Innobackupex contains some of the options

--user user name

--password Password

--databases Library Name

--no-timestamp a subdirectory name that does not use the system date for the backup directory

--apply-log Recovery Log

--copy-back Recovering data

Second, full backup and full recovery

1. Full backup

Innobackupex--user root--password 123456--database= "MySQL db66 performance sys"

--no-timestamp/allbak//Specify to back up three databases, the resulting backup file directory does not use the system time to do directory names

2. When recovering data, the database directory is empty and three system libraries must be backed up

First RESTORE log: Innobackupex--user root--password 123456--apply-log/allbak

Delete database directory: Rm-rf/var/lib/mysql

Create DATABASE directory: Mkdir/var/lib/mysql

Recover data: Innobackupex--user root--password 123456--copy-back/allbak/

give Data directory permissions: Chown-r mysql:mysql/var/lib/mysql

Restart service, verify: systemctl restart Mysqld

Iii. incremental backup and incremental recovery

1. Make a full backup before incremental backup.

innobackupex--user root--password 123456

--databases= "Performance_schema sys mysql db666"

--no-timestamp/onebak

2. incremental backup for the first time

Innobackupex--user root--password 123456//Specify User name password

--databases= "Performance_schema sys mysql db666"//Specify the database to be backed up

--incremental/new1//Specify the path of the incremental backup

--incremental-basedir=/onebak//Specify which backup to use as a reference for incremental backups

--no-timestamp

3. Second Incremental backup:

innobackupex--user root--password 123456

--databases= "Performance_schema sys mysql db666"

--INCREMENTAL/NEW2//The path of the second incremental backup

--INCREMENTAL-BASEDIR=/NEW1//Incremental backup as a reference to the last incremental backup

--no-timestamp

4, incremental recovery of the file directory description

xtrabackup_checkpoints xtrabackup_logfile//These two files record the modified LSN number

5. Recovery Steps

A, delete files under the data directory: rm-rf/var/lib/mysql/*

B. Log of recovery log---full backup

Innobackupex--user Root--password 123456

--databases= "Performance_schema sys mysql db666"

--apply-log--redo-only//recovery log, and merge logs

/onebak--no-timestamp

C, restore the first incremental backup (only need to restore the log, log merge can be)

Innobackupex--user Root--password 123456

--databases= "Performance_schema sys mysql db666"

--apply-log--redo-only/onebak//source log path

--incremental-dir=/new1--no-timestamp//path of the log file to be merged

D, restore the second incremental backup

innobackupex--user root--password 123456

--databases= "Performance_schema sys mysql db666"

--apply-log--redo-only/onebak//path of source log file

--incremental-dir=/new2--no-timestamp//Second incremental backup merged log

E, restore the data, just restore the full backup inside, because the content of the incremental backup has been merged into the full-ready

Innobackupex--user root--password 123456--copy-back/onebak

F, give permissions to the data directory, restart service validation

Chown-r Mysql:mysql/var/lib/mysql

Systemctl Restart Mysqld

Iv. recovering a single table from a fully backed up data

1, Export//Import table information import//Importing table space

Delete table space: ALTER TABLE table Discard tablespace;

2. Back up a single database:

Innobackupex--user Root--password 123456

--databases= "MyDB"--no-timestamp/mydb//Backup only one database

3, delete the table, create the table (the table field must be the same as before, the simulation tables are missing

drop table T1; CREATE TABLE t1 (id int);

4. Export table information: The parameter used is--export

Innobackupex--user root--password 123456--databases= "db66"--apply-log--export/db66

5. Delete Table space: operation on MySQL command line

ALTER TABLE DB66.T1 discard tablespace;

6. Copy the exported table information from the backup directory to the database directory, and give the permissions

Cp/db66/db66/t1. {CFG,EXP,IBD}/var/lib/mysql/db66/

Chown mysql/var/lib/mysql/db66/t1.*

7. Import Table Space

ALTER TABLE DB66.T1 import tablespace;

8, Verification: SELECT * from Db66.t1;

MySQL Series 6--------use third-party tool-percona to back up MySQL and restore