路漫漫其修远兮
吾将上下而求索
        找回密码

mysql学习：xtrabackup使用

2017-08-20 分类：mysql 评论(0)

使用Xtrabackup进行MySQL备份：

1、简介

Xtrabackup是由percona提供的mysql数据库备份工具，据官方介绍，这也是世界上惟一一款开源的能够对innodb和xtradb数据库进行热备的工具。特点：

(1)备份过程快速、可靠；
(2)备份过程不会打断正在执行的事务；
(3)能够基于压缩等功能节约磁盘空间和流量；
(4)自动实现备份检验；
(5)还原速度快；
(6)只能对innodb进行热备。MyISAM不支持热备，也不支持增量，增量也是完全备份

2、安装

其最新版的软件可从 http://www.percona.com/software/percona-xtrabackup/ 获得。本文基于centos6.9的系统，因此，直接下载相应版本的rpm包安装即可，这里不再演示其过程。

https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.9/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.9-1.el6.x86_64.rpm

备份的实现

此处使用hellodb.sql文件作为演示

hellodb.zip

环境
centos6.9
192.168.170.10，mysql-5.5.55 为运行服务的机器
192.168.170.12，mysql-5.5.55 为新的机器，供还原数据用
两台机器均要安装软件，注意，yum自带的mysql服务不支持此版本软件

mysql服务需开启二进制日志功能
innodb_file_per_table  将此选项打开，不然后面有的功能实现不了

选项说明

--defaults-file：指定my.cnf参数文件的位置[此配置文件里必须指定datadir]
--apply-log：同xtrabackup的--prepare参数,一般情况下,在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交
        的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据 文件仍处理不一致状态。--apply-log的作用是通过回滚未提交的
        事务及同步已经提交的事务至数据文件使数据文件处于一致性状态。
--copy-back：做数据恢复时将备份数据文件拷贝到MySQL服务器的datadir
--remote-host=HOSTNAME： 通过ssh将备份数据存储到进程服务器上
--stream=[tar]：备份文件输出格式, 该文件可在XtarBackup binary文件中获得. 在使用参数stream=tar备份的时候,你的xtrabackup_logfile可
        能会临时放在/tmp目录下,如果你备份的时候并发写入较大的话,xtrabackup_logfile可能会很大(5G+),很可能会撑满你的/tmp目录,可以
        通过参数--tmpdir指定目录来解决这个问题.
--tmpdir=DIRECTORY：当有指定--remote-host or --stream时, 事务日志临时存储的目录, 默认采用MySQL配置文件中所指定的临时目录tmpdir
--redo-only --apply-log：强制备份日志时只redo,跳过rollback,这在做增量备份时非常必要
--use-memory=*：该参数在prepare的时候使用,控制prepare时innodb实例使用的内存
--databases=LIST：列出需要备份的databases,如果没有指定该参数,所有包含MyISAM和InnoDB表的database都会被备份
--slave-info：备份从库, 加上--slave-info备份目录下会多生成一个xtrabackup_slave_info 文件, 这里会保存主日志文件以及偏移, 文件内容
        类似于:CHANGE MASTER TO MASTER_LOG_FILE='', MASTER_LOG_POS=0
--socket=SOCKET：指定mysql.sock所在位置，以便备份进程登录mysql.

1、完全备份

innobackupex --user=DBUSER --password=DBUSERPASS --host=  --defaults-file=/usr/local/mysql/conf/my.cnf /path/to/BACKUP-DIR/  #将数据库备份到哪个目录

如果要使用一个最小权限的用户进行备份，则可基于如下命令创建此类用户：

mysql> CREATE USER ’bkpuser’@’localhost’ IDENTIFIED BY ’s3cret’;
mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM ’bkpuser’;
mysql> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO ’bkpuser’@’localhost’;
mysql> FLUSH PRIVILEGES;

