mysql自动备份恢复工具_【MySQL】MySQL备份恢复工具之XtraBackup

XtraBackup概述

XtraBackup是Percona提供的一款热备工具，除开源、免费外，XtraBackup提供了以下优点：

备份集高效、完整、可用；

备份任务过程中不会阻塞事务；

节省磁盘空间，使用更好的压缩降低网络带宽；

备份集自动验证机制；

恢复更快；

XtraBackup安装

1、安装依赖包；

[root@strong ~]# yum install cmake gcc gcc-c++ libaio libaio-devel automake autoconf bison libtool ncurses-devel libgcrypt-devel libev-devel libcurl-devel vim-common;

2、下载XtraBackup压缩包；

[root@strong ~]# wget -P /u01/ https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.9/binary/tarball/percona-xtrabackup-2.4.9-Linux-x86_64.tar.gz

3、解压缩；

[root@db1 local]# tar zxvf /u01/percona-xtrabackup-2.4.9-Linux-x86_64.tar.gz

[root@db1 local]# chown -R mysql:mysql percona-xtrabackup-2.4.9-Linux-x86_64

[root@db1 local]# ll percona-xtrabackup-2.4.9-Linux-x86_64/bin/

total 212008

lrwxrwxrwx. 1 mysql mysql 10 Nov 23 17:20 innobackupex -> xtrabackup

-rwxr-xr-x. 1 mysql mysql 5357661 Nov 23 17:06 xbcloud

-rwxr-xr-x. 1 mysql mysql 3020 Nov 23 17:06 xbcloud_osenv

-rwxr-xr-x. 1 mysql mysql 5270021 Nov 23 17:06 xbcrypt

-rwxr-xr-x. 1 mysql mysql 5344286 Nov 23 17:06 xbstream

-rwxr-xr-x. 1 mysql mysql 201111701 Nov 23 17:18 xtrabackup

[root@db1 local]#

--将XtraBackup程序目录加入PATH路径

[root@db1 ~]# echo "export PATH=/usr/local/percona-xtrabackup-2.4.9-Linux-x86_64/bin:\$PATH" >> .bash_profile

[root@db1 ~]# source .bash_profile

4、创建XtraBackup用户；

mysql> create user 'xtrabk'@'localhost' identified by 'xtrabk';

Query OK, 0 rows affected (0.28 sec)

mysql> grant reload,lock tables,process,replication client on *.* to 'xtrabk'@'localhost';

Query OK, 0 rows affected (0.01 sec)

mysql>

XtraBackup备份

完全备份

1、执行备份；

[root@db1 ~]# xtrabackup --backup -u xtrabk -pxtrabk --socket=/tmp/mysql.sock --target-dir=/u01/hotback/

180316 10:56:14 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/tmp/mysql.sock' as 'xtrabk' (using password: YES).

180316 10:56:14 version_check Connected to MySQL server

180316 10:56:14 version_check Executing a version check against the server...

180316 10:56:14 version_check Done.

180316 10:56:14 Connecting to MySQL server host: localhost, user: xtrabk, password: set, port: not set, socket: /tmp/mysql.sock

Using server version 5.7.21-log

--xtrabackup版本信息

xtrabackup version 2.4.9 based on MySQL server 5.7.13 Linux (x86_64) (revision id: a467167cdd4)

xtrabackup: uses posix_fadvise().

--首先找到数据文件路径，收集Innodb引擎信息

xtrabackup: cd to /usr/local/mysql/data

xtrabackup: open files limit requested 0, set to 1024

xtrabackup: using the following InnoDB configuration:

xtrabackup: innodb_data_home_dir = .

xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend

xtrabackup: innodb_log_group_home_dir = ./

xtrabackup: innodb_log_files_in_group = 2

xtrabackup: innodb_log_file_size = 50331648

InnoDB: Number of pools: 1

180316 10:56:14 >> log scanned up to (5550616)

xtrabackup: Generating a list of tablespaces

InnoDB: Allocated tablespace ID 21 for sys/sys_config, old maximum was 0

180316 10:56:15 >> log scanned up to (5550616)

