mysql innodb xtrabackup_xtrabackup 实现MySQL数据库备份

[root@mysql~]#innobackupex--defaults-file=/etc/my.cnf--user=root--password=123/opt/mysqlbackup/full/

注：--defaults-file=/etc/my.cnf 指定mysql的配置文件my.cfg，如果指定则必须是第一个参数。/path/to/BACKUP-DIR/指定备份所存放的目标目录，备份过程会创建一个以当时备份时间命名的目录存放备份 文件。

出现如下图所示，则表示备份成功：

在备份的同时，备份数据会在备份目录下创建一个以当前日期时间为名字的目录存放备份文件：

[root@mysql~]#cd/opt/mysqlbackup/full/[root@mysql full]#lltotal0drwxr-x---6root root206Jun1522:352020-06-15_22-35-02[root@mysql full]#ll2020-06-15_22-35-02/total12336-rw-r-----1root root425Jun1522:35backup-my.cnf-rw-r-----1root root338Jun1522:35ib_buffer_pool-rw-r-----1root root12582912Jun1522:35ibdata1drwxr-x---2root root4096Jun1522:35mysqldrwxr-x---2root root8192Jun1522:35performance_schemadrwxr-x---2root root8192Jun1522:35sysdrwxr-x---2root root50Jun1522:35test1-rw-r-----1root root113Jun1522:35xtrabackup_checkpoints-rw-r-----1root root459Jun1522:35xtrabackup_info-rw-r-----1root root2560Jun1522:35xtrabackup_logfile

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

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

注意：相关选项说明：其中，--user指定连接数据库的用户名，--password指定连接数据库的密码，-defaults-file指定数据库的配置文件，innobackupex要从其中获取datadir等信息；--database指定要备份的数据库，这里指定的数据库只对MyISAM表有效，对于InnoDB数据来说都是全备(所有数据库中的InnoDB数据都进行了备份，不是只备份指定的数据库，恢复时也一样)；/opt/mysqlbackup/full是备份文件的存放位置。

注：备份数据库的用户需要具有相应权限，如果要使用一个最小权限的用户进行备份，则可基于如下 命令创建此类用户：

#创建用户mysql>create user'bkpuser'@'localhost'identifiedby'123456';#回收用户所有权限mysql>revoke all privileges,grant optionfrom'bkpuser'@'localhost';#授权有关备份的相应权限mysql>grant reload,locktables,replication client,process on*.*to'bkpuser'@'localhost';mysql>flush privileges;

至此全备完全成功，然后向mysql某个库插入几条数据，然后进行增量备份 对完全备份的后数据库更改进行

2.进制日志增量备份

#查看完全备份时binlog日志位置[root@mysql full]#cat/opt/mysqlbackup/full/2020-06-15_22-49-41/xtrabackup_binlog_infobin_log.000001154#模拟数据库修改：mysql>select*fromtb1;+------+----------+--------+|id|name|back||------+----------+--------||1|zhangsan|w||2|lisi|q||3|wangwu|e||4|zhaoliu|r||5|liuyi|t||6|didi|y|+------+----------+--------+6rowsinsetmysql>insertintotb1 values(7,'tom','u');mysql>insertintotb1 values(8,'tom2','i');

[root@mysql data]#pwd/usr/local/mysql/data[root@mysql data]#mysqlbinlog-v bin_log.000001# 可以通过mysqlbinlog二进制日志号[root@mysql data]#cat/opt/mysqlbackup/full/2020-06-15_22-49-41/xtrabackup_binlog_infobin_log.000001154# 后面这个154 就是开始#由于我全备之后还做了一些操作，所以我这里并不是154#增量备份二进制文件[root@mysql data]#mysqlbinlog--start-position=219/usr/local/mysql/data/bin_log.000001>/opt/mysqlbackup/inc/`date +%F`.sql[root@mysql data]#ls/opt/mysqlbackup/inc/2020-06-15.sql

3.还原数据库

模拟数据库损坏， 我这里直接使用删除数据目录文件来模拟损坏。

[root@mysql~]#rm-rf/usr/local/mysql/data/*

还原完全备份：

准备(prepare)一个完全备份 一般情况下，在备份完成后，数据尚且不能用于恢复操作， 因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文 件仍处理不一致状态。“准备”的主要作用正是通过回滚未提交的事务及同步已经提交的事务至数据文件也使得 数据文件处于一致性状态。在准备(prepare)过程结束后，InnoDB表数据已经前滚到整个备份结束的点，而 不是回滚到xtrabackup刚开始时的点。

