MySQL数据备份与恢复之Percona XtraBackup

一、Xtrabackup是什么？

官方文档：https://www.percona.com/doc/percona-xtrabackup/2.4/intro.html

Xtrabackup是一个对InnoDB做数据备份的工具，支持在线热备份(备份时不影响数据读写)，是商业备份工具InnoDB Hotbackup的一个很好的替代品。

Xtrabackup有两个主要的工具：xtrabackup、innobackupex

1、xtrabackup只能备份InnoDB和XtraDB两种数据表，而不能备份MyISAM数据表

2、innobackupex是参考了InnoDB Hotbackup的innoback脚本修改而来的.innobackupex是一个perl脚本封装，封装了xtrabackup。主要是为了方便的同时备份InnoDB和MyISAM引擎的表，但在处理myisam时需要加一个读锁。并且加入了一些使用的选项。如slave-info可以记录备份恢复后，作为slave需要的一些信息，根据这些信息，可以很方便的利用备份来重做slave。

二、Xtrabackup可以做什么？

1、在线(热)备份整个库的InnoDB、 XtraDB表

2、在xtrabackup的上一次整库备份基础上做增量备份(innodb only)以流的形式产生备份，可以直接保存到远程机器上(本机硬盘空间不足时很有用)

3、MySQL数据库本身提供的工具并不支持真正的增量备份，二进制日志恢复是point-in-time(时间点)的恢复而不是增量备份。

4、Xtrabackup工具支持对InnoDB存储引擎的增量备份。

三、Xtrabackup版本及下载地址

最新版本：2.4.12

版本之间的区别：

2.4专针对5.7开发，兼容5.6，5.5

2.3针对5.6开发，兼容5.5

2.2针对5.5开发

官网地址：https://www.percona.com/doc/percona-xtrabackup/2.4/index.html

四、Percona XtraBackup工作流程

官网地址：https://www.percona.com/doc/percona-xtrabackup/2.4/how_xtrabackup_works.html

五、Xtrabackup 使用说明文档

Xtrabackup下载与安装：

https://www.percona.com/doc/percona-xtrabackup/2.4/installation/yum_repo.html

Xtrabackup增量备份与恢复：

https://www.percona.com/doc/percona-xtrabackup/2.4/backup_scenarios/incremental_backup.html

Xtrabackup全量备份与恢复：

https://www.percona.com/doc/percona-xtrabackup/2.4/backup_scenarios/full_backup.html

Xtrabackup参数选项及使用说明：

https://www.percona.com/doc/percona-xtrabackup/LATEST/xtrabackup_bin/xbk_option_reference.html

六、Xtrabackup 安装与使用

6.1、安装Xtrabackup

安装页面官网地址：https://www.percona.com/doc/percona-xtrabackup/LATEST/installation/yum_repo.html

方式1、通过安装Percona存储库，安装Xtrabackup

yum install http://www.percona.com/downloads/percona-release/redhat/0.1-6/percona-release-0.1-6.noarch.rpm

#为了成功安装Percona XtraBackup libev包需要先安装。libev包可以从EPEL存储库安装。

yum install https://dl.fedoraproject.org/pub/epel/epel-release-latest-6.noarch.rpm

yum install percona-xtrabackup-24

#安装完毕后检测

innobackupex --help

方式2、使用下载的rpm软件包，安装XtraBackup

1、从下载页面下载适用于您的体系结构的所需系列的软件包，下载页面：https://www.percona.com/downloads/XtraBackup/

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

#为了成功安装Percona XtraBackup libev包需要先安装。libev包可以从EPEL存储库安装。

yum install https://dl.fedoraproject.org/pub/epel/epel-release-latest-6.noarch.rpm

yum localinstall percona-xtrabackup-24-2.4.12-1.el6.x86_64.rpm

#安装完毕后检测

innobackupex --help

6.2、安装完毕后开始使用

innobackupex详细参数: https://www.percona.com/doc/percona-xtrabackup/LATEST/innobackupex/innobackupex_option_reference.html

使用前对 innobackupex一些关键参数做介绍：

--defaults-file #指定MySQL配置文件，如果不指定–defaults-file，默认值为/etc/my.cnf

--user=root #备份操作用户名，一般都是root用户

/mnt/backup/ #备份路径

--socket=/tmp/mysql.sock #指定mysql.sock登录(可通过innobackupex --help查看)

--parallel=2 --throttle=200 #并行个数，根据主机配置选择合适的，默认是1个，多个可以加快备份速度。

2>/mnt/backup/bak.log #备份日志，将备份过程中的输出信息重定向到bak.log

--apply-log #在备份目录下生成一个xtrabackup_logfile事务日志文件，此外，创建新的事务日志。 InnoDB配置从文件“backup-my.cnf”中读取。

--redo-only #全备数据后对DB有增量更改，则必须执行此操作，在准备基本完全备份和合并除最后一个之外的所有增量备份时，应使用此选项

6.3、XtraBackup备份原理

XtraBackup基于InnoDB的crash-recovery功能。它会复制innodb 的data file，由于不锁表，复制出来的数据是不一致的，在恢复的时候使用crash-recovery，使得数据恢复一致。

InnoDB维护了一个redo log，又称为 transaction log，事务日志，它包含了innodb数据的所有改动情况。当InnoDB启动的时候，它会先去检查data file和transaction log，并且会做二步操作：

1.It applies committed transaction log entries to the data files

2.it performs an undo operation on any transactions that modified data but did not commit.

XtraBackup在备份的时候，一页一页地复制innodb的数据，而且不锁定表，与此同时，XtraBackup还有另外一个线程监视着transactions log，一旦log发生变化，就把变化过的log pages复制走。为什么要急着复制走呢？前几章的时候就提过这个问题，因为transactions log文件大小有限，写满之后，就会从头再开始写，所以新数据可能会覆盖到旧的数据。