--开始复制文件

180316 10:56:16 [01] Copying ./ibdata1 to /u01/hotback/ibdata1

180316 10:56:16 >> log scanned up to (5550616)

180316 10:56:17 >> log scanned up to (5550616)

180316 10:56:18 [01] ...done

180316 10:56:18 [01] Copying ./sys/sys_config.ibd to /u01/hotback/sys/sys_config.ibd

180316 10:56:18 [01] ...done

180316 10:56:18 [01] Copying ./test/t_mm.ibd to /u01/hotback/test/t_mm.ibd

180316 10:56:18 [01] ...done

180316 10:56:18 >> log scanned up to (5550616)

180316 10:56:19 [01] Copying ./test/t_emp.ibd to /u01/hotback/test/t_emp.ibd

180316 10:56:19 [01] ...done

180316 10:56:19 [01] Copying ./mysql/slave_worker_info.ibd to /u01/hotback/mysql/slave_worker_info.ibd

180316 10:56:19 [01] ...done

180316 10:56:19 [01] Copying ./mysql/time_zone_transition.ibd to /u01/hotback/mysql/time_zone_transition.ibd

.............................中间过程省略..........................

180316 10:56:19 >> log scanned up to (5550616)

180316 10:56:20 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...

180316 10:56:20 >> log scanned up to (5550616)

180316 10:56:21 Executing FLUSH TABLES WITH READ LOCK...

180316 10:56:21 Starting to backup non-InnoDB tables and files

180316 10:56:21 [01] Copying ./performance_schema/events_stages_summary_by_user_by_event_name.frm to /u01/hotback/performance_schema/events_stages_summary_by_user_by_event_name.frm

180316 10:56:21 [01] ...done

180316 10:56:21 [01] Copying ./performance_schema/threads.frm to /u01/hotback/performance_schema/threads.frm

180316 10:56:21 [01] ...done

180316 10:56:21 [01] Copying ./performance_schema/metadata_locks.frm to /u01/hotback/performance_schema/metadata_locks.frm

180316 10:56:21 [01] ...done

.............................中间过程省略..........................

180316 10:56:24 Finished backing up non-InnoDB tables and files

180316 10:56:24 [00] Writing /u01/hotback/xtrabackup_binlog_info

180316 10:56:24 [00] ...done

180316 10:56:24 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...

xtrabackup: The latest check point (for incremental): '5550607'

xtrabackup: Stopping log copying thread.

.180316 10:56:24 >> log scanned up to (5550616)

180316 10:56:24 Executing UNLOCK TABLES

180316 10:56:24 All tables unlocked

180316 10:56:24 [00] Copying ib_buffer_pool to /u01/hotback/ib_buffer_pool

180316 10:56:25 [00] ...done

180316 10:56:25 Backup created in directory '/u01/hotback/'

MySQL binlog position: filename 'mysql-bin.000007', position '12110'

180316 10:56:25 [00] Writing /u01/hotback/backup-my.cnf

180316 10:56:25 [00] ...done

180316 10:56:25 [00] Writing /u01/hotback/xtrabackup_info

180316 10:56:25 [00] ...done

--备份过程结束

xtrabackup: Transaction log of lsn (5550607) to (5550616) was copied.

180316 10:56:25 completed OK!

2、查看备份文件；

[root@db1 ~]# ls -lh /u01/hotback/

total 13M

-rw-r-----. 1 root root 426 Mar 16 10:56 backup-my.cnf

-rw-r-----. 1 root root 1.1K Mar 16 10:56 ib_buffer_pool

-rw-r-----. 1 root root 12M Mar 16 10:56 ibdata1

drwxr-x---. 2 root root 4.0K Mar 16 10:56 mysql

drwxr-x---. 2 root root 4.0K Mar 16 10:56 performance_schema

drwxr-x---. 2 root root 12K Mar 16 10:56 sys

drwxr-x---. 2 root root 4.0K Mar 16 10:56 test

-rw-r-----. 1 root root 23 Mar 16 10:56 xtrabackup_binlog_info

-rw-r-----. 1 root root 113 Mar 16 10:56 xtrabackup_checkpoints

