mysql备份-使用percona xtraback实施物理备份

1)介绍

PerconaXtraBackup(简称PXB)是Percona公司开发的一个用于MySQL数据库物理热备的备份工具,支持MySQl(Oracle)、Percona Server和MariaDB,并且全部开源,真可谓是业界良心。能够为InnoDB和XtraDB数据库执行非阻塞备份,特点如下:

1、快速、可靠的完成备份

2、备份期间不间断事务处理

3、节省磁盘空间和网络带宽

4、自动对备份文件进行验证

5、恢复快,保障在线运行时间持久性

percona-xtrabackup软件包中中包含了两个工具,一个是xtrabackup,另一个是innobackupex,innobackupex由per进行封装,在对innodb表进行备份时会自动调用xtraback工具,所以对InnoDB表做备份的实际是xtrabackup这个工具,xtrabackup也只能对innodb表做备份,这是一个专门对innodb开发的热备工具,而对myisam这样的其他引擎的表则由innobackupex来负责备份,若是全备份加增量的方案,那每次增量innobackupex工具对非innodb表都是全备份且会请求读锁。

xtrabackup对innodb表进行备份时不再只是简单复制文件,而是利用在innodb存储引擎层中的LSN(日志序列号)的新旧来识别这一数据页是否需要备份。

xtraback工具对innodb引擎完美支持真正的热备份,备份好的数据中数据文件与事务日志的文件因innodb cache等因素的存在,所以备份好的数据和事务日志中的数据往往是不一致的,所以,在做数据恢复时需要把事务日志中已提交的事务做redo,没有提交的事务做undo操作,这就是在做数据恢复时要做的准备工作,即prepare。

2)Xtrabackup备份原理

1、InnoDB的备份原理

在InnoDB内部会维护一个redo日志文件,我们也可以叫做事务日志文件。事务日志会存储每一个InnoDB表数据的记录修改。当InnoDB启动时,InnoDB会检查数据文件和事务日志,并执行两个步骤:它应用(前滚)已经提交的事务日志到数据文件,并将修改过但没有提交的数据进行回滚操作。

备份过程

Xtrabackup在启动时会记住log

sequence number(LSN),并且复制所有的数据文件。复制过程需要一些时间,所以这期间如果数据文件有改动,那么将会使数据库处于一个不同的时间点。这时,xtrabackup会运行一个后台进程,用于监视事务日志,并从事务日志复制最新的修改。Xtrabackup必须持续的做这个操作,是因为事务日志是会轮转重复的写入,并且事务日志可以被重用。所以xtrabackup自启动开始,就不停的将事务日志中每个数据文件的修改都记录下来。

准备过程

上面就是xtrabackup的备份过程。接下来是准备(prepare)过程。在这个过程中,xtrabackup使用之前复制的事务日志,对各个数据文件执行灾难恢复(就像mysql刚启动时要做的一样)。当这个过程结束后,数据库就可以做恢复还原了。

2、MyISAM的备份原理

以上的过程在xtrabackup的编译二进制程序中实现。程序innobackupex可以允许我们备份MyISAM表和frm文件从而增加了便捷和功能。Innobackupex会启动xtrabackup,直到xtrabackup复制数据文件后,然后执行FLUSH TABLES WITH READ LOCK来阻止新的写入进来并把MyISAM表数据刷到硬盘上,之后复制MyISAM数据文件,最后释放锁。

备份MyISAM和InnoDB表最终会处于一致,在准备(prepare)过程结束后,InnoDB表数据已经前滚到整个备份结束的点,而不是回滚到xtrabackup刚开始时的点。这个时间点与执行FLUSH TABLES WITH READ LOCK的时间点相同,所以myisam表数据与InnoDB表数据是同步的。类似oracle的,InnoDB的prepare过程可以称为recover(恢复),myisam的数据复制过程可以称为restore(还原)。

Xtrabackup和innobackupex这两个工具都提供了许多前文没有提到的功能特点。手册上有对各个功能都有详细的介绍。简单介绍下,这些工具提供了如流(streaming)备份,增量(incremental)备份等,通过复制数据文件,复制日志文件和提交日志到数据文件(前滚)实现了各种复合备份方式。

3)、安装

1、使用yum方式安装

配置仓库

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

检查仓库

yum list | grep percona

安装包

yum install percona-xtrabackup-24

2、使用rpm包安装

下载rpm包

wgethttps://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.4-1.el7.x86_64.rpm

安装:

yum localinstall percona-xtrabackup-24-2.4.4-1.el7.x86_64.rpm

3、安装后的目录结构


4)通信方式

2个工具之间的交互和协调是通过控制文件的创建和删除来实现的,主要文件有:

xtrabackup_suspended_1

xtrabackup_suspended_2

xtrabackup_log_copied

