xtrabackup全备+增备MySQL数据库实现方法

上一篇博客MySQL回滚到某一时刻数据的方法,说到了mysql没有Oracle,DB2这种企业级数据库常见的增量备份功能,只有类似于binlog的归档日志。可以实现先恢复全备,再重做归档日志进行回滚到之前任意时间点数据的功能。由于binlog是以事务为单位,记录了每条数据的时间信息,而且支持最好的ROW格式又格外占空间,会导致一些数据量较大的数据库的binlog也特别庞大,占用特别巨大的存储空间。一般最多也就保留几天。而企业级数据库往往要求能恢复到过去半年里每天某个时间的数据。这往往就用到了Oracle,DB2的全备和增备功能。由于这两个企业级数据库的增备相当于一个快照,里面不像归档日志那样包含时间信息,所以占用空间小,特别适合长时间保存。虽然Mysql官方没有提供增备的方法,但是一些第三方开源工具比如xtrabackup提供了这样类似快照的方法。而且是基于innodb里有关事务的特性,能做到强一致性,非常推荐。但是xtrabackup相对于企业级数据库还是有一点缺点,就是无法根据全备增备和归档日志回滚到之前某一时刻的数据。而在Oracle和DB2中,都可以先恢复全备,再逐个恢复增备,再重做归档日志到某一具体时间。而xtrabackup的备份是无法和binlog相结合的。适合于进行主从初始化复制和保留历史数据的功能,不太适合回滚。

下面简单列一下xtrabackup的优点和不足

优点

1、备份非常快速和可靠

2、备份期间不会影响数据库读写。这个是因为xtrabackup结合innodb引擎的事务特性,不会像mysqldump那样在备份期间进行锁表,所以xtrabackup在备份时对数据库性能影响非常小,在大数据库备份时优点非常可观。

3、可以备份到本地磁盘和网络上

不足

1、不能结合binlog进行事务的重做,无法回滚到任意时刻

2、恢复的操作基本靠手动逐个恢复,不像Oracle一样一行命令完成,但是同时提高了异地恢复的便捷性。


下面以实例介绍如何使用xtrabackup进行全备和增备。由于xtrabackup时percona的产品,所以这里使用percona5.7.21数据库为例,安装方法可以看我之前写的一片博客 基于Docker的MYSQL PXC集群搭建,由于进行full安装,所以默认xtrabackup和percona pxc集群数据库都会被安装上,减少了安装过程的繁琐。

大体过程如下

1、建库建表插入数据

2、进行一次全备,并异地还原一次

3、再插入数据,进行一次增备

4、继续插入数据,进行第二次增备

5、分别异地还原一次增备和二次增备的内容

首先我们启动percona数据库,建库建表,插入两条数据

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> create database dbtest;
Query OK, 1 row affected (0.12 sec)

mysql> use dbtest;
Database changed
mysql> create table table1 ( id int primary key, name varchar(40), birthday datetime);
Query OK, 0 rows affected (0.96 sec)

mysql> insert into table1 values (1,'befor_full1',NOW());
Query OK, 1 row affected (0.06 sec)

mysql> insert into table1 values (2,'befor_full2',NOW());
Query OK, 1 row affected (0.09 sec)

mysql> select * from table1;
+----+-------------+---------------------+
| id | name        | birthday            |
+----+-------------+---------------------+
|  1 | befor_full1 | 2018-06-10 01:31:54 |
|  2 | befor_full2 | 2018-06-10 01:32:00 |
+----+-------------+---------------------+
2 rows in set (0.00 sec)

下面开始全备,在这里由于用到了innodb的特性,所以xtrabackup需要链接数据库,所以就需要保证数据库的正常运行,另外还需要给xtrabackup指定一个具有读写权限的用户,这个用户给root是完全可以的,但是在生产中我们很少会提供给别人root用户的密码,所以我们可以新建一个用户。

mysql> CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY '123456';
mysql> GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'bkpuser'@'localhost';
mysql> FLUSH PRIVILEGES;

然后我们退出sql界面,准备开始进行全备,这个操作可以使用系统用户root,也可以用其他的系统用户

$ xtrabackup --user=bkpuser --password=123456 --backup --target-dir=/home/full/