-rw-r-----. 1 root root 508 Mar 16 10:56 xtrabackup_info

-rw-r-----. 1 root root 2.5K Mar 16 10:56 xtrabackup_logfile

[root@db1 ~]#

3、准备备份集；

备份后的数据并不处于一致性状态，为了可以用来恢复，需要使用--prepare准备备份集。

[root@db1 ~]# xtrabackup --prepare --target-dir=/u01/hotback/

xtrabackup version 2.4.9 based on MySQL server 5.7.13 Linux (x86_64) (revision id: a467167cdd4)

xtrabackup: cd to /u01/hotback/

xtrabackup: This target seems to be not prepared yet.

InnoDB: Number of pools: 1

xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(5550607)

xtrabackup: using the following InnoDB configuration for recovery:

.............................中间过程省略..........................

InnoDB: Shutdown completed; log sequence number 5551144

180316 11:49:33 completed OK!

[root@db1 ~]#

注：如果想让该全备作为增量备份的基础，需要使用--apply-log-only选项准备备份集。

增量备份

增量备份是基于完全备份，故进行增量备份前，须进行一次全备。增量备份之所以可行是因为InnoDB页包含了日志序列号(也称为LSN)，LSN是整个数据库的系统版本号，每一页的LSN记录着最近它的变化。

1、创建全备份；

[root@db1 ~]# xtrabackup --backup -uxtrabk -pxtrabk --socket=/tmp/mysql.sock --target-dir=/u01/hotback/base

--XtraBackup将备份结束时的LSN写到xtrabackup_checkpoints文件中

[root@db1 ~]# more /u01/hotback/base/xtrabackup_checkpoints

backup_type = full-backuped

from_lsn = 0

to_lsn = 5551689

last_lsn = 5551698

compact = 0

recover_binlog_info = 0

[root@db1 ~]#

2、插入一些数据；

mysql> insert into t_emp (name,sex) values('Incr1',1),('Test Incr1',0);