在prepare过程中，XtraBackup使用复制到的transactions log 对备份出来的innodb data file 进行crash recovery。

6.3、使用XtraBackup备份数据时，关于文件权限的说明

xtrabackup以read-write模式打开innodb的数据文件，然后对其进行复制。其实它不会修改此文件。也就是说，运行xtrabackup的用户，必须对innodb的数据文件具有读写权限。

为什么要用rw模式呢？直接read模式不好么？因为xtrabackup采用了其内置的innodb库来打开文件，而innodb库打开文件的时候就是rw的。

七、Xtrabackup 全量备份与全量恢复

备份单个或者多个库 --databases 用于指定要备份的数据库, 备份多个库使用方法: --databases="db_zone_9033 db_zone_9034" 用空格分开。

innobackupex --defaults-file=/usr/local/mysql/my.cnf --user=root --databases="db_zone_9033 db_zone_9034" --parallel=2 --throttle=200 /data/tmp/ --socket=/tmp/mysql.sock

# 查看备份后的数据

[root@10.11.11.111 /data/tmp]$ ll 2018-10-25_23-59-39

total 96136

-rw-r-----. 1 root root 481 Oct 26 00:00 backup-my.cnf

drwxr-x---. 2 root root 4096 Oct 26 00:00 db_zone_9033

drwxr-x---. 2 root root 4096 Oct 26 00:00 db_zone_9034

-rw-r-----. 1 root root 79691776 Oct 25 23:59 ibdata1

-rw-r-----. 1 root root 77 Oct 26 00:00 xtrabackup_binlog_info

-rw-r-----. 1 root root 125 Oct 26 00:00 xtrabackup_checkpoints

-rw-r-----. 1 root root 673 Oct 26 00:00 xtrabackup_info

-rw-r-----. 1 root root 18716160 Oct 26 00:00 xtrabackup_logfile

排除多个个库 --databases-exclude 根据名称排除数据库，使用方法：--databases-exclude="db_zone_battlereport_9037 db_zone_battlereport_9038" 用空格分开。

innobackupex --defaults-file=/usr/local/mysql/my.cnf --user=root --databases-exclude="db_zone_battlereport_9037 db_zone_battlereport_9038 db_zone_battlereport_9039 db_zone_battlereport_9040" --parallel=2 --throttle=200 /data/tmp/ --socket=/tmp/mysql.sock

7.1、全量备份

1.创建用于备份恢复的用户 mulong 并赋予权限

mysql> create user mulong@'localhost' identified by '123456';

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

2.创建数据存放目录

mkdir -p /data/db_backup/

3.进行数据全备

[root@szq ~]# /usr/bin/innobackupex --defaults-file=/usr/local/mysql/my.cnf --user=root --parallel=2 --throttle=200 /data/db_backup/xtrabackup --socket=/tmp/mysql.sock

#执行命令后输出：

xtrabackup: recognized server arguments:

xtrabackup: recognized client arguments:

180726 15:00:18 innobackupex: Starting the backup operation

IMPORTANT: Please check that the backup run completes successfully.

At the end of a successful backup run innobackupex

prints "completed OK!".

180726 15:00:28 All tables unlocked

180726 15:00:28 Backup created in directory '/data/db_backup/xtrabackup/2018-03-28_17-33-41/'

MySQL binlog position: filename 'master-bin.000008', position '191', GTID of the last change '5be531c0-3252-11e8-a5a9-000c2923f205:1-1079'

180726 15:00:28 [00] Writing /mnt/backup/2018-07-26_15-00-18/backup-my.cnf

180726 15:00:28 [00] ...done

180726 15:00:28 [00] Writing /mnt/backup/2018-07-26_15-00-18/xtrabackup_info

180726 15:00:28 [00] ...done

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

180726 15:00:29 completed OK!

以上可以看到整个备份过程，说明备份成功！

4.查看全备后生成的文件 /data/db_backup/xtrabackup/2018-03-28_17-33-41/

[root@szq 2018-03-28_17-33-41]# ll /data/db_backup/xtrabackup/2018-03-28_17-33-41/

total 131112

-rw-r-----. 1 root root 481 Mar 28 17:33 backup-my.cnf

-rw-r-----. 1 root root 12582912 Mar 28 17:38 ibdata1

-rw-r-----. 1 root root 50331648 Mar 28 17:38 ib_logfile0

-rw-r-----. 1 root root 50331648 Mar 28 17:38 ib_logfile1

-rw-r-----. 1 root root 12582912 Mar 28 17:38 ibtmp1

drwxr-x---. 2 root root 4096 Mar 28 17:38 incre1

drwxr-x---. 2 root root 4096 Mar 28 17:38 mysql

drwxr-x---. 2 root root 4096 Mar 28 17:38 performance_schema

drwxr-x---. 2 root root 4096 Mar 28 17:38 test

-rw-r-----. 1 root root 64 Mar 28 17:38 xtrabackup_binlog_info

-rw-r--r--. 1 root root 23 Mar 28 17:38 xtrabackup_binlog_pos_innodb

-rw-r-----. 1 root root 113 Mar 28 17:38 xtrabackup_checkpoints

-rw-r-----. 1 root root 724 Mar 28 17:38 xtrabackup_info

-rw-r-----. 1 root root 8388608 Mar 28 17:38 xtrabackup_logfile

-rw-r--r--. 1 root root 1 Mar 28 17:38 xtrabackup_master_key_id

配置文件注释：

(1) backup-my.cnf —— 备份命令用到的配置选项信息；

(2) ibdata1 —— 备份的表空间文件；

(3) xtrabackup_binary —— 备份中用到的xtrabackup的可执行文件；

(4) xtrabackup_binlog_info —— mysql服务器当前正在使用的二进制日志文件及至备份这一刻为止二进制日志事件的位置；