在全备中必须指定的参数是--user,也就是我们刚刚创建的bkpuser或者root,--password是该用户的密码,--target-dir就是我们本次备份生成的文件放置的目录,如果你不写--target-dir而是直接写一个目录,那么xtrabackup会自动在你写的目录下面以时间日期格式建立一个新目录,里面放备份的文件,如果你写了--target-dir那么xtrabackup就不会新建子目录,而是直接写道你提供的目录下。

另外有一些隐含参数,比如mysql的IP的端口还有socket位置什么的,这些你都可以通过你本地的mysql配置文件来解决默认,加入--defaults-file=/etc/my.cnf这样的参数即可。

另外你可以在全备的时候选择--compress压缩或者不压缩,这里我们先不压缩,后续使用xz工具进行统一压缩

xtrabackup --user=bkpuser --password=123456 --backup --target-dir=/home/full/
encryption: using gcrypt 1.6.5
180610 02:03:28  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/var/run/mysqld/mysqld.sock' as 'bkpuser'  (using password: YES).
180610 02:03:28  version_check Connected to MySQL server
180610 02:03:28  version_check Executing a version check against the server...
180610 02:03:28  version_check Done.
......
xtrabackup: Transaction log of lsn (2591133) to (2591158) was copied.
180610 02:03:47 completed OK!

备份完毕之后我们看一下有哪些文件

root@9f6505094522:/# ls -l /home/full/
total 12332
-rw-r----- 1 root root      487 Jun 10 02:03 backup-my.cnf
drwxr-x--- 2 root root     4096 Jun 10 02:03 dbtest
-rw-r----- 1 root root      352 Jun 10 02:03 ib_buffer_pool
-rw-r----- 1 root root 12582912 Jun 10 02:03 ibdata1
drwxr-x--- 2 root root     4096 Jun 10 02:03 mysql
drwxr-x--- 2 root root     4096 Jun 10 02:03 performance_schema
drwxr-x--- 2 root root    12288 Jun 10 02:03 sys
-rw-r----- 1 root root      113 Jun 10 02:03 xtrabackup_checkpoints
-rw-r----- 1 root root      455 Jun 10 02:03 xtrabackup_info
-rw-r----- 1 root root     2560 Jun 10 02:03 xtrabackup_logfile

然后我们打包压缩一下,传到其他服务器上进行异地恢复

cd /home
tar -Jcvf full.tar.xz full

压缩之前为26M,压缩后为279K,xz的压缩率还是比较好,当然你也可以在备份的时候就选择让xtrabackup给你压缩成qd格式,可以选择多线程压缩。

我们把full.tar.xz拷贝到新服务器的/home下面,然后解压出来。

解压出来之后我们就可以开始我们的恢复操作了。在恢复之前,首先我们要prepare一下,那么什么是prepare,为什么要prepare操作呢。简单来说,你如果不prepare一下的话,那么你恢复完之后是无法开启数据库的。因为你在备份的时候由于innodb事务的控制,有可能在备份的时候有新的事务进来,而xtrabackup是不会影响事务读写的,所以备份出来的东西有很多未完成的事务在里面,需要通过prepare将未完成的事务回滚或者其他操作。当你有增备的时候,你还需要通过prepare来把增备的数据添加到全备上再进行恢复,所以xtrabakcup手册上很明确的指出,在恢复之前必须进行prepare操作,而且这个prepare操作对于全备和增备是不同的。

好在进行prepare操作并不需要mysql服务的参与,只要在安装有xtrabackup的机器上即可进行。

xtrabackup --prepare --target-dir=/home/full

在只恢复全备的时候,只提供一个参数就行了,--target-dir,注意这个目录是写刚刚解压出压缩包的路径,而不是你mysql表空间的路径,这个操作可以使用系统用户root,也可以用其他的系统用户

# xtrabackup --prepare --target-dir=/home/full
encryption: using gcrypt 1.6.5
xtrabackup version 2.4.11 based on MySQL server 5.7.19 Linux (x86_64) (revision id: b4e0db5)
xtrabackup: cd to /home/full/
......
180610 02:17:51 completed OK!

prepare结束之后,我们就可以恢复数据库了。xtrabackup恢复数据库方法很特别,我们知道mysqldump出来的文件是一系列的sql语句,需要通过执行来达到恢复的效果。而xtrabackup非常简单粗暴,直接通过把文件拷贝到你的mysql表空间目录里完成恢复,这样做的好处就是比较快,我们知道复制文件的速度肯定比耗用CPU去解析sql然后疯狂插入要好得多,但代价是要把你的mysql停止掉,然后删掉原来表空间目录下的所有文件。