Query OK, 2 rows affected (0.03 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql>

3、进行第一次增量备份；

[root@db1 ~]# xtrabackup --backup -uxtrabk -pxtrabk --socket=/tmp/mysql.sock --target-dir=/u01/hotback/incr1 --incremental-basedir=/u01/hotback/base/

[root@db1 ~]# more /u01/hotback/incr1/xtrabackup_checkpoints

backup_type = incremental

from_lsn = 5551689 --该LSN是全备的to_lsn

to_lsn = 5552148

last_lsn = 5552157

compact = 0

recover_binlog_info = 0

[root@db1 ~]#

4、创建一些测试数据；

mysql> create table t_myisam(id int,name varchar(100),cdate datetime default now())engine=myisam;

Query OK, 0 rows affected (0.23 sec)

mysql> insert into t_myisam(id,name) values(1,'Alen'),(2,'MyISAM');

Query OK, 2 rows affected (0.01 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> insert into t_emp(name,sex)values('Test Incr2',0),('Incr2',1);

Query OK, 2 rows affected (0.03 sec)

Records: 2 Duplicates: 0 Warnings: 0

5、进行第二次增量备份；

[root@db1 ~]# xtrabackup --backup -uxtrabk -pxtrabk --socket=/tmp/mysql.sock --target-dir=/u01/hotback/incr2 --incremental-basedir=/u01/hotback/incr1/

[root@db1 ~]# more /u01/hotback/incr

incr1/ incr2/

[root@db1 ~]# more /u01/hotback/incr2/xtrabackup_checkpoints

backup_type = incremental

from_lsn = 5552148 --该LSN是第一次增备的to_lsn

to_lsn = 5552578

last_lsn = 5552587

compact = 0

recover_binlog_info = 0

[root@db1 ~]#

6、准备全备备份集；

[root@db1 ~]# xtrabackup --prepare --apply-log-only --target-dir=/u01/hotback/base/

xtrabackup version 2.4.9 based on MySQL server 5.7.13 Linux (x86_64) (revision id: a467167cdd4)

xtrabackup: cd to /u01/hotback/base/

xtrabackup: This target seems to be not prepared yet.

InnoDB: Number of pools: 1

xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(5551689)

.............................中间过程省略..........................

InnoDB: Shutdown completed; log sequence number 5551707

InnoDB: Number of pools: 1

180316 13:38:27 completed OK!

7、准备第一次增量备份集；

[root@db1 ~]# xtrabackup --prepare --apply-log-only --target-dir=/u01/hotback/base --incremental-dir=/u01/hotback/incr1

xtrabackup version 2.4.9 based on MySQL server 5.7.13 Linux (x86_64) (revision id: a467167cdd4)

incremental backup from 5551689 is enabled.

xtrabackup: cd to /u01/hotback/base/

xtrabackup: This target seems to be already prepared with --apply-log-only.

InnoDB: Number of pools: 1

xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(5552148)

.............................中间过程省略..........................

180316 13:46:38 [00] Copying /u01/hotback/incr1//xtrabackup_info to ./xtrabackup_info

180316 13:46:38 [00] ...done

180316 13:46:38 completed OK!

8、准备第二次增量备份集；

[root@db1 ~]# xtrabackup --prepare --target-dir=/u01/hotback/base --incremental-dir=/u01/hotback/incr2

xtrabackup version 2.4.9 based on MySQL server 5.7.13 Linux (x86_64) (revision id: a467167cdd4)

incremental backup from 5552148 is enabled.

xtrabackup: cd to /u01/hotback/base/

xtrabackup: This target seems to be already prepared with --apply-log-only.

InnoDB: Number of pools: 1

xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(5552578)

xtrabackup: using the following InnoDB configuration for recovery:

.............................中间过程省略..........................

InnoDB: 5.7.13 started; log sequence number 5552661

xtrabackup: starting shutdown with innodb_fast_shutdown = 1

InnoDB: page_cleaner: 1000ms intended loop took 15159ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)

InnoDB: FTS optimize thread exiting.

InnoDB: Starting shutdown...

InnoDB: Shutdown completed; log sequence number 5552680

180316 13:53:40 completed OK!

[root@db1 ~]#

注：xtrabackup --apply-log-only应被用在除最后一个增量的其他所有增量备份准备备份集中，即使用在最后一个，备份仍然处于一致性状态，但是这种情况下服务器会执行回滚。

压缩备份

1、执行压缩备份；

[root@db1 ~]# xtrabackup --backup -uxtrabk -pxtrabk --socket=/tmp/mysql.sock --compress --target-dir=/u01/compress_backup/

.............................中间过程省略..........................

180316 14:55:07 Executing UNLOCK TABLES

180316 14:55:07 All tables unlocked

180316 14:55:07 [00] Compressing ib_buffer_pool to /u01/compress_backup/ib_buffer_pool.qp

180316 14:55:07 [00] ...done

180316 14:55:07 Backup created in directory '/u01/compress_backup/'

MySQL binlog position: filename 'mysql-bin.000009', position '464'

180316 14:55:07 [00] Compressing /u01/compress_backup/backup-my.cnf.qp

180316 14:55:07 [00] ...done

180316 14:55:07 [00] Compressing /u01/compress_backup/xtrabackup_info.qp

180316 14:55:07 [00] ...done

xtrabackup: Transaction log of lsn (5553187) to (5553196) was copied.

180316 14:55:07 completed OK!

注：如果想提高压缩的速度，可以使用--compress-threads选项。

2、查看压缩后的文件；

[root@db1 ~]# ls -lh /u01/compress_backup/

total 552K

-rw-r-----. 1 root root 416 Mar 16 14:55 backup-my.cnf.qp

-rw-r-----. 1 root root 792 Mar 16 14:55 ib_buffer_pool.qp

-rw-r-----. 1 root root 501K Mar 16 14:55 ibdata1.qp

drwxr-x---. 2 root root 4.0K Mar 16 14:55 mysql

drwxr-x---. 2 root root 4.0K Mar 16 14:55 performance_schema

drwxr-x---. 2 root root 12K Mar 16 14:55 sys

drwxr-x---. 2 root root 4.0K Mar 16 14:55 test

-rw-r-----. 1 root root 108 Mar 16 14:55 xtrabackup_binlog_info.qp

-rw-r-----. 1 root root 113 Mar 16 14:55 xtrabackup_checkpoints

-rw-r-----. 1 root root 501 Mar 16 14:55 xtrabackup_info.qp

-rw-r-----. 1 root root 294 Mar 16 14:55 xtrabackup_logfile.qp

3、准备备份集；

--解压备份集

[root@db1 ~]# xtrabackup --decompress --target-dir=/u01/compress_backup/

xtrabackup version 2.4.9 based on MySQL server 5.7.13 Linux (x86_64) (revision id: a467167cdd4)

180316 15:27:52 [01] decompressing ./xtrabackup_info.qp

180316 15:27:53 [01] decompressing ./backup-my.cnf.qp

180316 15:27:53 [01] decompressing ./ib_buffer_pool.qp

180316 15:27:53 [01] decompressing ./performance_schema/events_transactions_summary_by_user_by_event_name.frm.qp

.............................中间过程省略..........................

180316 15:28:13 completed OK!

--准备备份集

[root@db1 ~]# xtrabackup --prepare --target-dir=/u01/compress_backup/

xtrabackup version 2.4.9 based on MySQL server 5.7.13 Linux (x86_64) (revision id: a467167cdd4)

xtrabackup: cd to /u01/compress_backup/

xtrabackup: This target seems to be not prepared yet.

InnoDB: Number of pools: 1

xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(5553187)

.............................中间过程省略..........................

InnoDB: 5.7.13 started; log sequence number 5553685

xtrabackup: starting shutdown with innodb_fast_shutdown = 1

InnoDB: page_cleaner: 1000ms intended loop took 5152ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)