(5) xtrabackup_logfile —— 备份的重做日志文件。

(6) xtrabackup_checkpoints —— 备份类型（如完全或增量）、备份状态（如是否已经为prepared状态）和LSN(日志序列号)范围信息；

xtrabackup_checkpoints 备份类型（如完全或增量）、备份状态（如是否已经为prepared状态）和LSN(日志序列号)范围信息：

[root@szq 2018-03-28_17-33-41]# cat xtrabackup_checkpoints

backup_type = full-prepared #可以看出是全备份

from_lsn = 0 #记录了LSN，日志偏移量

to_lsn = 1648181

last_lsn = 1648181 # LSN号

compact = 0

recover_binlog_info = 0

backup-my.cnf 备份命令用到的配置选项信息：

[root@szq 2018-03-28_17-33-41]# cat backup-my.cnf

# This MySQL options file was generated by innobackupex.

# The MySQL server

[mysqld]

innodb_checksum_algorithm=innodb

innodb_log_checksum_algorithm=innodb

innodb_data_file_path=ibdata1:12M:autoextend

innodb_log_files_in_group=2

innodb_log_file_size=50331648

innodb_fast_checksum=false

innodb_page_size=16384

innodb_log_block_size=512

innodb_undo_directory=.

innodb_undo_tablespaces=0

server_id=0

redo_log_version=0

server_uuid=6e843abf-3268-11e8-a639-000c2923f205

master_key_id=0

xtrabackup_binlog_info，mysql服务器当前正在使用的二进制日志文件及至备份这一刻为止二进制日志事件的位置：

[root@szq 2018-03-28_17-33-41]# cat xtrabackup_binlog_info

master-bin.000001 2481 6e843abf-3268-11e8-a639-000c2923f205:1-6

xtrabackup_info，记录备份的基本信息，uuid、备份命令、备份时间、binlog、LSN、以及其他加密压缩等信息：

[root@szq 2018-03-28_17-33-41]# cat xtrabackup_info

uuid = a102096b-326b-11e8-a639-000c2923f205

name =

tool_name = innobackupex

tool_command = --defaults-file=/usr/local/mysql/my.cnf --user=root --socket=/tmp/mysql.sock --incremental /data/db_backup/incremental --incremental-basedir=/data/db_backup/incremental/2018-03-28_17-34-58/ --parallel=2 --throttle=200

tool_version = 2.4.12

ibbackup_version = 2.4.12

server_version = 5.6.17-log

start_time = 2018-03-28 17:37:25

end_time = 2018-03-28 17:37:27

lock_time = 0

binlog_pos = filename 'master-bin.000001', position '2481', GTID of the last change '6e843abf-3268-11e8-a639-000c2923f205:1-6'

innodb_from_lsn = 1632332

innodb_to_lsn = 1648181

partial = N

incremental = Y

format = file

compact = N

compressed = N

encrypted = N

7.2、全量备份后，执行全量恢复

1.关闭数据库并移除数据文件，创建新的数据库目录

[root@szq 2018-03-28_17-33-41]# /usr/local/mysql/bin/mysqld_multi --defaults-file=/usr/local/mysql/my.cnf stop 1

[root@szq 2018-03-28_17-33-41]# mv /data/mysql/ /data/mysql_bak/

[root@szq 2018-03-28_17-33-41]# mkdir -p /data/mysql/

2.在全备目录上重放已提交的事务：

innobackupex --defaults-file=/usr/local/mysql/my.cnf --user=root --socket=/tmp/mysql.sock --apply-log --redo-only /data/db_backup/xtrabackup/2018-03-28_17-33-41/

事务提交后：查看backup_type是否为"log-applied"

cat /data/db_backup/xtrabackup/2018-03-28_17-33-41/

backup_type = log-applied

from_lsn = 0

to_lsn = 2034824383639

last_lsn = 2034830345359

compact = 0

recover_binlog_info = 0

3.用全备的数据文件 [/data/db_backup/xtrabackup/2018-03-28_17-33-41/] 来恢复数据 [若全备数据之后，数据有新的增删改查修改，那么就需要使用增量备份的方式去恢复数据，详见文章：7.3-7.4]

[root@szq db_backup]# innobackupex --defaults-file=/usr/local/mysql/my.cnf --user=root --socket=/tmp/mysql.sock --datadir=/data/mysql --copy-back /data/db_backup/xtrabackup/2018-03-28_17-33-41/

xtrabackup: recognized server arguments: --datadir=/data/mysql

xtrabackup: recognized client arguments: --datadir=/data/mysql

180328 17:14:22 innobackupex: Starting the copy-back operation

IMPORTANT: Please check that the copy-back run completes successfully.

At the end of a successful copy-back run innobackupex

prints "completed OK!".

innobackupex version 2.4.12 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 170eb8c)

180328 17:14:22 [01] Copying ib_logfile0 to /data/mysql/ib_logfile0

180328 17:14:23 [01] ...done

180328 17:14:23 [01] Copying ib_logfile1 to /data/mysql/ib_logfile1

180328 17:14:23 [01] ...done

180328 17:14:23 [01] Copying ibdata1 to /data/mysql/ibdata1

180328 17:14:23 [01] ...done

180328 17:14:23 [01] Copying ./mysql/time_zone.frm to /data/mysql/mysql/time_zone.frm

180328 17:14:23 [01] ...done

180328 17:14:23 [01] Copying ./mysql/general_log.frm to /data/mysql/mysql/general_log.frm

180328 17:14:23 [01] ...done

180328 17:14:23 [01] Copying ./mysql/time_zone_name.frm to /data/mysql/mysql/time_zone_name.frm

180328 17:14:23 [01] ...done

180328 17:14:23 [01] Copying ./mysql/help_topic.MYI to /data/mysql/mysql/help_topic.MYI