首先,停止mysql

/etc/init.d/mysql stop
注意,备份的时候是要开着mysql服务,这样才能链接innodb,但是prepare和恢复的时候,都推荐关掉mysql服务。

$ xtrabackup --copy-back --target-dir=/home/full/

这里一般只需要提供--target-dir即可,这里的目录和上面prepare一样,是填写你备份解压出来的路径,而不是表空间的路径。这里有默认参数,其中这里默认把你的表空间路径指定为/var/lib/mysql,如果你的表空间没有放在这里,那你需要指定你mysql配置文件my.cnf的位置。--defaults-file=/etc/mysql/my.cnf

然后如果我们的/var/lib/mysql目录不为空的话,就会报下面的错误

xtrabackup --copy-back --target-dir=/home/full/
encryption: using gcrypt 1.6.5
xtrabackup version 2.4.11 based on MySQL server 5.7.19 Linux (x86_64) (revision id: b4e0db5)
Original data directory /var/lib/mysql is not empty!

所以我们要删除现有的表空间,由于我们之前是全库备份,所以不要害怕,别忘了关闭mysql服务就好

rm -rf /var/lib/mysql/*

然后再执行恢复操作,xtrabackup就会慢慢的把文件拷贝到这个目录下面

# xtrabackup --copy-back --target-dir=/home/full/
encryption: using gcrypt 1.6.5
xtrabackup version 2.4.11 based on MySQL server 5.7.19 Linux (x86_64) (revision id: b4e0db5)
180610 02:27:35 [01] Copying ib_logfile0 to /var/lib/mysql/ib_logfile0
180610 02:27:35 [01]        ...done
180610 02:27:35 [01] Copying ib_logfile1 to /var/lib/mysql/ib_logfile1
180610 02:27:35 [01]        ...done
180610 02:27:35 [01] Copying ibdata1 to /var/lib/mysql/ibdata1
180610 02:27:35 [01]        ...done
......
180610 02:27:54 completed OK!

然后查看一下恢复完毕之后是什么文件

# ls -l /var/lib/mysql
total 122924
drwxr-x--- 2 root root     4096 Jun 10 02:27 dbtest
-rw-r----- 1 root root      352 Jun 10 02:27 ib_buffer_pool
-rw-r----- 1 root root 50331648 Jun 10 02:27 ib_logfile0
-rw-r----- 1 root root 50331648 Jun 10 02:27 ib_logfile1
-rw-r----- 1 root root 12582912 Jun 10 02:27 ibdata1
-rw-r----- 1 root root 12582912 Jun 10 02:27 ibtmp1
drwxr-x--- 2 root root     4096 Jun 10 02:27 mysql
drwxr-x--- 2 root root     4096 Jun 10 02:27 performance_schema
drwxr-x--- 2 root root    12288 Jun 10 02:27 sys
-rw-r----- 1 root root       29 Jun 10 02:27 xtrabackup_binlog_pos_innodb
-rw-r----- 1 root root       39 Jun 10 02:27 xtrabackup_galera_info
-rw-r----- 1 root root      455 Jun 10 02:27 xtrabackup_info
-rw-r----- 1 root root        1 Jun 10 02:27 xtrabackup_master_key_id

在看一下源数据库里的文件

# ls -l /var/lib/mysql
total 257932
-rw-r----- 1 mysql mysql      1941 Jun 10 01:53 9f6505094522-bin.000001
-rw-r----- 1 mysql mysql        26 Jun 10 01:29 9f6505094522-bin.index
-rw-r----- 1 mysql mysql        56 May 29 08:46 auto.cnf
-rw-r----- 1 mysql mysql       854 May 29 08:47 bf9cc24797ed-bin.000001
-rw-r----- 1 mysql mysql       823 May 29 09:18 bf9cc24797ed-bin.000002
-rw-r----- 1 mysql mysql      2602 May 29 09:44 bf9cc24797ed-bin.000003
-rw-r----- 1 mysql mysql        78 May 29 09:19 bf9cc24797ed-bin.index
-rw------- 1 mysql mysql      1676 May 29 08:46 ca-key.pem
-rw-r--r-- 1 mysql mysql      1120 May 29 08:46 ca.pem
-rw-r--r-- 1 mysql mysql      1120 May 29 08:46 client-cert.pem
-rw------- 1 mysql mysql      1680 May 29 08:46 client-key.pem
drwxr-x--- 2 mysql mysql      4096 Jun 10 01:31 dbtest
-rw-r----- 1 mysql mysql 134219048 Jun 10 01:53 galera.cache
-rw-r----- 1 mysql mysql       113 Jun 10 01:53 grastate.dat
-rw-r----- 1 mysql mysql       170 Jun 10 01:29 gvwstate.dat
-rw-r----- 1 mysql mysql       352 May 29 09:44 ib_buffer_pool
-rw-r----- 1 mysql mysql  50331648 Jun 10 02:03 ib_logfile0
-rw-r----- 1 mysql mysql  50331648 May 29 08:46 ib_logfile1
-rw-r----- 1 mysql mysql  12582912 Jun 10 02:03 ibdata1
-rw-r----- 1 mysql mysql  12582912 Jun 10 02:03 ibtmp1
-rw-r----- 1 mysql mysql     34395 May 29 09:20 innobackup.backup.log
drwxr-x--- 1 mysql mysql      4096 May 29 08:46 mysql
-rw-rw---- 1 root  root          3 Jun 10 01:29 mysqld_safe.pid
drwxr-x--- 1 mysql mysql      4096 May 29 08:46 performance_schema
-rw------- 1 mysql mysql      1680 May 29 08:46 private_key.pem
-rw-r--r-- 1 mysql mysql       452 May 29 08:46 public_key.pem
-rw-r--r-- 1 mysql mysql      1120 May 29 08:46 server-cert.pem
-rw------- 1 mysql mysql      1680 May 29 08:46 server-key.pem
drwxr-x--- 1 mysql mysql     12288 May 29 08:47 sys
-rw-r----- 1 mysql mysql   3932160 Jun 10 02:03 xb_doublewrite

我们会发现恢复出来的文件要比原来的少,但是这并不影响正常使用,很多文件在mysql启动的时候都会新建。现在我们需要把/var/lib/mysql下面的文件的owner都从root换成mysql

$ chown -R mysql:mysql /var/lib/mysql

然后就可以启动mysql服务器了,我这里是开启pxc集群

/etc/init.d/mysql bootstrap-pxc

然后查看一下数据是否恢复成功,由于是全库备份,所以我们root用户密码还是和源库是一样的。

mysql> select * from dbtest.table1;
+----+-------------+---------------------+
| id | name        | birthday            |
+----+-------------+---------------------+
|  1 | befor_full1 | 2018-06-10 01:31:54 |
|  2 | befor_full2 | 2018-06-10 01:32:00 |
+----+-------------+---------------------+
2 rows in set (0.02 sec)

可以看到,全库数据恢复成功。

下面我们进行第二项,进行两次增备,然后学习如何恢复增备的数据库。

首先我们在源库第一次增备前插入两条数据,并新建一个用户并授权,查看对于用户表的操作能否被增备

mysql> use dbtest;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> insert into table1 values (4,'befor_inc1_2',NOW());
Query OK, 1 row affected (0.06 sec)

mysql> insert into table1 values (3,'befor_inc1_1',NOW());
Query OK, 1 row affected (0.14 sec)

mysql> select * from table1;
+----+--------------+---------------------+
| id | name         | birthday            |
+----+--------------+---------------------+
|  1 | befor_full1  | 2018-06-10 01:31:54 |
|  2 | befor_full2  | 2018-06-10 01:32:00 |
|  3 | befor_inc1_1 | 2018-06-10 02:38:13 |
|  4 | befor_inc1_2 | 2018-06-10 02:37:55 |
+----+--------------+---------------------+
4 rows in set (0.00 sec)

再创建一个用户

mysql> CREATE USER 'alex'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.08 sec)

mysql> select user,host from mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| alex          | %         |
| root          | %         |
| bkpuser       | localhost |
| mysql.session | localhost |
| mysql.sys     | localhost |
| sstuser       | localhost |
+---------------+-----------+
6 rows in set (0.00 sec)

mysql> grant create,select,update,insert,delete on dbtest.* to 'alex'@'%';
Query OK, 0 rows affected (0.08 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.08 sec)

使用该用户登陆,是可以查看该表的

# mysql -ualex -p123456

mysql> select * from table1;
ERROR 1046 (3D000): No database selected
mysql> select * from dbtest.table1;
+----+--------------+---------------------+
| id | name         | birthday            |
+----+--------------+---------------------+
|  1 | befor_full1  | 2018-06-10 01:31:54 |
|  2 | befor_full2  | 2018-06-10 01:32:00 |
|  3 | befor_inc1_1 | 2018-06-10 02:38:13 |
|  4 | befor_inc1_2 | 2018-06-10 02:37:55 |
+----+--------------+---------------------+
4 rows in set (0.00 sec)

然后我们开始第一次增备,在这之前,我们先看一下之前全备的LSN号,LSN号是什么东西这里就不赘述了,只要知道只要全备和多个增备的LSN号连续,那么就可以逐个进行恢复。可以再备份目录xtrabackup_checkpoints文件中看到,其中全备的from_lsn=0,增备的from_lsn应该等于上一个增备或者全备的to_lsn

# cat /home/full/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 2591149
last_lsn = 2591158
compact = 0
recover_binlog_info = 1
$ xtrabackup --backup --user=bkpuser --password=123456 --target-dir=/home/inc1 --incremental-basedir=/home/full/

这里面--target-dir是你要把本次增备的文件放到那个文件夹下,--incremental-basedir是上次全备或者增备出来的文件夹。当第一次增备的时候,一般填上次全备,第二次增备的时候,如果--incremental-basedir填上次全备,那么本次增备就会包含上次全备到现在变化的内容,相当于oracle依次做1级,2级,3级。。。增备如果--incremental-basedir填第一次增备的目录,那么该次增备只包含第一次增备到现在的变化,文件会更小,相当于oracle rman里面每次都做1级增备;。

发现会因为权限不足而报错

# xtrabackup --backup --user=bkpuser --password=123456 --target-dir=/home/inc1 --incremental-basedir=/home/full/
encryption: using gcrypt 1.6.5
180610 03:30:44  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/var/run/mysqld/mysqld.sock' as 'bkpuser'  (using password: YES).
180610 03:30:44  version_check Connected to MySQL server
180610 03:30:44  version_check Executing a version check against the server...
180610 03:30:44  version_check Done.
180610 03:30:44 Connecting to MySQL server host: localhost, user: bkpuser, password: set, port: 3306, socket: /var/run/mysqld/mysqld.sock
Using server version 5.7.21-20-57-log
xtrabackup version 2.4.11 based on MySQL server 5.7.19 Linux (x86_64) (revision id: b4e0db5)
incremental backup from 2591149 is enabled.
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: open files limit requested 0, set to 1048576
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
180610 03:30:44 >> log scanned up to (2595766)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 21 for sys/sys_config, old maximum was 0
Error: failed to execute query FLUSH NO_WRITE_TO_BINLOG CHANGED_PAGE_BITMAPS: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

这是因为我们刚才再给bkpuser授权时,没有授予SUPER权限,本身不影响数据的增备,但是我们刚刚建立了新用户alex并且授权了,所以bkpuser的权限就不够了,我们可以换用root来进行这次增备,或者给bkpuser授权super来解决。其中增备的时间也不会特别短,跟全备差不多的时间,虽然只有两行变更。

# xtrabackup --backup --user=root --password=123456 --target-dir=/home/inc1 --incremental-basedir=/home/full/
encryption: using gcrypt 1.6.5
180610 03:36:48  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/var/run/mysqld/mysqld.sock' as 'root'  (using password: YES).
180610 03:36:48  version_check Connected to MySQL server
180610 03:36:48  version_check Executing a version check against the server...
180610 03:36:48  version_check Done.
180610 03:36:48 Connecting to MySQL server host: localhost, user: root, password: set, port: 3306, socket: /var/run/mysqld/mysqld.sock
Using server version 5.7.21-20-57-log
xtrabackup version 2.4.11 based on MySQL server 5.7.19 Linux (x86_64) (revision id: b4e0db5)
incremental backup from 2591149 is enabled.
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: open files limit requested 0, set to 1048576
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
......
xtrabackup: Transaction log of lsn (2595757) to (2595782) was copied.
180610 03:37:09 completed OK

备份完成后,我们看一下增备的lsn码是不是与上次全备连续

# cat /home/inc1/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 2591149
to_lsn = 2595773
last_lsn = 2595782
compact = 0
recover_binlog_info = 1

果然是连续的,而且backup_type上还能告诉我们这个备份是个增备


然后我们进行第四项,插入两条数据后做第二次增备

这次我们使用alex用户进行插入

mysql> insert into table1 values (5,'befor_inc2_1',NOW());
Query OK, 1 row affected (0.08 sec)

mysql> insert into table1 values (6,'befor_inc2_2',NOW());
Query OK, 1 row affected (0.15 sec)

mysql> select * from table1;
+----+--------------+---------------------+
| id | name         | birthday            |
+----+--------------+---------------------+
|  1 | befor_full1  | 2018-06-10 01:31:54 |
|  2 | befor_full2  | 2018-06-10 01:32:00 |
|  3 | befor_inc1_1 | 2018-06-10 02:38:13 |
|  4 | befor_inc1_2 | 2018-06-10 02:37:55 |
|  5 | befor_inc2_1 | 2018-06-10 03:40:37 |
|  6 | befor_inc2_2 | 2018-06-10 03:40:44 |
+----+--------------+---------------------+
6 rows in set (0.00 sec)

然后开始进行第二次增备,这次增备有两个选择,一是基于上一次全备进行增备,那么第一次增备将会失去作用,因为这样就包含了上次全备到第二次增备的全部变化内容。相当于Oracle数据库每次做1级,2级,3级备份。而第二种选择是基于第一次增备,这样文件会小一些,但是如果第一次增备丢失了,第二次增备也就没法恢复了,相当于Oracle每次都做1级备份。

为了演示基于增备的增备,所以我选用第二种方式,基于第一次的变化再变化的内容,为了不使用root,我们把SUPER权限授予bkpuser。

mysql> GRANT SUPER ON *.* TO 'bkpuser'@'localhost';
Query OK, 0 rows affected (0.08 sec)


# xtrabackup --backup --user=bkpuser --password=123456 --target-dir=/home/inc2 --incremental-basedir=/home/inc1
encryption: using gcrypt 1.6.5
180610 03:50:01  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/var/run/mysqld/mysqld.sock' as 'bkpuser'  (using password: YES).
180610 03:50:01  version_check Connected to MySQL server
180610 03:50:01  version_check Executing a version check against the server...
180610 03:50:01  version_check Done.
180610 03:50:01 Connecting to MySQL server host: localhost, user: bkpuser, password: set, port: 3306, socket: /var/run/mysqld/mysqld.sock
Using server version 5.7.21-20-57-log
xtrabackup version 2.4.11 based on MySQL server 5.7.19 Linux (x86_64) (revision id: b4e0db5)
incremental backup from 2595773 is enabled.
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: open files limit requested 0, set to 1048576
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
......
180610 03:50:22 [00] Writing /home/inc2/xtrabackup_info
180610 03:50:22 [00]        ...done
xtrabackup: Transaction log of lsn (2600041) to (2600066) was copied.
180610 03:50:22 completed OK!

顺便来看一下inc2的LSN码是不是和第一次增备连续

# cat /home/inc2/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 2595773
to_lsn = 2600057
last_lsn = 2600066
compact = 0
recover_binlog_info = 1

然后我们还是和之前一样,将/home/inc1,/home/inc2进行打包,和之前的全备打包一起拷贝到另外一台服务器上。注意不是之前做实验的那台服务器,因为我们要重新prepare,之前prepare过的已经不能用了。

# tar -Jcf inc1.tar.xz inc1
# tar -Jcf inc2.tar.xz inc2

我们将三个压缩包全部解压到新服务器上,然后我们先恢复第一次增备

这里要注意,我们依然要做prepare,而且是做两次prepare,第一次是把全备给prepare,不过这次和上次不一样,这次要加入--apply-log-only参数,因为在每个备份过程中,都会碰到一些事务进来执行,而备份结束时可能有些事务并没有执行完毕,所以在默认prepare中这些事务就会被回滚(rollback),而加入了--apply-log-only就不会回滚这些事务,而是等待prepare下次增备。第二次prepare就是准备第一次增备的目录,这个prepare目的就是把第一次增备期间发生的变化写入到全备的目录中去,然后第一次增备的目录就没有用处了,因为我们最终拷贝表空间文件是把增备目录拷贝过去。无论多少个增备,最后prepare的那个增备是不能加--apply-log-only参数的,因为最后一次增备还没有完成的事务就要彻底回滚来保证一致性。

首先prepare全备

$ xtrabackup --prepare --apply-log-only --target-dir=/home/full

然后prepare第一次增备

$ xtrabackup --prepare --target-dir=/home/full --incremental-dir=/home/inc1

这里的--target-dir是上一次全备的目录,因为我们要将第一次增备期间的变化写入到全备目录里,再拷贝到表空间中,所以我们--incremental-dir填第一次增备的目录,--target-dir填全备的目录。由于这次不考虑第二次增备,所以第一次增备就是最后一次增备,所以去掉--apply-log-only,回滚未提交的事务。

然后停止数据库,删除表空间文件,开始复制,注意在复制的时候--target-dir填全备的路径,不要填增备的,因为在prepare完成之后,增备的就可以删掉了

/etc/init.d/mysql stop
rm -rf /var/lib/mysql/*
xtrabackup --copy-back --target-dir=/home/full

然后我们登陆数据库观察,两行数据是否恢复成功,看看user表中的用户是否创建成功,权限是否正常

# chown -R mysql:mysql /var/lib/mysql
# /etc/init.d/mysql bootstrap-pxc
 * Bootstrapping Percona XtraDB Cluster database server mysqld                                [ OK ]

# mysql -ualex -p
Enter password:

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select * from dbtest.table1;
+----+--------------+---------------------+
| id | name         | birthday            |
+----+--------------+---------------------+
|  1 | befor_full1  | 2018-06-10 01:31:54 |
|  2 | befor_full2  | 2018-06-10 01:32:00 |
|  3 | befor_inc1_1 | 2018-06-10 02:38:13 |
|  4 | befor_inc1_2 | 2018-06-10 02:37:55 |
+----+--------------+---------------------+
4 rows in set (0.01 sec)

可以看到,alex用户可以正常使用,两条增量数据也恢复成功。

下面进行第六项,根据全备,第一次增备,第二次增备来恢复数据库。

整个步骤和上面恢复一次增备差不多,唯一的差别就在于--apply-log-only只写在第二次增备的prepare上。

首先我们找一个新服务器,或者把之前tar包解压出来的目录都删掉,因为我们要重新prepare

# /etc/init.d/mysql stop
 * Stopping MySQL (Percona XtraDB Cluster) mysqld                                                                [ OK ]
root@a0f24357ff78:/home# rm -rf /home/full/
root@a0f24357ff78:/home# rm -rf /home/inc1/
root@a0f24357ff78:/home# rm -rf /home/inc2/
root@a0f24357ff78:/home# tar -Jxf /home/full.tar.xz
root@a0f24357ff78:/home# tar -Jxf /home/inc1.tar.xz
root@a0f24357ff78:/home# tar -Jxf /home/inc2.tar.xz
然后步骤类似,也是先prepare,一定记住--apply-log-only只有最后一个增备不写

$ xtrabackup --prepare --apply-log-only --target-dir=/home/full
$ xtrabackup --prepare --apply-log-only --target-dir=/home/full --incremental-dir=/home/inc1
$ xtrabackup --prepare --target-dir=/home/full --incremental-dir=/home/inc2

然后拷贝并重启数据库

#/etc/init.d/mysql stop
# rm -rf /var/lib/mysql/*
# xtrabackup --copy-back --target-dir=/home/full
# chown -R mysql:mysql /var/lib/mysql
# /etc/init.d/mysql bootstrap-pxc
 * Bootstrapping Percona XtraDB Cluster database server mysqld                                [ OK ]

使用alex用户登陆进去看看是不是完整的6条数据

mysql> select * from dbtest.table1;
+----+--------------+---------------------+
| id | name         | birthday            |
+----+--------------+---------------------+
|  1 | befor_full1  | 2018-06-10 01:31:54 |
|  2 | befor_full2  | 2018-06-10 01:32:00 |
|  3 | befor_inc1_1 | 2018-06-10 02:38:13 |
|  4 | befor_inc1_2 | 2018-06-10 02:37:55 |
|  5 | befor_inc2_1 | 2018-06-10 03:40:37 |
|  6 | befor_inc2_2 | 2018-06-10 03:40:44 |
+----+--------------+---------------------+
6 rows in set (0.01 sec)
可以看到新用户alex的权限和table1中的数据都完整恢复了。

阅读更多
版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/lvshaorong/article/details/80638381
上一篇MySQL回滚到某一时刻数据的方法
下一篇WebSphere控制台SSL安全证书错误的解决方法
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭
关闭