举个栗子,我们来看备份时xtrabackup_suspended_2是怎么来协调2个工具进程的

innobackupex在启动xtrabackup进程后,会一直等xtrabackup备份完InnoDB文件,方式就是等待xtrabackup_suspended_2这个文件被创建出来;

xtrabackup在备完InnoDB数据后,就在指定目录下创建出这个文件,然后等这个文件被innobackupex删除;

innobackupex检测到文件xtrabackup_suspended_2被创建出来后,就继续往下走;

innobackupex在备份完非InnoDB表后,删除xtrabackup_suspended_2这个文件,这样就通知xtrabackup可以继续了,然后等xtrabackup_log_copied被创建;

xtrabackup检测到xtrabackup_suspended_2文件删除后,就可以继续往下了。

5)备份过程


innobackupex在启动后,会先fork一个进程,启动xtrabackup进程,然后就等待xtrabackup备份完ibd数据文件;

xtrabackup在备份InnoDB相关数据时,是有2种线程的,1种是redo拷贝线程,负责拷贝redo文件,1种是ibd拷贝线程,负责拷贝ibd文件;redo拷贝线程只有一个,在ibd拷贝线程之前启动,在ibd线程结束后结束。xtrabackup进程开始执行后,先启动redo拷贝线程,从最新的checkpoint点开始顺序拷贝redo日志;然后再启动ibd数据拷贝线程,在xtrabackup拷贝ibd过程中,innobackupex进程一直处于等待状态(等待文件被创建)。

xtrabackup拷贝完成idb后,通知innobackupex(通过创建文件),同时自己进入等待(redo线程仍然继续拷贝);

innobackupex收到xtrabackup通知后,执行FLUSH TABLES WITH READ LOCK (FTWRL),取得一致性位点,然后开始备份非InnoDB文件(包括frm、MYD、MYI、CSV、opt、par等)。拷贝非InnoDB文件过程中,因为数据库处于全局只读状态,如果在业务的主库备份的话,要特别小心,非InnoDB表(主要是MyISAM)比较多的话整库只读时间就会比较长,这个影响一定要评估到。

当innobackupex拷贝完所有非InnoDB表文件后,通知xtrabackup(通过删文件),同时自己进入等待(等待另一个文件被创建);

xtrabackup收到innobackupex备份完非InnoDB通知后,就停止redo拷贝线程,然后通知innobackupex redo log拷贝完成(通过创建文件);

innobackupex收到redo备份完成通知后,就开始解锁,执行UNLOCK TABLES;

最后innobackupex和xtrabackup进程各自完成收尾工作,如资源的释放、写备份元数据信息等,innobackupex等待xtrabackup子进程结束后退出。

在上面描述的文件拷贝,都是备份进程直接通过操作系统读取数据文件的,只在执行SQL命令时和数据库有交互,基本不影响数据库的运行,在备份非InnoDB时会有一段时间只读(如果没有MyISAM表的话,只读时间在几秒左右),在备份InnoDB数据文件时,对数据库完全没有影响,是真正的热备。

InnoDB和非InnoDB文件的备份都是通过拷贝文件来做的,但是实现的方式不同,前者是以page为粒度做的(xtrabackup),后者是cp或者tar命令(innobackupex),xtrabackup在读取每个page时会校验checksum值,保证数据块是一致的,而innobackupex在cp MyISAM文件时已经做了flush(FTWRL),磁盘上的文件也是完整的,所以最终备份集里的数据文件都是写入完整的。

6)增量备份

PXB是支持增量备份的,但是只能对InnoDB做增量,InnoDB每个page有个LSN号,LSN是全局递增的,page被更改时会记录当前的LSN号,page中的LSN越大,说明当前page越新(最近被更新)。每次备份会记录当前备份到的LSN(xtrabackup_checkpoints文件中),增量备份就是只拷贝LSN大于上次备份的page,比上次备份小的跳过,每个ibd文件最终备份出来的是增量delta文件。

MyISAM是没有增量的机制的,每次增量备份都是全部拷贝的。

增量备份过程和全量备份一样,只是在ibd文件拷贝上有不同。

7)恢复过程

如果看恢复备份集的日志,会发现和mysqld启动时非常相似,其实备份集的恢复就是类似mysqld crash后,做一次crash recover。

恢复的目的是把备份集中的数据恢复到一个一致性位点,所谓一致就是指原数据库某一时间点各引擎数据的状态,比如MyISAM中的数据对应的是15:00时间点的,InnoDB中的数据对应的是15:20的,这种状态的数据就是不一致的。PXB备份集对应的一致点,就是备份时FTWRL的时间点,恢复出来的数据,就对应原数据库FTWRL时的状态。