使用innobakupex备份时，其会调用xtrabackup备份所有的InnoDB表，复制所有关于表结构定义的相关文件(.frm)、以及MyISAM、MERGE、CSV和ARCHIVE表的相关文件，同时还会备份触发器和数据库配置信息相关的文件。这些文件会被保存至一个以时间命名的目录中。

在备份的同时，innobackupex还会在备份目录中创建如下文件：

(1)xtrabackup_checkpoints —— 备份类型（如完全或增量）、备份状态（如是否已经为prepared状态）和LSN(日志序列号)范围信息；
每个InnoDB页(通常为16k大小)都会包含一个日志序列号，即LSN。LSN是整个数据库系统的系统版本号，每个页面相关的LSN能够表明此页面最近是如何发生改变的。
(2)xtrabackup_binlog_info —— mysql服务器当前正在使用的二进制日志文件及至备份这一刻为止二进制日志事件的位置。
(3)xtrabackup_logfile —— 二进制日志文件
(4)xtrabackup_info —— 备份信息
(5)backup-my.cnf —— 备份命令用到的配置选项信息；

在使用innobackupex进行备份时，还可以使用–no-timestamp选项来阻止命令自动创建一个以时间命名的目录；如此一来，innobackupex命令将会创建一个BACKUP-DIR目录来存储备份数据。

192.168.170.10 机器操作

[root@master ~]#innobackupex --defaults-file=/usr/local/mysql/conf/my.cnf --user=root --password= --host=localhost /root/backup/
171203 17:50:07 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
171203 17:50:07 Executing FLUSH TABLES WITH READ LOCK...
，，，
171203 17:50:08 Executing UNLOCK TABLES
171203 17:50:08 All tables unlocked
171203 17:50:08 Backup created in directory '/root/backup/2017-12-03_17-50-05/'
MySQL binlog position: filename 'mysql-bin.000004', position '7787'
171203 17:50:08 [00] Writing /root/backup/2017-12-03_17-50-05/backup-my.cnf
171203 17:50:08 [00]        ...done
171203 17:50:08 [00] Writing /root/backup/2017-12-03_17-50-05/xtrabackup_info
171203 17:50:08 [00]        ...done
xtrabackup: Transaction log of lsn (1595675) to (1595675) was copied.
171203 17:50:08 completed OK

2、准备(prepare)一个完全备份

一般情况下，在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处理不一致状态。“准备”的主要作用正是通过回滚未提交的事务及同步已经提交的事务至数据文件也使得数据文件处于一致性状态。

innobakupex命令的–apply-log选项可用于实现上述功能。如下面的命令：

innobackupex --apply-log  /path/to/BACKUP-DIR

如果执行正确，其最后输出的几行信息通常如下：

InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1595944
171203 18:00:17 completed OK!

在实现“准备”的过程中，innobackupex通常还可以使用–use-memory选项来指定其可以使用的内存的大小，默认通常为100M。如果有足够的内存可用，可以多划分一些内存给prepare的过程，以提高其完成速度。

192.168.170.10 机器操作
将备份文件复制到新机器上面
[root@master ~]#scp -P22222 -r backup/ 192.168.170.12:/root

192.168.170.12 机器操作
[root@master ~]#innobackupex --apply-log /root/backup/2017-12-03_17-50-05/

3、从一个完全备份中恢复数据 

注意：恢复不用启动MySQL，已有的数据文件要全部删除

innobackupex命令的–copy-back选项用于执行恢复操作，其通过复制所有数据相关的文件至mysql服务器DATADIR目录中来执行恢复过程。innobackupex通过backup-my.cnf来获取DATADIR目录的相关信息。

innobackupex --copy-back  /path/to/BACKUP-DIR

如果执行正确，其输出信息的最后几行通常如下：

171203 18:06:15 [01] Copying ./ibtmp1 to /data/mysql/ibtmp1
171203 18:06:15 [01]        ...done
171203 18:06:15 completed OK!

请确保如上信息的最行一行出现“innobackupex: completed OK!”。