180328 17:14:24 [01] ...done

180328 17:14:24 [01] Copying ./performance_schema/events_statements_current.frm to /data/mysql/performance_schema/events_statements_current.frm

180328 17:14:24 [01] ...done

180328 17:14:24 [01] Copying ./performance_schema/events_statements_summary_by_host_by_event_name.frm to /data/mysql/performance_schema/events_statements_summary_by_host_by_event_name.frm

180328 17:14:24 [01] ...done

180328 17:14:24 [01] Copying ./performance_schema/setup_actors.frm to /data/mysql/performance_schema/setup_actors.frm

180328 17:14:24 [01] ...done

180328 17:14:24 [01] Copying ./performance_schema/socket_summary_by_event_name.frm to /data/mysql/performance_schema/socket_summary_by_event_name.frm

180328 17:14:24 [01] ...done

180328 17:14:24 [01] Copying ./performance_schema/events_statements_summary_by_account_by_event_name.frm to /data/mysql/performance_schema/events_statements_summary_by_account_by_event_name.frm

180328 17:14:24 [01] ...done

180328 17:14:24 [01] Copying ./test/db.opt to /data/mysql/test/db.opt

180328 17:14:24 [01] ...done

180328 17:14:24 [01] Copying ./xtrabackup_master_key_id to /data/mysql/xtrabackup_master_key_id

180328 17:14:24 [01] ...done

180328 17:14:24 [01] Copying ./ibtmp1 to /data/mysql/ibtmp1

180328 17:14:24 [01] ...done

180328 17:14:24 completed OK!

# 这里看出是一个copy数据的过程！

4.全备数据恢复后，授权数据库文件，并启动MySQL

[root@szq db_backup]# chown -R mysql.mysql /data/mysql

[root@szq db_backup]# /usr/local/mysql/bin/mysqld_multi --defaults-file=/usr/local/mysql/my.cnf start 1

5.可以看到数据库和表均已恢复：

mysql> show databases;

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

| Database |

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

| information_schema |

| incre1 |

| mysql |

| performance_schema |

| test |

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

5 rows in set (0.00 sec)

7.3、增量备份 7.3和7.4是一个整体

增量备份与恢复，官网地址：https://www.percona.com/doc/percona-xtrabackup/2.4/howtos/recipes_ibkx_inc.html

我们以之前做的全备文件为基准，在全备文件的基础上做增量备份：全备文件 /data/db_backup/xtrabackup/2018-03-28_17-33-41

增量备份1、新建一张表t_3party_server，不插入数据，作为增量备份1的标记

mysql> use incre1;

Database changed

mysql> show tables;

Empty set (0.00 sec)

