一、xtrabackup 简介
xtrabackup 是由percona公司开源免费的数据库热备软件，它能对InnoDB数据库和XtraDB存储引擎的数据库非阻塞地备份，对于myisam的备份同样需要加表锁。mysqldump备份方式是通过用户名密码连接到数据库，将其要备份的数据库用SQL语句的形式输出到标准输出，这种逻辑备份的方式最大的缺点是备份和恢复的速度较慢，如果数据库过大，mysqldump备份就显得力不从心了。这时选择用xtrabackup来备份就能很好解决数据库过大而导致备份过慢的问题。
xtrabackup的优点
1）备份还原过程快速、可靠
2）备份过程不会打断正在执行的事务
3）能够基于压缩等功能节约磁盘空间和流量
4）自动实现备份检验
5）开源，免费
xtrabackup2.2版本之前包括4个可执行文件：
innobackuppex：这个是一个perl脚本，它的主要作用是用来备份非innodb表，同时会调用xtrabackup命令来备份innodb表，还会和mysql server 发送命令进行交互，如家全局读锁（FTWRL）、获取位置点（show slave status）等。
xtrabackup:这个文件是用C/C 编译的二进制文件，它的主要作用用来备份Innodb表达，不能备份非Innodb表，也没有和mysql server交互。
xbcrypt:这个文件的主要作用用于加密解密
xbstream:这个文件的主要作用支持并发写的流文件格式
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的结束
9）子进程结束退出后，并告知主进程。主进程退出，备份结束。
二、xtrabackup安装
1）添加yum源
[[email protected] ~]# 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包信息
[[email protected] ~]# 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 [[email protected] ~]#
说明：默认情况我们添加了xtrabackup的yum源后，默认是安装的2.3版本，需要安装2.2或者2.4可在后面添加版本号，如下
[[email protected] ~]# 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 [[email protected] ~]# 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 [[email protected] ~]#
3）安装xtrabackup特定的版本
[[email protected] ~]# yum install -y percona-xtrabackup-24
4）查看percona-xtrabackup-24包安装路径
[[email protected] ~]# 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 [[email protected] ~]# file /usr/bin/innobackupex /usr/bin/innobackupex: symbolic link to `xtrabackup‘ [[email protected] ~]# ll /usr/bin/innobackupex lrwxrwxrwx. 1 root root 10 Jan 17 23:46 /usr/bin/innobackupex -> xtrabackup [[email protected] ~]# 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 [[email protected] ~]#
说明：2.2版本以前/usr/bin/innobackupex 文件都是一个perl脚本，2.2以后/usr/bin/innobackupex 是一个软连接指向/usr/bin/xtrabackup
[[email protected] ~]# 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 [[email protected] ~]# file /usr/bin/innobackupex /usr/bin/innobackupex: a /usr/bin/env perl script text executable [[email protected] ~]# file /usr/bin/xtrabackup /usr/bin/xtrabackup: ELF 64-bit LSB executable, x86-64, version 1 (SYSV), dynamically linked (uses shared libs), for GNU/Linux 2.6.18, stripped [[email protected] ~]#
二、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）不使用任何压缩工具复制全备
[[email protected] ~]# 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! [[email protected] ~]# 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 [[email protected] ~]#
说明：生产环境中需要指定用户名和密码来备份，这里需要注意一点xtrabackup备份是基于复制数据文件的方式来做的备份，它和mysqldump不一样的是，它不能连接到远端服务器上把备份文件拉取到本地，但是它可在远端服务器上把备份推到本地来。换句话说xtrabackup 只能备份本地数据库的文件，执行命令需要在本地执行。
2）基于流式传输和压缩备份（--stream=xbstream）
[[email protected] ~]# 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, 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: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! [[email protected] ~]# ll /root/backup2/ total 32768 -rw-r--r--. 1 root root 19908780 Jan 18 00:36 all_backup.xbstream [[email protected] ~]#
说明：以上方式是通过流传输模式以xbstream的二进制格式将备份输出到标准输出，然后通过输出重定向到一个以.xbstream结尾的文件。这样备份有一个好处就是它可以自动加密备份数据，使得我们备份的数据相比复制文件的方式要安全。要使用流式传输功能，必须使用--stream，提供流（tar或xbstream）的格式以及临时文件的存储位置
3）使用流备份并压缩
[[email protected] ~]# xtrabackup --stream=xbstream --backup --compress > /root/backup2/all_backup2.xbstream
说明：使用--compress选项就表示压缩数据，以上命令备份的文件要比不加--compress备份的文件要小得多
[[email protected] ~]# 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 [[email protected] ~]#
4）将流备份解压到指定目录
[[email protected] ~]# xbstream -x < backup2/all_backup.xbstream -C xxxx/ [[email protected] ~]# 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 [[email protected] ~]
说明：如果备份是通过--compress 备份，用以上命令解开后的文件是以.qp结尾的文件，这个文件是没法直接使用需要用对应的工具将其再解压。
[[email protected] ~]# xbstream -x < /root/backup2/all_backup2.xbstream -C /root/xtrabackup_backupfiles/ [[email protected] ~]# 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 [[email protected] ~]# file /root/xtrabackup_backupfiles/backup-my.cnf.qp /root/xtrabackup_backupfiles/backup-my.cnf.qp: data [[email protected] ~]#
说明：以上就是通过压缩后在通过流传输将标准输出到某一个文件后，解压该文件得到的文件，.qp的文件如果用cat 查看是乱码的，这个时候就需要工具来将其解压后就可正常查看。qpress工具就可将其文件解开。下载链接http://www.quicklz.com/qpress-11-linux-x64.tar。
[[email protected] ~]# ./qpress -d xtrabackup_backupfiles/backup-my.cnf.qp /root/ [[email protected] ~]# 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 [[email protected] ~]# 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 [[email protected] ~]#
说明：下载下来的tar包解开后就是一个二进制文件，我们可以将这个二进制文件直接拷贝到/usr/bin 这样就不用./去运行。解压用-d 后面跟要解压到文件 和解压后的文件存放目录，这里需要注意一点，它和gzip不一样，gzip解压后原文件就消失了，而它不会。
5）将压缩备份发送到另一台主机并解压缩它
[[email protected] ~]# xtrabackup --stream=xbstream --backup | ssh [email protected] "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, 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 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! [[email protected] ~]# ssh 192.168.0.11 Last login: Sat Jan 18 22:21:05 2020 from 192.168.0.10 [[email protected] ~]# 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 [[email protected] ~]#
说明：以上命令很容易理解通过标准输出内容交给管道，通过ssh 远程执行命令解压数据。做以上操作前提建议先做好ssh认证免密；有关ssh 基于key验证免密请参考https://www.cnblogs.com/qiuhom-1874/p/11783371.html
6）基于流式传输和压缩备份（--stream=tar）
将完整备份直接存储到tar归档文件中
[[email protected] ~]# 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, 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 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! [[email protected] ~]# 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 [[email protected] ~]#
要将tar存档发送到另一个主机
[[email protected] ~]# ssh 192.168.0.11 Last login: Sat Jan 18 22:45:34 2020 from 192.168.0.10 [[email protected] ~]# ls [[email protected] ~]# exit logout Connection to 192.168.0.11 closed. [[email protected] ~]# xtrabackup --stream=tar --backup | ssh [email protected] "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, 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 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! [[email protected] ~]# ssh 192.168.0.11 Last login: Sat Jan 18 22:47:19 2020 from 192.168.0.10 [[email protected] ~]# ls all.tar [[email protected] ~]# mkdir test [[email protected] ~]# tar xf all.tar -C test/ [[email protected] ~]# 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 [[email protected] ~]#
使用其他压缩工具压缩
gzip压缩
[[email protected] ~]# 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, 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 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! [[email protected] ~]# ls all.tar.gz xtrabackup_backupfiles [[email protected] ~]# ll xtrabackup_backupfiles/ total 0 [[email protected] ~]# tar xf all.tar.gz -C xtrabackup_backupfiles/ [[email protected] ~]# 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 [[email protected] ~]# 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 [[email protected] ~]#
bzip2压缩
[[email protected] ~]# rm -rf * [[email protected] ~]# ls [[email protected] ~]# 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, 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 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! [[email protected] ~]# ls all.tar.bz2 xtrabackup_backupfiles [[email protected] ~]# ll xtrabackup_backupfiles/ total 0 [[email protected] ~]# tar xf all.tar.bz2 -C xtrabackup_backupfiles/ [[email protected] ~]# 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 [[email protected] ~]#
xz压缩
[[email protected] ~]# rm -rf * [[email protected] ~]# ls [[email protected] ~]# 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, 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 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! [[email protected] ~]# 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 [[email protected] ~]# ll xtrabackup_backupfiles/ total 0 [[email protected] ~]# tar xf all.tar.xz -C xtrabackup_backupfiles/ [[email protected] ~]# 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 [[email protected] ~]#
四、xtrabackup全备还原实现
1）删库前数据库里的库表
[[email protected] ~]# 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 [[email protected] ~]#
2）全量备份
[[email protected] ~]# 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, 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 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! [[email protected] ~]# 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 [[email protected] ~]#
3）删库
[[email protected] ~]# 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 [[email protected] ~]# rm -rf /var/lib/mysql/* [[email protected] ~]# ll /var/lib/mysql/ total 0 [[email protected] ~]#
4）停掉数据库进行还原
[[email protected] ~]# systemctl stop mariadb [[email protected] ~]# 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 :::* [[email protected] ~]# 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, total size = 100M, instances = 1, 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! [[email protected] ~]# 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! [[email protected] ~]# chown -R mysql.mysql /var/lib/mysql/ [[email protected] ~]# 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 [[email protected] ~]#
说明：还原操作需要进行三步，第一步是预准备，这一步的主要作用是确保数据的一致性，提交完成的事务，回滚未完成的事务。第二步是复制预处理后的数据文件到mariadb的工作目录，第三部还原拷贝过去的文件的属性为mysql。执行拷贝操作需要确保原数据目录为空，或者加上--force-non-empty-directorires否则copy的时候会报错。
5）启动mariadb，查看数据库里的库表是否恢复到删除前的状态
[[email protected] ~]# systemctl start mariadb [[email protected] ~]# 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 :::* [[email protected] ~]# 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, 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.01 sec) MariaDB [hellodb]>
说明：可看到数据库已经恢复到备份前的状态
五、xtrabackup 增量备份还原实现
1）完全备份
[[email protected] ~]# ls [[email protected] ~]# mkdir /root/{full_bak,incre_bak1,incre_bak2} [[email protected] ~]# ls full_bak incre_bak1 incre_bak2 [[email protected] ~]# 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, 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 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! [[email protected] ~]#
2）修改数据，在做基于第一次全量备份做第一次增量备份
[[email protected] ~]# 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, 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)]> 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 [[email protected] ~]# [[email protected] ~]# 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, 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) 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! [[email protected] ~]#
3）第二次修改数据，做基于第一次增量比分为basedir做第二次整理备份
[[email protected] ~]# 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, 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 | | 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 [[email protected] ~]# [[email protected] ~]# 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, 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) 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! [[email protected] ~]#
到此两次增量备份已经全部做好，接下来查看数据，在删除库表来进行还原
[[email protected] ~]# 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, 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 | | 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 [[email protected] ~]#
删除前的数据如上，现在我们模拟删除mysql.user表和abc库，然后利用增量备份将其还原
[[email protected] ~]# 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, 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 | | 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]>
还原数据库
合并全量备份
[[email protected] ~]# 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, 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. 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 表示不会滚未完成的事务，因为后面还有增量备份。
合并第一次增量备份到完全备份里
[[email protected] ~]# 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, 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: 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!
合并第二次增量备份到完全备份里
[[email protected] ~]# 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, 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: 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, total size = 100M, instances = 1, 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=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! [[email protected] ~]#
说明：最后一次合并不需要加--apply-log-only选项 表示这是最后一次增量备份合并，后续提交已完成对事务，回滚未完成的事务，让数据一致
清空/var/lib/mysql目录，然后复制备份文件到该目录下
[[email protected] ~]# rm -rf /var/lib/mysql/* [[email protected] ~]# ll /var/lib/mysql/ total 0 [[email protected] ~]# 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!
还原属性
[[email protected] ~]# chown -R mysql.mysql /var/lib/mysql/ [[email protected] ~]# 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 [[email protected] ~]#
重启数据库，进到数据库查看数据是否恢复
[[email protected] ~]# systemctl restart mariadb [[email protected] ~]# 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 :::* [[email protected] ~]# 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, 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 | | 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,host,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 [[email protected] ~]#
说明：可看到我们删除的abc库已经恢复，删除的user表也全部恢复
六、xtrabackup单表导出和导入
前期准备
1）启用innodb_file_per_table选项，并重启mariadb服务
[[email protected] ~]# 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 [[email protected] ~]#
说明：此选项启用后，mariadb的表空间文件和数据文件就单独存放了，不再全部都存放在ibdata1里
2）导入数据库
[[email protected] ~]# rz rz waiting to receive. zmodem trl C ? 100% 7 KB 7 KB/s 00:00:01 0 Errors [[email protected] ~]# ls full_bak hellodb_innodb.sql incre_bak1 incre_bak2 table [[email protected] ~]# mysql < hellodb_innodb.sql [[email protected] ~]# 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 [[email protected] ~]# 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 [[email protected] ~]# [[email protected] ~]# 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)]> 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 [[email protected] ~]#
说明：可看到hellodb数据库里的表都是单独的数据文件和表结构文件
3）单表备份
[[email protected] ~]# 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, user: not set, password: not set, port: not set, 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! [[email protected] ~]#
4）备份表结构
[[email protected] ~]# mysql -e ‘show create table hellodb.students‘ > student.sql [[email protected] ~]# 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 [[email protected] ~]#
5）删除表
[[email protected] ~]# 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, 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 | | 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）整理备份文件
[[email protected] ~]# 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, 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: 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, total size = 100M, instances = 1, 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=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! [[email protected] ~]#
7）创建表
[[email protected] ~]# 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, 2017, Oracle, 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 `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; 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 [[email protected] ~]# 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 [[email protected] ~]# ll /var/lib/mysql/hellodb/students* -rw-rw----. 1 mysql mysql 8736 Jan 18 05:32 /var/lib/mysql/hellodb/students.frm [[email protected] ~]#
说明：可看到删除了表空间，对应的文件也被删除了
9）复制整理后的表文件到hellodb数据库工作目录
[[email protected] ~]# 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 [[email protected] ~]# cp /root/table/2020-01-18_05-22-44/hellodb/students.{cfg,exp,ibd} /var/lib/mysql/hellodb/ [[email protected] ~]# chown -R mysql.mysql /var/lib/mysql/hellodb/ [[email protected] ~]# 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 [[email protected] ~]#
说明：整理过后的表文件会多二个文件一个是.cfg的文件，一个是.exp文件，exp文件就是可以用于导入至其它服务器。
10）导入表空间
[[email protected] ~]# 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, 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 | | 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就可以导入表空间了