当数据恢复至DATADIR目录以后，还需要确保所有数据文件的属主和属组均为正确的用户，如mysql，否则，在启动mysqld之前还需要事先修改数据文件的属主和属组。

有可能遇到的问题：要重新设置配置文件：innodb_log_file_size = 48M，会更改这个事物日志文件，也可以将恢复机器上面的事物日志文件删除掉，也可以，因为事物日志已经提交了

192.168.170.12 机器操作

[root@master ~]#service mysqld stop
Stopping mysqld:                                           [  OK  ]

[root@master ~]#rm /data/mysql/*
moved to /data/trash/20171203-180456 ok
[root@master ~]#rm /data/mysql_binlog/*
moved to /data/trash/20171203-180502 ok

[root@master ~]#innobackupex --defaults-file=/usr/local/mysql/conf/my.cnf --copy-back /root/backup/2017-12-03_17-50-05/
，，，
171203 18:06:15 [01] Copying ./ibtmp1 to /data/mysql/ibtmp1
171203 18:06:15 [01]        ...done
171203 18:06:15 completed OK!

[root@master ~]#chown -R mysql.mysql /data/mysql*

[root@master ~]#service mysqld restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]

4、使用innobackupex进行增量备份和还原

每个InnoDB的页面都会包含一个LSN信息，每当相关的数据发生改变，相关的页面的LSN就会自动增长。这正是InnoDB表可以进行增量备份的基础，即innobackupex通过备份上次完全备份之后发生改变的页面来实现。

要实现第一次增量备份，可以使用下面的命令进行：

innobackupex --incremental /root/backup --incremental-basedir=BASEDIR

其中，BASEDIR指的是完全备份所在的目录，此命令执行结束后，innobackupex命令会在/root/backup目录中创建一个新的以时间命名的目录以存放所有的增量备份数据。另外，在执行过增量备份之后再一次进行增量备份时，其–incremental-basedir应该指向上一次的增量备份所在的目录。

需要注意的是，增量备份仅能应用于InnoDB或XtraDB表，对于MyISAM表而言，执行增量备份时其实进行的是完全备份。

还原说明=======

“准备”(prepare)增量备份与整理完全备份有着一些不同，尤其要注意的是：

(1)需要在每个备份(包括完全和各个增量备份)上，将已经提交的事务进行“重放”。“重放”之后，所有的备份数据将合并到完全备份上。
(2)基于所有的备份将未提交的事务进行“回滚”。

于是，操作就变成了：

innobackupex --apply-log --redo-only BASE-DIR

接着执行：

innobackupex --apply-log --redo-only BASE-DIR --incremental-dir=INCREMENTAL-DIR-1

而后是第二个增量：

innobackupex --apply-log --redo-only BASE-DIR --incremental-dir=INCREMENTAL-DIR-2

其中BASE-DIR指的是完全备份所在的目录，而INCREMENTAL-DIR-1指的是第一次增量备份的目录，INCREMENTAL-DIR-2指的是第二次增量备份的目录，其它依次类推，即如果有多次增量备份，每一次都要执行如上操作；

192.168.170.10 机器操作
接着上面的操作，假如上面是上周末，进行了一次全备份，然后线上环境接着操作

周一白天线上操作
[root@master ~]#mysql

mysql> use hellodb;
Database changed
mysql> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| teachers          |
| toc               |
+-------------------+
7 rows in set (0.00 sec)

mysql> drop table toc;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from classes;
+---------+----------------+----------+
| ClassID | Class          | NumOfStu |
+---------+----------------+----------+
|       1 | Shaolin Pai    |       10 |
|       2 | Emei Pai       |        7 |
|       3 | QingCheng Pai  |       11 |
|       4 | Wudang Pai     |       12 |
|       5 | Riyue Shenjiao |       31 |
|       6 | Lianshan Pai   |       27 |
|       7 | Ming Jiao      |       27 |
|       8 | Xiaoyao Pai    |       15 |
+---------+----------------+----------+
8 rows in set (0.01 sec)