InnoDB: FTS optimize thread exiting.

InnoDB: Starting shutdown...

InnoDB: Shutdown completed; log sequence number 5553704

180316 15:34:26 completed OK!

注：使用 --decompress压缩的备份集在准备备份之前需要解压，解压工具是qpress。解压后的原文件不会被删除，可以使用--remove-original选项清除。

XtraBackup恢复

全备恢复

1、全备进行恢复前需要使用--prepare选项准备备份集；

[root@db1 ~]# xtrabackup --prepare --target-dir=/u01/hotback/

xtrabackup version 2.4.9 based on MySQL server 5.7.13 Linux (x86_64) (revision id: a467167cdd4)

xtrabackup: cd to /u01/hotback/

--如果已经准备过备份集，会提示已经prepared

xtrabackup: This target seems to be already prepared.

InnoDB: Number of pools: 1

xtrabackup: notice: xtrabackup_logfile was already used to '--prepare'.

2、执行全备恢复，数据库需要处于关机状态；

[root@db1 ~]# mysqladmin -uroot -p shutdown;

Enter password:

[root@db1 ~]#

3、还原备份之前，datadir目录必须是空的；

[root@db1 ~]# mv /usr/local/mysql/data /usr/local/mysql/data_bak

[root@db1 ~]# mkdir /usr/local/mysql/data

4、执行还原全备；

[root@db1 ~]# xtrabackup --copy-back --target-dir=/u01/hotback/

xtrabackup version 2.4.9 based on MySQL server 5.7.13 Linux (x86_64) (revision id: a467167cdd4)

180316 12:10:19 [01] Copying ib_logfile0 to /usr/local/mysql/data/ib_logfile0

180316 12:10:22 [01] ...done

180316 12:10:22 [01] Copying ib_logfile1 to /usr/local/mysql/data/ib_logfile1

.............................中间过程省略..........................

180316 12:10:27 [01] ...done

180316 12:10:27 completed OK!

注：也可以使用下面方式实现

[root@db1 ~]# rsync -avrP /u01/hotback/ /usr/local/mysql/data/

5、修改data目录权限并启动MySQL服务；

[root@db1 ~]# chown -R mysql:mysql /usr/local/mysql/data

[root@db1 ~]# service mysql.server start

Starting MySQL.Logging to '/usr/local/mysql/data/db1.err'.