mysql> create table t_3party_server(

-> app varchar(64) NOT NULL,

-> server varchar(64) NOT NULL,

-> division varchar(64) NOT NULL,

-> node varchar(64) NOT NULL,

-> port varchar(64) NOT NULL,

-> status int(10) unsigned NOT NULL,

-> PRIMARY KEY (app,server,division,node,port)

-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Query OK, 0 rows affected (0.02 sec)

mysql> show tables;

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

| Tables_in_incre1 |

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

| t_3party_server |

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

1 row in set (0.00 sec)

mysql> select * from t_3party_server;

0 rows in set (0.00 sec)

开始执行增量备份1的命令：

一、以全备目录为基础做增量备份

--incremental 指定增量备份1的目录

--incremental-basedir=指定全量备份的目录

innobackupex --defaults-file=/usr/local/mysql/my.cnf --user=root --socket=/tmp/mysql.sock --incremental /data/db_backup/incremental --incremental-basedir=/data/db_backup/xtrabackup/2018-03-28_17-33-41/ --parallel=2 --throttle=200

backup_type = incrementa

增量备份2、在表t_3party_server内插入三条数据，作为增量备份2的标记

mysql> use incre1;

Database changed

mysql> show tables;

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

| Tables_in_incre1 |

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

| t_3party_server |

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

1 row in set (0.00 sec)

mysql> insert into t_3party_server(app,server,division,node,port,status) values('MOBA','haproxy','haproxy-login','10.117.16.242','30021','0');

Query OK, 1 row affected (0.01 sec)

mysql> insert into t_3party_server(app,server,division,node,port,status) values('MOBA','haproxy','haproxy-match','10.117.16.242','30032','0');

Query OK, 1 row affected (0.01 sec)

mysql> insert into t_3party_server(app,server,division,node,port,status) values('MOBA','haproxy','haproxy-test','10.117.16.242','30035','0');

Query OK, 1 row affected (0.01 sec)

mysql> select * from t_3party_server;

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

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

| MOBA | haproxy | haproxy-login | 10.117.16.242 | 30021 | 0 |

| MOBA | haproxy | haproxy-match | 10.117.16.242 | 30032 | 0 |

| MOBA | haproxy | haproxy-test | 10.117.16.242 | 30035 | 0 |

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

3 rows in set (0.00 sec)

开始执行增量备份2的命令：

二、增量备份1次之后，再次增量备份2的话，需要在增量备份1目录的基础上做增量备份2

--incremental 指定增量备份2的目录 (增量备份1和增量备份2的目录在同一父目录下)

--incremental-basedir=指定增量备份1的目录

innobackupex --defaults-file=/usr/local/mysql/my.cnf --user=root --socket=/tmp/mysql.sock --incremental /data/db_backup/incremental --incremental-basedir=/data/db_backup/incremental/2018-03-28_17-34-58/ --parallel=2 --throttle=200

7.4、增量备份恢复

增量备份的恢复需要有3个步骤

准备全备数据 [增量备份的数据是已全备数据为基础的]。

恢复增量备份到完全备份 [增量备份数据与全备数据合并，合并到全备目录]。

对整体的完全备份进行恢复，回滚未提交的数据 [增量数据和全备数据合并后，在恢复数据]。

增量备份恢复全过程：

一、准备全备数据：

/usr/bin/innobackupex --defaults-file=/usr/local/mysql/my.cnf --user=root --parallel=2 --throttle=200 /data/db_backup/xtrabackup --socket=/tmp/mysql.sock

二、准备增量备份1：在全备文件的基础上做增量备份，以全备文件为基础做增备1：

--incremental 指定增量备份1的目录

--incremental-basedir=指定全备文件的目录

backup_type = incrementa

######################################################################

小知识：增量备份1次之后，再次增量备份2的话，有两种方法：[方法二见2.1]

方法一：在在增量备份1目录的基础上做增量备份2 (那么数据恢复的时候，就需要先合并增量备份1然后在合并增量备份2)

--incremental 指定增量备份2的目录

--incremental-basedir=指定增量备份1的目录

innobackupex --defaults-file=/usr/local/mysql/my.cnf --user=root --socket=/tmp/mysql.sock --incremental /data/db_backup/incremental --incremental-basedir=/data/db_backup/incremental/2018-03-28_17-34-58/ --parallel=2 --throttle=200

以增备1为基础做增备2，数据如何恢复？

先全量备份数据+增量备份1数据合并，然后全量备份数据+增量备份2数据合并，数据最终合并到全量备份目录内：

--incremental-dir= 先指定增量备份1的目录

innobackupex --defaults-file=/usr/local/mysql/my.cnf --user=root --socket=/tmp/mysql.sock --apply-log --redo-only /data/db_backup/xtrabackup/2018-03-28_17-33-41/ --incremental-dir=/data/db_backup/incremental/2018-03-28_17-34-58/

--incremental-dir= 先指定增量备份2的目录

######################################################################

2.1、准备增量备份2：在全备的基础上增备，已全备目录为基础做增备2：

备注：每次的增量备份，都可以以全量备份为基础做增量备份1、2、3、4 ~~~~

--incremental 指定增量备份2的目录

--incremental-basedir=指定全备的目录

backup_type = incrementa

备份完毕后，可以通过检查文件的LSN号，来确认增量备份文件的一致性：

全备文件的LSN：0-1732070

[root@192.168.200.39 ]$ cat full/全量备份文件/xtrabackup_checkpoints

backup_type = full-prepared

from_lsn = 0

to_lsn = 1732070

last_lsn = 1732070

compact = 0

recover_binlog_info = 0

增量备份1文件的LSN：1702237-1716671 #[以全备为基础做增量备份1，可以看到LSN为：1702237-1716671]

[root@192.168.200.39 ]$ cat incre/增量备份文件1/xtrabackup_checkpoints

backup_type = incremental

from_lsn = 1702237

to_lsn = 1716671

last_lsn = 1716671

compact = 0

recover_binlog_info = 0

增量备份2文件的LSN：1702237-1732070 #[以全备为基础做增量备份2，可以看到LSN为：1702237-1732070]

[root@192.168.200.39 ]$ cat incre/增量备份文件2/xtrabackup_checkpoints

backup_type = incremental

from_lsn = 1702237

to_lsn = 1732070

last_lsn = 1732070

compact = 0

recover_binlog_info = 0

优点：数据恢复的时候，直接全备数据+最后一次增量备份n做数据合并，而不是[全量备份数据+增量备份1数据+增量备份2数据合并]

三、在全备目录上重放已提交的事务：

innobackupex --defaults-file=/usr/local/mysql/my.cnf --user=root --socket=/tmp/mysql.sock --apply-log --redo-only /data/db_backup/xtrabackup/2018-03-28_17-33-41/

四、全量备份数据+增量备份2数据合并，数据最终合并到全量备份目录内：

--incremental-dir= 指定增量备份2的目录

五、在全备目录(增量数据+全备数据合并) 开始回滚未提交的事务：

innobackupex --defaults-file=/usr/local/mysql/my.cnf --user=root --socket=/tmp/mysql.sock --apply-log /data/db_backup/xtrabackup/2018-03-28_17-33-41/

#####################################################################################

#注释：此行可不看

最终数据将位于全备备份目录中，而不是增量目录中。

您可以在每个目录中检查文件xtrabackup_checkpoints。

它们应该如下所示:

在全备备份中

backup_type = full - backuped

from_lsn = 0

to_lsn = 1291135

在增量备份中：

backup_type = incremental

from_lsn = 1291135

to_lsn = 1291340

事务提交后：查看backup_type是否为"log-applied"

cat /data/tmp/10.116.207.17_3306/xtrabackup_checkpoints

backup_type = log-applied

from_lsn = 0

to_lsn = 2034824383639

last_lsn = 2034830345359

compact = 0

recover_binlog_info = 0

#####################################################################################

六、恢复全备数据(全备+增备合并后的数据)

#关闭数据库

/usr/local/mysql/bin/mysqld_multi --defaults-file=/usr/local/mysql/my.cnf stop 1

#移除数据目录

mv /data/mysql/ /data/mysql_bak/

#重新创建MySQL数据目录

mkdir -p /data/mysql/

#将全备数据(全备+增备)恢复到MySQL

innobackupex --defaults-file=/usr/local/mysql/my.cnf --user=root --socket=/tmp/mysql.sock --datadir=/data/mysql --copy-back /data/db_backup/xtrabackup/2018-03-28_17-33-41/

#授权

chown -R mysql.mysql /data/mysql

#启动MySQL

/usr/local/mysql/bin/mysqld_multi --defaults-file=/usr/local/mysql/my.cnf start 1

七、检查数据库内的数据：

1、第一次增量备份：新建了一张数据表：t_3party_server。

2、第二次增量备份：在数据表t_3party_server内插入了3条数据。

#在数据库内存在数据表t_3party_server，且3条数据也同样存在。

mysql> show databases;

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

| Database |

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

| information_schema |

| incre1 |

| mysql |

| performance_schema |

| test |

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

5 rows in set (0.00 sec)

mysql> use incre1;

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> show tables;

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

| Tables_in_incre1 |

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

| t_3party_server |

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

1 row in set (0.00 sec)

mysql> select * from t_3party_server;

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

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

| MOBA | haproxy | haproxy-login | 10.117.16.242 | 30021 | 0 |

| MOBA | haproxy | haproxy-match | 10.117.16.242 | 30032 | 0 |

| MOBA | haproxy | haproxy-test | 10.117.16.242 | 30035 | 0 |

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

3 rows in set (0.00 sec)

疑问？

MySQL挂掉后能不能进行全备和增备？若果不能的话，数据如何恢复？会丢失多少数据？

MySQL挂掉后，不能进行增量备份，以及全量备份。此时恢复数据的步骤如下：

1、在全备目录上重放已提交的事务：

2、增量备份数据与全量备份数据合并，数据最终合并到全量备份目录内：

3、在全备目录(增量数据+全备数据合并) 开始回滚未提交的事务：

4、恢复全备数据(全备+增备合并后的数据)

5、丢失的数据：最后一次成功增量备份时间点~~MySQL挂掉的那个时间点，中间的数据全部丢失。

增量备份n+1次的话，为何要以上一次的增量备份n为基础，每次增量备份都以全量备份为基础可以吗？

本人亲测，增量备份2次，第二次增量备份2，无论是以全量备份为基础，还是以增量备份1为基础，数据都可以恢复！不过官方建议以增量备份1为基础做增量备份2。

7.5、使用Xtrabackup快速搭建MySQL主从复制

1、全备主库数据

/usr/bin/innobackupex --defaults-file=/usr/local/mysql/my.cnf --user=root --parallel=2 --throttle=200 /opt/ --socket=/tmp/mysql.sock

2、把主库的全备数据拷贝到从库

3、停掉从库的MySQL实例，并移除数据库目录，然后创建新的数据库目录

/usr/local/mysql/bin/mysqld_multi --defaults-file=/usr/local/mysql/my.cnf stop 1

mv /data/mysql /data/mysql_bak

mkdir -p /data/mysql

4、在从库上，把主库copy过来的全备文件上重放已提交的事务。[或者在主库上全备文件之后做此操作，然后拷贝到从库] (2选1)

innobackupex --defaults-file=/usr/local/mysql/my.cnf --user=root --socket=/tmp/mysql.sock --datadir=/data/mysql/ --apply-log --redo-only /data/tmp/10.116.207.17_3306/

--datadir #从库的MySQL数据目录

/data/tmp/10.116.207.17_3306/ #主库拷贝过来的全备文件

事务提交后：查看backup_type是否为"log-applied"

cat /data/tmp/10.116.207.17_3306/xtrabackup_checkpoints

backup_type = log-applied

from_lsn = 0

to_lsn = 2034824383639

last_lsn = 2034830345359

compact = 0

recover_binlog_info = 0

5、事务提交后，把全备文件内的数据恢复到从库的MySQL数据目录，即/data/mysql

innobackupex --defaults-file=/usr/local/mysql/my.cnf --user=root --socket=/tmp/mysql.sock --datadir=/data/mysql/ --copy-back /data/tmp/10.116.207.17_3306/

6、授权从库的/data/mysql/目录，并启动从库MySQL实例

chown -R mysql.mysql /data/mysql

/usr/local/mysql/bin/mysqld_multi --defaults-file=/usr/local/mysql/my.cnf start 1

7、通过全备目录内的xtrabackup_binlog_info文件，可以找到主从位置点：

cat /data/tmp/10.116.207.17_3306/xtrabackup_binlog_info

master-bin.002837 284721255 cc8e1365-3855-11e8-8ce0-0cc47a06f43e:1-130198911

8、从库通过执行CHANGE MASTER TO 创建主从关系，并检查主从复制状态是否已经完成。

mysql -u root --socket=/tmp/mysql.sock -e "show slave status\G;"

mysql -u root --socket=/tmp/mysql.sock -e "change master to master_host='10.116.207.17',master_user='replicate',master_password='moba2016',master_log_file='master-bin.002837',master_log_pos=284721255;"

mysql -u root --socket=/tmp/mysql.sock -e "show slave status\G;"

mysql -u root --socket=/tmp/mysql.sock -e "start slave;"

mysql -u root --socket=/tmp/mysql.sock -e "show slave status\G;"

7.6、线上MySQL使用Xtrabackup备份数据脚本

#!/bin/bash

# XtraBackup Use

# MySQL Server Backup

# sudada

# 2018/08/07

#每次增量备份都是基于全备目录，数据恢复：全备数据+最后一次增备数据合并

USER=root #备份使用的用户

INSTALL_DIR=/data/tmp #Xtrabackup安装路径

SOCKET_FILE=/tmp/mysql.sock2 #MySQL sock文件

XTRABACKUP_BIN=/usr/bin/innobackupex #innobackupex绝对路径

DEFAULTS_FILE=/usr/local/mysql/my.cnf #MySQL配置文件

BACK_DATA=/data/db_xtrabackup/`date +%F` #DB备份的主目录

BACK_DATA_FULLBACKUP=${BACK_DATA}/full #全量备份的目录

BACK_DATA_INCREBACKUP=${BACK_DATA}/incre #增量备份的目录

BACK_LOG=${BACK_DATA}/agent_mysql_xtrabackup.log #记录DB备份过程日志文件

XTRABACKUP_LOG=${BACK_DATA}/record_xtrabackup_status.log #记录xtrabackup备份过程的输出信息

FULLBACKUP_INTERVAL=86400 #全库备份的间隔周期，单位：秒

BACKUP_TIME=7 #数据备份时间(单位：天)

LOCKFILE='.xtrabackup.pid' #加锁

# 安装Xtrabackup服务

function Install_XtraBackup(){

if [ ! -d ${INSTALL_DIR} ];then

mkdir -p ${INSTALL_DIR}

if [ ! -f ${XTRABACKUP_BIN} ];then

echo " Install Percona Repository! " >>/dev/null 2>&1

cd ${INSTALL_DIR}

yum install -y http://www.percona.com/downloads/percona-release/redhat/0.1-6/percona-release-0.1-6.noarch.rpm >>/dev/null 2>&1

yum install -y https://dl.fedoraproject.org/pub/epel/epel-release-latest-6.noarch.rpm >>/dev/null 2>&1

yum install -y percona-xtrabackup-24 >>/dev/null 2>&1

innobackupex --help >>/dev/null 2>&1

Check=$?

if [ $Check -ne 0 ];then

echo "Install Xtrabackup Failed ！"

exit 0

}

# DB全备

function Back_All(){

if [ ! -e "${SOCKET_FILE}" ];then

exit 0

if [ ! -d ${BACK_DATA_FULLBACKUP} ];then

mkdir -p ${BACK_DATA_FULLBACKUP}

if [ ! -d ${BACK_DATA_INCREBACKUP} ];then

mkdir -p ${BACK_DATA_INCREBACKUP}

#函数开始运行的时间点，用来与全备文件做时间对比

START_TIME=`date +%s`

#查找最新的完全备份

LATEST_FULL_BACKUP=`/bin/find ${BACK_DATA_FULLBACKUP} -mindepth 1 -maxdepth 1 -type d -printf "%P\n" | sort -nr | head -1`

#查找最新全备文件的创建时间

LATEST_FULL_BACKUP_CREATED_TIME=`stat -c %Y ${BACK_DATA_FULLBACKUP}/${LATEST_FULL_BACKUP}`

#最新全备文件的创建时间与当前的时间差

FULL_TIME_DIFF=$((${START_TIME}-${LATEST_FULL_BACKUP_CREATED_TIME}))

#如果间隔小于86400秒，那么就基于全量备份做增量备份

if [ ${LATEST_FULL_BACKUP} ] && [ ${FULL_TIME_DIFF} -lt ${FULLBACKUP_INTERVAL} ];then

echo " " >> ${BACK_LOG}

echo " `date +%Y/%m/%d-%H:%M`" >> ${BACK_LOG}

echo " 基于全备目录：${BACK_DATA_FULLBACKUP}/${LATEST_FULL_BACKUP}，做增量备份！" >> ${BACK_LOG}

#调试使用1

#echo "增量备份--incremental ${BACK_DATA_INCREBACKUP}/${LATEST_INCRE_BACKUP}"

#echo "全备目录--incremental-basedir= ${BACK_DATA_FULLBACKUP}/${LATEST_FULL_BACKUP}"

${XTRABACKUP_BIN} --defaults-file=${DEFAULTS_FILE} --user=${USER} --socket=${SOCKET_FILE} --incremental ${BACK_DATA_INCREBACKUP} --incremental-basedir=${BACK_DATA_FULLBACKUP}/${LATEST_FULL_BACKUP} --parallel=2 --throttle=200 2>${XTRABACKUP_LOG} ;sleep 1

SUCESS_BACKUP=`/bin/awk -- "/Backup created in directory/ { split( \\\$0, p, \"'\" ) ; print p[2] }" ${XTRABACKUP_LOG}`

LOG_LATEST_INCRE_BACKUP=`/bin/find ${BACK_DATA_INCREBACKUP} -mindepth 1 -maxdepth 1 -type d -printf "%P\n" | sort -nr | head -1`

#查看备份日志，判断备份是否成功

if [ `/usr/bin/tail -1 ${XTRABACKUP_LOG} | grep 'completed OK!' | wc -l` -eq 1 ];then

#`grep 'incremental' ${LATEST_FULL_BACKUP}/xtrabackup_checkpoints | wc -l` -eq 1

echo " 增量备份1成功，目录地址：${BACK_DATA_INCREBACKUP}/${LOG_LATEST_INCRE_BACKUP}" >>${BACK_LOG}

else

echo " innobackupex 增量备份命令执行失败! " >>${BACK_LOG}

echo " -------------错误日志------------- " >>${BACK_LOG}

tail -10 ${XTRABACKUP_LOG} >>${BACK_LOG}

echo " ---------------------------------- " >>${BACK_LOG}

exit 1

#如果全备目录不存在，那么就重新全备一个文件

if [ ! ${LATEST_FULL_BACKUP} ] || [ ${FULL_TIME_DIFF} -gt ${FULLBACKUP_INTERVAL} ];then

echo " " >> ${BACK_LOG}

echo " `date +%Y/%m/%d-%H:%M` " >>${BACK_LOG}

echo " 正在执行全新的完全备份 ========>> " >>${BACK_LOG}

${XTRABACKUP_BIN} --defaults-file=${DEFAULTS_FILE} --user=${USER} --parallel=2 --throttle=200 ${BACK_DATA_FULLBACKUP} --socket=${SOCKET_FILE} 2>${XTRABACKUP_LOG} ;sleep 1

SUCESS_BACKUP=`/bin/awk -- "/Backup created in directory/ { split( \\\$0, p, \"'\" ) ; print p[2] }" ${XTRABACKUP_LOG}`

#查看备份日志，判断备份是否成功

if [ `/usr/bin/tail -1 ${XTRABACKUP_LOG} | grep 'completed OK!' | wc -l` -eq 1 ];then

#`grep 'full-backuped' ${LATEST_FULL_BACKUP}/xtrabackup_checkpoints | wc -l` -eq 1

echo " 数据成功备份到：${SUCESS_BACKUP} " >>${BACK_LOG}

else

echo " innobackupex 全备命令执行失败! " >>${BACK_LOG}

echo " -------------错误日志------------- " >>${BACK_LOG}

tail -10 ${XTRABACKUP_LOG} >>${BACK_LOG}

echo " ---------------------------------- " >>${BACK_LOG}

exit 1

}

#删除过期的备份文件[暂定10天]

function Delete_File(){

EXPIRED_FILE=`/bin/find /data/db_xtrabackup/ -maxdepth 1 -type d -mtime +${BACKUP_TIME} -name "20*"`

echo " `date +%Y/%m/%d-%H:%M`" >>${BACK_LOG}

echo " 检查是否有过期的文件：" >>${BACK_LOG}

if [ ${EXPIRED_FILE} ];then

echo " 找到了过期文件：${EXPIRED_FILE} " >>${BACK_LOG}

/bin/find /data/db_xtrabackup/ -maxdepth 1 -type d -mtime +${BACKUP_TIME} -name "20*" | xargs -I '{}' rm -fr {}

echo " `date +%Y/%m/%d_%H:%M`, 过期文件：${EXPIRED_FILE} 已被删除！" >>${BACK_LOG}

else

echo " 不存在过期的文件！ " >>${BACK_LOG}

}

function Main(){

touch ${LOCKFILE}

exec 57<>${LOCKFILE}

flock -xn 57

if [[ $? -eq 1 ]]; then

echo "$0 is Running, please wait"

exit 3

Install_XtraBackup

Back_All

Delete_File

exec 57>&-

}

Main

###############################################################################################################################################################

#数据恢复流程：

#1、在备份目录/data/db_backup/xtrabackup/下，找到需要恢复的文件

# 1.1、/data/db_backup/xtrabackup/2018-07-26/full #全备文件目录

# 1.2、/data/db_backup/xtrabackup/2018-07-26/incre #增量备份文件目录

#2、拷贝文件到主库机器，并检查数据的完整性

# 2.1、检查全备及增备目录下的xtrabackup_checkpoints文件

# backup_type = full-backuped

# from_lsn = 0

# to_lsn = 1639857

# last_lsn = 1639857

# compact = 0

# recover_binlog_info = 0

# 2.2、检查备份日志是否报错：

# cat agent_mysql_xtrabackup.log

#3、开始合并数据

# 3.1、全备目录上重放已提交的事务：

# innobackupex --defaults-file=/usr/local/mysql/my.cnf --user=root --socket=/tmp/mysql.sock --apply-log --redo-only /data/db_backup/xtrabackup/2018-07-26/full/2018-03-28_17-33-41/ #这里写全备文件

# 3.2、增量备份数据与全量备份数据合并，数据最终合并到全量备份目录内：

# # --redo-onl 后是全备文件

# # --incremental-dir= 指定增量备份2的目录(如果增量备份多次，那就指向最后一次增量备份的目录，这里指向的是增量备份2的目录)

# 3.3、在全备目录(增量数据+全备数据合并) 开始回滚未提交的事务：

# innobackupex --defaults-file=/usr/local/mysql/my.cnf --user=root --socket=/tmp/mysql.sock --apply-log /data/db_backup/xtrabackup/2018-07-26/full/2018-03-28_17-33-41/

#4、开始恢复数据：

# 4.1、关闭数据库

# /usr/local/mysql/bin/mysqld_multi --defaults-file=/usr/local/mysql/my.cnf stop 1

# 4.2、移除数据目录

# mv /data/mysql/ /data/mysql_bak/

# 4.3、重新创建MySQL数据目录

# mkdir -p /data/mysql/

# 4.4、将全备数据(全备+增备)恢复到MySQL

# innobackupex --defaults-file=/usr/local/mysql/my.cnf --user=root --socket=/tmp/mysql.sock --datadir=/data/mysql --copy-back /data/db_backup/xtrabackup/2018-03-28_17-33-41/

# 4.5、授权

# chown -R mysql.mysql /data/mysql

# 4.6、启动MySQL

# /usr/local/mysql/bin/mysqld_multi --defaults-file=/usr/local/mysql/my.cnf start 1

#5、检查

###############################################################################################################################################################

八、Mysqldump 与 Xtrabackup 线上数据备份对比测试

环境描述及结果展示：使用线上3台分区机器测试

MySQL版本：5.6.17

Xtrabackup版本：xtrabackup version 2.4

主机内存：32GB CPU：8核 Innodb buffer pool：128M 数据文件总大小：14GB

参数

xtrabackup

xtrabackup压缩备份

mysqldump压缩备份

CPU

11.82%

7.59%

7.33%

26.09%

17.43%

18.64%

16.57%

12.61%

12.82%

CPU IO wait

28.74%

31.79%

36.50%

21.19%

26.13%

30.10%

22.40%

17.24%

18.62%

Memory

50M

30M

310M

290M

270M

70M

50M

40M

Main processes

xtrabackup

xtrabackup&tar&gzip

mysqldump&mysqld&gzip

Backup file size

14.5 G

13 G

14.5 G

13 G

14.5 G

13 G

Time

8分钟

7分钟

22分钟

20分钟

37分钟

30分钟

28分钟

数据恢复(导入)时间

5分钟

4分钟

5分钟

4分钟

84分钟

78分钟

73分钟

结论：

Xtrabackup备份：占用的CPU与内存较低，消耗的IO较大，备份文件效率很高，数据恢复(导入)非常快。

Xtrabackup压缩备份：占用的CPU与内存较高，消耗的IO相对较大，备份文件效率较高，数据恢复(导入)非常快。

Mysqldump压缩备份时：占用的CPU与内存较少，消耗的IO较少，备份文件效率较低，数据恢复(导入)非常慢。

(责任编辑：IT)

搜索

热门标签:

MySQL数据备份与恢复之Percona XtraBackup