[root@mysql~]#innobackupex--apply-log/opt/mysqlbackup/full/2020-06-15_22-49-41/

注：/opt/mysqlbackup/full/2020-06-15_22-49-41/ 备份文件所在目录名称 如果执行正确，其最后输出的几行信 息通常如下：

在实现“准备”的过程中，innobackupex通常还可以使用--use-memory选项来指定其可以使用的内存的大小，默 认通常为100M。如果有足够的内存可用，可以多划分一些内存给prepare的过程，以提高其完成速度。innobackupex命令的--copy-back选项用于执行恢复操作，其通过复制所有数据相关的文件至mysql服务器 DATADIR目录中来执行恢复过程。innobackupex通过backup-my.cnf来获取DATADIR目录的相关信息。

还原数据库语法：

[root@mysql~]#innobackupex--copy-back/opt/mysqlbackup/full/2020-06-15_22-49-41/

这里的--copy-back指明是进行数据恢复。数据恢复完成之后，需要修改相关文件的权限mysql数据库才能正常启动。如果执行正确，其输出信息的最后几行通常如下：

请确保如上信息的最行一行出现“completed OK!”。修改还原后的数据目录权限:

[root@mysql~]#ll/usr/local/mysql/data/total122916-rw-r-----1root root357Jun1523:36ib_buffer_pool-rw-r-----1root root12582912Jun1523:36ibdata1-rw-r-----1root root50331648Jun1523:36ib_logfile0-rw-r-----1root root50331648Jun1523:36ib_logfile1-rw-r-----1root root12582912Jun1523:36ibtmp1drwxr-x---2root root4096Jun1523:36mysqldrwxr-x---2root root8192Jun1523:36performance_schemadrwxr-x---2root root8192Jun1523:36sysdrwxr-x---2root root50Jun1523:36test1-rw-r-----1root root504Jun1523:36xtrabackup_info

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

[root@mysql~]#chown-R mysql:mysql/usr/local/mysql/data/[root@mysql~]#ll/usr/local/mysql/data/total122916-rw-r-----1mysql mysql357Jun1523:36ib_buffer_pool-rw-r-----1mysql mysql12582912Jun1523:36ibdata1-rw-r-----1mysql mysql50331648Jun1523:36ib_logfile0-rw-r-----1mysql mysql50331648Jun1523:36ib_logfile1-rw-r-----1mysql mysql12582912Jun1523:36ibtmp1drwxr-x---2mysql mysql4096Jun1523:36mysqldrwxr-x---2mysql mysql8192Jun1523:36performance_schemadrwxr-x---2mysql mysql8192Jun1523:36sysdrwxr-x---2mysql mysql50Jun1523:36test1-rw-r-----1mysql mysql504Jun1523:36xtrabackup_info[root@mysql~]#systemctl restart mysqld#验证还原后的数据mysql>select*fromtb1;+------+----------+------+|id|name|back|+------+----------+------+|1|zhangsan|w||2|lisi|q||3|wangwu|e||4|zhaoliu|r||5|liuyi|t||6|didi|y|+------+----------+------+6rowsinset(0.00sec)

还原增量备份：为了防止还原时产生大量的二进制日志，在还原时可临时关闭二进制日志后再还原：

mysql>setsql_log_bin=0;mysql>source/opt/mysqlbackup/inc/2020-06-15.sql

也可以在命令行执行如下语句

mysql–uroot–p

重新启动二进制日志并验证还原数据：

mysql>setsql_log_bin=1;mysql>select*fromtb1;# 可以看到两个tom已经回来了+------+----------+------+|id|name|back|+------+----------+------+|1|zhangsan|w||2|lisi|q||3|wangwu|e||4|zhaoliu|r||5|liuyi|t||6|didi|y||7|tom|u||8|tom2|i|+------+----------+------+8rowsinset(0.00sec)

xtrabackup完全备份+xtrabacup增量备份

