数据库备份：Xtrabackup实现完全及增量备份
5.3 xtrabackup 备份工具5.3.1 xtrabackup 工具介绍xtrabackup 特点：xtrabackup工具文件组成： 5.3.3 xtrabackup 用法5.3.4 利用xtrabackup实现完全备份及还原新版 xtrabackup完全备份及还原新版xtrabackup增量备份及还原5.3 xtrabackup 备份工具
5.3.1 xtrabackup 工具介绍
Percona 公司：
官网：
percona-server
InnoDB --> XtraDB
Xtrabackup备份工具：
percona提供的mysql数据库备份工具，惟一开源的能够对innodb和xtradb数据库进行热备的工具手册：/doc/percona-xtrabackup/LATEST/index.html下载: /downloads/
xtrabackup 特点：
备份还原过程快速、可靠备份过程不会打断正在执行的事务能够基于压缩等功能节约磁盘空间和流量自动实现备份检验开源，免费xtrabackup工具文件组成：
Xtrabackup2.2 版之前包括4个可执行文件：
innobackupex: Perl 脚本
xtrabackup: C/C++，编译的二进制程序
xbcrypt: 加解密
xbstream: 支持并发写的流文件格式
说明:
xtrabackup 是用来备份 InnoDB 表的，不能备份非 InnoDB 表，和 MySQL Server 没有交互innobackupex 脚本用来备份非 InnoDB 表，同时会调用 xtrabackup 命令来备份 InnoDB 表，还会和MySQL Server 发送命令进行交互，如加全局读锁（FTWRL）、获取位点（SHOW SLAVE STATUS）等。即innobackupex是在 xtrabackup 之上做了一层封装实现的
xtrabackup的新版变化
xtrabackup版本升级到2.4后，相比之前的2.1有了比较大的变化：innobackupex 功能全部集成到xtrabackup 里面，只有一个 binary程序，另外为了兼容考虑，innobackupex作为 xtrabackup 的软链接，即xtrabackup现在支持非Innodb表备份，并且 Innobackupex 在下一版本中移除，建议通过xtrabackup替换innobackupex
备份生成的相关文件
使用innobackupex备份时，其会调用xtrabackup备份所有的InnoDB表，复制所有关于表结构定义的相关文件(.frm)、以及MyISAM、MERGE、CSV和ARCHIVE表的相关文件，同时还会备份触发器和数据库配置信息相关的文件。这些文件会被保存至一个以时间命名的目录中,在备份时，innobackupex还会在备份目录中创建如下文件：
xtrabackup_info：文本文件，innobackupex工具执行时的相关信息，包括版本，备份选项，备份时长，备份LSN(log sequence number日志序列号)，BINLOG的位置
xtrabackup_checkpoints：文本文件，备份类型（如完全或增量）、备份状态（如是否已经为prepared状态）和LSN范围信息,每个InnoDB页(通常为16k大小)都会包含一个日志序列号LSN。LSN是整个数据库系统的系统版本号，每个页面相关的LSN能够表明此页面最近是如何发生改变的
xtrabackup_binlog_info：文本文件，MySQL服务器当前正在使用的二进制日志文件及至备份这一刻为止二进制日志事件的位置，可利用实现基于binlog的恢复
backup-f：文本文件，备份命令用到的配置选项信息
xtrabackup_logfile：备份生成的二进制日志文件
#示例：相关文件[root@centos8 ~]#ll /backup/total 12340-rw-r----- 1 root root 487 Jun 12 15:07 backup-fdrwxr-x--- 2 root root 272 Jun 12 15:07 hellodb-rw-r----- 1 root root 425 Jun 12 15:07 ib_buffer_pool-rw-r----- 1 root root 12582912 Jun 12 15:07 ibdata1drwxr-x--- 2 root root 4096 Jun 12 15:07 mysqldrwxr-x--- 2 root root 8192 Jun 12 15:07 performance_schemadrwxr-x--- 2 root root 8192 Jun 12 15:07 sys-rw-r----- 1 root root 25 Jun 12 15:07 xtrabackup_binlog_info-rw-r----- 1 root root 135 Jun 12 15:07 xtrabackup_checkpoints-rw-r----- 1 root root 479 Jun 12 15:07 xtrabackup_info-rw-r----- 1 root root 2560 Jun 12 15:07 xtrabackup_logfile[root@centos8 ~]#cat /backup/xtrabackup_infouuid = 55a26ea0-ac7b-11ea-a8ab-000c293f7395name =tool_name = xtrabackuptool_command = -uroot -pmagedu --backup --target-dir=/backup/tool_version = 2.4.20ibbackup_version = 2.4.20server_version = 5.7.29-logstart_time = -06-12 15:07:08end_time = -06-12 15:07:10lock_time = 1binlog_pos = filename 'centos8-bin.000002', position '10185'innodb_from_lsn = 0innodb_to_lsn = 2687527partial = Nincremental = Nformat = filecompact = Ncompressed = Nencrypted = N[root@centos8 ~]#cat /backup/xtrabackup_checkpointsbackup_type = full-backupedfrom_lsn = 0to_lsn = 2687527last_lsn = 2687536compact = 0recover_binlog_info = 0flushed_lsn = 2687536[root@centos8 ~]#cat /backup/xtrabackup_binlog_infocentos8-bin.000002 10185[root@centos8 ~]#cat /backup/backup-f# This MySQL options file was generated by innobackupex.# The MySQL server[mysqld]innodb_checksum_algorithm=crc32innodb_log_checksum_algorithm=strict_crc32innodb_data_file_path=ibdata1:12M:autoextendinnodb_log_files_in_group=2innodb_log_file_size=50331648innodb_fast_checksum=falseinnodb_page_size=16384innodb_log_block_size=512innodb_undo_directory=./innodb_undo_tablespaces=0server_id=1redo_log_version=1server_uuid=6fb9641a-ac79-11ea-8bed-000c293f7395master_key_id=0[root@centos8 ~]#file /backup/xtrabackup_logfile/backup/xtrabackup_logfile: data
5.3.2 xtrabackup 安装
在EPEL源中yum install percona-xtrabackup最新版本下载安装：/downloads/XtraBackup/LATEST/[root@centos8 ~]#ll percona-xtrabackup-24-2.4.20-1.el8.x86_64.rpm-rw-r--r-- 1 root root 8045696 Oct 12 08:42 percona-xtrabackup-24-2.4.20-1.el8.x86_64.rpm[root@centos8 ~]#yum -y install percona-xtrabackup-24-2.4.20-1.el8.x86_64.rpm[root@centos8 ~]#rpm -ql percona-xtrabackup-24-2.4.20[root@centos8 ~]#ll /usr/bin/innobackupex[root@centos8 ~]#file /usr/bin/xtrabackup
5.3.3 xtrabackup 用法
xtrabackup工具备份和还原，需要三步实现:
备份：对数据库做完全或增量备份预准备： 还原前，先对备份的数据，整理至一个临时目录还原：将整理好的数据，复制回数据库目录中
备份：
innobackupex [option] BACKUP-ROOT-DIR选项说明：--user：#该选项表示备份账号--password：#该选项表示备份的密码--host：#该选项表示备份数据库的地址--databases：#该选项接受的参数为数据库名，如果要指定多个数据库，彼此间需要以空格隔开；如："xtra_test dba_test"，同时，在指定某数据库时，也可以只指定其中的某张表。如："mydatabase.mytable"。该选项对innodb引擎表无效，还是会备份所有innodb表--defaults-file：#该选项指定从哪个文件读取MySQL配置，必须放在命令行第一个选项位置--incremental：#该选项表示创建一个增量备份，需要指定--incremental-basedir--incremental-basedir：#该选项指定为前一次全备份或增量备份的目录，与--incremental同时使用--incremental-dir：#该选项表示还原时增量备份的目录--include=name：#指定表名，格式：databasename.tablename
准备：
innobackupex --apply-log [option] BACKUP-DIR选项说明：--apply-log：#一般情况下,在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处理不一致状态。此选项作用是通过回滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态--use-memory：#和--apply-log选项一起使用，当prepare 备份时，做crash recovery分配的内存大小，单位字节，也可1MB,1M,1G,1GB等，推荐1G--export：#表示开启可导出单独的表之后再导入其他Mysql中--redo-only：#此选项在prepare base full backup，往其中合并增量备份时候使用，但不包括对最后一个增量备份的合并
还原：
innobackupex --copy-back [选项] BACKUP-DIRinnobackupex --move-back [选项] [--defaults-group=GROUP-NAME] BACKUP-DIR选项说明：--copy-back：#做数据恢复时将备份数据文件拷贝到MySQL服务器的datadir--move-back：#这个选项与--copy-back相似，唯一的区别是它不拷贝文件，而是移动文件到目的地。这个选项移除backup文件，用时候必须小心。使用场景：没有足够的磁盘空间同时保留数据文件和Backup副本--force-non-empty-directories #指定该参数时候，使得innobackupex --copy-back或--moveback选项转移文件到非空目录，已存在的文件不会被覆盖。如果--copy-back和--move-back文件需要从备份目录拷贝一个在datadir已经存在的文件，会报错失败# 还原注意事项：1. datadir 目录必须为空。除非指定innobackupex --force-non-empty-directorires选项指定，否则--copy-back选项不会覆盖2. 在restore之前,必须shutdown MySQL实例，不能将一个运行中的实例restore到datadir目录中3. 由于文件属性会被保留，大部分情况下需要在启动实例之前将文件的属主改为mysql，这些文件将属于创建备份的用户, 执行chown -R mysql:mysql /data/mysql,以上需要在用户调用innobackupex之前完成
5.3.5 安装mysql环境[二进制安装包]
环境：两台CentOS8.3初始化系统，使用脚本加二进制安装包方式安装，mysql版本5.7.29
#脚本执行前准备root@kvm-centos8-3-1 my_sql]#mkdir -pv /data/mysql[root@kvm-centos8-3-1 my_sql]# cd /root/my_sql/[root@kvm-centos8-3-1 my_sql]# lsinstall_mysql5.7or8.0_for_centos.sh mysql-5.7.29-linux-glibc2.12-x86_64.tar.gz[root@kvm-centos8-3-1 my_sql]# pwd/root/my_sql#在root目录下创建一个my_sql目录,将脚本及二进制安装包都放入此目录中!#执行mysql进入数据库[root@kvm-centos8-3-1 my_sql]# mysql -uroot -p132117mysql: error while loading shared libraries: libncurses.so.5: cannot open shared object file: No such file or directory#如上报错,则需要安装软件包libncurses*yum -y install libncurses*#导入测试数据到库中!mysql -p132117 < hellodb_innodb.sql mysql: [Warning] Using a password on the command line interface can be insecure.
正式脚本
#!/bin/bash##********************************************************************#Author:莫忘初心yhj#QQ: 81213672#Date: -05-27#FileName：install_mysql5.7_for_centos.sh#URL: /weixin_43305348#Description：The test script#Copyright (C): All rights reserved#********************************************************************#MySQL Download URL: /get/Downloads/MySQL-5.7/mysql-5.7.29-linux-glibc2.12-x86_64.tar.gz. /etc/init.d/functions SRC_DIR=`pwd`MYSQL='mysql-5.7.29-linux-glibc2.12-x86_64.tar.gz'COLOR='echo -e \E[01;31m'END='\E[0m'MYSQL_ROOT_PASSWORD=132117check (){if [ $UID -ne 0 ]; thenaction "当前用户不是root,安装失败" falseexit 1ficd $SRC_DIRif [ ! -e $MYSQL ];then$COLOR"缺少${MYSQL}文件"$END$COLOR"请将相关软件放在${SRC_DIR}目录下"$ENDexitelif [ -e /usr/local/mysql ];thenaction "数据库已存在，安装失败" falseexitelsereturnfi} install_mysql(){$COLOR"开始安装MySQL数据库..."$ENDyum -y -q install libaio numactl-libs libaio &> /dev/nullcd $SRC_DIRtar xf $MYSQL -C /usr/local/MYSQL_DIR=`echo $MYSQL| sed -nr 's/^(.*[0-9]).*/\1/p'`ln -s /usr/local/$MYSQL_DIR /usr/local/mysqlchown -R root.root /usr/local/mysql/id mysql &> /dev/null || { useradd -s /sbin/nologin -r mysql ; action "创建mysql用户"; }echo 'PATH=/usr/local/mysql/bin/:$PATH' > /etc/profile.d/mysql.sh. /etc/profile.d/mysql.shln -s /usr/local/mysql/bin/* /usr/bin/cat > /etc/f <<-EOF[mysqld]server-id=1log-bindatadir=/data/mysqlsocket=/data/mysql/mysql.sock log-error=/data/mysql/mysql.logpid-file=/data/mysql/mysql.pid[client]socket=/data/mysql/mysql.sockEOFmysqld --initialize --user=mysql --datadir=/data/mysql cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqldchkconfig --add mysqldchkconfig mysqld onservice mysqld start[ $? -ne 0 ] && { $COLOR"数据库启动失败，退出!"$END;exit; }MYSQL_OLDPASSWORD=`awk '/A temporary password/{print $NF}' /data/mysql/mysql.log`mysqladmin -uroot -p$MYSQL_OLDPASSWORD password $MYSQL_ROOT_PASSWORD &>/dev/nullaction "数据库安装完成" }chown -R mysql.mysql /data/mysql/checkinstall_mysql
5.3.6 安装mysql环境[rpm安装包]
二进制和rpm安装包方法二选一即可
环境：
两台CentOS8.3初始化系统，使用rpm安装包方式进行安装，mysql版本5.7.29
1) # 下载mysql-5.27rpm的完整安装包wget /archives/get/p/23/file/mysql-5.7.29-1.el7.x86_64.rpm-bundle.tar#此处如果只下载server安装包,会提示需要解决依赖关系,所以将此完整安装包下载下来2)#解压 tar -xvf mysql-5.7.29-1.el7.x86_64.rpm-bundle.tar#开始安装yum -y install mysql-community-common-5.7.29-1.el7.x86_64.rpmyum -y install mysql-community-libs-5.7.29-1.el7.x86_64.rpmyum -y install mysql-community-client-5.7.29-1.el7.x86_64.rpmyum -y install mysql-community-server-5.7.29-1.el7.x86_64.rpm3)#安装完成启动,初始验证#systemcctl start mysqld.service#注意!此处使用mysql进入数据库的时候,由于mysql5.7.29版本安全策略原因,会有默认密码![root@kvm-centos8-3-1 mysql]# mysql ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)[root@kvm-centos8-3-1 mysql]# mysql -uroot -hlocalhostERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)#如下,一直登录失败,使用如下命令,先查看mysql数据库的初始密码[root@kvm-centos8-3-1 mysql]# grep 'temporary password' /var/log/mysqld.log -05-27T04:55:04.916018Z 1 [Note] A temporary password is generated for root@localhost: fXWYM.jGY1NQ# 然后使用如上密码登录,则成功进入数据库![root@kvm-centos8-3-1 mysql]# mysql -uroot -pfXWYM.jGY1NQmysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 10Server version: 5.7.29Copyright (c) 2000, , Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.#进入之后,查看数据库,会提示让你先修改密码,而且由于密码限制规则,还不能使用太过于简单的密码!mysql> show databases;ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.# 先修改成复杂密码登录 mysql> alter user 'root'@'localhost' identified by '6mZg8hJ@EZ';Query OK, 0 rows affected (0.00 sec)#查看并修改初始密码策略# 查看MySQL完整的初始密码规则mysql> SHOW VARIABLES LIKE 'validate_password%';+--------------------------------------+--------+| Variable_name | Value |+--------------------------------------+--------+| validate_password_check_user_name | OFF || validate_password_dictionary_file | || validate_password_length | 8|| validate_password_mixed_case_count | 1|| validate_password_number_count | 1|| validate_password_policy | MEDIUM || validate_password_special_char_count | 1|+--------------------------------------+--------+7 rows in set (0.00 sec)#密码的长度是由validate_password_length决定的,但是可以通过以下命令修改set global validate_password_length=6;#validate_password_policy决定密码的验证策略,默认等级为MEDIUM(中等),可通过以下命令修改为LOW(低)set global validate_password_policy=0;#验证mysql> SHOW VARIABLES LIKE 'validate_password%';+--------------------------------------+-------+| Variable_name | Value |+--------------------------------------+-------+| validate_password_check_user_name | OFF || validate_password_dictionary_file | || validate_password_length | 6|| validate_password_mixed_case_count | 1|| validate_password_number_count | 1|| validate_password_policy | LOW || validate_password_special_char_count | 1|+--------------------------------------+-------+7 rows in set (0.00 sec)#再次修改密码mysql> alter user 'root'@'localhost' identified by '123456';Query OK, 0 rows affected (0.00 sec)
5.3.4 利用xtrabackup实现完全备份及还原
注意：目前percona-xtrabackup-24-2.4.18-1.el8.x86_64.rpm不支持CentOS 8上的mariadb-10.3版本
新版 xtrabackup完全备份及还原
下载安装包: wget /downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.20/binary/redhat/8/x86_64/percona-xtrabackup-24-2.4.20-1.el8.x86_64.rpm# 本案例基于CentOS 8 的 MySQL5.7 实现,也支持MySQL5.5和Mariadb5.5#环境:CentOS8.3-1和CentOS8-3-2两台机器 ,在1上模拟备份,在2上模拟恢复1 安装xtrabackup包#先安装MySQL5.7[root@kvm-centos8-3-1]# yum -y install percona-xtrabackup-24-2.4.20-1.el8.x86_64.rpm[root@kvm-centos8-3-1 my_sql]# rpm -ql percona-xtrabackup-24[root@kvm-centos8-3-1 my_sql]# ll /usr/bin/innobackupex lrwxrwxrwx 1 root root 10 Apr 20 /usr/bin/innobackupex -> xtrabackup[root@kvm-centos8-3-1 my_sql]# file /usr/bin/xtrabackup /usr/bin/xtrabackup: ELF 64-bit LSB executable, x86-64, version 1 (GNU/Linux), dynamically linked, interpreter /lib64/ld-linux-x86-64.so.2, for GNU/Linux 3.2.0, BuildID[sha1]=ad45538c7f190bf3875f39562c7e33c39ef9425c, stripped2 在原主机做完全备份到/data/backup[root@kvm-centos8-3-1 ~]# xtrabackup -uroot -p132117 --backup --target-dir=/data/backup/#查看备份数据[root@kvm-centos8-3-1 backup]# cd /data/backup/[root@kvm-centos8-3-1 backup]# lltotal 12340-rw-r----- 1 root root487 May 27 00:45 backup-fdrwxr-x--- 2 root root272 May 27 00:45 hellodb-rw-r----- 1 root root425 May 27 00:45 ib_buffer_pool-rw-r----- 1 root root 12582912 May 27 00:45 ibdata1drwxr-x--- 2 root root4096 May 27 00:45 mysqldrwxr-x--- 2 root root8192 May 27 00:45 performance_schemadrwxr-x--- 2 root root8192 May 27 00:45 sys-rw-r----- 1 root root 33 May 27 00:45 xtrabackup_binlog_info-rw-r----- 1 root root135 May 27 00:45 xtrabackup_checkpoints-rw-r----- 1 root root492 May 27 00:45 xtrabackup_info-rw-r----- 1 root root2560 May 27 00:45 xtrabackup_logfile#查看源数据和备份数据大小[root@kvm-centos8-3-1 backup]# du -sh /data/mysql/135M/data/mysql/[root@kvm-centos8-3-1 backup]# du -sh /data/backup/27M/data/backup/预准备:#目标主机无需创建/backup目录,直接复制目录本身[root@kvm-centos8-3-1 ]#scp -r /root/my_sql/backup 192.168.0.102:/root/my_sql/3 在目标主机上还原1）预准备：确保数据安全,先将备份目录进行备份[root@kvm-centos8-3-2 my_sql]# cp -a backup/ /tmp/#为确保数据一致，提交完成的事务，回滚未完成的事务[root@kvm-centos8-3-2 my_sql]# cd /tmp/backup/[root@kvm-centos8-3-2 my_sql]#xtrabackup --prepare --target-dir=./#整理完成之后,再次查看目录内容及大小[root@kvm-centos8-3-2 backup]# du -sh ./143M./[root@kvm-centos8-3-2 backup]# lltotal 131128-rw-r----- 1 root root487 May 27 00:37 backup-fdrwxr-x--- 2 root root272 May 27 00:37 hellodb-rw-r----- 1 root root425 May 27 00:37 ib_buffer_pool-rw-r----- 1 root root 12582912 May 27 00:44 ibdata1-rw-r----- 1 root root 50331648 May 27 00:44 ib_logfile0-rw-r----- 1 root root 50331648 May 27 00:44 ib_logfile1-rw-r----- 1 root root 12582912 May 27 00:44 ibtmp1drwxr-x--- 2 root root4096 May 27 00:37 mysqldrwxr-x--- 2 root root8192 May 27 00:37 performance_schemadrwxr-x--- 2 root root8192 May 27 00:37 sys-rw-r----- 1 root root 33 May 27 00:37 xtrabackup_binlog_info-rw-r--r-- 1 root root 32 May 27 00:44 xtrabackup_binlog_pos_innodb-rw-r----- 1 root root135 May 27 00:44 xtrabackup_checkpoints-rw-r----- 1 root root492 May 27 00:37 xtrabackup_info-rw-r----- 1 root root 8388608 May 27 00:44 xtrabackup_logfile-rw-r--r-- 1 root root 1 May 27 00:44 xtrabackup_master_key_id2）复制到数据库目录#注意：数据库目录必须为空，并且MySQL服务不能启动#停止数据库服务[root@kvm-centos8-3-2 backup]# service mysqld stopShutting down MySQL.. SUCCESS!#清除原来数据[root@kvm-centos8-3-2 backup]# rm -rf /data/mysql/*[root@kvm-centos8-3-2 backup]# ll /data/mysql/total 0#将整理好的数据还原到数据库中[root@kvm-centos8-3-2 backup]# xtrabackup --copy-back --target-dir=/tmp/backup/[root@kvm-centos8-3-2 backup]# ll /data/mysql/total 122924drwxr-x--- 2 root root272 May 27 00:55 hellodb-rw-r----- 1 root root425 May 27 00:55 ib_buffer_pool-rw-r----- 1 root root 12582912 May 27 00:55 ibdata1-rw-r----- 1 root root 50331648 May 27 00:55 ib_logfile0-rw-r----- 1 root root 50331648 May 27 00:55 ib_logfile1-rw-r----- 1 root root 12582912 May 27 00:55 ibtmp1drwxr-x--- 2 root root4096 May 27 00:55 mysqldrwxr-x--- 2 root root8192 May 27 00:55 performance_schemadrwxr-x--- 2 root root8192 May 27 00:55 sys-rw-r----- 1 root root 32 May 27 00:55 xtrabackup_binlog_pos_innodb-rw-r----- 1 root root492 May 27 00:55 xtrabackup_info-rw-r----- 1 root root 1 May 27 00:55 xtrabackup_master_key_id[root@kvm-centos8-3-2 backup]# du -sh /data/mysql/135M/data/mysql/3）还原属性[root@centos8 ~]#chown -R mysql:mysql /data/mysql4）启动服务[root@centos8 ~]#service mysqld start
新版xtrabackup增量备份及还原
1 备份过程1）完全备份：[root@centos8 ~]#mkdir /backup/[root@centos8 ~]#xtrabackup -uroot -pmagedu --backup --target-dir=/backup/2）第一次修改数据#添加数据,模拟增量备份# 在8-3-1上数据库中进行添加数据操作;mysql> use hellodb;mysql> select * from teachers;+-----+---------------+-----+--------+| TID | Name| Age | Gender |+-----+---------------+-----+--------+| 1 | Song Jiang | 45 | M|| 2 | Zhang Sanfeng | 94 | M|| 3 | Miejue Shitai | 77 | F|| 4 | Lin Chaoying | 93 | F|+-----+---------------+-----+--------+4 rows in set (0.00 sec)#插入数据mysql> insert teachers (name,age,gender)values('Li Xiang','30','M');Query OK, 1 row affected (0.02 sec)mysql> insert teachers (name,age,gender)values('Wei Lai','28','M');Query OK, 1 row affected (0.01 sec)mysql> insert teachers (name,age,gender)values('Meng Xiang','29','F');Query OK, 1 row affected (0.01 sec)mysql> select * from teachers;+-----+---------------+-----+--------+| TID | Name| Age | Gender |+-----+---------------+-----+--------+| 1 | Song Jiang | 45 | M|| 2 | Zhang Sanfeng | 94 | M|| 3 | Miejue Shitai | 77 | F|| 4 | Lin Chaoying | 93 | F|| 5 | Li Xiang| 30 | M|| 6 | Wei Lai | 28 | M|| 7 | Meng Xiang | 29 | F|+-----+---------------+-----+--------+7 rows in set (0.01 sec)3）第一次增量备份#为模拟真实环境，将前面备份目录做一些小的调整[root@kvm-centos8-3-1 my_sql]# mkdir base[root@kvm-centos8-3-1 my_sql]# mv backup/* base/[root@kvm-centos8-3-1 my_sql]# mv base/ backup/[root@kvm-centos8-3-1 backup]# lltotal 0drwxr-xr-x 6 root root 238 May 27 02:19 base[root@kvm-centos8-3-1 backup]# pwd/root/my_sql/backup[root@kvm-centos8-3-1 backup]# ll base/total 12340-rw-r----- 1 root root487 May 27 00:45 backup-fdrwxr-x--- 2 root root272 May 27 00:45 hellodb-rw-r----- 1 root root425 May 27 00:45 ib_buffer_pool-rw-r----- 1 root root 12582912 May 27 00:45 ibdata1drwxr-x--- 2 root root4096 May 27 00:45 mysqldrwxr-x--- 2 root root8192 May 27 00:45 performance_schemadrwxr-x--- 2 root root8192 May 27 00:45 sys-rw-r----- 1 root root 33 May 27 00:45 xtrabackup_binlog_info-rw-r----- 1 root root135 May 27 00:45 xtrabackup_checkpoints-rw-r----- 1 root root492 May 27 00:45 xtrabackup_info-rw-r----- 1 root root2560 May 27 00:45 xtrabackup_logfile[root@kvm-centos8-3-1 backup]# xtrabackup -uroot -p132117 --backup --target-dir=/root/my_sql/backup/inc1 --incremental-basedir=/root/my_sql/backup/# --target-dir：指定备份目录#incremental-basedir：指定完全备份的路径[root@kvm-centos8-3-1 backup]# pwd/root/my_sql/backup[root@kvm-centos8-3-1 backup]# lldrwxr-xr-x 6 root root 238 May 27 02:19 basedrwxr-x--- 6 root root 264 May 27 02:23 inc1[root@kvm-centos8-3-1 backup]# du -sh inc1/3.5Minc1/#查看xtrabackup相关文件[root@kvm-centos8-3-1 inc1]# cat xtrabackup_info uuid = 06bfe13b-beb4-11eb-ae0b-5254008b229ename = tool_name = xtrabackuptool_command = -uroot -p132117 --backup --target-dir=/root/my_sql/backup/inc1 --incremental-basedir=/root/my_sql/backup/basetool_version = 2.4.20ibbackup_version = 2.4.20server_version = 5.7.29-logstart_time = -05-27 02:23:14end_time = -05-27 02:23:18lock_time = 3binlog_pos = filename 'kvm-centos8-3-1-bin.000002', position '11032'innodb_from_lsn = 2687801innodb_to_lsn = 2694249partial = Nincremental = Yformat = filecompact = Ncompressed = Nencrypted = N[root@kvm-centos8-3-1 inc1]# cat xtrabackup_checkpoints backup_type = incremental#增量备份from_lsn = 2687801to_lsn = 2694249last_lsn = 2694258compact = 0recover_binlog_info = 0flushed_lsn = 2694258[root@kvm-centos8-3-1 inc1]# cat xtrabackup_binlog_info kvm-centos8-3-1-bin.000002110324）第二次修改数据mysql> insert teachers (name,age,gender)values('Xiao Hu','25','M');Query OK, 1 row affected (0.01 sec)mysql> insert teachers (name,age,gender)values('Gala','23','M');Query OK, 1 row affected (0.02 sec)mysql> insert teachers (name,age,gender)values('Ming','23','M');Query OK, 1 row affected (0.01 sec)mysql> select * from teachers;+-----+---------------+-----+--------+| TID | Name| Age | Gender |+-----+---------------+-----+--------+| 1 | Song Jiang | 45 | M|| 2 | Zhang Sanfeng | 94 | M|| 3 | Miejue Shitai | 77 | F|| 4 | Lin Chaoying | 93 | F|| 5 | Li Xiang| 30 | M|| 6 | Wei Lai | 28 | M|| 7 | Meng Xiang | 29 | F|| 8 | Xiao Hu | 25 | M|| 9 | Gala| 23 | M|| 10 | Ming| 23 | M|+-----+---------------+-----+--------+10 rows in set (0.00 sec)5）第二次增量[root@kvm-centos8-3-1 backup]# xtrabackup -uroot -p132117 --backup --target-dir=/root/my_sql/backup/inc2 --incremental-basedir=/root/my_sql/backup/inc1#--incremental-basedir=/root/my_sql/backup/inc1 此处是第二次增量，所以是基于第一次之后的！#查看xtrabackup相关文件[root@kvm-centos8-3-1 backup]# cd inc2/[root@kvm-centos8-3-1 inc2]# cat xtrabackup_info uuid = 58d2aec5-beb8-11eb-ae0b-5254008b229ename = tool_name = xtrabackuptool_command = -uroot -p132117 --backup --target-dir=/root/my_sql/backup/inc2 --incremental-basedir=/root/my_sql/backup/inc1tool_version = 2.4.20ibbackup_version = 2.4.20server_version = 5.7.29-logstart_time = -05-27 02:54:10end_time = -05-27 02:54:13lock_time = 2binlog_pos = filename 'kvm-centos8-3-1-bin.000002', position '11869'innodb_from_lsn = 2694249innodb_to_lsn = 2700660partial = Nincremental = Yformat = filecompact = Ncompressed = Nencrypted = N[root@kvm-centos8-3-1 inc2]# cat xtrabackup_checkpoints backup_type = incrementalfrom_lsn = 2694249to_lsn = 2700660last_lsn = 2700669compact = 0recover_binlog_info = 0flushed_lsn = 2700669[root@kvm-centos8-3-1 inc2]# cat xtrabackup_binlog_info kvm-centos8-3-1-bin.00000211869#将数据拷贝到恢复主机 [因backup目录已存在，所以目录为backup2]6）[root@kvm-centos8-3-1 my_sql]# scp -r backup root@192.168.0.102:/root/my_sql/backup2#以下操作在8-3-2机器上进行操作！#备份过程生成三个备份目录[root@kvm-centos8-3-2 my_sql]# ll backup2/total 0drwxr-xr-x 6 root root 238 May 27 02:30 basedrwxr-x--- 6 root root 264 May 27 02:30 inc1drwxr-x--- 6 root root 264 May 27 02:30 inc2#拷贝数据，整理使用[root@kvm-centos8-3-2 my_sql]# cp -ra backup2/ /tmp/[root@kvm-centos8-3-2 backup2]# pwd/tmp/backup2[root@kvm-centos8-3-2 backup2]# du -sh base/27Mbase/2还原过程1）预准备完成备份，此选项--apply-log-only 阻止回滚未完成的事务[root@kvm-centos8-3-2 backup2]# xtrabackup --prepare --apply-log-only --target-dir=/tmp/backup2/base[root@kvm-centos8-3-2 backup2]# du -sh base/35Mbase/2）合并第1次增量备份到完全备份[root@kvm-centos8-3-2 backup2]# xtrabackup --prepare --apply-log-only --target-dir=/tmp/backup2/base --incremental-dir=/tmp/backup2/inc1[root@kvm-centos8-3-2 backup2]# du -sh inc1/12Minc1/[root@kvm-centos8-3-2 backup2]# du -sh /root/my_sql/backup2/inc1/3.5M/root/my_sql/backup2/inc1/3）合并第2次增量备份到完全备份：最后一次还原不需要加选项--apply-log-only[root@kvm-centos8-3-2 backup2]# xtrabackup --prepare --target-dir=/tmp/backup2/base --incremental-dir=/tmp/backup2/inc2[root@kvm-centos8-3-2 backup2]# du -sh base/143Mbase/#停止服务，并清除原来数据[root@kvm-centos8-3-2 backup2]# service mysqld stopShutting down MySQL.. SUCCESS![root@kvm-centos8-3-2 backup2]# rm -rf /data/mysql/*4）复制到数据库目录[root@kvm-centos8-3-2 backup2]# xtrabackup --copy-back --target-dir=/tmp/backup2/base[root@kvm-centos8-3-2 backup2]# ll /data/mysql/total 122924drwxr-x--- 2 root root272 May 27 02:47 hellodb-rw-r----- 1 root root425 May 27 02:47 ib_buffer_pool-rw-r----- 1 root root 12582912 May 27 02:47 ibdata1-rw-r----- 1 root root 50331648 May 27 02:47 ib_logfile0-rw-r----- 1 root root 50331648 May 27 02:47 ib_logfile1-rw-r----- 1 root root 12582912 May 27 02:47 ibtmp1drwxr-x--- 2 root root4096 May 27 02:47 mysqldrwxr-x--- 2 root root8192 May 27 02:47 performance_schemadrwxr-x--- 2 root root8192 May 27 02:47 sys-rw-r----- 1 root root 33 May 27 02:47 xtrabackup_binlog_pos_innodb-rw-r----- 1 root root556 May 27 02:47 xtrabackup_info-rw-r----- 1 root root 1 May 27 02:47 xtrabackup_master_key_id5）还原属性：[root@centos8 ~]#chown -R mysql:mysql /var/lib/mysql[root@kvm-centos8-3-2 backup2]# ll /data/mysql/total 122924drwxr-x--- 2 mysql mysql272 May 27 02:47 hellodb-rw-r----- 1 mysql mysql425 May 27 02:47 ib_buffer_pool-rw-r----- 1 mysql mysql 12582912 May 27 02:47 ibdata1-rw-r----- 1 mysql mysql 50331648 May 27 02:47 ib_logfile0-rw-r----- 1 mysql mysql 50331648 May 27 02:47 ib_logfile1-rw-r----- 1 mysql mysql 12582912 May 27 02:47 ibtmp1drwxr-x--- 2 mysql mysql4096 May 27 02:47 mysqldrwxr-x--- 2 mysql mysql8192 May 27 02:47 performance_schemadrwxr-x--- 2 mysql mysql8192 May 27 02:47 sys-rw-r----- 1 mysql mysql 33 May 27 02:47 xtrabackup_binlog_pos_innodb-rw-r----- 1 mysql mysql556 May 27 02:47 xtrabackup_info-rw-r----- 1 mysql mysql 1 May 27 02:47 xtrabackup_master_key_id6）启动服务：[root@kvm-centos8-3-2 backup2]# service mysqld startStarting MySQL.Logging to '/data/mysql/mysql.log'.SUCCESS! 7)验证mysql> select * from teachers;+-----+---------------+-----+--------+| TID | Name| Age | Gender |+-----+---------------+-----+--------+| 1 | Song Jiang | 45 | M|| 2 | Zhang Sanfeng | 94 | M|| 3 | Miejue Shitai | 77 | F|| 4 | Lin Chaoying | 93 | F|| 5 | Li Xiang| 30 | M|| 6 | Wei Lai | 28 | M|| 7 | Meng Xiang | 29 | F|| 8 | Xiao Hu | 25 | M|| 9 | Gala| 23 | M|| 10 | Ming| 23 | M|+-----+---------------+-----+--------+10 rows in set (0.00 sec)
如上，即是Xtrabackup实现对数据库的完全及增量备份完整过程 ，在实际工作环境中，如果需要实现自动备份，结合自己的实际需求，配合shell脚本加任务计划即可！
如果觉得《数据库备份：Xtrabackup实现完全备份及增量备份》对你有帮助，请点赞、收藏，并留下你的观点哦！
