一、xtrabackup 简介
xtrabackup 是由percona公司开源免费的数据库热备软件，它能对InnoDB数据库和XtraDB存储引擎的数据库非阻塞地备份，对于myisam的备份同样需要加表锁。MysqLdump备份方式是通过用户名密码连接到数据库，将其要备份的数据库用sql语句的形式输出到标准输出，这种逻辑备份的方式最大的缺点是备份和恢复的速度较慢，如果数据库过大，MysqLdump备份就显得力不从心了。这时选择用xtrabackup来备份就能很好解决数据库过大而导致备份过慢的问题。
xtrabackup的优点
1）备份还原过程快速、可靠
2）备份过程不会打断正在执行的事务
3）能够基于压缩等功能节约磁盘空间和流量
4）自动实现备份检验
5）开源，免费
innobackuppex：这个是一个perl脚本，它的主要作用是用来备份非innodb表，同时会调用xtrabackup命令来备份innodb表，还会和MysqL server 发送命令进行交互，如家全局读锁（FTWRL）、获取位置点（show slave status）等。
xtrabackup:这个文件是用C/C++编译的二进制文件，它的主要作用用来备份Innodb表达，不能备份非Innodb表，也没有和MysqL server交互。
xbcrypt:这个文件的主要作用用于加密解密
xtrabackup版本升级到2.4后，相比之前的2.1有了较大的变化，innobackupex功能全部集成到xtrabackup里面，只有一个二进制程序，另外为了兼容考虑，innobackupex作为xtrabackup的软连接，换句话说xtrabackup现在支持非Innodb表备份，并且innobackupex在下一个版本中移除，所以建议xtrabackup替换innobackupex
xtrabackup备份原理
1）备份开始时会启动两个进程一个是主进程innobackuppex，一个是子进程xtrabackup
2）xtrabackup创建一个redo线程，用于拷贝和实时监测msyql redo的变化，一旦有新的数据写入，它将立即把日志记录到日志文件xtrabackup_log中。
3）xtrabackup创建一个ibd线程，用于拷贝ibd（存放数据库的数据，索引）文件，ibd文件拷贝完成后，线程立即退出，并通知主进程ibd拷贝完毕
4）主进程对非Innodb表加全局读锁
5）主进程拷贝非innodb表的表空间文件，数据文件，索引文件，以及MysqL的配置文件
6）拷贝完非innodb表的文件后，主进程将给redo线程发送停止拷贝redo的消息
7）redo线程收到主进程的停止信号后，立刻停止，并给主进程发送redo拷贝结束的消息，然后线程退出
8）主进程收到redo拷贝完成对消息后，立刻对非innodb表解锁，并等待子进程xtrabackup的结束
二、xtrabackup安装
1）添加yum源
[root@test-centos7-node1 ~]# yum install http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm
说明：以上命令会在/etc/yum.repo.d/下面生成percona-release.repo 文件，此文件就是xtrabackup的yum仓库地址文件
2）查看xtrabackup包信息
[root@test-centos7-node1 ~]# yum info percona-xtrabackup
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
Available Packages
Name : percona-xtrabackup
Arch : x86_64
Version : 2.3.10
Release : 1.el7
Size : 5.0 M
Repo : percona-release-x86_64/7/x86_64
Summary : XtraBackup online backup for MysqL / InnoDB
URL : http://www.percona.com/software/percona-xtrabackup
License : GPLv2
Description : Percona XtraBackup is OpenSource online (non-blockable) backup solution for InnoDB and XtraDB engines
[root@test-centos7-node1 ~]#
说明：默认情况我们添加了xtrabackup的yum源后，默认是安装的2.3版本，需要安装2.2或者2.4可在后面添加版本号，如下
[root@test-centos7-node1 ~]# yum info percona-xtrabackup-22
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
Installed Packages
Name : percona-xtrabackup-22
Arch : x86_64
Version : 2.2.13
Release : 1.el7
Size : 18 M
Repo : installed
From repo : percona-release-x86_64
Summary : XtraBackup online backup for MysqL / InnoDB
URL : http://www.percona.com/software/percona-xtrabackup
License : GPLv2
Description : Percona XtraBackup is OpenSource online (non-blockable) backup solution for InnoDB and XtraDB engines
[root@test-centos7-node1 ~]# yum info percona-xtrabackup-24
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
Available Packages
Name : percona-xtrabackup-24
Arch : x86_64
Version : 2.4.18
Release : 1.el7
Size : 7.6 M
Repo : percona-release-x86_64/7/x86_64
Summary : XtraBackup online backup for MysqL / InnoDB
URL : http://www.percona.com/software/percona-xtrabackup
License : GPLv2
Description : Percona XtraBackup is OpenSource online (non-blockable) backup solution for InnoDB and XtraDB engines
[root@test-centos7-node1 ~]#
3）安装xtrabackup特定的版本
[root@test-centos7-node1 ~]# yum install -y percona-xtrabackup-24
4）查看percona-xtrabackup-24包安装路径
[root@test-centos7-node1 ~]# rpm -ql percona-xtrabackup-24
/usr/bin/innobackupex
/usr/bin/xbcloud
/usr/bin/xbcloud_osenv
/usr/bin/xbcrypt
/usr/bin/xbstream
/usr/bin/xtrabackup
/usr/lib64/xtrabackup/plugin/keyring_file.so
/usr/lib64/xtrabackup/plugin/keyring_vault.so
/usr/share/doc/percona-xtrabackup-24-2.4.18
/usr/share/doc/percona-xtrabackup-24-2.4.18/COPYING
/usr/share/man/man1/innobackupex.1.gz
/usr/share/man/man1/xbcrypt.1.gz
/usr/share/man/man1/xbstream.1.gz
/usr/share/man/man1/xtrabackup.1.gz
[root@test-centos7-node1 ~]# file /usr/bin/innobackupex
/usr/bin/innobackupex: symbolic link to `xtrabackup'
[root@test-centos7-node1 ~]# ll /usr/bin/innobackupex
lrwxrwxrwx. 1 root root 10 Jan 17 23:46 /usr/bin/innobackupex -> xtrabackup
[root@test-centos7-node1 ~]# file /usr/bin/xtrabackup
/usr/bin/xtrabackup: ELF 64-bit LSB executable,x86-64,version 1 (GNU/Linux),dynamically linked (uses shared libs),for GNU/Linux 2.6.32,BuildID[sha1]=259960b7e21a0a6a6aab5883cc81be34db03f42c,stripped
[root@test-centos7-node1 ~]#
说明：2.2版本以前/usr/bin/innobackupex 文件都是一个perl脚本，2.2以后/usr/bin/innobackupex 是一个软连接指向/usr/bin/xtrabackup
[root@test-centos6-node1 ~]# rpm -ql percona-xtrabackup-22
/usr/bin/innobackupex
/usr/bin/xbcrypt
/usr/bin/xbstream
/usr/bin/xtrabackup
/usr/share/doc/percona-xtrabackup-22-2.2.13
/usr/share/doc/percona-xtrabackup-22-2.2.13/COPYING
/usr/share/man/man1/innobackupex.1.gz
/usr/share/man/man1/xbcrypt.1.gz
/usr/share/man/man1/xbstream.1.gz
/usr/share/man/man1/xtrabackup.1.gz
[root@test-centos6-node1 ~]# file /usr/bin/innobackupex
/usr/bin/innobackupex: a /usr/bin/env perl script text executable
[root@test-centos6-node1 ~]# file /usr/bin/xtrabackup
/usr/bin/xtrabackup: ELF 64-bit LSB executable,version 1 (SYSV),for GNU/Linux 2.6.18,stripped
[root@test-centos6-node1 ~]#
二、xtrabackup用法
1）备份
--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
2）预处理整理
--apply-log：一般情况下,在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处理不一致状态。此选项作用是通过回滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态
--use-memory：和--apply-log选项一起使用，当prepare 备份时，做crash recovery分配的内存大小，单位字节，也可1MB,1M,1G,1GB等，推荐1G
--export：表示开启可导出单独的表之后再导入其他MysqL中
--redo-only：此选项在prepare base full backup，往其中合并增量备份时候使用，但不包括对最后一个增量备份的合并
3）还原
--copy-back：做数据恢复时将备份数据文件拷贝到MysqL服务器的datadir
--move-back：这个选项与--copy-back相似，唯一的区别是它不拷贝文件，而是移动文件到目的地。这个选项移除backup文件，用时候必须小心。使用场景：没有足够的磁盘空间同事保留数据文件和Backup副本
三、xtrabackup全备实现
1）不使用任何压缩工具复制全备
[root@test-centos7-node1 ~]# xtrabackup --backup --target-dir=/root/backup/
xtrabackup: recognized server arguments: --datadir=/var/lib/MysqL
xtrabackup: recognized client arguments: --backup=1 --target-dir=/root/backup/
200118 00:08:03 version_check Connecting to MysqL server with DSN 'dbi:MysqL:;MysqL_read_default_group=xtrabackup' (using password: NO).
200118 00:08:03 version_check Connected to MysqL server
200118 00:08:03 version_check Executing a version check against the server...
200118 00:08:03 version_check Done.
200118 00:08:03 Connecting to MysqL server host: localhost,user: not set,password: not set,port: not set,socket: not set
Using server version 5.5.56-MariaDB
xtrabackup version 2.4.18 based on MysqL server 5.7.26 Linux (x86_64) (revision id: 29b4ca5)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/MysqL
xtrabackup: open files limit requested 0,set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 5242880
InnoDB: Number of pools: 1
200118 00:08:03 >> log scanned up to (1597945)
xtrabackup: Generating a list of tablespaces
200118 00:08:04 [01] Copying ./ibdata1 to /root/backup/ibdata1
200118 00:08:04 [01] ...done
200118 00:08:04 >> log scanned up to (1597945)
200118 00:08:05 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
200118 00:08:05 Executing FLUSH TABLES WITH READ LOCK...
200118 00:08:05 Starting to backup non-InnoDB tables and files
200118 00:08:05 [01] Copying ./MysqL/db.frm to /root/backup/MysqL/db.frm
200118 00:08:05 [01] ...done
……省略部分内容
200118 00:08:05 [01] ...done
200118 00:08:05 Finished backing up non-InnoDB tables and files
200118 00:08:05 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '1597945'
xtrabackup: Stopping log copying thread.
.200118 00:08:05 >> log scanned up to (1597945)
200118 00:08:05 Executing UNLOCK TABLES
200118 00:08:05 All tables unlocked
200118 00:08:05 Backup created in directory '/root/backup/'
200118 00:08:05 [00] Writing /root/backup/backup-my.cnf
200118 00:08:05 [00] ...done
200118 00:08:05 [00] Writing /root/backup/xtrabackup_info
200118 00:08:05 [00] ...done
xtrabackup: Transaction log of lsn (1597945) to (1597945) was copied.
200118 00:08:05 completed OK!
[root@test-centos7-node1 ~]# ll backup/
total 18456
-rw-r-----. 1 root root 431 Jan 18 00:08 backup-my.cnf
-rw-r-----. 1 root root 18874368 Jan 18 00:08 ibdata1
drwxr-x---. 2 root root 4096 Jan 18 00:08 MysqL
drwxr-x---. 2 root root 4096 Jan 18 00:08 performance_schema
drwxr-x---. 2 root root 20 Jan 18 00:08 test
-rw-r-----. 1 root root 135 Jan 18 00:08 xtrabackup_checkpoints
-rw-r-----. 1 root root 425 Jan 18 00:08 xtrabackup_info
-rw-r-----. 1 root root 2560 Jan 18 00:08 xtrabackup_logfile
[root@test-centos7-node1 ~]#
说明：生产环境中需要指定用户名和密码来备份，这里需要注意一点xtrabackup备份是基于复制数据文件的方式来做的备份，它和MysqLdump不一样的是，它不能连接到远端服务器上把备份文件拉取到本地，但是它可在远端服务器上把备份推到本地来。换句话说xtrabackup 只能备份本地数据库的文件，执行命令需要在本地执行。
2）基于流式传输和压缩备份（--stream=xbstream）
[root@test-centos7-node1 ~]# xtrabackup --stream=xbstream --backup > /root/backup2/all_backup.xbstream
xtrabackup: recognized server arguments: --datadir=/var/lib/MysqL
xtrabackup: recognized client arguments: --stream=xbstream --backup=1
200118 00:36:52 version_check Connecting to MysqL server with DSN 'dbi:MysqL:;MysqL_read_default_group=xtrabackup' (using password: NO).
200118 00:36:52 version_check Connected to MysqL server
200118 00:36:52 version_check Executing a version check against the server...
200118 00:36:52 version_check Done.
200118 00:36:52 Connecting to MysqL server host: localhost,set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 5242880
InnoDB: Number of pools: 1
200118 00:36:52 >> log scanned up to (1597945)
xtrabackup: Generating a list of tablespaces
200118 00:36:53 [01] Streaming ./ibdata1
200118 00:36:53 [01] ...done
200118 00:36:53 >> log scanned up to (1597945)
200118 00:36:54 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
200118 00:36:54 Executing FLUSH TABLES WITH READ LOCK...
200118 00:36:54 Starting to backup non-InnoDB tables and files
200118 00:36:54 [01] Streaming ./MysqL/db.frm to <STDOUT>
200118 00:36:54 [01] ...done
……省略部分内容
200118 00:36:54 [01] Streaming ./performance_schema/threads.frm to <STDOUT>
200118 00:36:54 [01] ...done
200118 00:36:54 Finished backing up non-InnoDB tables and files
200118 00:36:54 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '1597945'
xtrabackup: Stopping log copying thread.
.200118 00:36:54 >> log scanned up to (1597945)
200118 00:36:54 Executing UNLOCK TABLES
200118 00:36:54 All tables unlocked
200118 00:36:54 Backup created in directory '/root/xtrabackup_backupfiles/'
200118 00:36:54 [00] Streaming <STDOUT>
200118 00:36:54 [00] ...done
200118 00:36:54 [00] Streaming <STDOUT>
200118 00:36:54 [00] ...done
xtrabackup: Transaction log of lsn (1597945) to (1597945) was copied.
200118 00:36:54 completed OK!
[root@test-centos7-node1 ~]# ll /root/backup2/
total 32768
-rw-r--r--. 1 root root 19908780 Jan 18 00:36 all_backup.xbstream
[root@test-centos7-node1 ~]#
说明：以上方式是通过流传输模式以xbstream的二进制格式将备份输出到标准输出，然后通过输出重定向到一个以.xbstream结尾的文件。这样备份有一个好处就是它可以自动加密备份数据，使得我们备份的数据相比复制文件的方式要安全。要使用流式传输功能，必须使用--stream，提供流（tar或xbstream）的格式以及临时文件的存储位置
3）使用流备份并压缩
[root@test-centos7-node1 ~]# xtrabackup --stream=xbstream --backup --compress > /root/backup2/all_backup2.xbstream
说明：使用--compress选项就表示压缩数据，以上命令备份的文件要比不加--compress备份的文件要小得多
[root@test-centos7-node1 ~]# ll backup2/
total 19984
-rw-r--r--. 1 root root 550056 Jan 18 00:42 all_backup2.xbstream
-rw-r--r--. 1 root root 19908780 Jan 18 00:36 all_backup.xbstream
[root@test-centos7-node1 ~]#
4）将流备份解压到指定目录
[root@test-centos7-node1 ~]# xbstream -x < backup2/all_backup.xbstream -C xxxx/
[root@test-centos7-node1 ~]# ll xxxx/
total 18456
-rw-r-----. 1 root root 431 Jan 18 01:00 backup-my.cnf
-rw-r-----. 1 root root 18874368 Jan 18 01:00 ibdata1
drwxr-x---. 2 root root 4096 Jan 18 01:00 MysqL
drwxr-x---. 2 root root 4096 Jan 18 01:00 performance_schema
drwxr-x---. 2 root root 20 Jan 18 01:00 test
-rw-r-----. 1 root root 135 Jan 18 01:00 xtrabackup_checkpoints
-rw-r-----. 1 root root 420 Jan 18 01:00 xtrabackup_info
-rw-r-----. 1 root root 2560 Jan 18 01:00 xtrabackup_logfile
[root@test-centos7-node1 ~]
说明：如果备份是通过--compress 备份，用以上命令解开后的文件是以.qp结尾的文件，这个文件是没法直接使用需要用对应的工具将其再解压。
[root@test-centos7-node1 ~]# xbstream -x < /root/backup2/all_backup2.xbstream -C /root/xtrabackup_backupfiles/
[root@test-centos7-node1 ~]# ll /root/xtrabackup_backupfiles/
total 292
-rw-r-----. 1 root root 407 Jan 18 00:56 backup-my.cnf.qp
-rw-r-----. 1 root root 270377 Jan 18 00:56 ibdata1.qp
drwxr-x---. 2 root root 4096 Jan 18 00:56 MysqL
drwxr-x---. 2 root root 4096 Jan 18 00:56 performance_schema
drwxr-x---. 2 root root 23 Jan 18 00:56 test
-rw-r-----. 1 root root 135 Jan 18 00:56 xtrabackup_checkpoints
-rw-r-----. 1 root root 423 Jan 18 00:56 xtrabackup_info.qp
-rw-r-----. 1 root root 498 Jan 18 00:56 xtrabackup_logfile.qp
[root@test-centos7-node1 ~]# file /root/xtrabackup_backupfiles/backup-my.cnf.qp
/root/xtrabackup_backupfiles/backup-my.cnf.qp: data
[root@test-centos7-node1 ~]#
说明：以上就是通过压缩后在通过流传输将标准输出到某一个文件后，解压该文件得到的文件，.qp的文件如果用cat 查看是乱码的，这个时候就需要工具来将其解压后就可正常查看。qpress工具就可将其文件解开。下载链接http://www.quicklz.com/qpress-11-linux-x64.tar。
[root@test-centos7-node1 ~]# ./qpress -d xtrabackup_backupfiles/backup-my.cnf.qp /root/
[root@test-centos7-node1 ~]# ll
total 160
drwxr-xr-x. 5 root root 172 Jan 18 00:08 backup
drwxr-xr-x. 2 root root 61 Jan 18 00:42 backup2
-rw-r--r--. 1 root root 431 Jan 18 01:12 backup-my.cnf
-rwxrwxrwx. 1 root root 75684 Sep 23 2010 qpress
-rw-r--r--. 1 root root 81920 Jan 18 01:09 qpress-11-linux-x64.tar
drwxr-x---. 5 root root 184 Jan 18 00:56 xtrabackup_backupfiles
drwxr-xr-x. 5 root root 172 Jan 18 01:00 xxxx
[root@test-centos7-node1 ~]# 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:10M:autoextend
innodb_log_files_in_group=2
innodb_log_file_size=5242880
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
master_key_id=0
[root@test-centos7-node1 ~]#
说明：下载下来的tar包解开后就是一个二进制文件，我们可以将这个二进制文件直接拷贝到/usr/bin 这样就不用./去运行。解压用-d 后面跟要解压到文件 和解压后的文件存放目录，这里需要注意一点，它和gzip不一样，gzip解压后原文件就消失了，而它不会。
5）将压缩备份发送到另一台主机并解压缩它
[root@test-centos7-node1 ~]# xtrabackup --stream=xbstream --backup | ssh root@192.168.0.11 "xbstream -x -C /root/"
xtrabackup: recognized server arguments: --datadir=/var/lib/MysqL
xtrabackup: recognized client arguments: --stream=xbstream --backup=1
200118 01:24:07 version_check Connecting to MysqL server with DSN 'dbi:MysqL:;MysqL_read_default_group=xtrabackup' (using password: NO).
200118 01:24:07 version_check Connected to MysqL server
200118 01:24:07 version_check Executing a version check against the server...
200118 01:24:07 version_check Done.
200118 01:24:07 Connecting to MysqL server host: localhost,set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 5242880
InnoDB: Number of pools: 1
200118 01:24:07 >> log scanned up to (1597945)
xtrabackup: Generating a list of tablespaces
200118 01:24:07 [01] Streaming ./ibdata1
200118 01:24:07 [01] ...done
200118 01:24:08 >> log scanned up to (1597945)
200118 01:24:08 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
200118 01:24:08 Executing FLUSH TABLES WITH READ LOCK...
200118 01:24:08 Starting to backup non-InnoDB tables and files
200118 01:24:08 [01] Streaming ./MysqL/db.frm to <STDOUT>
200118 01:24:08 [01] ...done
……省略部分内容
200118 01:24:08 [01] Streaming ./performance_schema/threads.frm to <STDOUT>
200118 01:24:08 [01] ...done
200118 01:24:08 Finished backing up non-InnoDB tables and files
200118 01:24:08 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '1597945'
xtrabackup: Stopping log copying thread.
.200118 01:24:08 >> log scanned up to (1597945)
200118 01:24:09 Executing UNLOCK TABLES
200118 01:24:09 All tables unlocked
200118 01:24:09 Backup created in directory '/root/xtrabackup_backupfiles/'
200118 01:24:09 [00] Streaming <STDOUT>
200118 01:24:09 [00] ...done
200118 01:24:09 [00] Streaming <STDOUT>
200118 01:24:09 [00] ...done
xtrabackup: Transaction log of lsn (1597945) to (1597945) was copied.
200118 01:24:09 completed OK!
[root@test-centos7-node1 ~]# ssh 192.168.0.11
Last login: Sat Jan 18 22:21:05 2020 from 192.168.0.10
[root@test-centos6-node1 ~]# ll
total 18460
-rw-r-----. 1 root root 431 Jan 18 22:23 backup-my.cnf
-rw-r-----. 1 root root 18874368 Jan 18 22:23 ibdata1
drwx------. 2 root root 4096 Jan 18 22:23 MysqL
drwx------. 2 root root 4096 Jan 18 22:23 performance_schema
drwx------. 2 root root 4096 Jan 18 22:23 test
-rw-r-----. 1 root root 135 Jan 18 22:23 xtrabackup_checkpoints
-rw-r-----. 1 root root 420 Jan 18 22:23 xtrabackup_info
-rw-r-----. 1 root root 2560 Jan 18 22:23 xtrabackup_logfile
[root@test-centos6-node1 ~]#
说明：以上命令很容易理解通过标准输出内容交给管道，通过ssh 远程执行命令解压数据。做以上操作前提建议先做好ssh认证免密；有关ssh 基于key验证免密请参考https://www.cnblogs.com/qiuhom-1874/p/11783371.html
6）基于流式传输和压缩备份（--stream=tar）
将完整备份直接存储到tar归档文件中
[root@test-centos7-node1 ~]# xtrabackup --stream=tar --backup > /root/all.tar
xtrabackup: recognized server arguments: --datadir=/var/lib/MysqL
xtrabackup: recognized client arguments: --stream=tar --backup=1
200118 01:36:41 version_check Connecting to MysqL server with DSN 'dbi:MysqL:;MysqL_read_default_group=xtrabackup' (using password: NO).
200118 01:36:41 version_check Connected to MysqL server
200118 01:36:41 version_check Executing a version check against the server...
200118 01:36:41 version_check Done.
200118 01:36:41 Connecting to MysqL server host: localhost,set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 5242880
InnoDB: Number of pools: 1
200118 01:36:41 >> log scanned up to (1597945)
xtrabackup: Generating a list of tablespaces
200118 01:36:41 [01] Streaming ./ibdata1
200118 01:36:41 [01] ...done
200118 01:36:42 >> log scanned up to (1597945)
200118 01:36:42 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
200118 01:36:42 Executing FLUSH TABLES WITH READ LOCK...
200118 01:36:42 Starting to backup non-InnoDB tables and files
200118 01:36:42 [01] Streaming ./MysqL/db.frm to <STDOUT>
200118 01:36:42 [01] ...done
……省略部分内容
200118 01:36:42 [01] Streaming ./performance_schema/threads.frm to <STDOUT>
200118 01:36:42 [01] ...done
200118 01:36:42 Finished backing up non-InnoDB tables and files
200118 01:36:42 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '1597945'
xtrabackup: Stopping log copying thread.
.200118 01:36:42 >> log scanned up to (1597945)
200118 01:36:42 Executing UNLOCK TABLES
200118 01:36:42 All tables unlocked
200118 01:36:42 Backup created in directory '/root/xtrabackup_backupfiles/'
200118 01:36:42 [00] Streaming <STDOUT>
200118 01:36:42 [00] ...done
200118 01:36:42 [00] Streaming <STDOUT>
200118 01:36:42 [00] ...done
xtrabackup: Transaction log of lsn (1597945) to (1597945) was copied.
200118 01:36:43 completed OK!
[root@test-centos7-node1 ~]# ll /root/
total 32928
-rw-r--r--. 1 root root 19964416 Jan 18 01:36 all.tar
drwxr-xr-x. 5 root root 172 Jan 18 00:08 backup
drwxr-xr-x. 2 root root 61 Jan 18 00:42 backup2
-rw-r--r--. 1 root root 431 Jan 18 01:12 backup-my.cnf
-rwxrwxrwx. 1 root root 75684 Sep 23 2010 qpress
-rw-r--r--. 1 root root 81920 Jan 18 01:09 qpress-11-linux-x64.tar
drwxr-x---. 5 root root 184 Jan 18 00:56 xtrabackup_backupfiles
drwxr-xr-x. 5 root root 172 Jan 18 01:00 xxxx
[root@test-centos7-node1 ~]#
要将tar存档发送到另一个主机
[root@test-centos7-node1 ~]# ssh 192.168.0.11
Last login: Sat Jan 18 22:45:34 2020 from 192.168.0.10
[root@test-centos6-node1 ~]# ls
[root@test-centos6-node1 ~]# exit
logout
Connection to 192.168.0.11 closed.
[root@test-centos7-node1 ~]# xtrabackup --stream=tar --backup | ssh root@192.168.0.11 "cat - > /root/all.tar"
xtrabackup: recognized server arguments: --datadir=/var/lib/MysqL
xtrabackup: recognized client arguments: --stream=tar --backup=1
200118 01:47:47 version_check Connecting to MysqL server with DSN 'dbi:MysqL:;MysqL_read_default_group=xtrabackup' (using password: NO).
200118 01:47:47 version_check Connected to MysqL server
200118 01:47:47 version_check Executing a version check against the server...
200118 01:47:47 version_check Done.
200118 01:47:47 Connecting to MysqL server host: localhost,set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 5242880
InnoDB: Number of pools: 1
200118 01:47:47 >> log scanned up to (1597945)
xtrabackup: Generating a list of tablespaces
200118 01:47:47 [01] Streaming ./ibdata1
200118 01:47:47 [01] ...done
200118 01:47:48 >> log scanned up to (1597945)
200118 01:47:48 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
200118 01:47:48 Executing FLUSH TABLES WITH READ LOCK...
200118 01:47:48 Starting to backup non-InnoDB tables and files
200118 01:47:48 [01] Streaming ./MysqL/db.frm to <STDOUT>
200118 01:47:48 [01] ...done
……省略部分内容
200118 01:47:48 [01] Streaming ./performance_schema/threads.frm to <STDOUT>
200118 01:47:48 [01] ...done
200118 01:47:48 Finished backing up non-InnoDB tables and files
200118 01:47:48 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '1597945'
xtrabackup: Stopping log copying thread.
.200118 01:47:48 >> log scanned up to (1597945)
200118 01:47:48 Executing UNLOCK TABLES
200118 01:47:48 All tables unlocked
200118 01:47:48 Backup created in directory '/root/xtrabackup_backupfiles/'
200118 01:47:48 [00] Streaming <STDOUT>
200118 01:47:48 [00] ...done
200118 01:47:48 [00] Streaming <STDOUT>
200118 01:47:48 [00] ...done
xtrabackup: Transaction log of lsn (1597945) to (1597945) was copied.
200118 01:47:48 completed OK!
[root@test-centos7-node1 ~]# ssh 192.168.0.11
Last login: Sat Jan 18 22:47:19 2020 from 192.168.0.10
[root@test-centos6-node1 ~]# ls
all.tar
[root@test-centos6-node1 ~]# mkdir test
[root@test-centos6-node1 ~]# tar xf all.tar -C test/
[root@test-centos6-node1 ~]# ll test/
total 18460
-rw-rw----. 1 root root 431 Jan 18 14:47 backup-my.cnf
-rw-rw----. 1 root root 18874368 Jan 18 13:06 ibdata1
drwxr-xr-x. 2 root root 4096 Jan 18 22:47 MysqL
drwxr-xr-x. 2 root root 4096 Jan 18 22:47 performance_schema
drwxr-xr-x. 2 root root 4096 Jan 18 22:47 test
-rw-rw----. 1 root root 135 Jan 18 14:47 xtrabackup_checkpoints
-rw-rw----. 1 root root 410 Jan 18 14:47 xtrabackup_info
-rw-rw----. 1 root root 2560 Jan 18 14:47 xtrabackup_logfile
[root@test-centos6-node1 ~]#
使用其他压缩工具压缩
gzip压缩
[root@test-centos7-node1 ~]# xtrabackup --stream=tar --backup | gzip - >/root/all.tar.gz
xtrabackup: recognized server arguments: --datadir=/var/lib/MysqL
xtrabackup: recognized client arguments: --stream=tar --backup=1
200118 01:51:13 version_check Connecting to MysqL server with DSN 'dbi:MysqL:;MysqL_read_default_group=xtrabackup' (using password: NO).
200118 01:51:13 version_check Connected to MysqL server
200118 01:51:13 version_check Executing a version check against the server...
200118 01:51:13 version_check Done.
200118 01:51:13 Connecting to MysqL server host: localhost,set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 5242880
InnoDB: Number of pools: 1
200118 01:51:13 >> log scanned up to (1597945)
xtrabackup: Generating a list of tablespaces
200118 01:51:13 [01] Streaming ./ibdata1
200118 01:51:14 [01] ...done
200118 01:51:14 >> log scanned up to (1597945)
200118 01:51:14 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
200118 01:51:14 Executing FLUSH TABLES WITH READ LOCK...
200118 01:51:14 Starting to backup non-InnoDB tables and files
200118 01:51:14 [01] Streaming ./MysqL/db.frm to <STDOUT>
200118 01:51:14 [01] ...done
……省略部分内容
200118 01:51:15 [01] Streaming ./performance_schema/threads.frm to <STDOUT>
200118 01:51:15 [01] ...done
200118 01:51:15 Finished backing up non-InnoDB tables and files
200118 01:51:15 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '1597945'
xtrabackup: Stopping log copying thread.
.200118 01:51:15 >> log scanned up to (1597945)
200118 01:51:15 Executing UNLOCK TABLES
200118 01:51:15 All tables unlocked
200118 01:51:15 Backup created in directory '/root/xtrabackup_backupfiles/'
200118 01:51:15 [00] Streaming <STDOUT>
200118 01:51:15 [00] ...done
200118 01:51:15 [00] Streaming <STDOUT>
200118 01:51:15 [00] ...done
xtrabackup: Transaction log of lsn (1597945) to (1597945) was copied.
200118 01:51:15 completed OK!
[root@test-centos7-node1 ~]# ls
all.tar.gz xtrabackup_backupfiles
[root@test-centos7-node1 ~]# ll xtrabackup_backupfiles/
total 0
[root@test-centos7-node1 ~]# tar xf all.tar.gz -C xtrabackup_backupfiles/
[root@test-centos7-node1 ~]# ll xtrabackup_backupfiles/
total 18456
-rw-rw----. 1 root root 431 Jan 18 01:51 backup-my.cnf
-rw-rw----. 1 root root 18874368 Jan 18 00:06 ibdata1
drwxr-xr-x. 2 root root 4096 Jan 18 01:51 MysqL
drwxr-xr-x. 2 root root 4096 Jan 18 01:51 performance_schema
drwxr-xr-x. 2 root root 20 Jan 18 01:51 test
-rw-rw----. 1 root root 135 Jan 18 01:51 xtrabackup_checkpoints
-rw-rw----. 1 root root 410 Jan 18 01:51 xtrabackup_info
-rw-rw----. 1 root root 2560 Jan 18 01:51 xtrabackup_logfile
[root@test-centos7-node1 ~]# ll
total 212
-rw-r--r--. 1 root root 213460 Jan 18 01:51 all.tar.gz
drwxr-x---. 5 root root 172 Jan 18 01:51 xtrabackup_backupfiles
[root@test-centos7-node1 ~]#
bzip2压缩
[root@test-centos7-node1 ~]# rm -rf *
[root@test-centos7-node1 ~]# ls
[root@test-centos7-node1 ~]# xtrabackup --backup --stream=tar |bzip2 - > /root/all.tar.bz2
xtrabackup: recognized server arguments: --datadir=/var/lib/MysqL
xtrabackup: recognized client arguments: --backup=1 --stream=tar
200118 01:57:02 version_check Connecting to MysqL server with DSN 'dbi:MysqL:;MysqL_read_default_group=xtrabackup' (using password: NO).
200118 01:57:02 version_check Connected to MysqL server
200118 01:57:02 version_check Executing a version check against the server...
200118 01:57:02 version_check Done.
200118 01:57:02 Connecting to MysqL server host: localhost,set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 5242880
InnoDB: Number of pools: 1
200118 01:57:02 >> log scanned up to (1597945)
xtrabackup: Generating a list of tablespaces
200118 01:57:03 [01] Streaming ./ibdata1
200118 01:57:03 [01] ...done
200118 01:57:03 >> log scanned up to (1597945)
200118 01:57:04 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
200118 01:57:04 Executing FLUSH TABLES WITH READ LOCK...
200118 01:57:04 Starting to backup non-InnoDB tables and files
200118 01:57:04 [01] Streaming ./MysqL/db.frm to <STDOUT>
200118 01:57:04 [01] ...done
……省略部分内容
200118 01:57:04 [01] Streaming ./performance_schema/threads.frm to <STDOUT>
200118 01:57:04 [01] ...done
200118 01:57:04 Finished backing up non-InnoDB tables and files
200118 01:57:04 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '1597945'
xtrabackup: Stopping log copying thread.
.200118 01:57:04 >> log scanned up to (1597945)
200118 01:57:04 Executing UNLOCK TABLES
200118 01:57:04 All tables unlocked
200118 01:57:04 Backup created in directory '/root/xtrabackup_backupfiles/'
200118 01:57:04 [00] Streaming <STDOUT>
200118 01:57:04 [00] ...done
200118 01:57:04 [00] Streaming <STDOUT>
200118 01:57:04 [00] ...done
xtrabackup: Transaction log of lsn (1597945) to (1597945) was copied.
200118 01:57:04 completed OK!
[root@test-centos7-node1 ~]# ls
all.tar.bz2 xtrabackup_backupfiles
[root@test-centos7-node1 ~]# ll xtrabackup_backupfiles/
total 0
[root@test-centos7-node1 ~]# tar xf all.tar.bz2 -C xtrabackup_backupfiles/
[root@test-centos7-node1 ~]# ll xtrabackup_backupfiles/
total 18456
-rw-rw----. 1 root root 431 Jan 18 01:57 backup-my.cnf
-rw-rw----. 1 root root 18874368 Jan 18 00:06 ibdata1
drwxr-xr-x. 2 root root 4096 Jan 18 01:57 MysqL
drwxr-xr-x. 2 root root 4096 Jan 18 01:57 performance_schema
drwxr-xr-x. 2 root root 20 Jan 18 01:57 test
-rw-rw----. 1 root root 135 Jan 18 01:57 xtrabackup_checkpoints
-rw-rw----. 1 root root 410 Jan 18 01:57 xtrabackup_info
-rw-rw----. 1 root root 2560 Jan 18 01:57 xtrabackup_logfile
[root@test-centos7-node1 ~]#
xz压缩
[root@test-centos7-node1 ~]# rm -rf *
[root@test-centos7-node1 ~]# ls
[root@test-centos7-node1 ~]# xtrabackup --backup --stream=tar |xz - > /root/all.tar.xz
xtrabackup: recognized server arguments: --datadir=/var/lib/MysqL
xtrabackup: recognized client arguments: --backup=1 --stream=tar
200118 01:58:45 version_check Connecting to MysqL server with DSN 'dbi:MysqL:;MysqL_read_default_group=xtrabackup' (using password: NO).
200118 01:58:45 version_check Connected to MysqL server
200118 01:58:45 version_check Executing a version check against the server...
200118 01:58:45 version_check Done.
200118 01:58:45 Connecting to MysqL server host: localhost,set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 5242880
InnoDB: Number of pools: 1
200118 01:58:45 >> log scanned up to (1597945)
xtrabackup: Generating a list of tablespaces
200118 01:58:45 [01] Streaming ./ibdata1
200118 01:58:46 [01] ...done
200118 01:58:46 >> log scanned up to (1597945)
200118 01:58:46 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
200118 01:58:46 Executing FLUSH TABLES WITH READ LOCK...
200118 01:58:46 Starting to backup non-InnoDB tables and files
200118 01:58:46 [01] Streaming ./MysqL/db.frm to <STDOUT>
200118 01:58:46 [01] ...done
……省略部分内容
200118 01:58:47 [01] Streaming ./performance_schema/threads.frm to <STDOUT>
200118 01:58:47 [01] ...done
200118 01:58:47 Finished backing up non-InnoDB tables and files
200118 01:58:47 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '1597945'
xtrabackup: Stopping log copying thread.
.200118 01:58:47 >> log scanned up to (1597945)
200118 01:58:47 Executing UNLOCK TABLES
200118 01:58:47 All tables unlocked
200118 01:58:47 Backup created in directory '/root/xtrabackup_backupfiles/'
200118 01:58:47 [00] Streaming <STDOUT>
200118 01:58:47 [00] ...done
200118 01:58:47 [00] Streaming <STDOUT>
200118 01:58:47 [00] ...done
xtrabackup: Transaction log of lsn (1597945) to (1597945) was copied.
200118 01:58:47 completed OK!
[root@test-centos7-node1 ~]# ll
total 136
-rw-r--r--. 1 root root 136148 Jan 18 01:58 all.tar.xz
drwxr-x---. 2 root root 6 Jan 18 01:58 xtrabackup_backupfiles
[root@test-centos7-node1 ~]# ll xtrabackup_backupfiles/
total 0
[root@test-centos7-node1 ~]# tar xf all.tar.xz -C xtrabackup_backupfiles/
[root@test-centos7-node1 ~]# ll xtrabackup_backupfiles/
total 18456
-rw-rw----. 1 root root 431 Jan 18 01:58 backup-my.cnf
-rw-rw----. 1 root root 18874368 Jan 18 00:06 ibdata1
drwxr-xr-x. 2 root root 4096 Jan 18 01:59 MysqL
drwxr-xr-x. 2 root root 4096 Jan 18 01:59 performance_schema
drwxr-xr-x. 2 root root 20 Jan 18 01:59 test
-rw-rw----. 1 root root 135 Jan 18 01:58 xtrabackup_checkpoints
-rw-rw----. 1 root root 410 Jan 18 01:58 xtrabackup_info
-rw-rw----. 1 root root 2560 Jan 18 01:58 xtrabackup_logfile
[root@test-centos7-node1 ~]#
四、xtrabackup全备还原实现
1）删库前数据库里的库表
[root@test-centos7-node1 ~]# MysqL
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 5.5.56-MariaDB MariaDB Server
Copyright (c) 2000,2017,Oracle,MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| MysqL |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
MariaDB [(none)]> use hellodb
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
MariaDB [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
7 rows in set (0.00 sec)
MariaDB [hellodb]> \q
Bye
[root@test-centos7-node1 ~]#
2）全量备份
[root@test-centos7-node1 ~]# xtrabackup --backup --target-dir=/root/backup
xtrabackup: recognized server arguments: --datadir=/var/lib/MysqL
xtrabackup: recognized client arguments: --backup=1 --target-dir=/root/backup
200118 02:29:28 version_check Connecting to MysqL server with DSN 'dbi:MysqL:;MysqL_read_default_group=xtrabackup' (using password: NO).
200118 02:29:28 version_check Connected to MysqL server
200118 02:29:28 version_check Executing a version check against the server...
200118 02:29:28 version_check Done.
200118 02:29:28 Connecting to MysqL server host: localhost,set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 5242880
InnoDB: Number of pools: 1
200118 02:29:28 >> log scanned up to (1628321)
xtrabackup: Generating a list of tablespaces
200118 02:29:28 [01] Copying ./ibdata1 to /root/backup/ibdata1
200118 02:29:28 [01] ...done
200118 02:29:29 >> log scanned up to (1628321)
200118 02:29:29 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
200118 02:29:29 Executing FLUSH TABLES WITH READ LOCK...
200118 02:29:29 Starting to backup non-InnoDB tables and files
200118 02:29:29 [01] Copying ./MysqL/db.frm to /root/backup/MysqL/db.frm
200118 02:29:29 [01] ...done
……省略部分内容
200118 02:29:30 [01] Copying ./hellodb/toc.frm to /root/backup/hellodb/toc.frm
200118 02:29:30 [01] ...done
200118 02:29:30 Finished backing up non-InnoDB tables and files
200118 02:29:30 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '1628321'
xtrabackup: Stopping log copying thread.
.200118 02:29:30 >> log scanned up to (1628321)
200118 02:29:30 Executing UNLOCK TABLES
200118 02:29:30 All tables unlocked
200118 02:29:30 Backup created in directory '/root/backup/'
200118 02:29:30 [00] Writing /root/backup/backup-my.cnf
200118 02:29:30 [00] ...done
200118 02:29:30 [00] Writing /root/backup/xtrabackup_info
200118 02:29:30 [00] ...done
xtrabackup: Transaction log of lsn (1628321) to (1628321) was copied.
200118 02:29:30 completed OK!
[root@test-centos7-node1 ~]# ll /root/backup/
total 18456
-rw-r-----. 1 root root 431 Jan 18 02:29 backup-my.cnf
drwxr-x---. 2 root root 146 Jan 18 02:29 hellodb
-rw-r-----. 1 root root 18874368 Jan 18 02:29 ibdata1
drwxr-x---. 2 root root 4096 Jan 18 02:29 MysqL
drwxr-x---. 2 root root 4096 Jan 18 02:29 performance_schema
drwxr-x---. 2 root root 20 Jan 18 02:29 test
-rw-r-----. 1 root root 135 Jan 18 02:29 xtrabackup_checkpoints
-rw-r-----. 1 root root 424 Jan 18 02:29 xtrabackup_info
-rw-r-----. 1 root root 2560 Jan 18 02:29 xtrabackup_logfile
[root@test-centos7-node1 ~]#
3）删库
[root@test-centos7-node1 ~]# ll /var/lib/MysqL/
total 28700
-rw-rw----. 1 MysqL MysqL 16384 Jan 18 02:26 aria_log.00000001
-rw-rw----. 1 MysqL MysqL 52 Jan 18 02:26 aria_log_control
drwx------. 2 MysqL MysqL 146 Jan 18 02:27 hellodb
-rw-rw----. 1 MysqL MysqL 18874368 Jan 18 02:27 ibdata1
-rw-rw----. 1 MysqL MysqL 5242880 Jan 18 02:27 ib_logfile0
-rw-rw----. 1 MysqL MysqL 5242880 Jan 18 02:26 ib_logfile1
drwx------. 2 MysqL MysqL 4096 Jan 18 02:26 MysqL
srwxrwxrwx. 1 MysqL MysqL 0 Jan 18 02:26 MysqL.sock
drwx------. 2 MysqL MysqL 4096 Jan 18 02:26 performance_schema
drwx------. 2 MysqL MysqL 6 Jan 18 02:26 test
[root@test-centos7-node1 ~]# rm -rf /var/lib/MysqL/*
[root@test-centos7-node1 ~]# ll /var/lib/MysqL/
total 0
[root@test-centos7-node1 ~]#
4）停掉数据库进行还原
[root@test-centos7-node1 ~]# systemctl stop mariadb
[root@test-centos7-node1 ~]# ss -ntl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 *:22 *:*
LISTEN 0 100 127.0.0.1:25 *:*
LISTEN 0 128 :::22 :::*
LISTEN 0 100 ::1:25 :::*
[root@test-centos7-node1 ~]# xtrabackup --prepare --target-dir=/root/backup/
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=innodb --innodb_log_checksum_algorithm=innodb --innodb_data_file_path=ibdata1:10M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=5242880 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=. --innodb_undo_tablespaces=0 --server-id=0 --redo-log-version=0
xtrabackup: recognized client arguments: --prepare=1 --target-dir=/root/backup/
xtrabackup version 2.4.18 based on MysqL server 5.7.26 Linux (x86_64) (revision id: 29b4ca5)
xtrabackup: cd to /root/backup/
xtrabackup: This target seems to be not prepared yet.
InnoDB: Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608,start_lsn=(1629224)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = .
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 8388608
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = .
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 8388608
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
InnoDB: PUNCH HOLE support available
InnoDB: Mutexes and rw_locks use GCC atomic builtins
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 buffer pool
InnoDB: page_cleaner coordinator priority: -20
InnoDB: Highest supported file format is Barracuda.
InnoDB: Creating shared tablespace for temporary tables
InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
InnoDB: File './ibtmp1' size is now 12 MB.
InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
InnoDB: 32 non-redo rollback segment(s) are active.
InnoDB: 5.7.26 started; log sequence number 1629224
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1629243
InnoDB: Number of pools: 1
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = .
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 5242880
InnoDB: PUNCH HOLE support available
InnoDB: Mutexes and rw_locks use GCC atomic builtins
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,chunk size = 100M
InnoDB: Completed initialization of buffer pool
InnoDB: page_cleaner coordinator priority: -20
InnoDB: Setting log file ./ib_logfile101 size to 5 MB
InnoDB: Setting log file ./ib_logfile1 size to 5 MB
InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
InnoDB: New log files created,LSN=1629243
InnoDB: Highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 1629708
InnoDB: Doing recovery: scanned up to log sequence number 1629717 (0%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: Removed temporary tablespace data file: "ibtmp1"
InnoDB: Creating shared tablespace for temporary tables
InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
InnoDB: File './ibtmp1' size is now 12 MB.
InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
InnoDB: 32 non-redo rollback segment(s) are active.
InnoDB: 5.7.26 started; log sequence number 1629717
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1629736
200118 03:09:08 completed OK!
[root@test-centos7-node1 ~]# xtrabackup --copy-back --target-dir=/root/backup/
xtrabackup: recognized server arguments: --datadir=/var/lib/MysqL
xtrabackup: recognized client arguments: --copy-back=1 --target-dir=/root/backup/
xtrabackup version 2.4.18 based on MysqL server 5.7.26 Linux (x86_64) (revision id: 29b4ca5)
200118 03:10:13 [01] Copying ib_logfile0 to /var/lib/MysqL/ib_logfile0
200118 03:10:13 [01] ...done
……省略部分内容
200118 03:10:13 [01] Copying ./ibtmp1 to /var/lib/MysqL/ibtmp1
200118 03:10:14 [01] ...done
200118 03:10:14 completed OK!
[root@test-centos7-node1 ~]# chown -R MysqL.MysqL /var/lib/MysqL/
[root@test-centos7-node1 ~]# ll /var/lib/MysqL/
total 40976
drwxr-x---. 2 MysqL MysqL 146 Jan 18 03:10 hellodb
-rw-r-----. 1 MysqL MysqL 18874368 Jan 18 03:10 ibdata1
-rw-r-----. 1 MysqL MysqL 5242880 Jan 18 03:10 ib_logfile0
-rw-r-----. 1 MysqL MysqL 5242880 Jan 18 03:10 ib_logfile1
-rw-r-----. 1 MysqL MysqL 12582912 Jan 18 03:10 ibtmp1
drwxr-x---. 2 MysqL MysqL 4096 Jan 18 03:10 MysqL
drwxr-x---. 2 MysqL MysqL 4096 Jan 18 03:10 performance_schema
drwxr-x---. 2 MysqL MysqL 20 Jan 18 03:10 test
-rw-r-----. 1 MysqL MysqL 425 Jan 18 03:10 xtrabackup_info
-rw-r-----. 1 MysqL MysqL 1 Jan 18 03:10 xtrabackup_master_key_id
[root@test-centos7-node1 ~]#
说明：还原操作需要进行三步，第一步是预准备，这一步的主要作用是确保数据的一致性，提交完成的事务，回滚未完成的事务。第二步是复制预处理后的数据文件到mariadb的工作目录，第三部还原拷贝过去的文件的属性为MysqL。执行拷贝操作需要确保原数据目录为空，或者加上--force-non-empty-directorires否则copy的时候会报错。
5）启动mariadb，查看数据库里的库表是否恢复到删除前的状态
[root@test-centos7-node1 ~]# systemctl start mariadb
[root@test-centos7-node1 ~]# ss -ntl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 *:22 *:*
LISTEN 0 100 127.0.0.1:25 *:*
LISTEN 0 50 *:3306 *:*
LISTEN 0 128 :::22 :::*
LISTEN 0 100 ::1:25 :::*
[root@test-centos7-node1 ~]# MysqL
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.56-MariaDB MariaDB Server
Copyright (c) 2000,MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| MysqL |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
MariaDB [(none)]> use hellodb
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
MariaDB [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
7 rows in set (0.01 sec)
MariaDB [hellodb]>
说明：可看到数据库已经恢复到备份前的状态
五、xtrabackup 增量备份还原实现
1）完全备份
[root@test-centos7-node1 ~]# ls
[root@test-centos7-node1 ~]# mkdir /root/{full_bak,incre_bak1,incre_bak2}
[root@test-centos7-node1 ~]# ls
full_bak incre_bak1 incre_bak2
[root@test-centos7-node1 ~]# xtrabackup --backup --target-dir=/root/full_bak/
xtrabackup: recognized server arguments: --datadir=/var/lib/MysqL
xtrabackup: recognized client arguments: --backup=1 --target-dir=/root/full_bak/
200118 03:28:20 version_check Connecting to MysqL server with DSN 'dbi:MysqL:;MysqL_read_default_group=xtrabackup' (using password: NO).
200118 03:28:20 version_check Connected to MysqL server
200118 03:28:20 version_check Executing a version check against the server...
200118 03:28:20 version_check Done.
200118 03:28:20 Connecting to MysqL server host: localhost,set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 5242880
InnoDB: Number of pools: 1
200118 03:28:20 >> log scanned up to (1629736)
xtrabackup: Generating a list of tablespaces
200118 03:28:20 [01] Copying ./ibdata1 to /root/full_bak/ibdata1
200118 03:28:20 [01] ...done
200118 03:28:21 >> log scanned up to (1629736)
200118 03:28:21 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
200118 03:28:21 Executing FLUSH TABLES WITH READ LOCK...
200118 03:28:21 Starting to backup non-InnoDB tables and files
200118 03:28:21 [01] Copying ./MysqL/db.frm to /root/full_bak/MysqL/db.frm
200118 03:28:21 [01] ...done
……省略部分内容
200118 03:28:21 [01] Copying ./hellodb/toc.frm to /root/full_bak/hellodb/toc.frm
200118 03:28:21 [01] ...done
200118 03:28:21 Finished backing up non-InnoDB tables and files
200118 03:28:21 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '1629736'
xtrabackup: Stopping log copying thread.
.200118 03:28:21 >> log scanned up to (1629736)
200118 03:28:21 Executing UNLOCK TABLES
200118 03:28:21 All tables unlocked
200118 03:28:21 Backup created in directory '/root/full_bak/'
200118 03:28:21 [00] Writing /root/full_bak/backup-my.cnf
200118 03:28:21 [00] ...done
200118 03:28:21 [00] Writing /root/full_bak/xtrabackup_info
200118 03:28:21 [00] ...done
xtrabackup: Transaction log of lsn (1629736) to (1629736) was copied.
200118 03:28:21 completed OK!
[root@test-centos7-node1 ~]#
2）修改数据，在做基于第一次全量备份做第一次增量备份
[root@test-centos7-node1 ~]# MysqL
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 6
Server version: 5.5.56-MariaDB MariaDB Server
Copyright (c) 2000,MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| MysqL |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
MariaDB [(none)]> create database abc;
Query OK,1 row affected (0.00 sec)
MariaDB [(none)]> use abc
Database changed
MariaDB [abc]> create table test(id int );
Query OK,0 rows affected (0.01 sec)
MariaDB [abc]> insert test(id)value(1);
Query OK,1 row affected (0.00 sec)
MariaDB [abc]> insert test(id)value(2),(3),(4);
Query OK,3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
MariaDB [abc]> select * from test ;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
+------+
4 rows in set (0.00 sec)
MariaDB [abc]> \q
Bye
[root@test-centos7-node1 ~]#
[root@test-centos7-node1 ~]# xtrabackup --backup --target-dir=/root/incre_bak1/ --incremental-basedir=/root/full_bak/
xtrabackup: recognized server arguments: --datadir=/var/lib/MysqL
xtrabackup: recognized client arguments: --backup=1 --target-dir=/root/incre_bak1/ --incremental-basedir=/root/full_bak/
200118 03:49:15 version_check Connecting to MysqL server with DSN 'dbi:MysqL:;MysqL_read_default_group=xtrabackup' (using password: NO).
200118 03:49:15 version_check Connected to MysqL server
200118 03:49:15 version_check Executing a version check against the server...
200118 03:49:15 version_check Done.
200118 03:49:15 Connecting to MysqL server host: localhost,socket: not set
Using server version 5.5.56-MariaDB
xtrabackup version 2.4.18 based on MysqL server 5.7.26 Linux (x86_64) (revision id: 29b4ca5)
incremental backup from 1629736 is enabled.
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/MysqL
xtrabackup: open files limit requested 0,set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 5242880
InnoDB: Number of pools: 1
200118 03:49:15 >> log scanned up to (1631702)
xtrabackup: Generating a list of tablespaces
xtrabackup: using the full scan for incremental backup
200118 03:49:15 [01] Copying ./ibdata1 to /root/incre_bak1/ibdata1.delta
200118 03:49:15 [01] ...done
200118 03:49:16 >> log scanned up to (1631702)
200118 03:49:16 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
200118 03:49:16 Executing FLUSH TABLES WITH READ LOCK...
200118 03:49:16 Starting to backup non-InnoDB tables and files
200118 03:49:16 [01] Copying ./MysqL/db.frm to /root/incre_bak1/MysqL/db.frm
200118 03:49:16 [01] ...done
……省略部分内容
200118 03:49:17 [01] Copying ./abc/test.frm to /root/incre_bak1/abc/test.frm
200118 03:49:17 [01] ...done
200118 03:49:17 Finished backing up non-InnoDB tables and files
200118 03:49:17 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '1631702'
xtrabackup: Stopping log copying thread.
.200118 03:49:17 >> log scanned up to (1631702)
200118 03:49:17 Executing UNLOCK TABLES
200118 03:49:17 All tables unlocked
200118 03:49:17 Backup created in directory '/root/incre_bak1/'
200118 03:49:17 [00] Writing /root/incre_bak1/backup-my.cnf
200118 03:49:17 [00] ...done
200118 03:49:17 [00] Writing /root/incre_bak1/xtrabackup_info
200118 03:49:17 [00] ...done
xtrabackup: Transaction log of lsn (1631702) to (1631702) was copied.
200118 03:49:17 completed OK!
[root@test-centos7-node1 ~]#
3）第二次修改数据，做基于第一次增量比分为basedir做第二次整理备份
[root@test-centos7-node1 ~]# MysqL
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 13
Server version: 5.5.56-MariaDB MariaDB Server
Copyright (c) 2000,MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| abc |
| hellodb |
| MysqL |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.02 sec)
MariaDB [(none)]> create user test;
Query OK,0 rows affected (0.07 sec)
MariaDB [(none)]> select user,host from MysqL.user;
+------+--------------------+
| user | host |
+------+--------------------+
| test | % |
| root | 127.0.0.1 |
| root | ::1 |
| root | localhost |
| root | test-centos7-node1 |
+------+--------------------+
5 rows in set (0.00 sec)
MariaDB [(none)]> \q
Bye
[root@test-centos7-node1 ~]#
[root@test-centos7-node1 ~]# xtrabackup --backup --target-dir=/root/incre_bak2/ --incremental-basedir=/root/incre_bak1/
xtrabackup: recognized server arguments: --datadir=/var/lib/MysqL
xtrabackup: recognized client arguments: --backup=1 --target-dir=/root/incre_bak2/ --incremental-basedir=/root/incre_bak1/
200118 03:52:43 version_check Connecting to MysqL server with DSN 'dbi:MysqL:;MysqL_read_default_group=xtrabackup' (using password: NO).
200118 03:52:43 version_check Connected to MysqL server
200118 03:52:43 version_check Executing a version check against the server...
200118 03:52:43 version_check Done.
200118 03:52:43 Connecting to MysqL server host: localhost,socket: not set
Using server version 5.5.56-MariaDB
xtrabackup version 2.4.18 based on MysqL server 5.7.26 Linux (x86_64) (revision id: 29b4ca5)
incremental backup from 1631702 is enabled.
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/MysqL
xtrabackup: open files limit requested 0,set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 5242880
InnoDB: Number of pools: 1
200118 03:52:43 >> log scanned up to (1631702)
xtrabackup: Generating a list of tablespaces
xtrabackup: using the full scan for incremental backup
200118 03:52:43 [01] Copying ./ibdata1 to /root/incre_bak2/ibdata1.delta
200118 03:52:43 [01] ...done
200118 03:52:44 >> log scanned up to (1631702)
200118 03:52:44 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
200118 03:52:44 Executing FLUSH TABLES WITH READ LOCK...
200118 03:52:44 Starting to backup non-InnoDB tables and files
200118 03:52:44 [01] Copying ./MysqL/db.frm to /root/incre_bak2/MysqL/db.frm
200118 03:52:44 [01] ...done
……省略部分内容
200118 03:52:44 [01] Copying ./abc/test.frm to /root/incre_bak2/abc/test.frm
200118 03:52:44 [01] ...done
200118 03:52:44 Finished backing up non-InnoDB tables and files
200118 03:52:44 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '1631702'
xtrabackup: Stopping log copying thread.
.200118 03:52:44 >> log scanned up to (1631702)
200118 03:52:45 Executing UNLOCK TABLES
200118 03:52:45 All tables unlocked
200118 03:52:45 Backup created in directory '/root/incre_bak2/'
200118 03:52:45 [00] Writing /root/incre_bak2/backup-my.cnf
200118 03:52:45 [00] ...done
200118 03:52:45 [00] Writing /root/incre_bak2/xtrabackup_info
200118 03:52:45 [00] ...done
xtrabackup: Transaction log of lsn (1631702) to (1631702) was copied.
200118 03:52:45 completed OK!
[root@test-centos7-node1 ~]#
到此两次增量备份已经全部做好，接下来查看数据，在删除库表来进行还原
[root@test-centos7-node1 ~]# MysqL
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 16
Server version: 5.5.56-MariaDB MariaDB Server
Copyright (c) 2000,MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| abc |
| hellodb |
| MysqL |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.02 sec)
MariaDB [(none)]> use abc
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
MariaDB [abc]> show tables;
+---------------+
| Tables_in_abc |
+---------------+
| test |
+---------------+
1 row in set (0.00 sec)
MariaDB [abc]> select * from test;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
+------+
4 rows in set (0.01 sec)
MariaDB [abc]> select user,host,password from MysqL.user;
+------+--------------------+----------+
| user | host | password |
+------+--------------------+----------+
| root | localhost | |
| root | test-centos7-node1 | |
| root | 127.0.0.1 | |
| root | ::1 | |
| test | % | |
+------+--------------------+----------+
5 rows in set (0.01 sec)
MariaDB [abc]> \q
Bye
[root@test-centos7-node1 ~]#
删除前的数据如上，现在我们模拟删除MysqL.user表和abc库，然后利用增量备份将其还原
[root@test-centos7-node1 ~]# MysqL
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 17
Server version: 5.5.56-MariaDB MariaDB Server
Copyright (c) 2000,MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| abc |
| hellodb |
| MysqL |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.00 sec)
MariaDB [(none)]> drop database abc;
Query OK,1 row affected (0.01 sec)
MariaDB [(none)]> drop table MysqL.user;
Query OK,0 rows affected (0.00 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| MysqL |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
MariaDB [(none)]> use MysqL
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
MariaDB [MysqL]> show tables;
+---------------------------+
| Tables_in_MysqL |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
+---------------------------+
23 rows in set (0.00 sec)
MariaDB [MysqL]>
还原数据库
合并全量备份
[root@test-centos7-node1 ~]# xtrabackup --prepare --apply-log-only --target-dir=/root/full_bak/
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=innodb --innodb_log_checksum_algorithm=innodb --innodb_data_file_path=ibdata1:10M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=5242880 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=. --innodb_undo_tablespaces=0 --server-id=0 --redo-log-version=0
xtrabackup: recognized client arguments: --prepare=1 --apply-log-only=1 --target-dir=/root/full_bak/
xtrabackup version 2.4.18 based on MysqL server 5.7.26 Linux (x86_64) (revision id: 29b4ca5)
xtrabackup: cd to /root/full_bak/
xtrabackup: This target seems to be not prepared yet.
InnoDB: Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608,start_lsn=(1629736)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = .
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 8388608
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = .
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 8388608
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
InnoDB: PUNCH HOLE support available
InnoDB: Mutexes and rw_locks use GCC atomic builtins
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,chunk size = 100M
InnoDB: Completed initialization of buffer pool
InnoDB: page_cleaner coordinator priority: -20
InnoDB: Highest supported file format is Barracuda.
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1629745
InnoDB: Number of pools: 1
200118 04:01:33 completed OK!
说明：合并全量备份要加选项--apply-log-only 表示不会滚未完成的事务，因为后面还有增量备份。
合并第一次增量备份到完全备份里
[root@test-centos7-node1 ~]# xtrabackup --prepare --apply-log-only --target-dir=/root/full_bak/ --incremental-dir=/root/incre_bak1
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=innodb --innodb_log_checksum_algorithm=innodb --innodb_data_file_path=ibdata1:10M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=5242880 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=. --innodb_undo_tablespaces=0 --server-id=0 --redo-log-version=0
xtrabackup: recognized client arguments: --prepare=1 --apply-log-only=1 --target-dir=/root/full_bak/ --incremental-dir=/root/incre_bak1
xtrabackup version 2.4.18 based on MysqL server 5.7.26 Linux (x86_64) (revision id: 29b4ca5)
incremental backup from 1629736 is enabled.
xtrabackup: cd to /root/full_bak/
xtrabackup: This target seems to be already prepared with --apply-log-only.
InnoDB: Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608,start_lsn=(1631702)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = /root/incre_bak1/
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 8388608
xtrabackup: Generating a list of tablespaces
xtrabackup: page size for /root/incre_bak1//ibdata1.delta is 16384 bytes space id is 0
Applying /root/incre_bak1//ibdata1.delta to ./ibdata1...
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = /root/incre_bak1/
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 8388608
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
InnoDB: PUNCH HOLE support available
InnoDB: Mutexes and rw_locks use GCC atomic builtins
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,chunk size = 100M
InnoDB: Completed initialization of buffer pool
InnoDB: page_cleaner coordinator priority: -20
InnoDB: Highest supported file format is Barracuda.
InnoDB: The log sequence number 1629736 in the system tablespace does not match the log sequence number 1631702 in the ib_logfiles!
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1631711
InnoDB: Number of pools: 1
200118 04:02:37 [01] Copying /root/incre_bak1/MysqL/db.frm to ./MysqL/db.frm
200118 04:02:37 [01] ...done
……省略部分内容
200118 04:02:37 [01] Copying /root/incre_bak1/abc/test.frm to ./abc/test.frm
200118 04:02:37 [01] ...done
200118 04:02:37 [00] Copying /root/incre_bak1//xtrabackup_info to ./xtrabackup_info
200118 04:02:37 [00] ...done
200118 04:02:37 completed OK!
合并第二次增量备份到完全备份里
[root@test-centos7-node1 ~]# xtrabackup --prepare --target-dir=/root/full_bak/ --incremental-dir=/root/incre_bak2
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=innodb --innodb_log_checksum_algorithm=innodb --innodb_data_file_path=ibdata1:10M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=5242880 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=. --innodb_undo_tablespaces=0 --server-id=0 --redo-log-version=0
xtrabackup: recognized client arguments: --prepare=1 --target-dir=/root/full_bak/ --incremental-dir=/root/incre_bak2
xtrabackup version 2.4.18 based on MysqL server 5.7.26 Linux (x86_64) (revision id: 29b4ca5)
incremental backup from 1631702 is enabled.
xtrabackup: cd to /root/full_bak/
xtrabackup: This target seems to be already prepared with --apply-log-only.
InnoDB: Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608,start_lsn=(1631702)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = /root/incre_bak2/
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 8388608
xtrabackup: Generating a list of tablespaces
xtrabackup: page size for /root/incre_bak2//ibdata1.delta is 16384 bytes space id is 0
Applying /root/incre_bak2//ibdata1.delta to ./ibdata1...
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = /root/incre_bak2/
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 8388608
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
InnoDB: PUNCH HOLE support available
InnoDB: Mutexes and rw_locks use GCC atomic builtins
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,chunk size = 100M
InnoDB: Completed initialization of buffer pool
InnoDB: page_cleaner coordinator priority: -20
InnoDB: Highest supported file format is Barracuda.
InnoDB: The log sequence number 1631711 in the system tablespace does not match the log sequence number 1631702 in the ib_logfiles!
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: Creating shared tablespace for temporary tables
InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
InnoDB: File './ibtmp1' size is now 12 MB.
InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
InnoDB: 32 non-redo rollback segment(s) are active.
InnoDB: 5.7.26 started; log sequence number 1631702
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1631721
InnoDB: Number of pools: 1
200118 04:03:22 [01] Copying /root/incre_bak2/MysqL/db.frm to ./MysqL/db.frm
200118 04:03:22 [01] ...done
……省略部分内容
200118 04:03:23 [00] Copying /root/incre_bak2//xtrabackup_info to ./xtrabackup_info
200118 04:03:23 [00] ...done
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = .
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 5242880
InnoDB: PUNCH HOLE support available
InnoDB: Mutexes and rw_locks use GCC atomic builtins
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,LSN=1631721
InnoDB: Highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 1631756
InnoDB: Doing recovery: scanned up to log sequence number 1631765 (0%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: Removed temporary tablespace data file: "ibtmp1"
InnoDB: Creating shared tablespace for temporary tables
InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
InnoDB: File './ibtmp1' size is now 12 MB.
InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
InnoDB: 32 non-redo rollback segment(s) are active.
InnoDB: 5.7.26 started; log sequence number 1631765
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1631784
200118 04:03:25 completed OK!
[root@test-centos7-node1 ~]#
说明：最后一次合并不需要加--apply-log-only选项 表示这是最后一次增量备份合并，后续提交已完成对事务，回滚未完成的事务，让数据一致
清空/var/lib/MysqL目录，然后复制备份文件到该目录下
[root@test-centos7-node1 ~]# rm -rf /var/lib/MysqL/*
[root@test-centos7-node1 ~]# ll /var/lib/MysqL/
total 0
[root@test-centos7-node1 ~]# xtrabackup --copy-back --target-dir=/root/full_bak/
xtrabackup: recognized server arguments: --datadir=/var/lib/MysqL
xtrabackup: recognized client arguments: --copy-back=1 --target-dir=/root/full_bak/
xtrabackup version 2.4.18 based on MysqL server 5.7.26 Linux (x86_64) (revision id: 29b4ca5)
200118 04:12:32 [01] Copying ib_logfile0 to /var/lib/MysqL/ib_logfile0
200118 04:12:32 [01] ...done
……省略部分内容
200118 04:12:32 [01] Copying ./xtrabackup_info to /var/lib/MysqL/xtrabackup_info
200118 04:12:32 [01] ...done
200118 04:12:32 [01] Copying ./ibtmp1 to /var/lib/MysqL/ibtmp1
200118 04:12:32 [01] ...done
200118 04:12:32 completed OK!
还原属性
[root@test-centos7-node1 ~]# chown -R MysqL.MysqL /var/lib/MysqL/
[root@test-centos7-node1 ~]# ll /var/lib/MysqL/
total 40976
drwxr-x---. 2 MysqL MysqL 36 Jan 18 04:12 abc
drwxr-x---. 2 MysqL MysqL 146 Jan 18 04:12 hellodb
-rw-r-----. 1 MysqL MysqL 18874368 Jan 18 04:12 ibdata1
-rw-r-----. 1 MysqL MysqL 5242880 Jan 18 04:12 ib_logfile0
-rw-r-----. 1 MysqL MysqL 5242880 Jan 18 04:12 ib_logfile1
-rw-r-----. 1 MysqL MysqL 12582912 Jan 18 04:12 ibtmp1
drwxr-x---. 2 MysqL MysqL 4096 Jan 18 04:12 MysqL
drwxr-x---. 2 MysqL MysqL 4096 Jan 18 04:12 performance_schema
drwxr-x---. 2 MysqL MysqL 20 Jan 18 04:12 test
-rw-r-----. 1 MysqL MysqL 475 Jan 18 04:12 xtrabackup_info
-rw-r-----. 1 MysqL MysqL 1 Jan 18 04:12 xtrabackup_master_key_id
[root@test-centos7-node1 ~]#
[root@test-centos7-node1 ~]# systemctl restart mariadb
[root@test-centos7-node1 ~]# ss -ntl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 *:22 *:*
LISTEN 0 100 127.0.0.1:25 *:*
LISTEN 0 50 *:3306 *:*
LISTEN 0 128 :::22 :::*
LISTEN 0 100 ::1:25 :::*
[root@test-centos7-node1 ~]# MysqL
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.56-MariaDB MariaDB Server
Copyright (c) 2000,MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| abc |
| hellodb |
| MysqL |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.00 sec)
MariaDB [(none)]> use abc
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
MariaDB [abc]> show tables;
+---------------+
| Tables_in_abc |
+---------------+
| test |
+---------------+
1 row in set (0.00 sec)
MariaDB [abc]> select * from test;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
+------+
4 rows in set (0.00 sec)
MariaDB [abc]> use MysqL
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
MariaDB [MysqL]> show tables;
+---------------------------+
| Tables_in_MysqL |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
24 rows in set (0.01 sec)
MariaDB [MysqL]> select user,password from user;
+------+--------------------+----------+
| user | host | password |
+------+--------------------+----------+
| root | localhost | |
| root | test-centos7-node1 | |
| root | 127.0.0.1 | |
| root | ::1 | |
| test | % | |
+------+--------------------+----------+
5 rows in set (0.00 sec)
MariaDB [MysqL]> \q
Bye
[root@test-centos7-node1 ~]#
说明：可看到我们删除的abc库已经恢复，删除的user表也全部恢复
六、xtrabackup单表导出和导入
前期准备
1）启用innodb_file_per_table选项，并重启mariadb服务
[root@test-centos7-node1 ~]# grep -C 2 innodb_file_per_table /etc/my.cnf
[MysqLd]
datadir=/var/lib/MysqL
innodb_file_per_table
socket=/var/lib/MysqL/MysqL.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
[root@test-centos7-node1 ~]#
说明：此选项启用后，mariadb的表空间文件和数据文件就单独存放了，不再全部都存放在ibdata1里
2）导入数据库
[root@test-centos7-node1 ~]# rz
rz waiting to receive.
zmodem trl+C ȡ
100% 7 KB 7 KB/s 00:00:01 0 Errors
[root@test-centos7-node1 ~]# ls
full_bak hellodb_innodb.sql incre_bak1 incre_bak2 table
[root@test-centos7-node1 ~]# MysqL < hellodb_innodb.sql
[root@test-centos7-node1 ~]# ll /var/lib/MysqL/
total 40996
drwxr-x---. 2 MysqL MysqL 36 Jan 18 05:06 abc
-rw-rw----. 1 MysqL MysqL 16384 Jan 18 05:12 aria_log.00000001
-rw-rw----. 1 MysqL MysqL 52 Jan 18 05:12 aria_log_control
drwx------. 2 MysqL MysqL 272 Jan 18 05:21 hellodb
-rw-r-----. 1 MysqL MysqL 18874368 Jan 18 05:20 ibdata1
-rw-r-----. 1 MysqL MysqL 5242880 Jan 18 05:21 ib_logfile0
-rw-r-----. 1 MysqL MysqL 5242880 Jan 18 05:06 ib_logfile1
-rw-r-----. 1 MysqL MysqL 12582912 Jan 18 05:06 ibtmp1
drwxr-x---. 2 MysqL MysqL 4096 Jan 18 05:06 MysqL
srwxrwxrwx. 1 MysqL MysqL 0 Jan 18 05:12 MysqL.sock
drwxr-x---. 2 MysqL MysqL 4096 Jan 18 05:06 performance_schema
drwxr-x---. 2 MysqL MysqL 20 Jan 18 05:06 test
-rw-r-----. 1 MysqL MysqL 475 Jan 18 05:06 xtrabackup_info
-rw-r-----. 1 MysqL MysqL 1 Jan 18 05:06 xtrabackup_master_key_id
[root@test-centos7-node1 ~]# ll /var/lib/MysqL/hellodb/
total 1432
-rw-rw----. 1 MysqL MysqL 8636 Jan 18 05:21 classes.frm
-rw-rw----. 1 MysqL MysqL 98304 Jan 18 05:22 classes.ibd
-rw-rw----. 1 MysqL MysqL 8630 Jan 18 05:21 coc.frm
-rw-rw----. 1 MysqL MysqL 98304 Jan 18 05:22 coc.ibd
-rw-rw----. 1 MysqL MysqL 8602 Jan 18 05:21 courses.frm
-rw-rw----. 1 MysqL MysqL 98304 Jan 18 05:22 courses.ibd
-rw-rw----. 1 MysqL MysqL 61 Jan 18 05:21 db.opt
-rw-rw----. 1 MysqL MysqL 8658 Jan 18 05:21 scores.frm
-rw-rw----. 1 MysqL MysqL 98304 Jan 18 05:22 scores.ibd
-rw-rw----. 1 MysqL MysqL 8736 Jan 18 05:21 students.frm
-rw-rw----. 1 MysqL MysqL 98304 Jan 18 05:22 students.ibd
-rw-rw----. 1 MysqL MysqL 8656 Jan 18 05:21 teachers.frm
-rw-rw----. 1 MysqL MysqL 98304 Jan 18 05:22 teachers.ibd
-rw-rw----. 1 MysqL MysqL 8622 Jan 18 05:21 toc.frm
-rw-rw----. 1 MysqL MysqL 98304 Jan 18 05:22 toc.ibd
[root@test-centos7-node1 ~]#
[root@test-centos7-node1 ~]# MysqL
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 5.5.56-MariaDB MariaDB Server
Copyright (c) 2000,MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> select * from hellodb.students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.00 sec)
MariaDB [(none)]> \q
Bye
[root@test-centos7-node1 ~]#
说明：可看到hellodb数据库里的表都是单独的数据文件和表结构文件
3）单表备份
[root@test-centos7-node1 ~]# innobackupex --include='hellodb.students' /root/table/
xtrabackup: recognized server arguments: --datadir=/var/lib/MysqL --innodb_file_per_table=1
xtrabackup: recognized client arguments:
200118 05:22:44 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!".
200118 05:22:45 version_check Connecting to MysqL server with DSN 'dbi:MysqL:;MysqL_read_default_group=xtrabackup' (using password: NO).
200118 05:22:45 version_check Connected to MysqL server
200118 05:22:45 version_check Executing a version check against the server...
200118 05:22:45 version_check Done.
200118 05:22:45 Connecting to MysqL server host: localhost,socket: not set
Using server version 5.5.56-MariaDB
innobackupex version 2.4.18 based on MysqL server 5.7.26 Linux (x86_64) (revision id: 29b4ca5)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/MysqL
xtrabackup: open files limit requested 0,set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 5242880
InnoDB: Number of pools: 1
200118 05:22:45 >> log scanned up to (1676617)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 14 for hellodb/students,old maximum was 0
200118 05:22:45 [01] Copying ./ibdata1 to /root/table/2020-01-18_05-22-44/ibdata1
200118 05:22:45 [01] ...done
200118 05:22:45 [01] Copying ./hellodb/students.ibd to /root/table/2020-01-18_05-22-44/hellodb/students.ibd
200118 05:22:45 [01] ...done
200118 05:22:46 >> log scanned up to (1676617)
200118 05:22:46 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
200118 05:22:46 Executing FLUSH TABLES WITH READ LOCK...
200118 05:22:46 Starting to backup non-InnoDB tables and files
200118 05:22:46 [01] Skipping ./ib_logfile0.
……省略部分内容
200118 05:22:46 [01] Skipping ./hellodb/toc.ibd.
200118 05:22:46 Finished backing up non-InnoDB tables and files
200118 05:22:46 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '1676617'
xtrabackup: Stopping log copying thread.
.200118 05:22:46 >> log scanned up to (1676617)
200118 05:22:46 Executing UNLOCK TABLES
200118 05:22:46 All tables unlocked
200118 05:22:46 Backup created in directory '/root/table/2020-01-18_05-22-44/'
200118 05:22:46 [00] Writing /root/table/2020-01-18_05-22-44/backup-my.cnf
200118 05:22:46 [00] ...done
200118 05:22:46 [00] Writing /root/table/2020-01-18_05-22-44/xtrabackup_info
200118 05:22:46 [00] ...done
xtrabackup: Transaction log of lsn (1676617) to (1676617) was copied.
200118 05:22:46 completed OK!
[root@test-centos7-node1 ~]#
4）备份表结构
[root@test-centos7-node1 ~]# MysqL -e 'show create table hellodb.students' > student.sql
[root@test-centos7-node1 ~]# cat student.sql
Table Create Table
students CREATE TABLE `students` (\n `StuID` int(10) unsigned NOT NULL AUTO_INCREMENT,\n `Name` varchar(50) NOT NULL,\n `Age` tinyint(3) unsigned NOT NULL,\n `Gender` enum('F','M') NOT NULL,\n `ClassID` tinyint(3) unsigned DEFAULT NULL,\n `TeacherID` int(10) unsigned DEFAULT NULL,\n PRIMARY KEY (`StuID`)\n) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8
[root@test-centos7-node1 ~]#
5）删除表
[root@test-centos7-node1 ~]# MysqL
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 17
Server version: 5.5.56-MariaDB MariaDB Server
Copyright (c) 2000,MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| abc |
| hellodb |
| MysqL |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.00 sec)
MariaDB [(none)]> use hellodb;
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
MariaDB [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
7 rows in set (0.00 sec)
MariaDB [hellodb]> drop table students ;
Query OK,0 rows affected (0.01 sec)
MariaDB [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| teachers |
| toc |
+-------------------+
6 rows in set (0.00 sec)
MariaDB [hellodb]>
6）整理备份文件
[root@test-centos7-node1 ~]# innobackupex --apply-log --export /root/table/2020-01-18_05-22-44/
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=innodb --innodb_log_checksum_algorithm=innodb --innodb_data_file_path=ibdata1:10M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=5242880 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=. --innodb_undo_tablespaces=0 --server-id=0 --redo-log-version=0
xtrabackup: recognized client arguments:
200118 05:31:50 innobackupex: Starting the apply-log operation
IMPORTANT: Please check that the apply-log run completes successfully.
At the end of a successful apply-log run innobackupex
prints "completed OK!".
innobackupex version 2.4.18 based on MysqL server 5.7.26 Linux (x86_64) (revision id: 29b4ca5)
xtrabackup: auto-enabling --innodb-file-per-table due to the --export option
xtrabackup: cd to /root/table/2020-01-18_05-22-44/
xtrabackup: This target seems to be not prepared yet.
InnoDB: Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608,start_lsn=(1676617)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = .
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 8388608
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = .
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 8388608
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
InnoDB: PUNCH HOLE support available
InnoDB: Mutexes and rw_locks use GCC atomic builtins
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,chunk size = 100M
InnoDB: Completed initialization of buffer pool
InnoDB: page_cleaner coordinator priority: -20
InnoDB: Highest supported file format is Barracuda.
InnoDB: The log sequence number 1631794 in the system tablespace does not match the log sequence number 1676617 in the ib_logfiles!
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: Removing missing table `hellodb/classes` from InnoDB data dictionary.
InnoDB: Removing missing table `hellodb/coc` from InnoDB data dictionary.
InnoDB: Removing missing table `hellodb/courses` from InnoDB data dictionary.
InnoDB: Removing missing table `hellodb/scores` from InnoDB data dictionary.
InnoDB: Removing missing table `hellodb/teachers` from InnoDB data dictionary.
InnoDB: Removing missing table `hellodb/toc` from InnoDB data dictionary.
InnoDB: Creating shared tablespace for temporary tables
InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
InnoDB: File './ibtmp1' size is now 12 MB.
InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
InnoDB: 32 non-redo rollback segment(s) are active.
InnoDB: 5.7.26 started; log sequence number 1676617
xtrabackup: export option is specified.
xtrabackup: export Metadata of table 'hellodb/students' to file `./hellodb/students.exp` (1 indexes)
xtrabackup: name=PRIMARY,id.low=27,page=3
xtrabackup: starting shutdown with innodb_fast_shutdown = 0
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1682140
InnoDB: Number of pools: 1
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = .
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 5242880
InnoDB: PUNCH HOLE support available
InnoDB: Mutexes and rw_locks use GCC atomic builtins
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,LSN=1682140
InnoDB: Highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 1682444
InnoDB: Doing recovery: scanned up to log sequence number 1682453 (0%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: Removed temporary tablespace data file: "ibtmp1"
InnoDB: Creating shared tablespace for temporary tables
InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
InnoDB: File './ibtmp1' size is now 12 MB.
InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
InnoDB: 32 non-redo rollback segment(s) are active.
InnoDB: 5.7.26 started; log sequence number 1682453
xtrabackup: starting shutdown with innodb_fast_shutdown = 0
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1682472
200118 05:31:54 completed OK!
[root@test-centos7-node1 ~]#
7）创建表
[root@test-centos7-node1 ~]# MysqL
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 18
Server version: 5.5.56-MariaDB MariaDB Server
Copyright (c) 2000,MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> use hellodb;
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
MariaDB [hellodb]> CREATE TABLE `students` (\n `StuID` int(10) unsigned NOT NULL AUTO_INCREMENT,\n PRIMARY KEY (`StuID`)\n) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8;
PAGER set to stdout
PAGER set to stdout
PAGER set to stdout
PAGER set to stdout
PAGER set to stdout
PAGER set to stdout
PAGER set to stdout
PAGER set to stdout
Query OK,0 rows affected (0.01 sec)
MariaDB [hellodb]> show tables ;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
7 rows in set (0.01 sec)
MariaDB [hellodb]>
8）删除表空间
MariaDB [hellodb]> alter table students discard tablespace;
Query OK,0 rows affected (0.00 sec)
MariaDB [hellodb]> \q
Bye
[root@test-centos7-node1 ~]# ll /var/lib/MysqL/hellodb/
total 664
-rw-rw----. 1 MysqL MysqL 8636 Jan 18 05:21 classes.frm
-rw-rw----. 1 MysqL MysqL 98304 Jan 18 05:22 classes.ibd
-rw-rw----. 1 MysqL MysqL 8630 Jan 18 05:21 coc.frm
-rw-rw----. 1 MysqL MysqL 98304 Jan 18 05:22 coc.ibd
-rw-rw----. 1 MysqL MysqL 8602 Jan 18 05:21 courses.frm
-rw-rw----. 1 MysqL MysqL 98304 Jan 18 05:22 courses.ibd
-rw-rw----. 1 MysqL MysqL 61 Jan 18 05:21 db.opt
-rw-rw----. 1 MysqL MysqL 8658 Jan 18 05:21 scores.frm
-rw-rw----. 1 MysqL MysqL 98304 Jan 18 05:22 scores.ibd
-rw-rw----. 1 MysqL MysqL 8736 Jan 18 05:32 students.frm
-rw-rw----. 1 MysqL MysqL 8656 Jan 18 05:21 teachers.frm
-rw-rw----. 1 MysqL MysqL 98304 Jan 18 05:22 teachers.ibd
-rw-rw----. 1 MysqL MysqL 8622 Jan 18 05:21 toc.frm
-rw-rw----. 1 MysqL MysqL 98304 Jan 18 05:22 toc.ibd
[root@test-centos7-node1 ~]# ll /var/lib/MysqL/hellodb/students*
-rw-rw----. 1 MysqL MysqL 8736 Jan 18 05:32 /var/lib/MysqL/hellodb/students.frm
[root@test-centos7-node1 ~]#
[root@test-centos7-node1 ~]# ll /root/table/2020-01-18_05-22-44/hellodb/students.*
-rw-r--r--. 1 root root 640 Jan 18 05:31 /root/table/2020-01-18_05-22-44/hellodb/students.cfg
-rw-r-----. 1 root root 16384 Jan 18 05:31 /root/table/2020-01-18_05-22-44/hellodb/students.exp
-rw-r-----. 1 root root 8736 Jan 18 05:22 /root/table/2020-01-18_05-22-44/hellodb/students.frm
-rw-r-----. 1 root root 98304 Jan 18 05:22 /root/table/2020-01-18_05-22-44/hellodb/students.ibd
[root@test-centos7-node1 ~]# cp /root/table/2020-01-18_05-22-44/hellodb/students.{cfg,exp,ibd} /var/lib/MysqL/hellodb/
[root@test-centos7-node1 ~]# chown -R MysqL.MysqL /var/lib/MysqL/hellodb/
[root@test-centos7-node1 ~]# ll /var/lib/MysqL/hellodb/
total 780
-rw-rw----. 1 MysqL MysqL 8636 Jan 18 05:21 classes.frm
-rw-rw----. 1 MysqL MysqL 98304 Jan 18 05:22 classes.ibd
-rw-rw----. 1 MysqL MysqL 8630 Jan 18 05:21 coc.frm
-rw-rw----. 1 MysqL MysqL 98304 Jan 18 05:22 coc.ibd
-rw-rw----. 1 MysqL MysqL 8602 Jan 18 05:21 courses.frm
-rw-rw----. 1 MysqL MysqL 98304 Jan 18 05:22 courses.ibd
-rw-rw----. 1 MysqL MysqL 61 Jan 18 05:21 db.opt
-rw-rw----. 1 MysqL MysqL 8658 Jan 18 05:21 scores.frm
-rw-rw----. 1 MysqL MysqL 98304 Jan 18 05:22 scores.ibd
-rw-r--r--. 1 MysqL MysqL 640 Jan 18 05:40 students.cfg
-rw-r-----. 1 MysqL MysqL 16384 Jan 18 05:40 students.exp
-rw-rw----. 1 MysqL MysqL 8736 Jan 18 05:32 students.frm
-rw-r-----. 1 MysqL MysqL 98304 Jan 18 05:40 students.ibd
-rw-rw----. 1 MysqL MysqL 8656 Jan 18 05:21 teachers.frm
-rw-rw----. 1 MysqL MysqL 98304 Jan 18 05:22 teachers.ibd
-rw-rw----. 1 MysqL MysqL 8622 Jan 18 05:21 toc.frm
-rw-rw----. 1 MysqL MysqL 98304 Jan 18 05:22 toc.ibd
[root@test-centos7-node1 ~]#
说明：整理过后的表文件会多二个文件一个是.cfg的文件，一个是.exp文件，exp文件就是可以用于导入至其它服务器。
10）导入表空间
[root@test-centos7-node1 ~]# MysqL
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 5.5.56-MariaDB MariaDB Server
Copyright (c) 2000,MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| abc |
| hellodb |
| MysqL |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.00 sec)
MariaDB [(none)]> use hellodb
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
MariaDB [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
7 rows in set (0.00 sec)
MariaDB [hellodb]> select * from students;
ERROR 1030 (HY000): Got error -1 from storage engine
MariaDB [hellodb]> alter table hellodb.students import tablespace;
ERROR 1030 (HY000): Got error -1 from storage engine
MariaDB [hellodb]> show variables like 'innodb_import%';
+-------------------------------------+-------+
| Variable_name | Value |
+-------------------------------------+-------+
| innodb_import_table_from_xtrabackup | 0 |
+-------------------------------------+-------+
1 row in set (0.00 sec)
MariaDB [hellodb]> set global innodb_import_table_from_xtrabackup=1;
Query OK,0 rows affected (0.00 sec)
MariaDB [hellodb]> show variables like 'innodb_import%';
+-------------------------------------+-------+
| Variable_name | Value |
+-------------------------------------+-------+
| innodb_import_table_from_xtrabackup | 1 |
+-------------------------------------+-------+
1 row in set (0.00 sec)
MariaDB [hellodb]> alter table hellodb.students import tablespace;
Query OK,0 rows affected (0.01 sec)
MariaDB [hellodb]> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.00 sec)
MariaDB [hellodb]>
说明：最开始不能查看数据的原因是表空间没有导入进来，所以不能查看表里的内容。开始导入不了表空间的原因是innodb_import_table_from_xtrabackup 变量默认是0 不开启从xtrabackup导入，设置为1后则允许导入。这里需要提醒下MysqL5.5.10之前需要开启innodb_expand_import才可以导入表空间，后面的版本将innodb_expand_import变量改名为innodb_import_table_from_xtrabackup 所以5.5.10后需要开启innodb_import_table_from_xtrabackup=1就可以导入表空间了