因为备份时FTWRL后,数据库是处于只读的,非InnoDB数据是在持有全局读锁情况下拷贝的,所以非InnoDB数据本身就对应FTWRL时间点;InnoDB的ibd文件拷贝是在FTWRL前做的,拷贝出来的不同ibd文件最后更新时间点是不一样的,这种状态的ibd文件是不能直接用的,但是redo log是从备份开始一直持续拷贝的,最后的redo日志点是在持有FTWRL后取得的,所以最终通过redo应用后的ibd数据时间点也是和FTWRL一致的。

所以恢复过程只涉及InnoDB文件的恢复,非InnoDB数据是不动的。备份恢复完成后,就可以把数据文件拷贝到对应的目录,然后通过mysqld来启动了

8)备份实例

1、全量备份

[root@linux-node2 backups]#xtrabackup --backup

--target-dir=/data/backups/$(date +%F) -uroot -p123456

170427 10:46:41version_check Connecting to MySQL server withDSN'dbi:mysql:;mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/usr/local/mysql/data/mysqld.sock'as 'root'(using password: YES).

170427 10:46:41version_check Connected to MySQL server

170427 10:46:41version_check Executing a version checkagainst the server...

170427 10:46:41version_check Done.

170427 10:46:41 Connecting to MySQL serverhost: localhost, user: root, password: set, port: 3306, socket:/usr/local/mysql/data/mysqld.sock

Using server version 5.6.35-log

xtrabackup version 2.4.7 based on MySQLserver 5.7.13 Linux (x86_64) (revision id: 6f7a799)

xtrabackup: uses posix_fadvise().

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

xtrabackup: open files limit requested 65535,set to 1024000

xtrabackup: using the following InnoDBconfiguration:

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 = 536870912

InnoDB: Number of pools: 1

170427 10:46:41 >> log scanned up to(1743474)

xtrabackup: Generating a list of tablespaces

InnoDB: Allocated tablespace ID 15 formysql/slave_master_info, old maximum was 0

170427 10:46:41 [01] Copying ./ibdata1 to/data/backups/2017-04-27/ibdata1

170427 10:46:41 [01]...done

170427 10:46:41 [01] Copying./mysql/slave_master_info.ibd to/data/backups/2017-04-27/mysql/slave_master_info.ibd

170427 10:46:41 [01]...done

170427 10:46:41 [01] Copying./mysql/slave_relay_log_info.ibd to/data/backups/2017-04-27/mysql/slave_relay_log_info.ibd

170427 10:46:41 [01]...done

…………

170427 10:46:42 [01] Copying ./test/db.opt to/data/backups/2017-04-27/test/db.opt

170427 10:46:42 [01]...done

170427 10:46:42 Finished backing upnon-InnoDB tables and files

170427 10:46:42 [00] Writingxtrabackup_binlog_info

170427 10:46:42 [00]...done

170427 10:46:42 Executing FLUSHNO_WRITE_TO_BINLOG ENGINE LOGS...

xtrabackup: The latest check point (forincremental): '1743474'

xtrabackup: Stopping log copying thread.

.170427 10:46:42 >> log scanned up to(1743474)

170427 10:46:43 Executing UNLOCK TABLES

170427 10:46:43 All tables unlocked

170427 10:46:43 Backup created in directory'/data/backups/2017-04-27/'

MySQL binlog position: filename'mysql-bin.000004', position '191', GTID of the last change'0e9896a7-14f7-11e7-a0e6-000c2900551e:1-3'

170427 10:46:43 [00] Writing backup-my.cnf

170427 10:46:43 [00]...done

170427 10:46:43 [00] Writing xtrabackup_info

170427 10:46:43 [00]...done

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

170427 10:46:43 completed OK!#表示备份成功。

[root@linux-node2 backups]#

备份成功后,备份目录的结构如下:


2、增量备份

[root@linux-node2 backups]# xtrabackup--backup --target-dir=/data/backups/increment/$(date +%F-%H-%M-%S)--incremental-basedir=/data/backups/2017-04-27/ -uroot -p123456

170427 14:41:49version_check Connecting to MySQL server withDSN'dbi:mysql:;mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/usr/local/mysql/data/mysqld.sock'as 'root'(using password: YES).

170427 14:41:49version_check Connected to MySQL server

170427 14:41:49version_check Executing a version checkagainst the server...

170427 14:41:49version_check Done.

170427 14:41:49 Connecting to MySQL serverhost: localhost, user: root, password: set, port: 3306, socket:/usr/local/mysql/data/mysqld.sock

Using server version 5.6.35-log

xtrabackup version 2.4.7 based on MySQLserver 5.7.13 Linux (x86_64) (revision id: 6f7a799)

incremental backup from 1743474 is enabled.

xtrabackup: uses posix_fadvise().

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

xtrabackup: open files limit requested 65535,set to 1024000

xtrabackup: using the following InnoDBconfiguration:

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 = 536870912