前面我们进行增量备份时，使用的还是老方法：备份二进制日志。其实xtrabackup还支持进行增量备份。先介 绍下xtrabackup的备份原理 在InnoDB内部会维护一个redo日志文件，我们也可以叫做事务日志文件 (transaction log，事务日志)。事务日志会存储每一个InnoDB表数据的记录修改。当InnoDB启动时，InnoDB 会检查数据文件和事务日志，并执行两个步骤：它应用已经提交的事务日志到数据文件，并将修改过但没有提 交的数据进行回滚操作。xtrabackup在启动时会记住log sequence number(LSN)，并且复制所有的数据文 件。复制过程需要一些时间，所以这期间如果数据文件有改动，那么将会使数据库处于一个不同的时间点。这 时，xtrabackup会运行一个后台进程，用于监视事务日志，并从事务日志复制最新的修改。xtrabackup必须持 续的做这个操作，是因为事务日志是会轮转重复的写入，并且事务日志可以被重用。所以xtrabackup自启动开 始，就不停的将事务日志中每个数据文件的修改都记录下来。这就是xtrabackup的备份过程 所以每个InnoDB的页面都会包含一个LSN信息，每当相关的数据发生改变，相关的页面的LSN就会自动增长。这正是InnoDB表可以进行增量备份的基础。xtraBackup基于InnoDB的crash-recovery功能。它会复制innodb的 data file，由于不锁表，复制出来的数据是不一致的，在恢复的时候使用crash-recovery，使得数据恢复一致。当InnoDB启动的时候，它会先去检查data file和transaction log，并且会做二步操作：

1.It applies committed transaction logentries to the data files2.it performs an undo operation on anytransactions that modified data but did not commit. 所以在prepare过程中，XtraBackup使用复制到的transactions log对备份出来的innodb data file进行crash recovery

准备测试环境，创建一个测试数据库，并创建一张表输入几行数据

mysql>create database test2;mysql>usetest2;mysql>create table tab1(idint,name varchar(20));mysql>insertintotab1 values(1,'tom1');mysql>insertintotab1 values(2,'tom2');

xtrabacup进行备份执行完全备份：

[root@mysql~]#xtrabackup--default-file=/etc/my.cnf--user=root--password="123"--port=3306--backup--target-dir=/opt/mysqlbackup/full/full_incre_$(date+%Y-%m-%d_%H:%M:%S)#查看完全备份文件[root@mysql~]#ls/opt/mysqlbackup/full/-ltotal0drwxr-x---6root root288Jun1523:332020-06-15_22-49-41drwxr-x---7root root249Jun1523:56full_incre_#xtrabackup进行增量备份 先录入些数据，实现第一次增量数据：mysql>usetest2;mysql>insertintotab1 values(3,'tom3');[root@mysql~]#xtrabackup--defaults-file=/etc/my.cnf--user=root--password="123"--port=3306--backup--target-dir=/opt/mysqlbackup/inc/incre_$(date+%Y%m%d_%H%M%S)--incremental-basedir=/opt/mysqlbackup/full/full_incre_

部分显示信息如下图所示：

#查看增量备份文件：[root@mysql~]#ls-l/opt/mysqlbackup/inc/total8drwxr-x---7root root275Jun1600:04incre_20200616_000403#这里的增量备份其实只针对的是InnoDB，对于MyISAM来说，还是完整备份。#向表中再插入几行数据，继续第二次增量备份mysql>insertintotab1 values(4,'tom4');mysql>commit;#接下来进行第二次增量备份[root@mysql~]#xtrabackup--defaults-file=/etc/my.cnf--user=root--password="123"--port=3306--backup--target-dir=/opt/mysqlbackup/inc/incre_$(date+%Y%m%d_%H%M%S)--incremental-basedir=/opt/mysqlbackup/inc/incre_20200616_000403

如下图所示:

注：第二次增量备份--incremental-basedir指向上一次增量备份文件的位置。注：第二次增量备份--incremental-basedir指向上一次增量备份文件的位置。注：第二次增量备份--incremental-basedir指向上一次增量备份文件的位置。

#查看增量备份文件[root@mysql~]#ls-l/opt/mysqlbackup/inc/total8drwxr-x---7root root275Jun1600:04incre_20200616_000403drwxr-x---7root root275Jun1600:08incre_20200616_000826

xtrabacup进行增量恢复 为了验证比对，先删除两个增量备份前表里面的数据

mysql>deletefromtab1whereid=3;

完整备份恢复：在进行恢复前，如果完整备份在远程主机上，首先将完整备份复制到本地主机上，如果是tar 包，则需要先解包，解包命令为：tar –izxf xxx.tar，这里必须使用-i参数(忽略存档中的 0 字节块(通常意味着 文件结束))。