mysql> insert into classes (Class) values("andy"),("bob");
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> delete from classes where ClassID=1;
Query OK, 1 row affected (0.00 sec)

mysql> select * from classes;
+---------+----------------+----------+
| ClassID | Class          | NumOfStu |
+---------+----------------+----------+
|       2 | Emei Pai       |        7 |
|       3 | QingCheng Pai  |       11 |
|       4 | Wudang Pai     |       12 |
|       5 | Riyue Shenjiao |       31 |
|       6 | Lianshan Pai   |       27 |
|       7 | Ming Jiao      |       27 |
|       8 | Xiaoyao Pai    |       15 |
|       9 | andy           |     NULL |
|      10 | bob            |     NULL |
+---------+----------------+----------+
9 rows in set (0.00 sec)

周一晚上进行一次增量备份
[root@master ~]#innobackupex --defaults-file=/usr/local/mysql/conf/my.cnf --user=root --password= --host=localhost 
--incremental /root/backup/ --incremental-basedir=/root/backup/2017-12-03_17-50-05/
，，，
xtrabackup: Transaction log of lsn (1595675) to (1595675) was copied.
171203 18:38:38 completed OK!

[root@master ~/backup]#ls
2017-12-03_17-50-05  2017-12-03_18-38-36
[root@master ~/backup]#cd 2017-12-03_17-50-05/
[root@master ~/backup/2017-12-03_17-50-05]#ls
backup-my.cnf  hellodb  ibdata1  mysql  performance_schema  test  xtrabackup_binlog_info  xtrabackup_checkpoints  xtrabackup_info  xtrabackup_logfile
[root@master ~/backup/2017-12-03_17-50-05]#cat xtrabackup_checkpoints 
backup_type = full-backuped
from_lsn = 0
to_lsn = 1595675
last_lsn = 1595675
compact = 0
recover_binlog_info = 0

[root@master ~/backup]#cd 2017-12-03_18-38-36/
[root@master ~/backup/2017-12-03_18-38-36]#cat xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 1595675
to_lsn = 1595675
last_lsn = 1595675
compact = 0
recover_binlog_info = 0

[root@master ~/backup/2017-12-03_18-38-36]#cd ..
[root@master ~/backup]#du -sh *
20M	2017-12-03_17-50-05
1.5M	2017-12-03_18-38-36


周二白天进行操作
mysql> select * from classes;
+---------+----------------+----------+
| ClassID | Class          | NumOfStu |
+---------+----------------+----------+
|       2 | Emei Pai       |        7 |
|       3 | QingCheng Pai  |       11 |
|       4 | Wudang Pai     |       12 |
|       5 | Riyue Shenjiao |       31 |
|       6 | Lianshan Pai   |       27 |
|       7 | Ming Jiao      |       27 |
|       8 | Xiaoyao Pai    |       15 |
|       9 | andy           |     NULL |
|      10 | bob            |     NULL |
+---------+----------------+----------+
9 rows in set (0.00 sec)

mysql> insert into classes (Class) values("tom"),("mary");
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> delete from classes where ClassID=2;
Query OK, 1 row affected (0.01 sec)

mysql> select * from classes;
+---------+----------------+----------+
| ClassID | Class          | NumOfStu |
+---------+----------------+----------+
|      11 | tom            |     NULL |
|       3 | QingCheng Pai  |       11 |
|       4 | Wudang Pai     |       12 |
|       5 | Riyue Shenjiao |       31 |
|       6 | Lianshan Pai   |       27 |
|       7 | Ming Jiao      |       27 |
|       8 | Xiaoyao Pai    |       15 |
|       9 | andy           |     NULL |
|      10 | bob            |     NULL |
|      12 | mary           |     NULL |
+---------+----------------+----------+
10 rows in set (0.00 sec)