InnoDB: Number of pools: 1

170427 14:41:49 >> log scanned up to(1752524)

xtrabackup: Generating a list of tablespaces

InnoDB: Allocated tablespace ID 15 formysql/slave_master_info, old maximum was 0

xtrabackup: using the full scan forincremental backup

170427 14:41:49 [01] Copying ./ibdata1 to/data/backups/increment/2017-04-27-14-41-49/ibdata1.delta

170427 14:41:49 [01]...done

170427 14:41:49 [01] Copying./mysql/slave_master_info.ibd to /data/backups/increment/2017-04-27-14-41-49/mysql/slave_master_info.ibd.delta

170427 14:41:49 [01]...done

170427 14:41:49 [01] Copying./mysql/slave_relay_log_info.ibd to/data/backups/increment/2017-04-27-14-41-49/mysql/slave_relay_log_info.ibd.delta

…………

170427 14:41:50 [01] Copying./incrememtal1/t1.frm to/data/backups/increment/2017-04-27-14-41-49/incrememtal1/t1.frm

170427 14:41:50 [01]...done

170427 14:41:50 Finished backing upnon-InnoDB tables and files

170427 14:41:50 [00] Writing xtrabackup_binlog_info

170427 14:41:50 [00]...done

170427 14:41:50 Executing FLUSHNO_WRITE_TO_BINLOG ENGINE LOGS...

xtrabackup: The latest check point (forincremental): '1752524'

xtrabackup: Stopping log copying thread.

.170427 14:41:50 >> log scanned up to(1752524)

170427 14:41:50 Executing UNLOCK TABLES

170427 14:41:50 All tables unlocked

170427 14:41:50 Backup created in directory'/data/backups/increment/2017-04-27-14-41-49/'

MySQL binlog position: filename'mysql-bin.000004', position '525', GTID of the last change'0e9896a7-14f7-11e7-a0e6-000c2900551e:1-5'

170427 14:41:50 [00] Writing backup-my.cnf

170427 14:41:50 [00]...done

170427 14:41:50 [00] Writing xtrabackup_info

170427 14:41:50 [00]...done

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

170427 14:41:50 completed OK!

备份成功后的目录结构:


查看xtrabackup_checkpoints文件,显示备份是从lsn

1743474到lsn 1752524,通过查看上一次全量备份目录的xtrabackup_checkpoints文件显示,最后一个lsn是1743474。

[root@linux-node22017-04-27-14-41-49]# cat xtrabackup_checkpoints

backup_type = incremental

from_lsn = 1743474

to_lsn = 1752524

last_lsn = 1752524

compact = 0

recover_binlog_info = 0

[root@linux-node2 2017-04-27-14-41-49]#

3、再次增量备份(基于上一次增量备份)

[root@linux-node2 increment]# xtrabackup--backup --target-dir=/data/backups/increment/$(date +%F-%H-%M-%S)--incremental-basedir=/data/backups/increment/2017-04-27-14-41-49/ -uroot-p123456

170427 15:39:26version_check Connecting to MySQL server withDSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/usr/local/mysql/data/mysqld.sock'as 'root'(using password: YES).

170427 15:39:26version_check Connected to MySQL server

170427 15:39:26version_check Executing a version checkagainst the server...

170427 15:39:26version_check Done.

170427 15:39:26 Connecting to MySQL serverhost: localhost, user: root, password: set, port: 3306, socket:/usr/local/mysql/data/mysqld.sock

Using server version 5.6.35-log

xtrabackup version 2.4.7 based on MySQLserver 5.7.13 Linux (x86_64) (revision id: 6f7a799)

incremental backup from 1752524 is enabled.

xtrabackup: uses posix_fadvise().

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

xtrabackup: open files limit requested 65535,set to 1024000

xtrabackup: using the following InnoDBconfiguration:

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 = 536870912

InnoDB: Number of pools: 1

170427 15:39:26 >> log scanned up to(1758973)

xtrabackup: Generating a list of tablespaces

InnoDB: Allocated tablespace ID 15 formysql/slave_master_info, old maximum was 0

xtrabackup: using the full scan forincremental backup

170427 15:39:26 [01] Copying ./ibdata1 to/data/backups/increment/2017-04-27-15-39-25/ibdata1.delta

170427 15:39:26 [01]...done

……

170427 15:39:26 [01] Copying./incrememtal2/t1.ibd to/data/backups/increment/2017-04-27-15-39-25/incrememtal2/t1.ibd.delta

170427 15:39:26 [01]...done

170427 15:39:27 >> log scanned up to(1758973)

170427 15:39:27 Executing FLUSHNO_WRITE_TO_BINLOG TABLES...

170427 15:39:27 Executing FLUSH TABLES WITHREAD LOCK...

170427 15:39:27 Starting to backup non-InnoDBtables and files

