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