周二晚上进行增量备份
[root@master ~]#innobackupex --defaults-file=/usr/local/mysql/conf/my.cnf --user=root --password= --host=localhost 
--incremental /root/backup/ --incremental-basedir=/root/backup/2017-12-03_18-38-36/

[root@master ~/backup]#du -sh *
20M	2017-12-03_17-50-05
1.5M	2017-12-03_18-38-36
1.5M	2017-12-03_18-48-42


周三早上进行了几次操作，然后数据库出故障了
mysql> select * from classes;
+---------+----------------+----------+
| ClassID | Class          | NumOfStu |
+---------+----------------+----------+
|      11 | tom            |     NULL |
|       3 | QingCheng Pai  |       11 |
|       4 | Wudang Pai     |       12 |
|       5 | Riyue Shenjiao |       31 |
|       6 | Lianshan Pai   |       27 |
|       7 | Ming Jiao      |       27 |
|       8 | Xiaoyao Pai    |       15 |
|       9 | andy           |     NULL |
|      10 | bob            |     NULL |
|      12 | mary           |     NULL |
+---------+----------------+----------+
10 rows in set (0.01 sec)

mysql> insert into classes (Class) values("alix");
Query OK, 1 row affected (0.00 sec)

mysql> select * from classes;
+---------+----------------+----------+
| ClassID | Class          | NumOfStu |
+---------+----------------+----------+
|      11 | tom            |     NULL |
|      13 | alix           |     NULL |
|       3 | QingCheng Pai  |       11 |
|       4 | Wudang Pai     |       12 |
|       5 | Riyue Shenjiao |       31 |
|       6 | Lianshan Pai   |       27 |
|       7 | Ming Jiao      |       27 |
|       8 | Xiaoyao Pai    |       15 |
|       9 | andy           |     NULL |
|      10 | bob            |     NULL |
|      12 | mary           |     NULL |
+---------+----------------+----------+
11 rows in set (0.00 sec)

mysql> delete from classes where ClassID > 10;   这里的where语句条件写错了，导致一部分数据丢失，需要将数据恢复到此之前状态
Query OK, 3 rows affected (0.00 sec)

mysql> select * from classes;
+---------+----------------+----------+
| ClassID | Class          | NumOfStu |
+---------+----------------+----------+
|       3 | QingCheng Pai  |       11 |
|       4 | Wudang Pai     |       12 |
|       5 | Riyue Shenjiao |       31 |
|       6 | Lianshan Pai   |       27 |
|       7 | Ming Jiao      |       27 |
|       8 | Xiaoyao Pai    |       15 |
|       9 | andy           |     NULL |
|      10 | bob            |     NULL |
+---------+----------------+----------+
8 rows in set (0.00 sec)

下面是还原部分

192.168.170.12  机器操作

现在有以下数据
[root@master ~/backup]#ll
total 24
drwxr-x--- 6 root root 4096 Dec  3 19:03 2017-12-03_17-50-05   每周一次的全备份
drwxr-x--- 6 root root 4096 Dec  3 19:03 2017-12-03_18-38-36   每天一次的增量备份
drwxr-x--- 6 root root 4096 Dec  3 19:03 2017-12-03_18-48-42   每天一次的增量备份
-rw-r----- 1 root root 9483 Dec  3 19:03 mysql-bin.000004      出事当天的二进制日志文件

[root@master ~]#innobackupex --apply-log --redo-only /root/backup/2017-12-03_17-50-05/
[root@master ~]#innobackupex --apply-log --redo-only /root/backup/2017-12-03_17-50-05/ --incremental-dir=/root/backup/2017-12-03_18-38-36/
[root@master ~]#innobackupex --apply-log --redo-only /root/backup/2017-12-03_17-50-05/ --incremental-dir=/root/backup/2017-12-03_18-48-42/

[root@master ~]#service mysqld stop
Stopping mysqld:                                           [  OK  ]