170427 15:39:27 [01] Copying./mysql/servers.frm to/data/backups/increment/2017-04-27-15-39-25/mysql/servers.frm

170427 15:39:27 [01]...done

……

170427 15:39:27 [01] Copying./incrememtal2/t1.frm to /data/backups/increment/2017-04-27-15-39-25/incrememtal2/t1.frm

170427 15:39:27 [01]...done

170427 15:39:27 Finished backing upnon-InnoDB tables and files

170427 15:39:27 [00] Writingxtrabackup_binlog_info

170427 15:39:27 [00]...done

170427 15:39:27 Executing FLUSH NO_WRITE_TO_BINLOGENGINE LOGS...

xtrabackup: The latest check point (forincremental): '1758973'

xtrabackup: Stopping log copying thread.

.170427 15:39:27 >> log scanned up to(1758973)

170427 15:39:27 Executing UNLOCK TABLES

170427 15:39:27 All tables unlocked

170427 15:39:27 Backup created in directory'/data/backups/increment/2017-04-27-15-39-25/'

MySQL binlog position: filename'mysql-bin.000004', position '859', GTID of the last change'0e9896a7-14f7-11e7-a0e6-000c2900551e:1-7'

170427 15:39:27 [00] Writing backup-my.cnf

170427 15:39:27 [00]...done

170427 15:39:27 [00] Writing xtrabackup_info

170427 15:39:27 [00]...done

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

170427 15:39:27 completed OK!

查看备份完成后的目录结构:


4、准备恢复(preparing a backup)-基于全量备份

在在使用备份文件恢复数据之前,你需要对备份的数据进行整理。具体原因在前面的内容已经介绍过,我们先看使用完全备份恢复

[root@linux-node2 2017-04-27-15-39-25]#xtrabackup --prepare --target-dir=/data/backups/2017-04-27/

xtrabackup version 2.4.7 based on MySQLserver 5.7.13 Linux (x86_64) (revision id: 6f7a799)

xtrabackup: cd to /data/backups/2017-04-27/

xtrabackup: This target seems to be notprepared yet.

InnoDB: Number of pools: 1

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

xtrabackup: using the following InnoDBconfiguration for recovery:

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 = 1

xtrabackup:innodb_log_file_size = 8388608

xtrabackup: using the following InnoDBconfiguration for recovery:

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 = 1

xtrabackup:innodb_log_file_size = 8388608

xtrabackup: Starting InnoDB instance forrecovery.

xtrabackup: Using 104857600 bytes for bufferpool (set by --use-memory parameter)

InnoDB: PUNCH HOLE support available

InnoDB: Mutexes and rw_locks use GCC atomicbuiltins

InnoDB: Uses event mutexes

InnoDB: GCC builtin __atomic_thread_fence()is used for memory barrier

InnoDB: Compressed tables use zlib 1.2.7

InnoDB: Number of pools: 1

InnoDB: Using CPU crc32 instructions

InnoDB: Initializing buffer pool, total size= 100M, instances = 1, chunk size = 100M

InnoDB: Completed initialization of bufferpool

InnoDB: page_cleaner coordinator priority:-20

InnoDB: Highest supported file format isBarracuda.

InnoDB: The log sequence number 1732944 in thesystem tablespace does not match the log sequence number 1743474 in theib_logfiles!

InnoDB: Database was not shutdown normally!

InnoDB: Starting crash recovery.

InnoDB: Doing recovery: scanned up to logsequence number 1743474 (0%)

InnoDB: xtrabackup: Last MySQL binlog fileposition 1169, file name master-bin.000002

InnoDB: Creating shared tablespace fortemporary tables

InnoDB: Setting file './ibtmp1' size to 12MB. Physically writing the file full; Please wait ...

InnoDB: File './ibtmp1' size is now 12 MB.

InnoDB: 96 redo rollback segment(s) found. 1redo rollback segment(s) are active.

InnoDB: 32 non-redo rollback segment(s) areactive.

InnoDB: Waiting for purge to start

InnoDB: 5.7.13 started; log sequence number1743474

InnoDB: xtrabackup: Last MySQL binlog fileposition 1169, file name master-bin.000002

xtrabackup: starting shutdown withinnodb_fast_shutdown = 1

InnoDB: FTS optimize thread exiting.

InnoDB: Starting shutdown...

InnoDB: Shutdown completed; log sequencenumber 1743493

InnoDB: Number of pools: 1

xtrabackup: using the following InnoDBconfiguration for recovery:

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 = 536870912

InnoDB: PUNCH HOLE support available

InnoDB: Mutexes and rw_locks use GCC atomicbuiltins

InnoDB: Uses event mutexes

InnoDB: GCC builtin __atomic_thread_fence()is used for memory barrier

InnoDB: Compressed tables use zlib 1.2.7