开始全备份恢复 ：

[root@mysql~]#xtrabackup--defaults-file=/etc/my.cnf--prepare--user=root--password="123"--apply-log-only--target-dir=/opt/mysqlbackup/full/full_incre_/

恢复成功如图所示：

恢复到第一次增量的时刻 增量备份恢复的步骤和完整备份恢复的步骤基本一致，只是应用日志的过程稍有不 同。增量备份恢复时，是先将所有的增量备份挨个应用到完整备份的数据文件中，然后再将完整备份中的数据 恢复到数据库中。

[root@mysql~]#xtrabackup--defaults-file=/etc/my.cnf--prepare--user=root--password="123"--apply-log-only--target-dir=/opt/mysqlbackup/full/full_incre_/--incremental-dir=/opt/mysqlbackup/inc/incre_20200616_000403/

如图所示：

恢复到第二次增量备份前面：

[root@mysql ~] # xtrabackup --defaults-file=/etc/my.cnf --prepare --user=root --password="123" --apply-log-only --target-dir=/opt/mysqlbackup/full/full_incre_/ --incremental-dir=/opt/mysqlbackup/inc/incre_20200616_000826/

恢复整个库 ：

[root@mysql ~] # xtrabackup --defaults-file=/etc/my.cnf --prepare --user=root --password="123" --target-dir=/opt/mysqlbackup/full/full_incre_/

停止mysql数据库：

[root@mysql~]#systemctl stop mysqld

开始rsync同步数据文件：

[root@mysql full_incre_]#rsync-rvt--exclude'xtrabackup_checkpoints'--exclude'xtrabackup_logfile'./ /usr/local/mysql/data/

#当数据恢复至DATADIR目录以后，还需要确保所有数据文件的属主和属组均为正确的用户，如mysql，否则，在 启动mysqld之前还需要事先修改数据文件的属主和属组。[root@mysql data]#lltotal122972-rw-r-----1mysql mysql56Jun1523:38auto.cnf-rw-r-----1root root425Jun1523:56backup-my.cnf-rw-r-----1mysql mysql177Jun1523:38bin_log.000001-rw-r-----1mysql mysql177Jun1523:40bin_log.000002-rw-r-----1mysql mysql1847Jun1600:42bin_log.000003-rw-r-----1mysql mysql111Jun1523:41bin_log.index-rw-r-----1root root298Jun1523:56ib_buffer_pool-rw-r-----1root root12582912Jun1600:19ibdata1-rw-r-----1root root50331648Jun1600:19ib_logfile0-rw-r-----1root root50331648Jun1600:19ib_logfile1-rw-r-----1root root12582912Jun1600:19ibtmp1drwxr-x---2mysql mysql4096Jun1600:18mysql-rw-r-----1mysql mysql22460Jun1600:42mysql.errdrwxr-x---2mysql mysql8192Jun1600:18performance_schemadrwxr-x---2mysql mysql8192Jun1600:18sysdrwxr-x---2mysql mysql50Jun1600:18test1drwxr-x---2mysql mysql52Jun1600:18test2-rw-r-----1root root20Jun1600:18xtrabackup_binlog_info-rw-r--r--1root root20Jun1600:19xtrabackup_binlog_pos_innodb-rw-r-----1root root628Jun1600:18xtrabackup_info[root@mysql data]# chown -R mysql:mysql /usr/local/mysql/data/[root@mysql data]# systemctl restart mysqldmysql> select * from tab1;+------+------+| id | name |+------+------+| 1 | tom1 || 2 | tom2 || 3 | tom3 || 4 | tom4 |+------+------+

innobackupex全库备份+innobackupex增量备份

测试环境准备创建一个测试数据库，并创建一张表输入几行数据

mysql> create database test3;mysql> use test3;mysql> create table tab1(id int,name varchar(20));mysql> insert into tab1 values(1,'lisi1');mysql> insert into tab1 values(2,'lisi2');

1、innobackupex先做完全备份

[root@mysql data]# innobackupex --defaults-file=/etc/my.cnf --user=root --password="123" /opt/mysqlbackup/full/full_incre_$(date +%Y%m%d_%H%M%S) --no-timestamp#查看完全备份文件[root@mysql data]# ls /opt/mysqlbackup/full/full_incre_20200616_005156

