Shulou(Shulou.com)06/01 Report--

Use third-party tools to back up mysql-percona

I. preliminary preparatory 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. Install the main package: percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm

Percona is an online hot spare tool.

3. It contains two components:

/ usr/bin/xtrabackup: supports innodb and xtraDB (incremental backup)

/ usr/bin/innobackupex: innodb and myisam are supported (only complete, not incremental)

4. Some options included in innobackupex

-- user user name

-- password password

-- databases library name

No-timestamp does not use the system date as the subdirectory name of the backup directory

-- apply-log recovery Lo

-- copy-back recovers data

II. Full backup and full recovery

1. Full backup

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

-- no-timestamp / allbak / / specifies that three databases should be backed up. The backup file directory generated does not need the system time as the directory name.

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

First restore the log: innobackupex-- user root-- password 123456-- apply-log / allbak

Delete the database directory: rm-rf / var/lib/mysql

Create a 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 the service, verify: systemctl restart mysqld

III. Incremental backup and incremental recovery

1. Make a full backup before the incremental backup.

Innobackupex-user root-password 123456

-databases= "performance_schema sys mysql db666"

-- no-timestamp / onebak

2. The first incremental backup

Innobackupex-- user root-- password 123456 / / specify username and password

-- databases= "performance_schema sys mysql db666" / / specifies the database to be backed up

-- incremental / new1 / / specify the path to the incremental backup

-- incremental-basedir=/onebak / / specify which backup to use as the 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 / / use the last incremental backup as a reference to do incremental backup

-- no-timestamp

4. Description of the file directory for incremental recovery

Xtrabackup_checkpoints xtrabackup_logfile / / these two files record the modified lsn number

5. Recovery steps

Delete the files in the data directory: rm-rf / var/lib/mysql/*

B. Recovery log-full backup log

Innobackupex-user root-password 123456

-databases= "performance_schema sys mysql db666"

-- apply-log-- redo-only / / restore logs and merge logs

/ onebak-- no-timestamp

C. Restore the first incremental backup (only need to restore the logs and merge the logs)

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 log files 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 to the source log file

-- incremental-dir=/new2-- no-timestamp / / the merged log of the second incremental backup

To restore data, you only need to restore the contents of the full backup, because the contents of the incremental backup have been merged into the full backup.

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

F. Give permissions to the data directory and restart service verification

Chown-R mysql:mysql / var/lib/mysql

Systemctl restart mysqld

Restore a single table in the fully backed up data

1. Export / / Export table information import / / Import tablespace

Delete tablespaces: alter table table discard tablespace

2. Back up a single database:

Innobackupex-user root-password 123456

-- databases= "mydb"-- no-timestamp / mydb / / back up only one database

3. Delete the table and create the table (the table field must be the same as before, and the simulation table is lost.

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 tablespaces: operate on the mysql command line

Alter table db66.t1 discard tablespace

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

Cp / db66/db66/t1. {cfg,exp,ibd} / var/lib/mysql/db66/

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

7. Import tablespaces

Alter table db66.t1 import tablespace

8. Verification: select * from db66.t1

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.