InnoDB: Number of pools: 1

InnoDB: Using CPU crc32 instructions

InnoDB: Initializing buffer pool, total size= 100M, instances = 1, chunk size = 100M

InnoDB: Completed initialization of bufferpool

InnoDB: page_cleaner coordinator priority:-20

InnoDB: Setting log file ./ib_logfile101 sizeto 512 MB

InnoDB: Progress in MB:

100200 300 400 500

InnoDB: Setting log file ./ib_logfile1 sizeto 512 MB

InnoDB: Progress in MB:

100200 300 400 500

InnoDB: Renaming log file ./ib_logfile101 to./ib_logfile0

InnoDB: New log files created, LSN=1743493

InnoDB: Highest supported file format isBarracuda.

InnoDB: Log scan progressed past thecheckpoint lsn 1743884

InnoDB: Doing recovery: scanned up to logsequence number 1743893 (0%)

InnoDB: Doing recovery: scanned up to logsequence number 1743893 (0%)

InnoDB: Database was not shutdown normally!

InnoDB: Starting crash recovery.

InnoDB: xtrabackup: Last MySQL binlog fileposition 1169, file name master-bin.000002

InnoDB: Removed temporary tablespace datafile: "ibtmp1"

InnoDB: Creating shared tablespace fortemporary tables

InnoDB: Setting file './ibtmp1' size to 12MB. Physically writing the file full; Please wait ...

InnoDB: File './ibtmp1' size is now 12 MB.

InnoDB: 96 redo rollback segment(s) found. 1redo rollback segment(s) are active.

InnoDB: 32 non-redo rollback segment(s) areactive.

InnoDB: Waiting for purge to start

InnoDB: 5.7.13 started; log sequence number1743893

xtrabackup: starting shutdown withinnodb_fast_shutdown = 1

InnoDB: FTS optimize thread exiting.

InnoDB: Starting shutdown...

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

InnoDB: Shutdown completed; log sequencenumber 1743912

170427 15:52:06 completed OK!

如果已经进行过数据整理,再次运行会有如下提示信息:

xtrabackup: This target seems to be alreadyprepared.

xtrabackup: notice: xtrabackup_logfile wasalready used to '--prepare'.

5、恢复备份(基于全量)

首先我们需要所整理完数据后的备份目录内的文件复制到mysql的数据目录,复制的方法很多,可以使用xtrabackup –copy-back选项,或者—move-back选项,当然我们也可以使用rsync或者cp等命令。(注意在操作前应该先停止mysql服务,然后清空数据目录)

[root@linux-node2 mysql]# mysqladmin -uroot-p123456 shutdown

Warning: Using a password on the command lineinterface can be insecure.

[root@linux-node2 mysql]# 170427 16:11:27mysqld_safe mysqld from pid file /usr/local/mysql/data/db01.pid ended

[1]+Done/usr/local/mysql/bin/mysqld_safe(wd: ~)

(wd now: /usr/local/mysql)

[root@linux-node2 mysql]# mv /usr/local/mysql/data/tmp/

完成后使用xtraback恢复数据

[root@linux-node2 mysql]# xtrabackup--copy-back --target-dir=/data/backups/2017-04-27/

xtrabackup version 2.4.7 based on MySQLserver 5.7.13 Linux (x86_64) (revision id: 6f7a799)

170427 16:13:43 [01] Copying ib_logfile0 to /usr/local/mysql/data/ib_logfile0

170427 16:13:46 [01]...done

170427 16:13:46 [01] Copying ib_logfile1 to/usr/local/mysql/data/ib_logfile1

170427 16:13:48 [01]...done

…………

170427 16:13:49 [01]...done

170427 16:13:49 [01] Copying ./xtrabackup_infoto /usr/local/mysql/data/xtrabackup_info

170427 16:13:49 [01]...done

170427 16:13:49 [01] Copying./xtrabackup_binlog_pos_innodb to/usr/local/mysql/data/xtrabackup_binlog_pos_innodb

170427 16:13:49 [01]...done

170427 16:13:49 [01] Copying ./ibtmp1 to/usr/local/mysql/data/ibtmp1

170427 16:13:49 [01]...done

170427 16:13:49 completed OK!

对恢复后的文件授权:

[root@linux-node2

mysql]# chown -R mysql.mysql /usr/local/mysql/data/

最后重启启动mysql服务

[root@linux-node2data]# Logging to '/usr/local/mysql/data/mysql-error.log'.

17042716:15:08 mysqld_safe Starting mysqld daemon with databases from/usr/local/mysql/data

[root@linux-node2data]# lsof -i :3306

COMMANDPIDUSERFDTYPE DEVICE SIZE/OFF NODE NAME

mysqld4116 mysql13uIPv6260840t0TCP *:mysql (LISTEN)

[root@linux-node2data]# mysql -uroot -p