.... SUCCESS!

[root@db1 ~]#

6、查看数据的恢复状态；

[root@db1 ~]# mysql -uroot -p test -e 'select *from t_emp';

Enter password:

+----+--------+---------------------+------+

| id | name | cdate | sex |

+----+--------+---------------------+------+

| 1 | 张三 | 2018-03-13 18:53:44 | 1 |

| 3 | 李四 | 2018-03-13 18:53:44 | 1 |

| 5 | Alen | 2018-03-13 17:37:34 | 1 |

| 7 | Lucy | 2018-03-13 17:39:45 | 0 |

| 9 | Hanmei | 2018-03-14 17:19:07 | 0 |

| 11 | 李磊 | 2018-03-14 17:19:07 | 1 |

| 13 | James | 2018-03-14 17:19:07 | 1 |

| 15 | 王五 | 2018-03-14 22:13:26 | 1 |

| 17 | Kim | 2018-03-14 22:13:26 | 1 |

| 19 | Eile | 2018-03-14 22:13:26 | 0 |

+----+--------+---------------------+------+

[root@db1 ~]#

7、通过分析Binlog，使数据库恢复到当前状态；

[root@db1 ~]# mysqlbinlog /usr/local/mysql/binlog/mysql-bin.000007 --start-position=12260 | mysql -uroot -p test

Enter password:

[root@db1 ~]# mysql -uroot -p test -e 'select *from t_emp';

Enter password:

+----+-------------+---------------------+------+

| id | name | cdate | sex |

+----+-------------+---------------------+------+

| 1 | 张三 | 2018-03-13 18:53:44 | 1 |

| 3 | 李四 | 2018-03-13 18:53:44 | 1 |

| 5 | Alen | 2018-03-13 17:37:34 | 1 |

| 7 | Lucy | 2018-03-13 17:39:45 | 0 |

| 9 | Hanmei | 2018-03-14 17:19:07 | 0 |

| 11 | 李磊 | 2018-03-14 17:19:07 | 1 |

| 13 | James | 2018-03-14 17:19:07 | 1 |

| 15 | 王五 | 2018-03-14 22:13:26 | 1 |

| 17 | Kim | 2018-03-14 22:13:26 | 1 |

| 19 | Eile | 2018-03-14 22:13:26 | 0 |

| 21 | Full Backup | 2018-03-16 12:04:07 | 1 |

| 23 | Full Test | 2018-03-16 12:04:07 | 0 |

+----+-------------+---------------------+------+

[root@db1 ~]#

至此，全库恢复完成。

增量恢复

1、增量备份后插入几条记录作为当前环境；

mysql> insert into t_emp(name,sex)values('Test Recover',1),('Rec Incr',0);

Query OK, 2 rows affected (0.02 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> select count(1) from t_emp;

+----------+

| count(1) |

+----------+

| 18 |

+----------+

1 row in set (0.04 sec)

mysql>

2、执行完增量备份准备备份集后进行恢复；

[root@db1 ~]# xtrabackup --copy-back --target-dir=/u01/hotback/base

xtrabackup version 2.4.9 based on MySQL server 5.7.13 Linux (x86_64) (revision id: a467167cdd4)

180316 14:25:52 [01] Copying ib_logfile0 to /usr/local/mysql/data/ib_logfile0

180316 14:25:52 [01] ...done

.............................中间过程省略..........................

180316 14:25:55 [01] Copying ./ib_buffer_pool to /usr/local/mysql/data/ib_buffer_pool

180316 14:25:55 [01] ...done

180316 14:25:55 completed OK!

3、通过分析Binlog，使数据库恢复到当前状态；

[root@db1 ~]# mysqlbinlog /usr/local/mysql/binlog/mysql-bin.000008 --start-position=1846 | mysql -uroot -p

Enter password:

[root@db1 ~]# mysql -uroot -p test -e 'select count(1) from t_emp';

Enter password:

+----------+

| count(1) |

+----------+

| 18 |

+----------+

[root@db1 ~]#

至此，增量恢复完成。

压缩恢复

和全备恢复过程一样，在此不做演示。