做第一次增量备份 先录入增量数据

mysql> insert into tab1 values(3,'lisi3');#进行增强备份[root@mysql data]# innobackupex --incremental /opt/mysqlbackup/inc/incre_$(date +%Y%m%d_%H%M%S) --incremental-basedir=/opt/mysqlbackup/full/full_incre_20200616_005156 --user=root --password="123" --no-timestamp#查看增量备份文件[root@mysql data]# ll /opt/mysqlbackup/inc/drwxr-x--- 8 root root 288 Jun 16 00:56 incre_20200616_005610

基于全备和第一个增量备份来做第二次增量备份 先录入增量数据录入

mysql> insert into tab1 values(4,'lisi4');[root@mysql inc]# innobackupex --incremental /opt/mysqlbackup/inc/incre_$(date +%Y%m%d_%H%M%S) --incremental-basedir=/opt/mysqlbackup/inc/incre_20200616_005610/ --user=root --password="123" --no-timestamp#查看增量备份文件[root@mysql inc]# lltotal 8drwxr-x--- 8 root root 288 Jun 16 00:56 incre_20200616_005610drwxr-x--- 8 root root 288 Jun 16 01:03 incre_20200616_010301

#**先删除两次增量数据，用来查看验证恢复结果**mysql> delete from tab1 ;#开始做恢复，恢复全备份[root@mysql inc]# innobackupex --apply-log --redo-only /opt/mysqlbackup/full/full_incre_20200616_005156/#--redo-only 用于准备增量备份内容把数据合并到全备份目录，配合incremental-dir 增量备份目录使用#基于全备份进行第一次增量备份的恢复[root@mysql ~]# innobackupex --apply-log --redo-only /opt/mysqlbackup/full/full_incre_20200616_005156/ --incremental-dir=/opt/mysqlbackup/inc/incre_20200616_005610/#基于全备份和第一次增量备份，恢复第二次增量备份[root@mysql ~]# innobackupex --apply-log --redo-only /opt/mysqlbackup/full/full_incre_20200616_005156/ --incremental-dir=/opt/mysqlbackup/inc/incre_20200616_010301/

#停止数据库[root@mysql ~]# systemctl stop mysqld#清空数据目录下所有文件[root@mysql ~]# mkdir -p /tmp/mysqldatabak[root@mysql ~]# mv /usr/local/mysql/data/* /tmp/mysqldatabak/#将恢复好的数据按照配置文件的需求拷贝到相应目录[root@mysql ~]# innobackupex --defaults-file=/etc/my.cnf --user=root --password="123" --copy-back /opt/mysqlbackup/full/full_incre_20200616_005156/

#当数据恢复至DATADIR目录以后，还需要确保所有数据文件的属主和属组均为正确的用户，如mysql，否则，在 启动mysqld之前还需要事先修改数据文件的属主和属组[root@mysql ~]# cd /usr/local/mysql/data/[root@mysql data]# lltotal 12328-rw-r----- 1 root root 298 Jun 16 01:12 ib_buffer_pool-rw-r----- 1 root root 12582912 Jun 16 01:12 ibdata1drwxr-x--- 2 root root 4096 Jun 16 01:12 mysqldrwxr-x--- 2 root root 8192 Jun 16 01:12 performance_schemadrwxr-x--- 2 root root 8192 Jun 16 01:12 sysdrwxr-x--- 2 root root 50 Jun 16 01:12 test1drwxr-x--- 2 root root 52 Jun 16 01:12 test2drwxr-x--- 2 root root 52 Jun 16 01:12 test3-rw-r----- 1 root root 20 Jun 16 01:12 xtrabackup_binlog_pos_innodb-rw-r----- 1 root root 598 Jun 16 01:12 xtrabackup_info

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

#启动服务[root@mysql data]# systemctl restart mysqld

登录mysql界面，查看数据是否已经恢复

mysql> select * from tab1;+------+-------+| id | name |+------+-------+| 1 | lisi1 || 2 | lisi2 || 3 | lisi3 || 4 | lisi4 |+------+-------+4 rows in set (0.00 sec)

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

# innobackupex --user=root --password="123" --stream=tar /opt/mysqlbackup/full/ | gzip >/opt/mysqlbackup/full/full_`date +%F_%H%M%S`.tar.gz