Enterpassword:

Welcome tothe MySQL monitor.Commands end with ;or \g.

Your MySQLconnection id is 1

Serverversion: 5.6.35-log MySQL Community Server (GPL)

Copyright(c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle isa registered trademark of Oracle Corporation and/or its

affiliates.Other names may be trademarks of their respective

owners.

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

mysql>show databases;

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

|Database|

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

|information_schema |

|mysql|

|performance_schema |

|test|

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

4 rows inset (0.00 sec)

6、基于增量备份进行恢复

我们现在的备份情况如下:

/data/backups/2017-04-27全备

[root@linux-node2 backups]# cat 2017-04-27/xtrabackup_checkpoints

backup_type = full-backuped

from_lsn = 0

to_lsn = 1780797

last_lsn = 1780797

compact = 0

recover_binlog_info = 0

/data/backups/increment/2017-04-27-16-51-43第一次增备

[root@linux-node2 backups]# catincrement/2017-04-27-16-51-43/xtrabackup_checkpoints

backup_type = incremental

from_lsn = 1780797

to_lsn = 1788921

last_lsn = 1788921

compact = 0

recover_binlog_info = 0

/data/backups/increment/2017-04-27-16-52-18第二次增备

[root@linux-node2 backups]# catincrement/2017-04-27-16-52-18/xtrabackup_checkpoints

backup_type = incremental

from_lsn = 1788921

to_lsn = 1796758

last_lsn = 1796758

compact = 0

recover_binlog_info = 0

恢复过程如下:

使用全备进行恢复准备操作

[root@linux-node2 backups]# xtrabackup--prepare --apply-log-only --target-dir=/data/backups/2017-04-27/

xtrabackup version 2.4.7 based on MySQLserver 5.7.13 Linux (x86_64) (revision id: 6f7a799)

xtrabackup: cd to /data/backups/2017-04-27/

xtrabackup: This target seems to be notprepared yet.

InnoDB: Number of pools: 1

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

xtrabackup: using the following InnoDBconfiguration for recovery:

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 = 1

xtrabackup:innodb_log_file_size = 8388608

xtrabackup: using the following InnoDBconfiguration for recovery:

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 = 1

xtrabackup:innodb_log_file_size = 8388608

xtrabackup: Starting InnoDB instance forrecovery.

xtrabackup: Using 104857600 bytes for bufferpool (set by --use-memory parameter)

InnoDB: PUNCH HOLE support available

InnoDB: Mutexes and rw_locks use GCC atomicbuiltins

InnoDB: Uses event mutexes

InnoDB: GCC builtin __atomic_thread_fence()is used for memory barrier

InnoDB: Compressed tables use zlib 1.2.7

InnoDB: Number of pools: 1

InnoDB: Using CPU crc32 instructions

InnoDB: Initializing buffer pool, total size= 100M, instances = 1, chunk size = 100M

InnoDB: Completed initialization of bufferpool

InnoDB: page_cleaner coordinator priority:-20

InnoDB: Highest supported file format isBarracuda.

InnoDB: The log sequence number 1761000 inthe system tablespace does not match the log sequence number 1780797 in theib_logfiles!

InnoDB: Database was not shutdown normally!

InnoDB: Starting crash recovery.

InnoDB: Doing recovery: scanned up to logsequence number 1780797 (0%)

InnoDB: xtrabackup: Last MySQL binlog fileposition 1169, file name master-bin.000002

InnoDB: xtrabackup: Last MySQL binlog fileposition 1169, file name master-bin.000002

xtrabackup: starting shutdown withinnodb_fast_shutdown = 1

InnoDB: Starting shutdown...

InnoDB: Shutdown completed; log sequencenumber 1780806

InnoDB: Number of pools: 1

170427 16:55:24 completed OK!

合并第一次增备

[root@linux-node2 backups]# xtrabackup--prepare --apply-log-only --target-dir=/data/backups/2017-04-27/--incremental-dir=/data/backups/increment/2017-04-27-16-51-43/

合并第二次增备:

[root@linux-node2 backups]# xtrabackup--prepare --target-dir=/data/backups/2017-04-27/ --incremental-dir=/data/backups/increment/2017-04-27-16-52-18/

合并完成后,使用上面第五步的方法恢复数据库。

9)压缩备份(compressed backup)

XtraBackup支持压缩备份

1、创建Compressed Backups

[root@linux-node2 backups]# xtrabackup--backup --compress --target-dir=/data/backups/compressed/$(date +%F) -uroot-p123456

170427 17:28:03version_check Connecting to MySQL server withDSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/usr/local/mysql/data/mysqld.sock'as 'root'(using password: YES).

170427 17:28:03version_check Connected to MySQL server

170427 17:28:03version_check Executing a version checkagainst the server...

170427 17:28:03version_check Done.