[root@master ~]#rm /data/mysql/*
moved to /data/trash/20171203-191359 ok
[root@master ~]#rm /data/mysql_binlog/*
moved to /data/trash/20171203-191403 ok

[root@master ~]#innobackupex --defaults-file=/usr/local/mysql/conf/my.cnf --copy-back /root/backup/2017-12-03_17-50-05/

[root@master ~]#chown -R mysql.mysql /data/mysql*

[root@master ~]#service mysqld restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]

[root@master ~]#mysql

mysql> use hellodb;
Database changed
mysql> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| teachers          |
+-------------------+
6 rows in set (0.00 sec)

mysql> select * from classes;
+---------+----------------+----------+
| ClassID | Class          | NumOfStu |
+---------+----------------+----------+
|      11 | tom            |     NULL |
|       3 | QingCheng Pai  |       11 |
|       4 | Wudang Pai     |       12 |
|       5 | Riyue Shenjiao |       31 |
|       6 | Lianshan Pai   |       27 |
|       7 | Ming Jiao      |       27 |
|       8 | Xiaoyao Pai    |       15 |
|       9 | andy           |     NULL |
|      10 | bob            |     NULL |
|      12 | mary           |     NULL |
+---------+----------------+----------+
10 rows in set (0.00 sec)

数据已经恢复到事发前天晚上备份时的状态了，然后根据二进制日志进行操作

根据最后一次备份，查看二进制日志位置
[root@master ~/backup]#cat 2017-12-03_18-48-42/xtrabackup_binlog_info 
mysql-bin.000004	8957

查看出事当天二进制日志，找出最后一条delete语句，将之前语句复制出来，如下
[root@master ~/backup]#mysqlbinlog --no-defaults mysql-bin.000004 --start-position=8957 --stop-position=9236 > tmp.sql

[root@master ~]#mysql

mysql> use hellodb;
Database changed
mysql> source /root/backup/tmp.sql;

mysql> select * from classes;
+---------+----------------+----------+
| ClassID | Class          | NumOfStu |
+---------+----------------+----------+
|      11 | tom            |     NULL |
|      13 | alix           |     NULL |
|       3 | QingCheng Pai  |       11 |
|       4 | Wudang Pai     |       12 |
|       5 | Riyue Shenjiao |       31 |
|       6 | Lianshan Pai   |       27 |
|       7 | Ming Jiao      |       27 |
|       8 | Xiaoyao Pai    |       15 |
|       9 | andy           |     NULL |
|      10 | bob            |     NULL |
|      12 | mary           |     NULL |
+---------+----------------+----------+
11 rows in set (0.00 sec)

可以看到数据成功恢复，撒花

5、Xtrabackup的“流”及“备份压缩”功能

Xtrabackup对备份的数据文件支持“流”功能，即可以将备份的数据通过STDOUT传输给tar程序进行归档，而不是默认的直接保存至某备份目录中。要使用此功能，仅需要使用–stream选项即可。如：

innobackupex --stream=tar  /backup | gzip > /backup/`date +%F_%H-%M-%S`.tar.gz

甚至也可以使用类似如下命令将数据备份至其它服务器：

innobackupex --stream=tar  /backup | ssh user@abc.com  "cat -  > /backups/`date +%F_%H-%M-%S`.tar"

此外，在执行本地备份时，还可以使用–parallel选项对多个文件进行并行复制。此选项用于指定在复制时启动的线程数目。当然，在实际进行备份时要利用此功能的便利性，也需要启用innodb_file_per_table选项或共享的表空间通过innodb_data_file_path选项存储在多个ibdata文件中。对某一数据库的多个文件的复制无法利用到此功能。

–

参考文档：http://www.cnblogs.com/zhoujinyi/tag/backup/

–

–

–

未经允许不得转载：江哥架构师笔记 » mysql学习：xtrabackup使用

分享到：更多 ()

评论 抢沙发

评论前必须登录！