170427 17:28:03 Connecting to MySQL serverhost: localhost, user: root, password: set, port: 3306, socket:/usr/local/mysql/data/mysqld.sock

Using server version 5.6.35-log

xtrabackup version 2.4.7 based on MySQLserver 5.7.13 Linux (x86_64) (revision id: 6f7a799)

xtrabackup: uses posix_fadvise().

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

xtrabackup: open files limit requested 65535,set to 1024000

xtrabackup: using the following InnoDBconfiguration:

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 = 536870912

InnoDB: Number of pools: 1

170427 17:28:03 >> log scanned up to(1796758)

xtrabackup: Generating a list of tablespaces

InnoDB: Allocated tablespace ID 15 formysql/slave_master_info, old maximum was 0

170427 17:28:04 [01] Compressing ./ibdata1 to/data/backups/compressed/2017-04-27/ibdata1.qp

170427 17:28:04 [01]...done

…………

170427 17:28:05 [01] Compressing./inc2/t1.frm to /data/backups/compressed/2017-04-27/inc2/t1.frm.qp

170427 17:28:05 [01]...done

170427 17:28:05 Finished backing upnon-InnoDB tables and files

170427 17:28:05 [00] Compressingxtrabackup_binlog_info

170427 17:28:05 [00]...done

170427 17:28:05 Executing FLUSHNO_WRITE_TO_BINLOG ENGINE LOGS...

xtrabackup: The latest check point (forincremental): '1796758'

xtrabackup: Stopping log copying thread.

.170427 17:28:05 >> log scanned up to(1796758)

170427 17:28:05 Executing UNLOCK TABLES

170427 17:28:05 All tables unlocked

170427 17:28:05 Backup created in directory'/data/backups/compressed/2017-04-27/'

MySQL binlog position: filename'mysql-bin.000005', position '1761', GTID of the last change '0e9896a7-14f7-11e7-a0e6-000c2900551e:1-17'

170427 17:28:05 [00] Compressingbackup-my.cnf

170427 17:28:05 [00]...done

170427 17:28:05 [00] Compressingxtrabackup_info

170427 17:28:05 [00]...done

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

170427 17:28:05 completed OK!

备份完成后目录结构如下

[root@linux-node2 backups]# llcompressed/2017-04-27/

total 248

-rw-r----- 1 root root400 Apr 27 17:28 backup-my.cnf.qp

-rw-r----- 1 root root 223722 Apr 27 17:28ibdata1.qp

drwxr-x--- 2 root root54 Apr 27 17:28 inc1

drwxr-x--- 2 root root54 Apr 27 17:28 inc2

drwxr-x--- 2 root root54 Apr 27 17:28 incrememtal1

drwxr-x--- 2 root root54 Apr 27 17:28 incrememtal2

drwxr-x--- 2 root root4096 Apr 27 17:28 mysql

drwxr-x--- 2 root root4096 Apr 27 17:28 performance_schema

drwxr-x--- 2 root root22 Apr 27 17:28 test

-rw-r----- 1 root root152 Apr 27 17:28 xtrabackup_binlog_info.qp

-rw-r----- 1 root root113 Apr 27 17:28 xtrabackup_checkpoints

-rw-r----- 1 root root560 Apr 27 17:28 xtrabackup_info.qp

-rw-r----- 1 root root381 Apr 27 17:28 xtrabackup_logfile.qp

[root@linux-node2 backups]#

2、恢复准备preparing(数据整理)

准备整理数据前需要先解压备份数据,使用—decompress选项,如果出现如下错误,请安装qpress,可以使用yum安装。

[root@linux-node2 backups]# xtrabackup--decompress --target-dir=/data/backups/compressed/2017-04-27/

xtrabackup version 2.4.7 based on MySQLserver 5.7.13 Linux (x86_64) (revision id: 6f7a799)

170427 17:34:54 [01] decompressing./xtrabackup_logfile.qp

sh: qpress: command not found

cat: write error: Broken pipe

Error: thread 0 failed.

[root@linux-node2 backups]# xtrabackup --decompress--target-dir=/data/backups/compressed/2017-04-27/

xtrabackup version 2.4.7 based on MySQL server 5.7.13 Linux (x86_64)(revision id: 6f7a799)

170427 17:35:45 [01] decompressing ./xtrabackup_logfile.qp

170427 17:35:45 [01] decompressing ./ibdata1.qp

…………

170427 17:35:46 [01] decompressing ./xtrabackup_binlog_info.qp

170427 17:35:46 [01] decompressing ./backup-my.cnf.qp

170427 17:35:46 [01] decompressing ./xtrabackup_info.qp

170427 17:35:46 completed OK!

解压后再使用前面实例中的第4,5步操作,完成数据恢复。�r�?�6R

推荐阅读更多精彩内容