MySQL/MariaDB数据库的xtrabackup实现完全备份
作者：尹正杰
版权声明：原创作品，谢绝转载！否则将追究法律责任。
一.xtrabackup概述
1>.Percona公司概述
Percona是无偏向开源数据库解决方案的领先提供商，该解决方案使组织能够轻松，安全且负担得起地保持业务敏捷性，最小化风险并保持竞争力。 和MariaDB一样，Percona也有基于MySQL分支开源的数据库产品，即percona-server。
Percona和Google及Sun Microsystems等公司为InnoDB Plugin提供了patch补丁包，使其性能发挥至极致。 XtraDB存储引擎是由Percona开发的一款MySQL数据库的高性能存储引擎，其目的是用来代替InnoDB存储引擎，可用于需要更高性能的环境。 XtraDB可以看作是InnoDB存储引擎的增强版本，它在InnoDB上进行了大量的修改和patched，它完全兼容InnoDB，且提供了很多InnoDB不具备的有用的功能。例如：在多核CPU上面的性能和伸缩性要更好；对于内存的分配和使用也要更好；也解除了InnoDB的很多限制；提供了比InnoDB更多的配置和性能监控参数。 博主推荐阅读: https://www.percona.com/
InnoDB发展历史: InnoDB引擎是由InnobaseOy公司开发。 2001年，Innobase公司开始与MySQL AB公司进行合作并开源InnoDB存储引擎的代码。 Oracle公司在2005年以迅雷不及掩耳之势收购了Innobase公司。 2008年Sun公司收购MySQL AB公司。 2009年Oracle收购Sun公司，而MySQL数据库最终到了Oracle的手中，InnoDB存储引擎和MySQL终于又在一起了。 2008年发布InnoDB Plugin，适用于MySQL 5.1版本，这是Oracle创建的下一代InnoDB引擎，其拥有者是InnoDB而不是MySQL。 Google，Percona，Sun Microsystems等公司为InnoDB Plugin提供了patch补丁包，使其性能发挥至极致。 MySQL 5.1.38前的版本中，当你需要安装InnoDB Plugin时，必须下载Plugin的文件，解压后再进行一系列的安装。
2>.什么是xtrabackup
Xtrabackup是percona提供的mysql数据库备份工具，惟一开源的能够对innodb和xtradb数据库进行热备的工具。
Xtrabackup的特点：
备份还原过程快速、可靠
备份过程不会打断正在执行的事务
能够基于压缩等功能节约磁盘空间和流量
自动实现备份检验
开源，免费
博主推荐阅读:
https://www.percona.com/doc/percona-xtrabackup/LATEST/index.html（在线文档手册）
3>.xtrabackup的新版变化
Xtrabackup2.2版之前包括4个可执行文件： innobackupex: Perl语言编写的脚本 xtrabackup: C/C++ 编译的二进制, 是用来备份 InnoDB 表的，不能备份非 InnoDB 表，和 MySQL Server 没有交互 xbcrypt: 加解密 xbstream: 支持并发写的流文件格式 温馨提示: innobackupex脚本用来备份非InnoDB表，同时会调用xtrabackup命令来备份InnoDB表，还会和MySQL Server发送命令进行交互，如加全局读锁（FTWRL）、获取位点（SHOW SLAVE STATUS）等。 即innobackupex是在 xtrabackup 之上做了一层封装实现的xtrabackup版本升级到2.4后，相比之前的2.1有了比较大的变化：innobackupex 功能全部集成到xtrabackup里面，只有一个binary程序，另外为了兼容考虑，innobackupex作为xtrabackup的软链接，即xtrabackup现在支持非Innodb表备份，并且Innobackupex在下一版本中移除，建议通过xtrabackup替换innobackupex. 截止2019年11月7日，xtrabackup最新版本已发布到8.0.7(直接从2.4版本跳到8.0版本)。 博主推荐阅读: https://www.percona.com/downloads/Percona-XtraBackup-LATEST/
4>.xtrabackup备份过程
5>.xtrabackup安装
在EPEL源中默认就有xtrabackup的安装包，如果你的操作系统配置的有EPEL源直接安装即可: yum -y install percona-xtrabackup
最新版本xtrabackup下载安装： https://www.percona.com/downloads/XtraBackup/LATEST/
[root@node102.yinzhengjie.org.cn ~]# yum -y install epel-release Loaded plugins: fastestmirror Loading mirror speeds from cached hostfile * base: mirrors.huaweicloud.com * extras: mirrors.huaweicloud.com * updates: mirrors.huaweicloud.com base | 3.6 kB 00:00:00 docker-ce-stable | 3.5 kB 00:00:00 extras | 2.9 kB 00:00:00 updates | 2.9 kB 00:00:00 Resolving Dependencies --> Running transaction check ---> Package epel-release.noarch 0:7-11 will be installed --> Finished Dependency Resolution Dependencies Resolved ================================================================================================================================================= Package Arch Version Repository Size ================================================================================================================================================= Installing: epel-release noarch 7-11 extras 15 k Transaction Summary ================================================================================================================================================= Install 1 Package Total download size: 15 k Installed size: 24 k Downloading packages: epel-release-7-11.noarch.rpm | 15 kB 00:00:00 Running transaction check Running transaction test Transaction test succeeded Running transaction Installing : epel-release-7-11.noarch 1/1 Verifying : epel-release-7-11.noarch 1/1 Installed: epel-release.noarch 0:7-11 Complete! [root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]# yum info percona-xtrabackup #查看版本为2.3.6，版本较低不推荐安装 Loaded plugins: fastestmirror Loading mirror speeds from cached hostfile * base: mirrors.huaweicloud.com * epel: mirrors.yun-idc.com * extras: mirrors.huaweicloud.com * updates: mirrors.huaweicloud.com Available Packages Name : percona-xtrabackup Arch : x86_64 Version : 2.3.6 Release : 1.el7 Size : 4.6 M Repo : epel/x86_64 Summary : Online backup for InnoDB/XtraDB in MySQL, Percona Server and MariaDB URL : http://www.percona.com/software/percona-xtrabackup/ License : GPLv2 Description : Online backup for InnoDB/XtraDB in MySQL, MariaDB and Percona Server. [root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]# yum -y install percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm Loaded plugins: fastestmirror Examining percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm: percona-xtrabackup-24-2.4.12-1.el7.x86_64 Marking percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm to be installed Resolving Dependencies --> Running transaction check ---> Package percona-xtrabackup-24.x86_64 0:2.4.12-1.el7 will be installed --> Processing Dependency: rsync for package: percona-xtrabackup-24-2.4.12-1.el7.x86_64 Loading mirror speeds from cached hostfile * base: mirrors.huaweicloud.com * epel: mirrors.yun-idc.com * extras: mirrors.huaweicloud.com * updates: mirrors.huaweicloud.com --> Processing Dependency: libev.so.4()(64bit) for package: percona-xtrabackup-24-2.4.12-1.el7.x86_64 --> Running transaction check ---> Package libev.x86_64 0:4.15-7.el7 will be installed ---> Package rsync.x86_64 0:3.1.2-6.el7_6.1 will be installed --> Finished Dependency Resolution Dependencies Resolved ================================================================================================================================================= Package Arch Version Repository Size ================================================================================================================================================= Installing: percona-xtrabackup-24 x86_64 2.4.12-1.el7 /percona-xtrabackup-24-2.4.12-1.el7.x86_64 31 M Installing for dependencies: libev x86_64 4.15-7.el7 extras 44 k rsync x86_64 3.1.2-6.el7_6.1 base 404 k Transaction Summary ================================================================================================================================================= Install 1 Package (+2 Dependent packages) Total size: 32 M Total download size: 448 k Installed size: 32 M Downloading packages: (1/2): libev-4.15-7.el7.x86_64.rpm | 44 kB 00:00:00 (2/2): rsync-3.1.2-6.el7_6.1.x86_64.rpm | 404 kB 00:00:00 ------------------------------------------------------------------------------------------------------------------------------------------------- Total 1.3 MB/s | 448 kB 00:00:00 Running transaction check Running transaction test Transaction test succeeded Running transaction Installing : libev-4.15-7.el7.x86_64 1/3 Installing : rsync-3.1.2-6.el7_6.1.x86_64 2/3 Installing : percona-xtrabackup-24-2.4.12-1.el7.x86_64 3/3 Verifying : rsync-3.1.2-6.el7_6.1.x86_64 1/3 Verifying : libev-4.15-7.el7.x86_64 2/3 Verifying : percona-xtrabackup-24-2.4.12-1.el7.x86_64 3/3 Installed: percona-xtrabackup-24.x86_64 0:2.4.12-1.el7 Dependency Installed: libev.x86_64 0:4.15-7.el7 rsync.x86_64 0:3.1.2-6.el7_6.1 Complete! [root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]# rpm -ql percona-xtrabackup-24 #查看安装的xtrabackup软件包包含的文件列表 /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.12 /usr/share/doc/percona-xtrabackup-24-2.4.12/COPYING /usr/share/man/man1/innobackupex.1.gz /usr/share/man/man1/xbcrypt.1.gz /usr/share/man/man1/xbstream.1.gz /usr/share/man/man1/xtrabackup.1.gz [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]# 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, Build ID[sha1]=c280fc53609b523c6b1bbc3b1c8459c04e75839e, stripped[root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]# ll /usr/bin/innobackupex lrwxrwxrwx 1 root root 10 Nov 7 06:52 /usr/bin/innobackupex -> xtrabackup [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]# ll /usr/bin/xtrabackup -rwxr-xr-x 1 root root 21739024 Jun 14 2018 /usr/bin/xtrabackup [root@node102.yinzhengjie.org.cn ~]#
二.xtrabackup用法概述
1>.备份
innobackupex [option] BACKUP-ROOT-DIR 选项说明:[option] --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 博主推荐阅读: https://www.percona.com/doc/percona-xtrabackup/LATEST/genindex.html
2>.预处理(Prepare)
innobackupex --apply-log [option] BACKUP-DIR
选项说明:[option] --apply-log：
一般情况下,在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处理不一致状态。此选项作用是通过回滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态 --use-memory：
和--apply-log选项一起使用，当prepare 备份时，做crash recovery分配的内存大小，单位字节，也可1MB,1M,1G,1GB等，推荐1G --export：
表示开启可导出单独的表之后再导入其他Mysql中 --redo-only：
此选项在prepare base full backup，往其中合并增量备份时候使用，但不包括对最后一个增量备份的合并
3>.还原（innobackupex --copy-back [选项] BACKUP-DIR）
innobackupex --move-back [选项] [--defaults-group=GROUP-NAME] BACKUP-DIR 选项说明： --copy-back： 做数据恢复时将备份数据文件拷贝到MySQL服务器的datadir --move-back： 这个选项与--copy-back相似，唯一的区别是它不拷贝文件，而是移动文件到目的地。这个选项移除backup文件，用时候必须小心。 使用场景：没有足够的磁盘空间同事保留数据文件和Backup副本 还原注意事项： 1.datadir 目录必须为空。除非指定innobackupex --force-non-empty-directorires选项指定，否则--copy-backup选项不会覆盖 2.在restore之前,必须shutdown MySQL实例，不能将一个运行中的实例restore到datadir目录中 3.由于文件属性会被保留，大部分情况下需要在启动实例之前将文件的属主改为mysql，这些文件将属于创建备份的用户 chown -R mysql:mysql /data/mysql 以上需要在用户调用innobackupex之前完成 --force-non-empty-directories： 指定该参数时候，使得innobackupex --copy-back或--move-back选项转移文件到非空目录，已存在的文件不会被覆盖。
如果--copy-back和--move-back文件需要从备份目录拷贝一个在datadir已经存在的文件，会报错失败
4>.备份生成的相关文件
使用innobackupex备份时，其会调用xtrabackup备份所有的InnoDB表，复制所有关于表结构定义的相关文件(.frm)、以及MyISAM、MERGE、CSV和ARCHIVE表的相关文件，同时还会备份触发器和数据库配置信息相关的文件。这些文件会被保存至一个以时间命名的目录中,在备份时，innobackupex还会在备份目录中创建如下文件： (1)xtrabackup_info：
innobackupex工具执行时的相关信息，包括版本，备份选项，备份时长，备份LSN(log sequence number日志序列号)，BINLOG的位置 (2)xtrabackup_checkpoints：
备份类型（如完全或增量）、备份状态（如是否已经为prepared状态）和LSN范围信息,每个InnoDB页(通常为16k大小)都会包含一个日志序列号LSN。LSN是整个数据库系统的系统版本号，每个页面相关的LSN能够表明此页面最近是如何发生改变的 (3)xtrabackup_binlog_info：
MySQL服务器当前正在使用的二进制日志文件及至备份这一刻为止二进制日志事件的位置，可利用实现基于binlog的恢复 (4)backup-my.cnf：
备份命令用到的配置选项信息 (5)xtrabackup_logfile：
备份生成的日志文件
三.xtrabackup备份还原概述
1>.旧版(2.4版本以前)xtrabackup完全备份及还原
(1)在原主机 innobackupex --user=root /backups scp -r /backups/2018-02-23_11-55-57/ 目标主机:/data/ (2)在目标主机 innobackupex --apply-log /data/2018-02-23_11-55-57/ systemctl stop mariadb rm -rf /var/lib/mysql/* innobackupex --copy-back /data/2018-02-23_11-55-57/ chown -R mysql.mysql /var/lib/mysql/ systemctl start mariadb
2>. 新版xtrabackup完全备份及还原
(1)在原主机做完全备份到/backups xtrabackup --backup --target-dir=/backups/ scp -r /backups/* 目标主机:/backups (2)在目标主机上 1)预准备：
确保数据一致，提交完成的事务，回滚未完成的事务 xtrabackup --prepare --target-dir=/backups/ 2)复制到数据库目录 注意：数据库目录必须为空，MySQL服务不能启动 xtrabackup --copy-back --target-dir=/backups/ 3)还原属性 chown -R mysql:mysql /var/lib/mysql 4)启动服务 systemctl start mariadb
3>.旧版xtrabackup完全，增量备份及还原
(1)在原主机 innobackupex /backups mkdir /backups/inc{1,2} 修改数据库内容 innobackupex --incremental /backups/inc1 --incremental-basedir=/backups/2018-02-23_14-21-42（完全备份生成的路径） 再次修改数据库内容 innobackupex --incremental /backups/inc2 --incremental-basedir=/backups/inc1/2018-02-23_14-26-17 （上次增量备份生成的路径） scp -r /backups/* 目标主机:/data/ (2)在目标主机 不启动mariadb rm -rf /var/lib/mysql/* innobackupex --apply-log --redo-only /data/2018-02-23_14-21-42/ innobackupex --apply-log --redo-only /data/2018-02-23_14-21-42/ --incremental-dir=/data/inc1/2018-02-23_14-26-17 innobackupex --apply-log /data/2018-02-23_14-21-42/ --incremental-dir=/data/inc2/2018-02-23_14-28-29/ ls /var/lib/mysql/ innobackupex --copy-back /data/2018-02-23_14-21-42/ chown -R mysql.mysql /var/lib/mysql/ systemctl start mariadb
4>.新版xtrabackup完全，增量备份及还原
(1)备份过程 1)完全备份：xtrabackup --backup --target-dir=/backups/base 2)第一次修改数据 3)第一次增量备份 xtrabackup --backup --target-dir=/backups/inc1 --incremental-basedir=/backups/base 4)第二次修改数据 5)第二次增量 xtrabackup --backup --target-dir=/backups/inc2 --incremental-basedir=/backups/inc1 6)scp -r /backups/* 目标主机:/backups/ 备份过程生成三个备份目录 /backups/{base，inc1，inc2} (2)还原过程 1)预准备完成备份，此选项--apply-log-only 阻止回滚未完成的事务 xtrabackup --prepare --apply-log-only --target-dir=/backups/base 2)合并第1次增量备份到完全备份， xtrabackup --prepare --apply-log-only --target-dir=/backups/base --incremental-dir=/backups/inc1 3)合并第2次增量备份到完全备份：最后一次还原不需要加选项--apply-log-only xtrabackup --prepare --target-dir=/backups/base --incremental-dir=/backups/inc2 4)复制到数据库目录，注意数据库目录必须为空，MySQL服务不能启动 xtrabackup --copy-back --target-dir=/backups/base 5)还原属性：chown -R mysql:mysql /var/lib/mysql 6)启动服务：systemctl start mariadb
5>.xtrabackup单表导出和导入（最好使用Mariadb 10.2及以上版本试验）
(1)单表备份 innobackupex --include='hellodb.students' /backups (2)备份表结构 mysql -e 'show create table hellodb.students' > student.sql (3)删除表 mysql -e 'drop table hellodb.students‘ (4)innobackupex --apply-log --export /backups/2018-02-23_15-03-23/ (5)创建表 mysql>CREATE TABLE `students` ( `StuID` int(10) unsigned NOT NULL AUTO_INCREMENT, `Name` varchar(50) NOT NULL, `Age` tinyint(3) unsigned NOT NULL, `Gender` enum('F','M') NOT NULL, `ClassID` tinyint(3) unsigned DEFAULT NULL, `TeacherID` int(10) unsigned DEFAULT NULL, PRIMARY KEY (`StuID`) ) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8 (6)删除表空间 alter table students discard tablespace; (7)cp /backups/2018-02-23_15-03-23/hellodb/students.{cfg,exp,ibd} /var/lib/mysql/hellodb/ (8)chown -R mysql.mysql /var/lib/mysql/hellodb/ (9)mysql>alter table students import tablespace;
四.xtrabackup实现完全备份
1>.原主机备份操作
[root@node102.yinzhengjie.org.cn ~]# ss -ntl #确保MariaDB服务是正常运行的 State Recv-Q Send-Q Local Address:Port Peer Address:Port LISTEN 0 50 *:3306 *:* LISTEN 0 128 *:22 *:* LISTEN 0 128 :::22 :::* [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]# ps -ef | grep mysql mysql 4179 1 0 Nov06 ? 00:00:00 /bin/sh /usr/bin/mysqld_safe --basedir=/usr mysql 4365 4179 0 Nov06 ? 00:00:28 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/log/mariadb/mariadb.log --pid-file=/var/run/mariadb/mariadb.pid --socket=/var/lib/mysql/mysql.sockroot 10395 10336 0 07:48 pts/1 00:00:00 grep --color=auto mysql [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]# systemctl status mariadb ● mariadb.service - MariaDB database server Loaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled; vendor preset: disabled) Active: active (running) since Wed 2019-11-06 11:45:53 CST; 20h ago Main PID: 4179 (mysqld_safe) CGroup: /system.slice/mariadb.service ├─4179 /bin/sh /usr/bin/mysqld_safe --basedir=/usr └─4365 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/log/mariad... Nov 06 11:45:51 node102.yinzhengjie.org.cn mariadb-prepare-db-dir[4092]: MySQL manual for more instructions. Nov 06 11:45:51 node102.yinzhengjie.org.cn mariadb-prepare-db-dir[4092]: Please report any problems at http://mariadb.org/jira Nov 06 11:45:51 node102.yinzhengjie.org.cn mariadb-prepare-db-dir[4092]: The latest information about MariaDB is available at http://mari...org/. Nov 06 11:45:51 node102.yinzhengjie.org.cn mariadb-prepare-db-dir[4092]: You can find additional information about the MySQL part at: Nov 06 11:45:51 node102.yinzhengjie.org.cn mariadb-prepare-db-dir[4092]: http://dev.mysql.com Nov 06 11:45:51 node102.yinzhengjie.org.cn mariadb-prepare-db-dir[4092]: Consider joining MariaDB's strong and vibrant community: Nov 06 11:45:51 node102.yinzhengjie.org.cn mariadb-prepare-db-dir[4092]: https://mariadb.org/get-involved/ Nov 06 11:45:51 node102.yinzhengjie.org.cn mysqld_safe[4179]: 191106 11:45:51 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'. Nov 06 11:45:51 node102.yinzhengjie.org.cn mysqld_safe[4179]: 191106 11:45:51 mysqld_safe Starting mysqld daemon with databases from /va.../mysql Nov 06 11:45:53 node102.yinzhengjie.org.cn systemd[1]: Started MariaDB database server. Hint: Some lines were ellipsized, use -l to show in full. [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]# mkdir -pv /data/backups/ mkdir: created directory ‘/data/backups/’ [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]# xtrabackup --backup --target-dir=/data/backups/ #生产环境需要使用指定用户名和密码，它才会自动连接MySQL实例，我这里并没有为MySQL配置初始密码 xtrabackup: recognized server arguments: --log_bin=/data/logbin/mysql-bin --datadir=/var/lib/mysql xtrabackup: recognized client arguments: --log_bin=/data/logbin/mysql-bin --datadir=/var/lib/mysql --backup=1 --target-dir=/data/backups/ 191107 07:49:40 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' (using password: NO). 191107 07:49:40 version_check Connected to MySQL server 191107 07:49:40 version_check Executing a version check against the server... ******************************************************************* Using the default of SSL_verify_mode of SSL_VERIFY_NONE for client is deprecated! Please set SSL_verify_mode to SSL_VERIFY_PEER possibly with SSL_ca_file|SSL_ca_path for verification. If you really don't want to verify the certificate and keep the connection open to Man-In-The-Middle attacks please set SSL_verify_mode explicitly to SSL_VERIFY_NONE in your application. ******************************************************************* at - line 237. ******************************************************************* Using the default of SSL_verify_mode of SSL_VERIFY_NONE for client is deprecated! Please set SSL_verify_mode to SSL_VERIFY_PEER possibly with SSL_ca_file|SSL_ca_path for verification. If you really don't want to verify the certificate and keep the connection open to Man-In-The-Middle attacks please set SSL_verify_mode explicitly to SSL_VERIFY_NONE in your application. ******************************************************************* at - line 237. # A software update is available: 191107 07:49:43 version_check Done. 191107 07:49:43 Connecting to MySQL server host: localhost, user: not set, password: not set, port: not set, socket: not set Using server version 5.5.64-MariaDB xtrabackup version 2.4.12 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 170eb8c) xtrabackup: uses posix_fadvise(). xtrabackup: cd to /var/lib/mysql xtrabackup: open files limit requested 0, set to 1048576 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 191107 07:49:43 >> log scanned up to (1610604) xtrabackup: Generating a list of tablespaces 191107 07:49:43 [01] Copying ./ibdata1 to /data/backups/ibdata1 191107 07:49:43 [01] ...done 191107 07:49:44 >> log scanned up to (1610604) 191107 07:49:44 Executing FLUSH NO_WRITE_TO_BINLOG TABLES... 191107 07:49:44 Executing FLUSH TABLES WITH READ LOCK... 191107 07:49:44 Starting to backup non-InnoDB tables and files 191107 07:49:44 [01] Copying ./mysql/tables_priv.frm to /data/backups/mysql/tables_priv.frm 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./mysql/tables_priv.MYI to /data/backups/mysql/tables_priv.MYI 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./mysql/tables_priv.MYD to /data/backups/mysql/tables_priv.MYD 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./mysql/time_zone.MYD to /data/backups/mysql/time_zone.MYD 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./mysql/host.MYD to /data/backups/mysql/host.MYD 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./mysql/ndb_binlog_index.MYI to /data/backups/mysql/ndb_binlog_index.MYI 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./mysql/plugin.frm to /data/backups/mysql/plugin.frm 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./mysql/plugin.MYD to /data/backups/mysql/plugin.MYD 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./mysql/proc.MYI to /data/backups/mysql/proc.MYI 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./mysql/procs_priv.frm to /data/backups/mysql/procs_priv.frm 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./mysql/procs_priv.MYD to /data/backups/mysql/procs_priv.MYD 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./mysql/proxies_priv.MYI to /data/backups/mysql/proxies_priv.MYI 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./mysql/servers.MYI to /data/backups/mysql/servers.MYI 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./mysql/time_zone.MYI to /data/backups/mysql/time_zone.MYI 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./mysql/time_zone_name.frm to /data/backups/mysql/time_zone_name.frm 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./mysql/time_zone_name.MYI to /data/backups/mysql/time_zone_name.MYI 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./mysql/time_zone_name.MYD to /data/backups/mysql/time_zone_name.MYD 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./mysql/time_zone_transition.frm to /data/backups/mysql/time_zone_transition.frm 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./mysql/time_zone_transition.MYI to /data/backups/mysql/time_zone_transition.MYI 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./mysql/time_zone_transition.MYD to /data/backups/mysql/time_zone_transition.MYD 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./mysql/time_zone_transition_type.frm to /data/backups/mysql/time_zone_transition_type.frm 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./mysql/time_zone_transition_type.MYI to /data/backups/mysql/time_zone_transition_type.MYI 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./mysql/time_zone_transition_type.MYD to /data/backups/mysql/time_zone_transition_type.MYD 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./mysql/user.frm to /data/backups/mysql/user.frm 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./mysql/user.MYI to /data/backups/mysql/user.MYI 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./mysql/user.MYD to /data/backups/mysql/user.MYD 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./mysql/time_zone_leap_second.frm to /data/backups/mysql/time_zone_leap_second.frm 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./mysql/time_zone_leap_second.MYI to /data/backups/mysql/time_zone_leap_second.MYI 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./mysql/general_log.frm to /data/backups/mysql/general_log.frm 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./mysql/general_log.CSM to /data/backups/mysql/general_log.CSM 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./mysql/general_log.CSV to /data/backups/mysql/general_log.CSV 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./mysql/slow_log.frm to /data/backups/mysql/slow_log.frm 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./mysql/slow_log.CSM to /data/backups/mysql/slow_log.CSM 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./mysql/slow_log.CSV to /data/backups/mysql/slow_log.CSV 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./mysql/servers.frm to /data/backups/mysql/servers.frm 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./mysql/servers.MYD to /data/backups/mysql/servers.MYD 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./mysql/time_zone_leap_second.MYD to /data/backups/mysql/time_zone_leap_second.MYD 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./mysql/columns_priv.frm to /data/backups/mysql/columns_priv.frm 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./mysql/columns_priv.MYI to /data/backups/mysql/columns_priv.MYI 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./mysql/columns_priv.MYD to /data/backups/mysql/columns_priv.MYD 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./mysql/db.frm to /data/backups/mysql/db.frm 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./mysql/db.MYI to /data/backups/mysql/db.MYI 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./mysql/db.MYD to /data/backups/mysql/db.MYD 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./mysql/event.frm to /data/backups/mysql/event.frm 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./mysql/event.MYI to /data/backups/mysql/event.MYI 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./mysql/event.MYD to /data/backups/mysql/event.MYD 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./mysql/func.frm to /data/backups/mysql/func.frm 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./mysql/func.MYI to /data/backups/mysql/func.MYI 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./mysql/func.MYD to /data/backups/mysql/func.MYD 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./mysql/help_category.frm to /data/backups/mysql/help_category.frm 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./mysql/help_category.MYI to /data/backups/mysql/help_category.MYI 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./mysql/help_category.MYD to /data/backups/mysql/help_category.MYD 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./mysql/help_keyword.frm to /data/backups/mysql/help_keyword.frm 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./mysql/help_keyword.MYI to /data/backups/mysql/help_keyword.MYI 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./mysql/help_keyword.MYD to /data/backups/mysql/help_keyword.MYD 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./mysql/help_relation.frm to /data/backups/mysql/help_relation.frm 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./mysql/help_relation.MYI to /data/backups/mysql/help_relation.MYI 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./mysql/help_relation.MYD to /data/backups/mysql/help_relation.MYD 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./mysql/help_topic.frm to /data/backups/mysql/help_topic.frm 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./mysql/help_topic.MYI to /data/backups/mysql/help_topic.MYI 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./mysql/help_topic.MYD to /data/backups/mysql/help_topic.MYD 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./mysql/host.frm to /data/backups/mysql/host.frm 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./mysql/host.MYI to /data/backups/mysql/host.MYI 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./mysql/ndb_binlog_index.frm to /data/backups/mysql/ndb_binlog_index.frm 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./mysql/ndb_binlog_index.MYD to /data/backups/mysql/ndb_binlog_index.MYD 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./mysql/plugin.MYI to /data/backups/mysql/plugin.MYI 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./mysql/proc.frm to /data/backups/mysql/proc.frm 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./mysql/proc.MYD to /data/backups/mysql/proc.MYD 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./mysql/procs_priv.MYI to /data/backups/mysql/procs_priv.MYI 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./mysql/proxies_priv.frm to /data/backups/mysql/proxies_priv.frm 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./mysql/proxies_priv.MYD to /data/backups/mysql/proxies_priv.MYD 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./mysql/time_zone.frm to /data/backups/mysql/time_zone.frm 191107 07:49:44 [01] ...done 191107 07:49:44 [00] Writing /data/backups/test/db.opt 191107 07:49:44 [00] ...done 191107 07:49:44 [01] Copying ./yinzhengjiedb/db.opt to /data/backups/yinzhengjiedb/db.opt 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./yinzhengjiedb/students.frm to /data/backups/yinzhengjiedb/students.frm 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./yinzhengjiedb/employee.frm to /data/backups/yinzhengjiedb/employee.frm 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./yinzhengjiedb/custom.frm to /data/backups/yinzhengjiedb/custom.frm 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./performance_schema/db.opt to /data/backups/performance_schema/db.opt 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./performance_schema/cond_instances.frm to /data/backups/performance_schema/cond_instances.frm 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./performance_schema/events_waits_current.frm to /data/backups/performance_schema/events_waits_current.frm 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./performance_schema/events_waits_history.frm to /data/backups/performance_schema/events_waits_history.frm 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./performance_schema/events_waits_history_long.frm to /data/backups/performance_schema/events_waits_history_long.frm 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./performance_schema/events_waits_summary_by_instance.frm to /data/backups/performance_schema/events_waits_summary_b y_instance.frm191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./performance_schema/events_waits_summary_by_thread_by_event_name.frm to /data/backups/performance_schema/events_wai ts_summary_by_thread_by_event_name.frm191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./performance_schema/events_waits_summary_global_by_event_name.frm to /data/backups/performance_schema/events_waits_ summary_global_by_event_name.frm191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./performance_schema/file_instances.frm to /data/backups/performance_schema/file_instances.frm 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./performance_schema/file_summary_by_event_name.frm to /data/backups/performance_schema/file_summary_by_event_name.f rm191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./performance_schema/file_summary_by_instance.frm to /data/backups/performance_schema/file_summary_by_instance.frm 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./performance_schema/mutex_instances.frm to /data/backups/performance_schema/mutex_instances.frm 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./performance_schema/performance_timers.frm to /data/backups/performance_schema/performance_timers.frm 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./performance_schema/rwlock_instances.frm to /data/backups/performance_schema/rwlock_instances.frm 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./performance_schema/setup_consumers.frm to /data/backups/performance_schema/setup_consumers.frm 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./performance_schema/setup_instruments.frm to /data/backups/performance_schema/setup_instruments.frm 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./performance_schema/setup_timers.frm to /data/backups/performance_schema/setup_timers.frm 191107 07:49:44 [01] ...done 191107 07:49:44 [01] Copying ./performance_schema/threads.frm to /data/backups/performance_schema/threads.frm 191107 07:49:44 [01] ...done 191107 07:49:44 Finished backing up non-InnoDB tables and files 191107 07:49:44 [00] Writing /data/backups/xtrabackup_binlog_info 191107 07:49:44 [00] ...done 191107 07:49:44 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS... xtrabackup: The latest check point (for incremental): '1610604' xtrabackup: Stopping log copying thread. .191107 07:49:44 >> log scanned up to (1610604) 191107 07:49:45 Executing UNLOCK TABLES 191107 07:49:45 All tables unlocked 191107 07:49:45 Backup created in directory '/data/backups/' MySQL binlog position: filename 'mysql-bin.000006', position '245' 191107 07:49:45 [00] Writing /data/backups/backup-my.cnf 191107 07:49:45 [00] ...done 191107 07:49:45 [00] Writing /data/backups/xtrabackup_info 191107 07:49:45 [00] ...done xtrabackup: Transaction log of lsn (1610604) to (1610604) was copied. 191107 07:49:45 completed OK! [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]# ll /data/backups/ total 18460 -rw-r----- 1 root root 431 Nov 7 07:49 backup-my.cnf -rw-r----- 1 root root 18874368 Nov 7 07:49 ibdata1 drwxr-x--- 2 root root 4096 Nov 7 07:49 mysql drwxr-x--- 2 root root 4096 Nov 7 07:49 performance_schema drwxr-x--- 2 root root 20 Nov 7 07:49 test -rw-r----- 1 root root 21 Nov 7 07:49 xtrabackup_binlog_info -rw-r----- 1 root root 113 Nov 7 07:49 xtrabackup_checkpoints -rw-r----- 1 root root 469 Nov 7 07:49 xtrabackup_info -rw-r----- 1 root root 2560 Nov 7 07:49 xtrabackup_logfile drwxr-x--- 2 root root 78 Nov 7 07:49 yinzhengjiedb [root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]# ll /data/backups/ #备份后，数据库的内容会在我们的目标目录存在 total 18460 -rw-r----- 1 root root 431 Nov 7 07:49 backup-my.cnf -rw-r----- 1 root root 18874368 Nov 7 07:49 ibdata1 drwxr-x--- 2 root root 4096 Nov 7 07:49 mysql drwxr-x--- 2 root root 4096 Nov 7 07:49 performance_schema drwxr-x--- 2 root root 20 Nov 7 07:49 test -rw-r----- 1 root root 21 Nov 7 07:49 xtrabackup_binlog_info -rw-r----- 1 root root 113 Nov 7 07:49 xtrabackup_checkpoints -rw-r----- 1 root root 469 Nov 7 07:49 xtrabackup_info -rw-r----- 1 root root 2560 Nov 7 07:49 xtrabackup_logfile drwxr-x--- 2 root root 78 Nov 7 07:49 yinzhengjiedb [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]# cat /data/backups/xtrabackup_binlog_info mysql-bin.000006 245 [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]# cat /data/backups/xtrabackup_checkpoints backup_type = full-backuped from_lsn = 0 to_lsn = 1610604 last_lsn = 1610604 compact = 0 recover_binlog_info = 0 [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]# cat /data/backups/xtrabackup_info uuid = 1c0b8ed2-00f0-11ea-bf7a-08002750b826 name = tool_name = xtrabackup tool_command = --backup --target-dir=/data/backups/ tool_version = 2.4.12 ibbackup_version = 2.4.12 server_version = 5.5.64-MariaDB start_time = 2019-11-07 07:49:43 end_time = 2019-11-07 07:49:45 lock_time = 0 binlog_pos = filename 'mysql-bin.000006', position '245' innodb_from_lsn = 0 innodb_to_lsn = 1610604 partial = N incremental = N format = file compact = N compressed = N encrypted = N [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]# cat /data/backups/backup-my.cnf # This MySQL options file was generated by innobackupex. # The MySQL server [mysqld] innodb_checksum_algorithm=innodb innodb_log_checksum_algorithm=innodb innodb_data_file_path=ibdata1:10M:autoextend innodb_log_files_in_group=2 innodb_log_file_size=5242880 innodb_fast_checksum=false innodb_page_size=16384 innodb_log_block_size=512 innodb_undo_directory=. innodb_undo_tablespaces=0 server_id=0 redo_log_version=0 master_key_id=0 [root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]# cat /etc/hosts 127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 ::1 localhost localhost.localdomain localhost6 localhost6.localdomain6 172.30.1.101 node101.yinzhengjie.org.cn 172.30.1.102 node102.yinzhengjie.org.cn 172.30.1.103 node103.yinzhengjie.org.cn 172.30.1.104 node104.yinzhengjie.org.cn 172.30.1.105 node105.yinzhengjie.org.cn 172.30.1.106 node106.yinzhengjie.org.cn 172.30.1.107 node107.yinzhengjie.org.cn 172.30.1.108 node108.yinzhengjie.org.cn [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]# scp -r /data/backups/ node105.yinzhengjie.org.cn:/data/backups/ The authenticity of host 'node105.yinzhengjie.org.cn (172.30.1.105)' can't be established. ECDSA key fingerprint is SHA256:gSEjXfmO4dusRsYmhoCbT1fEwpcVEfiTZUTcOO8IPLU. ECDSA key fingerprint is MD5:97:8d:25:7b:cd:04:58:82:f6:07:85:55:e3:4a:3a:1a. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added 'node105.yinzhengjie.org.cn,172.30.1.105' (ECDSA) to the list of known hosts. root@node105.yinzhengjie.org.cn's password: xtrabackup_logfile 100% 2560 3.8MB/s 00:00 ibdata1 100% 18MB 98.4MB/s 00:00 tables_priv.frm 100% 8955 11.1MB/s 00:00 tables_priv.MYI 100% 4096 9.1MB/s 00:00 tables_priv.MYD 100% 0 0.0KB/s 00:00 time_zone.MYD 100% 0 0.0KB/s 00:00 host.MYD 100% 0 0.0KB/s 00:00 ndb_binlog_index.MYI 100% 1024 1.9MB/s 00:00 plugin.frm 100% 8586 11.2MB/s 00:00 plugin.MYD 100% 0 0.0KB/s 00:00 proc.MYI 100% 4096 7.0MB/s 00:00 procs_priv.frm 100% 8875 14.6MB/s 00:00 procs_priv.MYD 100% 0 0.0KB/s 00:00 proxies_priv.MYI 100% 5120 9.9MB/s 00:00 servers.MYI 100% 1024 2.3MB/s 00:00 time_zone.MYI 100% 1024 2.5MB/s 00:00 time_zone_name.frm 100% 8606 17.7MB/s 00:00 time_zone_name.MYI 100% 1024 2.2MB/s 00:00 time_zone_name.MYD 100% 0 0.0KB/s 00:00 time_zone_transition.frm 100% 8686 12.3MB/s 00:00 time_zone_transition.MYI 100% 1024 2.3MB/s 00:00 time_zone_transition.MYD 100% 0 0.0KB/s 00:00 time_zone_transition_type.frm 100% 8748 15.6MB/s 00:00 time_zone_transition_type.MYI 100% 1024 2.5MB/s 00:00 time_zone_transition_type.MYD 100% 0 0.0KB/s 00:00 user.frm 100% 10KB 19.8MB/s 00:00 user.MYI 100% 2048 4.0MB/s 00:00 user.MYD 100% 272 644.1KB/s 00:00 time_zone_leap_second.frm 100% 8624 15.6MB/s 00:00 time_zone_leap_second.MYI 100% 1024 2.2MB/s 00:00 general_log.frm 100% 8776 14.4MB/s 00:00 general_log.CSM 100% 35 63.1KB/s 00:00 general_log.CSV 100% 0 0.0KB/s 00:00 slow_log.frm 100% 8976 16.4MB/s 00:00 slow_log.CSM 100% 35 83.8KB/s 00:00 slow_log.CSV 100% 0 0.0KB/s 00:00 servers.frm 100% 8838 17.1MB/s 00:00 servers.MYD 100% 0 0.0KB/s 00:00 time_zone_leap_second.MYD 100% 0 0.0KB/s 00:00 columns_priv.frm 100% 8820 13.8MB/s 00:00 columns_priv.MYI 100% 4096 5.8MB/s 00:00 columns_priv.MYD 100% 0 0.0KB/s 00:00 db.frm 100% 9582 16.0MB/s 00:00 db.MYI 100% 5120 10.4MB/s 00:00 db.MYD 100% 880 1.8MB/s 00:00 event.frm 100% 10KB 18.0MB/s 00:00 event.MYI 100% 2048 3.7MB/s 00:00 event.MYD 100% 0 0.0KB/s 00:00 func.frm 100% 8665 16.2MB/s 00:00 func.MYI 100% 1024 2.0MB/s 00:00 func.MYD 100% 0 0.0KB/s 00:00 help_category.frm 100% 8700 14.0MB/s 00:00 help_category.MYI 100% 3072 5.8MB/s 00:00 help_category.MYD 100% 1092 2.6MB/s 00:00 help_keyword.frm 100% 8612 16.9MB/s 00:00 help_keyword.MYI 100% 16KB 24.9MB/s 00:00 help_keyword.MYD 100% 89KB 61.0MB/s 00:00 help_relation.frm 100% 8630 16.4MB/s 00:00 help_relation.MYI 100% 19KB 28.1MB/s 00:00 help_relation.MYD 100% 9252 17.6MB/s 00:00 help_topic.frm 100% 8770 15.6MB/s 00:00 help_topic.MYI 100% 20KB 31.5MB/s 00:00 help_topic.MYD 100% 440KB 84.6MB/s 00:00 host.frm 100% 9510 14.8MB/s 00:00 host.MYI 100% 2048 4.0MB/s 00:00 ndb_binlog_index.frm 100% 8778 16.4MB/s 00:00 ndb_binlog_index.MYD 100% 0 0.0KB/s 00:00 plugin.MYI 100% 1024 2.1MB/s 00:00 proc.frm 100% 10KB 15.1MB/s 00:00 proc.MYD 100% 296 710.8KB/s 00:00 procs_priv.MYI 100% 4096 8.4MB/s 00:00 proxies_priv.frm 100% 8800 13.1MB/s 00:00 proxies_priv.MYD 100% 1386 2.9MB/s 00:00 time_zone.frm 100% 8636 16.2MB/s 00:00 db.opt 100% 0 0.0KB/s 00:00 db.opt 100% 67 138.9KB/s 00:00 students.frm 100% 8723 16.5MB/s 00:00 employee.frm 100% 8723 17.3MB/s 00:00 custom.frm 100% 8723 17.8MB/s 00:00 db.opt 100% 61 96.6KB/s 00:00 cond_instances.frm 100% 8624 16.9MB/s 00:00 events_waits_current.frm 100% 9220 18.9MB/s 00:00 events_waits_history.frm 100% 9220 15.1MB/s 00:00 events_waits_history_long.frm 100% 9220 14.4MB/s 00:00 events_waits_summary_by_instance.frm 100% 8878 14.7MB/s 00:00 events_waits_summary_by_thread_by_event_name.frm 100% 8854 16.2MB/s 00:00 events_waits_summary_global_by_event_name.frm 100% 8814 14.7MB/s 00:00 file_instances.frm 100% 8654 17.1MB/s 00:00 file_summary_by_event_name.frm 100% 8800 14.9MB/s 00:00 file_summary_by_instance.frm 100% 8840 14.6MB/s 00:00 mutex_instances.frm 100% 8684 14.3MB/s 00:00 performance_timers.frm 100% 8776 16.7MB/s 00:00 rwlock_instances.frm 100% 8758 18.0MB/s 00:00 setup_consumers.frm 100% 8605 17.5MB/s 00:00 setup_instruments.frm 100% 8637 16.0MB/s 00:00 setup_timers.frm 100% 8650 17.0MB/s 00:00 threads.frm 100% 8650 16.9MB/s 00:00 xtrabackup_binlog_info 100% 21 46.4KB/s 00:00 xtrabackup_checkpoints 100% 113 261.4KB/s 00:00 backup-my.cnf 100% 431 1.0MB/s 00:00 xtrabackup_info 100% 469 1.1MB/s 00:00 [root@node102.yinzhengjie.org.cn ~]#
2>.目标主机还原操作
[root@node105.yinzhengjie.org.cn ~]# ll /data/backups/ total 18460 -rw-r----- 1 root root 431 Nov 7 07:59 backup-my.cnf -rw-r----- 1 root root 18874368 Nov 7 07:59 ibdata1 drwxr-x--- 2 root root 4096 Nov 7 07:59 mysql drwxr-x--- 2 root root 4096 Nov 7 07:59 performance_schema drwxr-x--- 2 root root 20 Nov 7 07:59 test -rw-r----- 1 root root 21 Nov 7 07:59 xtrabackup_binlog_info -rw-r----- 1 root root 113 Nov 7 07:59 xtrabackup_checkpoints -rw-r----- 1 root root 469 Nov 7 07:59 xtrabackup_info -rw-r----- 1 root root 2560 Nov 7 07:59 xtrabackup_logfile drwxr-x--- 2 root root 78 Nov 7 07:59 yinzhengjiedb [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# du -sh /data/backups/ #预准备之前查看数据库备份文件大小 20M /data/backups/ [root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# xtrabackup --prepare --target-dir=/data/backups/ #预准备 xtrabackup: recognized server arguments: --innodb_checksum_algorithm=innodb --innodb_log_checksum_algorithm=innodb --innodb_data_file_path=ibdata 1: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: --innodb_checksum_algorithm=innodb --innodb_log_checksum_algorithm=innodb --innodb_data_file_path=ibdata 1: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 --prepare=1 --target-dir=/data/backups/ xtrabackup version 2.4.12 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 170eb8c) xtrabackup: cd to /data/backups/ xtrabackup: This target seems to be not prepared yet. InnoDB: Number of pools: 1 xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(1610604) 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 0 in the system tablespace does not match the log sequence number 1610604 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: Waiting for purge to start InnoDB: 5.7.19 started; log sequence number 1610604 xtrabackup: starting shutdown with innodb_fast_shutdown = 1 InnoDB: FTS optimize thread exiting. InnoDB: Starting shutdown... InnoDB: Shutdown completed; log sequence number 1610623 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=1610623 InnoDB: Highest supported file format is Barracuda. InnoDB: Log scan progressed past the checkpoint lsn 1610764 InnoDB: Doing recovery: scanned up to log sequence number 1610773 (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: Waiting for purge to start InnoDB: 5.7.19 started; log sequence number 1610773 xtrabackup: starting shutdown with innodb_fast_shutdown = 1 InnoDB: FTS optimize thread exiting. InnoDB: Starting shutdown... InnoDB: Shutdown completed; log sequence number 1610792 191107 08:07:07 completed OK! [root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# ll /data/backups/ total 49180 -rw-r----- 1 root root 431 Nov 7 07:59 backup-my.cnf -rw-r----- 1 root root 18874368 Nov 7 08:07 ibdata1 -rw-r----- 1 root root 5242880 Nov 7 08:07 ib_logfile0 -rw-r----- 1 root root 5242880 Nov 7 08:07 ib_logfile1 -rw-r----- 1 root root 12582912 Nov 7 08:07 ibtmp1 drwxr-x--- 2 root root 4096 Nov 7 07:59 mysql drwxr-x--- 2 root root 4096 Nov 7 07:59 performance_schema drwxr-x--- 2 root root 20 Nov 7 07:59 test -rw-r----- 1 root root 21 Nov 7 07:59 xtrabackup_binlog_info -rw-r----- 1 root root 113 Nov 7 08:07 xtrabackup_checkpoints -rw-r----- 1 root root 469 Nov 7 07:59 xtrabackup_info -rw-r----- 1 root root 8388608 Nov 7 08:07 xtrabackup_logfile -rw-r--r-- 1 root root 1 Nov 7 08:07 xtrabackup_master_key_id drwxr-x--- 2 root root 78 Nov 7 07:59 yinzhengjiedb [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# du -sh /data/backups/ #预准备之后查看数据库备份文件大小 50M /data/backups/ [root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# ss -ntl #注意,数据库目录必须为空，MySQL服务不能启动 State Recv-Q Send-Q Local Address:Port Peer Address:Port LISTEN 0 128 *:22 *:* LISTEN 0 128 :::22 :::* [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# xtrabackup --copy-back --target-dir=/data/backups/ #复制到数据库目录 xtrabackup: recognized server arguments: --datadir=/var/lib/mysql --datadir=/data/mysql --log_bin=mysql-bin --server-id=1 xtrabackup: recognized client arguments: --datadir=/var/lib/mysql --datadir=/data/mysql --log_bin=mysql-bin --server-id=1 --port=3306 --socket=/t mp/mysql.sock --copy-back=1 --target-dir=/data/backups/ xtrabackup version 2.4.12 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 170eb8c) 191107 08:12:47 [01] Copying ib_logfile0 to /data/mysql/ib_logfile0 191107 08:12:47 [01] ...done 191107 08:12:47 [01] Copying ib_logfile1 to /data/mysql/ib_logfile1 191107 08:12:47 [01] ...done 191107 08:12:47 [01] Copying ibdata1 to /data/mysql/ibdata1 191107 08:12:47 [01] ...done 191107 08:12:47 [01] Copying ./mysql/tables_priv.frm to /data/mysql/mysql/tables_priv.frm 191107 08:12:47 [01] ...done 191107 08:12:47 [01] Copying ./mysql/tables_priv.MYI to /data/mysql/mysql/tables_priv.MYI 191107 08:12:47 [01] ...done 191107 08:12:47 [01] Copying ./mysql/tables_priv.MYD to /data/mysql/mysql/tables_priv.MYD 191107 08:12:47 [01] ...done 191107 08:12:47 [01] Copying ./mysql/time_zone.MYD to /data/mysql/mysql/time_zone.MYD 191107 08:12:47 [01] ...done 191107 08:12:47 [01] Copying ./mysql/host.MYD to /data/mysql/mysql/host.MYD 191107 08:12:47 [01] ...done 191107 08:12:47 [01] Copying ./mysql/ndb_binlog_index.MYI to /data/mysql/mysql/ndb_binlog_index.MYI 191107 08:12:47 [01] ...done 191107 08:12:47 [01] Copying ./mysql/plugin.frm to /data/mysql/mysql/plugin.frm 191107 08:12:47 [01] ...done 191107 08:12:47 [01] Copying ./mysql/plugin.MYD to /data/mysql/mysql/plugin.MYD 191107 08:12:47 [01] ...done 191107 08:12:47 [01] Copying ./mysql/proc.MYI to /data/mysql/mysql/proc.MYI 191107 08:12:47 [01] ...done 191107 08:12:47 [01] Copying ./mysql/procs_priv.frm to /data/mysql/mysql/procs_priv.frm 191107 08:12:47 [01] ...done 191107 08:12:47 [01] Copying ./mysql/procs_priv.MYD to /data/mysql/mysql/procs_priv.MYD 191107 08:12:47 [01] ...done 191107 08:12:47 [01] Copying ./mysql/proxies_priv.MYI to /data/mysql/mysql/proxies_priv.MYI 191107 08:12:47 [01] ...done 191107 08:12:47 [01] Copying ./mysql/servers.MYI to /data/mysql/mysql/servers.MYI 191107 08:12:47 [01] ...done 191107 08:12:47 [01] Copying ./mysql/time_zone.MYI to /data/mysql/mysql/time_zone.MYI 191107 08:12:47 [01] ...done 191107 08:12:47 [01] Copying ./mysql/time_zone_name.frm to /data/mysql/mysql/time_zone_name.frm 191107 08:12:47 [01] ...done 191107 08:12:47 [01] Copying ./mysql/time_zone_name.MYI to /data/mysql/mysql/time_zone_name.MYI 191107 08:12:47 [01] ...done 191107 08:12:47 [01] Copying ./mysql/time_zone_name.MYD to /data/mysql/mysql/time_zone_name.MYD 191107 08:12:47 [01] ...done 191107 08:12:47 [01] Copying ./mysql/time_zone_transition.frm to /data/mysql/mysql/time_zone_transition.frm 191107 08:12:47 [01] ...done 191107 08:12:47 [01] Copying ./mysql/time_zone_transition.MYI to /data/mysql/mysql/time_zone_transition.MYI 191107 08:12:47 [01] ...done 191107 08:12:47 [01] Copying ./mysql/time_zone_transition.MYD to /data/mysql/mysql/time_zone_transition.MYD 191107 08:12:47 [01] ...done 191107 08:12:47 [01] Copying ./mysql/time_zone_transition_type.frm to /data/mysql/mysql/time_zone_transition_type.frm 191107 08:12:47 [01] ...done 191107 08:12:47 [01] Copying ./mysql/time_zone_transition_type.MYI to /data/mysql/mysql/time_zone_transition_type.MYI 191107 08:12:47 [01] ...done 191107 08:12:47 [01] Copying ./mysql/time_zone_transition_type.MYD to /data/mysql/mysql/time_zone_transition_type.MYD 191107 08:12:47 [01] ...done 191107 08:12:47 [01] Copying ./mysql/user.frm to /data/mysql/mysql/user.frm 191107 08:12:47 [01] ...done 191107 08:12:47 [01] Copying ./mysql/user.MYI to /data/mysql/mysql/user.MYI 191107 08:12:47 [01] ...done 191107 08:12:47 [01] Copying ./mysql/user.MYD to /data/mysql/mysql/user.MYD 191107 08:12:47 [01] ...done 191107 08:12:47 [01] Copying ./mysql/time_zone_leap_second.frm to /data/mysql/mysql/time_zone_leap_second.frm 191107 08:12:47 [01] ...done 191107 08:12:47 [01] Copying ./mysql/time_zone_leap_second.MYI to /data/mysql/mysql/time_zone_leap_second.MYI 191107 08:12:47 [01] ...done 191107 08:12:47 [01] Copying ./mysql/general_log.frm to /data/mysql/mysql/general_log.frm 191107 08:12:47 [01] ...done 191107 08:12:47 [01] Copying ./mysql/general_log.CSM to /data/mysql/mysql/general_log.CSM 191107 08:12:47 [01] ...done 191107 08:12:47 [01] Copying ./mysql/general_log.CSV to /data/mysql/mysql/general_log.CSV 191107 08:12:47 [01] ...done 191107 08:12:47 [01] Copying ./mysql/slow_log.frm to /data/mysql/mysql/slow_log.frm 191107 08:12:47 [01] ...done 191107 08:12:47 [01] Copying ./mysql/slow_log.CSM to /data/mysql/mysql/slow_log.CSM 191107 08:12:47 [01] ...done 191107 08:12:47 [01] Copying ./mysql/slow_log.CSV to /data/mysql/mysql/slow_log.CSV 191107 08:12:47 [01] ...done 191107 08:12:47 [01] Copying ./mysql/servers.frm to /data/mysql/mysql/servers.frm 191107 08:12:47 [01] ...done 191107 08:12:47 [01] Copying ./mysql/servers.MYD to /data/mysql/mysql/servers.MYD 191107 08:12:47 [01] ...done 191107 08:12:47 [01] Copying ./mysql/time_zone_leap_second.MYD to /data/mysql/mysql/time_zone_leap_second.MYD 191107 08:12:47 [01] ...done 191107 08:12:47 [01] Copying ./mysql/columns_priv.frm to /data/mysql/mysql/columns_priv.frm 191107 08:12:47 [01] ...done 191107 08:12:47 [01] Copying ./mysql/columns_priv.MYI to /data/mysql/mysql/columns_priv.MYI 191107 08:12:47 [01] ...done 191107 08:12:47 [01] Copying ./mysql/columns_priv.MYD to /data/mysql/mysql/columns_priv.MYD 191107 08:12:47 [01] ...done 191107 08:12:47 [01] Copying ./mysql/db.frm to /data/mysql/mysql/db.frm 191107 08:12:47 [01] ...done 191107 08:12:47 [01] Copying ./mysql/db.MYI to /data/mysql/mysql/db.MYI 191107 08:12:47 [01] ...done 191107 08:12:47 [01] Copying ./mysql/db.MYD to /data/mysql/mysql/db.MYD 191107 08:12:47 [01] ...done 191107 08:12:47 [01] Copying ./mysql/event.frm to /data/mysql/mysql/event.frm 191107 08:12:47 [01] ...done 191107 08:12:47 [01] Copying ./mysql/event.MYI to /data/mysql/mysql/event.MYI 191107 08:12:47 [01] ...done 191107 08:12:47 [01] Copying ./mysql/event.MYD to /data/mysql/mysql/event.MYD 191107 08:12:47 [01] ...done 191107 08:12:47 [01] Copying ./mysql/func.frm to /data/mysql/mysql/func.frm 191107 08:12:47 [01] ...done 191107 08:12:47 [01] Copying ./mysql/func.MYI to /data/mysql/mysql/func.MYI 191107 08:12:47 [01] ...done 191107 08:12:47 [01] Copying ./mysql/func.MYD to /data/mysql/mysql/func.MYD 191107 08:12:47 [01] ...done 191107 08:12:47 [01] Copying ./mysql/help_category.frm to /data/mysql/mysql/help_category.frm 191107 08:12:47 [01] ...done 191107 08:12:47 [01] Copying ./mysql/help_category.MYI to /data/mysql/mysql/help_category.MYI 191107 08:12:47 [01] ...done 191107 08:12:47 [01] Copying ./mysql/help_category.MYD to /data/mysql/mysql/help_category.MYD 191107 08:12:47 [01] ...done 191107 08:12:47 [01] Copying ./mysql/help_keyword.frm to /data/mysql/mysql/help_keyword.frm 191107 08:12:47 [01] ...done 191107 08:12:47 [01] Copying ./mysql/help_keyword.MYI to /data/mysql/mysql/help_keyword.MYI 191107 08:12:47 [01] ...done 191107 08:12:48 [01] Copying ./mysql/help_keyword.MYD to /data/mysql/mysql/help_keyword.MYD 191107 08:12:48 [01] ...done 191107 08:12:48 [01] Copying ./mysql/help_relation.frm to /data/mysql/mysql/help_relation.frm 191107 08:12:48 [01] ...done 191107 08:12:48 [01] Copying ./mysql/help_relation.MYI to /data/mysql/mysql/help_relation.MYI 191107 08:12:48 [01] ...done 191107 08:12:48 [01] Copying ./mysql/help_relation.MYD to /data/mysql/mysql/help_relation.MYD 191107 08:12:48 [01] ...done 191107 08:12:48 [01] Copying ./mysql/help_topic.frm to /data/mysql/mysql/help_topic.frm 191107 08:12:48 [01] ...done 191107 08:12:48 [01] Copying ./mysql/help_topic.MYI to /data/mysql/mysql/help_topic.MYI 191107 08:12:48 [01] ...done 191107 08:12:48 [01] Copying ./mysql/help_topic.MYD to /data/mysql/mysql/help_topic.MYD 191107 08:12:48 [01] ...done 191107 08:12:48 [01] Copying ./mysql/host.frm to /data/mysql/mysql/host.frm 191107 08:12:48 [01] ...done 191107 08:12:48 [01] Copying ./mysql/host.MYI to /data/mysql/mysql/host.MYI 191107 08:12:48 [01] ...done 191107 08:12:48 [01] Copying ./mysql/ndb_binlog_index.frm to /data/mysql/mysql/ndb_binlog_index.frm 191107 08:12:48 [01] ...done 191107 08:12:48 [01] Copying ./mysql/ndb_binlog_index.MYD to /data/mysql/mysql/ndb_binlog_index.MYD 191107 08:12:48 [01] ...done 191107 08:12:48 [01] Copying ./mysql/plugin.MYI to /data/mysql/mysql/plugin.MYI 191107 08:12:48 [01] ...done 191107 08:12:48 [01] Copying ./mysql/proc.frm to /data/mysql/mysql/proc.frm 191107 08:12:48 [01] ...done 191107 08:12:48 [01] Copying ./mysql/proc.MYD to /data/mysql/mysql/proc.MYD 191107 08:12:48 [01] ...done 191107 08:12:48 [01] Copying ./mysql/procs_priv.MYI to /data/mysql/mysql/procs_priv.MYI 191107 08:12:48 [01] ...done 191107 08:12:48 [01] Copying ./mysql/proxies_priv.frm to /data/mysql/mysql/proxies_priv.frm 191107 08:12:48 [01] ...done 191107 08:12:48 [01] Copying ./mysql/proxies_priv.MYD to /data/mysql/mysql/proxies_priv.MYD 191107 08:12:48 [01] ...done 191107 08:12:48 [01] Copying ./mysql/time_zone.frm to /data/mysql/mysql/time_zone.frm 191107 08:12:48 [01] ...done 191107 08:12:48 [01] Copying ./test/db.opt to /data/mysql/test/db.opt 191107 08:12:48 [01] ...done 191107 08:12:48 [01] Copying ./yinzhengjiedb/db.opt to /data/mysql/yinzhengjiedb/db.opt 191107 08:12:48 [01] ...done 191107 08:12:48 [01] Copying ./yinzhengjiedb/students.frm to /data/mysql/yinzhengjiedb/students.frm 191107 08:12:48 [01] ...done 191107 08:12:48 [01] Copying ./yinzhengjiedb/employee.frm to /data/mysql/yinzhengjiedb/employee.frm 191107 08:12:48 [01] ...done 191107 08:12:48 [01] Copying ./yinzhengjiedb/custom.frm to /data/mysql/yinzhengjiedb/custom.frm 191107 08:12:48 [01] ...done 191107 08:12:48 [01] Copying ./performance_schema/db.opt to /data/mysql/performance_schema/db.opt 191107 08:12:48 [01] ...done 191107 08:12:48 [01] Copying ./performance_schema/cond_instances.frm to /data/mysql/performance_schema/cond_instances.frm 191107 08:12:48 [01] ...done 191107 08:12:48 [01] Copying ./performance_schema/events_waits_current.frm to /data/mysql/performance_schema/events_waits_current.frm 191107 08:12:48 [01] ...done 191107 08:12:48 [01] Copying ./performance_schema/events_waits_history.frm to /data/mysql/performance_schema/events_waits_history.frm 191107 08:12:48 [01] ...done 191107 08:12:48 [01] Copying ./performance_schema/events_waits_history_long.frm to /data/mysql/performance_schema/events_waits_history_long.frm 191107 08:12:48 [01] ...done 191107 08:12:48 [01] Copying ./performance_schema/events_waits_summary_by_instance.frm to /data/mysql/performance_schema/events_waits_summary_by_ instance.frm191107 08:12:48 [01] ...done 191107 08:12:48 [01] Copying ./performance_schema/events_waits_summary_by_thread_by_event_name.frm to /data/mysql/performance_schema/events_waits _summary_by_thread_by_event_name.frm191107 08:12:48 [01] ...done 191107 08:12:48 [01] Copying ./performance_schema/events_waits_summary_global_by_event_name.frm to /data/mysql/performance_schema/events_waits_su mmary_global_by_event_name.frm191107 08:12:48 [01] ...done 191107 08:12:48 [01] Copying ./performance_schema/file_instances.frm to /data/mysql/performance_schema/file_instances.frm 191107 08:12:48 [01] ...done 191107 08:12:48 [01] Copying ./performance_schema/file_summary_by_event_name.frm to /data/mysql/performance_schema/file_summary_by_event_name.frm 191107 08:12:48 [01] ...done 191107 08:12:48 [01] Copying ./performance_schema/file_summary_by_instance.frm to /data/mysql/performance_schema/file_summary_by_instance.frm 191107 08:12:48 [01] ...done 191107 08:12:48 [01] Copying ./performance_schema/mutex_instances.frm to /data/mysql/performance_schema/mutex_instances.frm 191107 08:12:48 [01] ...done 191107 08:12:48 [01] Copying ./performance_schema/performance_timers.frm to /data/mysql/performance_schema/performance_timers.frm 191107 08:12:48 [01] ...done 191107 08:12:48 [01] Copying ./performance_schema/rwlock_instances.frm to /data/mysql/performance_schema/rwlock_instances.frm 191107 08:12:48 [01] ...done 191107 08:12:48 [01] Copying ./performance_schema/setup_consumers.frm to /data/mysql/performance_schema/setup_consumers.frm 191107 08:12:48 [01] ...done 191107 08:12:48 [01] Copying ./performance_schema/setup_instruments.frm to /data/mysql/performance_schema/setup_instruments.frm 191107 08:12:48 [01] ...done 191107 08:12:48 [01] Copying ./performance_schema/setup_timers.frm to /data/mysql/performance_schema/setup_timers.frm 191107 08:12:48 [01] ...done 191107 08:12:48 [01] Copying ./performance_schema/threads.frm to /data/mysql/performance_schema/threads.frm 191107 08:12:48 [01] ...done 191107 08:12:48 [01] Copying ./xtrabackup_info to /data/mysql/xtrabackup_info 191107 08:12:48 [01] ...done 191107 08:12:48 [01] Copying ./xtrabackup_master_key_id to /data/mysql/xtrabackup_master_key_id 191107 08:12:48 [01] ...done 191107 08:12:48 [01] Copying ./ibtmp1 to /data/mysql/ibtmp1 191107 08:12:48 [01] ...done 191107 08:12:48 completed OK! [root@node105.yinzhengjie.org.cn ~]#
五.xtrabackup实现增量备份
1>.原主机完全备份操作
[root@node102.yinzhengjie.org.cn ~]# mkdir -pv /backup/{base,inc1,inc2} #创建备份目录结构 mkdir: created directory ‘/backup’ mkdir: created directory ‘/backup/base’ mkdir: created directory ‘/backup/inc1’ mkdir: created directory ‘/backup/inc2’ [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]# tree /backup/ /backup/ ├── base #用于完全备份 ├── inc1 #用于模拟第一次增量备份 └── inc2 #用于模拟第二次增量备份 3 directories, 0 files [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]# xtrabackup --backup --target-dir=/backup/base #完全备份现有数据库 xtrabackup: recognized server arguments: --log_bin=/data/logbin/mysql-bin --datadir=/var/lib/mysql xtrabackup: recognized client arguments: --log_bin=/data/logbin/mysql-bin --datadir=/var/lib/mysql --backup=1 --target-dir=/backup/base 191107 20:33:17 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' (using password: NO). 191107 20:33:17 version_check Connected to MySQL server 191107 20:33:17 version_check Executing a version check against the server... 191107 20:33:17 version_check Done. 191107 20:33:17 Connecting to MySQL server host: localhost, user: not set, password: not set, port: not set, socket: not set Using server version 5.5.64-MariaDB xtrabackup version 2.4.12 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 170eb8c) xtrabackup: uses posix_fadvise(). xtrabackup: cd to /var/lib/mysql xtrabackup: open files limit requested 0, set to 1048576 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 191107 20:33:17 >> log scanned up to (1610604) xtrabackup: Generating a list of tablespaces 191107 20:33:17 [01] Copying ./ibdata1 to /backup/base/ibdata1 191107 20:33:17 [01] ...done 191107 20:33:18 >> log scanned up to (1610604) 191107 20:33:18 Executing FLUSH NO_WRITE_TO_BINLOG TABLES... 191107 20:33:18 Executing FLUSH TABLES WITH READ LOCK... 191107 20:33:18 Starting to backup non-InnoDB tables and files 191107 20:33:18 [01] Copying ./mysql/tables_priv.frm to /backup/base/mysql/tables_priv.frm 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./mysql/tables_priv.MYI to /backup/base/mysql/tables_priv.MYI 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./mysql/tables_priv.MYD to /backup/base/mysql/tables_priv.MYD 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./mysql/time_zone.MYD to /backup/base/mysql/time_zone.MYD 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./mysql/host.MYD to /backup/base/mysql/host.MYD 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./mysql/ndb_binlog_index.MYI to /backup/base/mysql/ndb_binlog_index.MYI 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./mysql/plugin.frm to /backup/base/mysql/plugin.frm 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./mysql/plugin.MYD to /backup/base/mysql/plugin.MYD 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./mysql/proc.MYI to /backup/base/mysql/proc.MYI 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./mysql/procs_priv.frm to /backup/base/mysql/procs_priv.frm 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./mysql/procs_priv.MYD to /backup/base/mysql/procs_priv.MYD 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./mysql/proxies_priv.MYI to /backup/base/mysql/proxies_priv.MYI 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./mysql/servers.MYI to /backup/base/mysql/servers.MYI 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./mysql/time_zone.MYI to /backup/base/mysql/time_zone.MYI 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./mysql/time_zone_name.frm to /backup/base/mysql/time_zone_name.frm 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./mysql/time_zone_name.MYI to /backup/base/mysql/time_zone_name.MYI 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./mysql/time_zone_name.MYD to /backup/base/mysql/time_zone_name.MYD 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./mysql/time_zone_transition.frm to /backup/base/mysql/time_zone_transition.frm 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./mysql/time_zone_transition.MYI to /backup/base/mysql/time_zone_transition.MYI 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./mysql/time_zone_transition.MYD to /backup/base/mysql/time_zone_transition.MYD 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./mysql/time_zone_transition_type.frm to /backup/base/mysql/time_zone_transition_type.frm 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./mysql/time_zone_transition_type.MYI to /backup/base/mysql/time_zone_transition_type.MYI 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./mysql/time_zone_transition_type.MYD to /backup/base/mysql/time_zone_transition_type.MYD 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./mysql/user.frm to /backup/base/mysql/user.frm 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./mysql/user.MYI to /backup/base/mysql/user.MYI 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./mysql/user.MYD to /backup/base/mysql/user.MYD 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./mysql/time_zone_leap_second.frm to /backup/base/mysql/time_zone_leap_second.frm 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./mysql/time_zone_leap_second.MYI to /backup/base/mysql/time_zone_leap_second.MYI 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./mysql/general_log.frm to /backup/base/mysql/general_log.frm 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./mysql/general_log.CSM to /backup/base/mysql/general_log.CSM 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./mysql/general_log.CSV to /backup/base/mysql/general_log.CSV 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./mysql/slow_log.frm to /backup/base/mysql/slow_log.frm 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./mysql/slow_log.CSM to /backup/base/mysql/slow_log.CSM 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./mysql/slow_log.CSV to /backup/base/mysql/slow_log.CSV 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./mysql/servers.frm to /backup/base/mysql/servers.frm 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./mysql/servers.MYD to /backup/base/mysql/servers.MYD 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./mysql/time_zone_leap_second.MYD to /backup/base/mysql/time_zone_leap_second.MYD 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./mysql/columns_priv.frm to /backup/base/mysql/columns_priv.frm 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./mysql/columns_priv.MYI to /backup/base/mysql/columns_priv.MYI 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./mysql/columns_priv.MYD to /backup/base/mysql/columns_priv.MYD 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./mysql/db.frm to /backup/base/mysql/db.frm 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./mysql/db.MYI to /backup/base/mysql/db.MYI 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./mysql/db.MYD to /backup/base/mysql/db.MYD 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./mysql/event.frm to /backup/base/mysql/event.frm 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./mysql/event.MYI to /backup/base/mysql/event.MYI 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./mysql/event.MYD to /backup/base/mysql/event.MYD 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./mysql/func.frm to /backup/base/mysql/func.frm 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./mysql/func.MYI to /backup/base/mysql/func.MYI 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./mysql/func.MYD to /backup/base/mysql/func.MYD 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./mysql/help_category.frm to /backup/base/mysql/help_category.frm 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./mysql/help_category.MYI to /backup/base/mysql/help_category.MYI 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./mysql/help_category.MYD to /backup/base/mysql/help_category.MYD 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./mysql/help_keyword.frm to /backup/base/mysql/help_keyword.frm 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./mysql/help_keyword.MYI to /backup/base/mysql/help_keyword.MYI 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./mysql/help_keyword.MYD to /backup/base/mysql/help_keyword.MYD 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./mysql/help_relation.frm to /backup/base/mysql/help_relation.frm 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./mysql/help_relation.MYI to /backup/base/mysql/help_relation.MYI 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./mysql/help_relation.MYD to /backup/base/mysql/help_relation.MYD 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./mysql/help_topic.frm to /backup/base/mysql/help_topic.frm 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./mysql/help_topic.MYI to /backup/base/mysql/help_topic.MYI 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./mysql/help_topic.MYD to /backup/base/mysql/help_topic.MYD 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./mysql/host.frm to /backup/base/mysql/host.frm 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./mysql/host.MYI to /backup/base/mysql/host.MYI 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./mysql/ndb_binlog_index.frm to /backup/base/mysql/ndb_binlog_index.frm 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./mysql/ndb_binlog_index.MYD to /backup/base/mysql/ndb_binlog_index.MYD 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./mysql/plugin.MYI to /backup/base/mysql/plugin.MYI 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./mysql/proc.frm to /backup/base/mysql/proc.frm 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./mysql/proc.MYD to /backup/base/mysql/proc.MYD 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./mysql/procs_priv.MYI to /backup/base/mysql/procs_priv.MYI 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./mysql/proxies_priv.frm to /backup/base/mysql/proxies_priv.frm 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./mysql/proxies_priv.MYD to /backup/base/mysql/proxies_priv.MYD 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./mysql/time_zone.frm to /backup/base/mysql/time_zone.frm 191107 20:33:18 [01] ...done 191107 20:33:18 [00] Writing /backup/base/test/db.opt 191107 20:33:18 [00] ...done 191107 20:33:18 [01] Copying ./yinzhengjiedb/db.opt to /backup/base/yinzhengjiedb/db.opt 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./yinzhengjiedb/students.frm to /backup/base/yinzhengjiedb/students.frm 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./yinzhengjiedb/employee.frm to /backup/base/yinzhengjiedb/employee.frm 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./yinzhengjiedb/custom.frm to /backup/base/yinzhengjiedb/custom.frm 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./performance_schema/db.opt to /backup/base/performance_schema/db.opt 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./performance_schema/cond_instances.frm to /backup/base/performance_schema/cond_instances.frm 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./performance_schema/events_waits_current.frm to /backup/base/performance_schema/events_waits_current.frm 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./performance_schema/events_waits_history.frm to /backup/base/performance_schema/events_waits_history.frm 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./performance_schema/events_waits_history_long.frm to /backup/base/performance_schema/events_waits_history_long.frm 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./performance_schema/events_waits_summary_by_instance.frm to /backup/base/performance_schema/events_waits_summary_by _instance.frm191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./performance_schema/events_waits_summary_by_thread_by_event_name.frm to /backup/base/performance_schema/events_wait s_summary_by_thread_by_event_name.frm191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./performance_schema/events_waits_summary_global_by_event_name.frm to /backup/base/performance_schema/events_waits_s ummary_global_by_event_name.frm191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./performance_schema/file_instances.frm to /backup/base/performance_schema/file_instances.frm 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./performance_schema/file_summary_by_event_name.frm to /backup/base/performance_schema/file_summary_by_event_name.fr m191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./performance_schema/file_summary_by_instance.frm to /backup/base/performance_schema/file_summary_by_instance.frm 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./performance_schema/mutex_instances.frm to /backup/base/performance_schema/mutex_instances.frm 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./performance_schema/performance_timers.frm to /backup/base/performance_schema/performance_timers.frm 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./performance_schema/rwlock_instances.frm to /backup/base/performance_schema/rwlock_instances.frm 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./performance_schema/setup_consumers.frm to /backup/base/performance_schema/setup_consumers.frm 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./performance_schema/setup_instruments.frm to /backup/base/performance_schema/setup_instruments.frm 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./performance_schema/setup_timers.frm to /backup/base/performance_schema/setup_timers.frm 191107 20:33:18 [01] ...done 191107 20:33:18 [01] Copying ./performance_schema/threads.frm to /backup/base/performance_schema/threads.frm 191107 20:33:18 [01] ...done 191107 20:33:18 Finished backing up non-InnoDB tables and files 191107 20:33:18 [00] Writing /backup/base/xtrabackup_binlog_info 191107 20:33:18 [00] ...done 191107 20:33:18 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS... xtrabackup: The latest check point (for incremental): '1610604' xtrabackup: Stopping log copying thread. .191107 20:33:18 >> log scanned up to (1610604) 191107 20:33:19 Executing UNLOCK TABLES 191107 20:33:19 All tables unlocked 191107 20:33:19 Backup created in directory '/backup/base/' MySQL binlog position: filename 'mysql-bin.000006', position '245' 191107 20:33:19 [00] Writing /backup/base/backup-my.cnf 191107 20:33:19 [00] ...done 191107 20:33:19 [00] Writing /backup/base/xtrabackup_info 191107 20:33:19 [00] ...done xtrabackup: Transaction log of lsn (1610604) to (1610604) was copied. 191107 20:33:19 completed OK! [root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]# tree -L 2 /backup/ #查看备份后目录结构变化，"/backup/base"目录有相应文件生成 /backup/ ├── base │ ├── backup-my.cnf │ ├── ibdata1 │ ├── mysql │ ├── performance_schema │ ├── test │ ├── xtrabackup_binlog_info │ ├── xtrabackup_checkpoints │ ├── xtrabackup_info │ ├── xtrabackup_logfile │ └── yinzhengjiedb ├── inc1 └── inc2 directories, 6 files [root@node102.yinzhengjie.org.cn ~]#
2>.进行完全备份后，修改现有数据库数据并实现增量备份
[root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]# mysql Welcome to the MariaDB monitor. Commands end with ; or g. Your MariaDB connection id is 15 Server version: 5.5.64-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. MariaDB [(none)]> MariaDB [(none)]> USE yinzhengjiedb 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 [yinzhengjiedb]> MariaDB [yinzhengjiedb]> SHOW TABLES; +-------------------------+ | Tables_in_yinzhengjiedb | +-------------------------+ | custom | | employee | | students | +-------------------------+ 3 rows in set (0.00 sec) MariaDB [yinzhengjiedb]> MariaDB [yinzhengjiedb]> MariaDB [yinzhengjiedb]> SELECT * FROM students; +----+---------------------+------+------+--------+-----------+ | id | name | sex | age | mobile | address | +----+---------------------+------+------+--------+-----------+ | 1 | Jason Yin | boy | 18 | 10000 | beijing | | 2 | Jay | boy | 40 | 10086 | Taiwan | | 3 | yinzhengjie | boy | 27 | NULL | shanxi | | 4 | Gloria Tang Tsz-Kei | girl | 28 | NULL | Hong Kong | +----+---------------------+------+------+--------+-----------+ 4 rows in set (0.00 sec) MariaDB [yinzhengjiedb]> MariaDB [yinzhengjiedb]> DELETE FROM students WHERE id < 2; Query OK, 1 row affected (0.01 sec) MariaDB [yinzhengjiedb]> MariaDB [yinzhengjiedb]> SELECT * FROM students; +----+---------------------+------+------+--------+-----------+ | id | name | sex | age | mobile | address | +----+---------------------+------+------+--------+-----------+ | 2 | Jay | boy | 40 | 10086 | Taiwan | | 3 | yinzhengjie | boy | 27 | NULL | shanxi | | 4 | Gloria Tang Tsz-Kei | girl | 28 | NULL | Hong Kong | +----+---------------------+------+------+--------+-----------+ 3 rows in set (0.00 sec) MariaDB [yinzhengjiedb]> MariaDB [yinzhengjiedb]> MariaDB [yinzhengjiedb]> DELETE FROM students WHERE id < 3; Query OK, 1 row affected (0.00 sec) MariaDB [yinzhengjiedb]> MariaDB [yinzhengjiedb]> SELECT * FROM students; +----+---------------------+------+------+--------+-----------+ | id | name | sex | age | mobile | address | +----+---------------------+------+------+--------+-----------+ | 3 | yinzhengjie | boy | 27 | NULL | shanxi | | 4 | Gloria Tang Tsz-Kei | girl | 28 | NULL | Hong Kong | +----+---------------------+------+------+--------+-----------+ 2 rows in set (0.00 sec)
[root@node102.yinzhengjie.org.cn ~]# xtrabackup --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/base #第一次增量备份 xtrabackup: recognized server arguments: --log_bin=/data/logbin/mysql-bin --datadir=/var/lib/mysql xtrabackup: recognized client arguments: --log_bin=/data/logbin/mysql-bin --datadir=/var/lib/mysql --backup=1 --target-dir=/backup/inc1 --increme ntal-basedir=/backup/base 191107 20:44:25 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' (using password: NO). 191107 20:44:25 version_check Connected to MySQL server 191107 20:44:25 version_check Executing a version check against the server... 191107 20:44:25 version_check Done. 191107 20:44:25 Connecting to MySQL server host: localhost, user: not set, password: not set, port: not set, socket: not set Using server version 5.5.64-MariaDB xtrabackup version 2.4.12 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 170eb8c) incremental backup from 1610604 is enabled. xtrabackup: uses posix_fadvise(). xtrabackup: cd to /var/lib/mysql xtrabackup: open files limit requested 0, set to 1048576 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 191107 20:44:25 >> log scanned up to (1612975) xtrabackup: Generating a list of tablespaces xtrabackup: using the full scan for incremental backup 191107 20:44:25 [01] Copying ./ibdata1 to /backup/inc1/ibdata1.delta 191107 20:44:26 [01] ...done 191107 20:44:26 >> log scanned up to (1612975) 191107 20:44:26 Executing FLUSH NO_WRITE_TO_BINLOG TABLES... 191107 20:44:26 Executing FLUSH TABLES WITH READ LOCK... 191107 20:44:26 Starting to backup non-InnoDB tables and files 191107 20:44:26 [01] Copying ./mysql/tables_priv.frm to /backup/inc1/mysql/tables_priv.frm 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./mysql/tables_priv.MYI to /backup/inc1/mysql/tables_priv.MYI 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./mysql/tables_priv.MYD to /backup/inc1/mysql/tables_priv.MYD 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./mysql/time_zone.MYD to /backup/inc1/mysql/time_zone.MYD 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./mysql/host.MYD to /backup/inc1/mysql/host.MYD 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./mysql/ndb_binlog_index.MYI to /backup/inc1/mysql/ndb_binlog_index.MYI 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./mysql/plugin.frm to /backup/inc1/mysql/plugin.frm 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./mysql/plugin.MYD to /backup/inc1/mysql/plugin.MYD 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./mysql/proc.MYI to /backup/inc1/mysql/proc.MYI 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./mysql/procs_priv.frm to /backup/inc1/mysql/procs_priv.frm 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./mysql/procs_priv.MYD to /backup/inc1/mysql/procs_priv.MYD 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./mysql/proxies_priv.MYI to /backup/inc1/mysql/proxies_priv.MYI 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./mysql/servers.MYI to /backup/inc1/mysql/servers.MYI 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./mysql/time_zone.MYI to /backup/inc1/mysql/time_zone.MYI 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./mysql/time_zone_name.frm to /backup/inc1/mysql/time_zone_name.frm 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./mysql/time_zone_name.MYI to /backup/inc1/mysql/time_zone_name.MYI 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./mysql/time_zone_name.MYD to /backup/inc1/mysql/time_zone_name.MYD 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./mysql/time_zone_transition.frm to /backup/inc1/mysql/time_zone_transition.frm 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./mysql/time_zone_transition.MYI to /backup/inc1/mysql/time_zone_transition.MYI 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./mysql/time_zone_transition.MYD to /backup/inc1/mysql/time_zone_transition.MYD 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./mysql/time_zone_transition_type.frm to /backup/inc1/mysql/time_zone_transition_type.frm 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./mysql/time_zone_transition_type.MYI to /backup/inc1/mysql/time_zone_transition_type.MYI 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./mysql/time_zone_transition_type.MYD to /backup/inc1/mysql/time_zone_transition_type.MYD 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./mysql/user.frm to /backup/inc1/mysql/user.frm 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./mysql/user.MYI to /backup/inc1/mysql/user.MYI 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./mysql/user.MYD to /backup/inc1/mysql/user.MYD 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./mysql/time_zone_leap_second.frm to /backup/inc1/mysql/time_zone_leap_second.frm 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./mysql/time_zone_leap_second.MYI to /backup/inc1/mysql/time_zone_leap_second.MYI 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./mysql/general_log.frm to /backup/inc1/mysql/general_log.frm 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./mysql/general_log.CSM to /backup/inc1/mysql/general_log.CSM 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./mysql/general_log.CSV to /backup/inc1/mysql/general_log.CSV 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./mysql/slow_log.frm to /backup/inc1/mysql/slow_log.frm 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./mysql/slow_log.CSM to /backup/inc1/mysql/slow_log.CSM 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./mysql/slow_log.CSV to /backup/inc1/mysql/slow_log.CSV 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./mysql/servers.frm to /backup/inc1/mysql/servers.frm 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./mysql/servers.MYD to /backup/inc1/mysql/servers.MYD 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./mysql/time_zone_leap_second.MYD to /backup/inc1/mysql/time_zone_leap_second.MYD 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./mysql/columns_priv.frm to /backup/inc1/mysql/columns_priv.frm 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./mysql/columns_priv.MYI to /backup/inc1/mysql/columns_priv.MYI 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./mysql/columns_priv.MYD to /backup/inc1/mysql/columns_priv.MYD 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./mysql/db.frm to /backup/inc1/mysql/db.frm 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./mysql/db.MYI to /backup/inc1/mysql/db.MYI 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./mysql/db.MYD to /backup/inc1/mysql/db.MYD 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./mysql/event.frm to /backup/inc1/mysql/event.frm 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./mysql/event.MYI to /backup/inc1/mysql/event.MYI 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./mysql/event.MYD to /backup/inc1/mysql/event.MYD 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./mysql/func.frm to /backup/inc1/mysql/func.frm 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./mysql/func.MYI to /backup/inc1/mysql/func.MYI 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./mysql/func.MYD to /backup/inc1/mysql/func.MYD 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./mysql/help_category.frm to /backup/inc1/mysql/help_category.frm 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./mysql/help_category.MYI to /backup/inc1/mysql/help_category.MYI 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./mysql/help_category.MYD to /backup/inc1/mysql/help_category.MYD 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./mysql/help_keyword.frm to /backup/inc1/mysql/help_keyword.frm 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./mysql/help_keyword.MYI to /backup/inc1/mysql/help_keyword.MYI 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./mysql/help_keyword.MYD to /backup/inc1/mysql/help_keyword.MYD 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./mysql/help_relation.frm to /backup/inc1/mysql/help_relation.frm 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./mysql/help_relation.MYI to /backup/inc1/mysql/help_relation.MYI 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./mysql/help_relation.MYD to /backup/inc1/mysql/help_relation.MYD 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./mysql/help_topic.frm to /backup/inc1/mysql/help_topic.frm 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./mysql/help_topic.MYI to /backup/inc1/mysql/help_topic.MYI 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./mysql/help_topic.MYD to /backup/inc1/mysql/help_topic.MYD 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./mysql/host.frm to /backup/inc1/mysql/host.frm 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./mysql/host.MYI to /backup/inc1/mysql/host.MYI 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./mysql/ndb_binlog_index.frm to /backup/inc1/mysql/ndb_binlog_index.frm 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./mysql/ndb_binlog_index.MYD to /backup/inc1/mysql/ndb_binlog_index.MYD 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./mysql/plugin.MYI to /backup/inc1/mysql/plugin.MYI 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./mysql/proc.frm to /backup/inc1/mysql/proc.frm 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./mysql/proc.MYD to /backup/inc1/mysql/proc.MYD 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./mysql/procs_priv.MYI to /backup/inc1/mysql/procs_priv.MYI 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./mysql/proxies_priv.frm to /backup/inc1/mysql/proxies_priv.frm 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./mysql/proxies_priv.MYD to /backup/inc1/mysql/proxies_priv.MYD 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./mysql/time_zone.frm to /backup/inc1/mysql/time_zone.frm 191107 20:44:26 [01] ...done 191107 20:44:26 [00] Writing /backup/inc1/test/db.opt 191107 20:44:26 [00] ...done 191107 20:44:26 [01] Copying ./yinzhengjiedb/db.opt to /backup/inc1/yinzhengjiedb/db.opt 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./yinzhengjiedb/students.frm to /backup/inc1/yinzhengjiedb/students.frm 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./yinzhengjiedb/employee.frm to /backup/inc1/yinzhengjiedb/employee.frm 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./yinzhengjiedb/custom.frm to /backup/inc1/yinzhengjiedb/custom.frm 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./performance_schema/db.opt to /backup/inc1/performance_schema/db.opt 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./performance_schema/cond_instances.frm to /backup/inc1/performance_schema/cond_instances.frm 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./performance_schema/events_waits_current.frm to /backup/inc1/performance_schema/events_waits_current.frm 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./performance_schema/events_waits_history.frm to /backup/inc1/performance_schema/events_waits_history.frm 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./performance_schema/events_waits_history_long.frm to /backup/inc1/performance_schema/events_waits_history_long.frm 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./performance_schema/events_waits_summary_by_instance.frm to /backup/inc1/performance_schema/events_waits_summary_by _instance.frm191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./performance_schema/events_waits_summary_by_thread_by_event_name.frm to /backup/inc1/performance_schema/events_wait s_summary_by_thread_by_event_name.frm191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./performance_schema/events_waits_summary_global_by_event_name.frm to /backup/inc1/performance_schema/events_waits_s ummary_global_by_event_name.frm191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./performance_schema/file_instances.frm to /backup/inc1/performance_schema/file_instances.frm 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./performance_schema/file_summary_by_event_name.frm to /backup/inc1/performance_schema/file_summary_by_event_name.fr m191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./performance_schema/file_summary_by_instance.frm to /backup/inc1/performance_schema/file_summary_by_instance.frm 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./performance_schema/mutex_instances.frm to /backup/inc1/performance_schema/mutex_instances.frm 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./performance_schema/performance_timers.frm to /backup/inc1/performance_schema/performance_timers.frm 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./performance_schema/rwlock_instances.frm to /backup/inc1/performance_schema/rwlock_instances.frm 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./performance_schema/setup_consumers.frm to /backup/inc1/performance_schema/setup_consumers.frm 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./performance_schema/setup_instruments.frm to /backup/inc1/performance_schema/setup_instruments.frm 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./performance_schema/setup_timers.frm to /backup/inc1/performance_schema/setup_timers.frm 191107 20:44:26 [01] ...done 191107 20:44:26 [01] Copying ./performance_schema/threads.frm to /backup/inc1/performance_schema/threads.frm 191107 20:44:26 [01] ...done 191107 20:44:26 Finished backing up non-InnoDB tables and files 191107 20:44:26 [00] Writing /backup/inc1/xtrabackup_binlog_info 191107 20:44:26 [00] ...done 191107 20:44:26 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS... xtrabackup: The latest check point (for incremental): '1612975' xtrabackup: Stopping log copying thread. .191107 20:44:26 >> log scanned up to (1612975) 191107 20:44:26 Executing UNLOCK TABLES 191107 20:44:26 All tables unlocked 191107 20:44:26 Backup created in directory '/backup/inc1/' MySQL binlog position: filename 'mysql-bin.000006', position '663' 191107 20:44:26 [00] Writing /backup/inc1/backup-my.cnf 191107 20:44:26 [00] ...done 191107 20:44:26 [00] Writing /backup/inc1/xtrabackup_info 191107 20:44:26 [00] ...done xtrabackup: Transaction log of lsn (1612975) to (1612975) was copied. 191107 20:44:26 completed OK! [root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]# tree -L 2 /backup/ #查看增量备份后目录结构变化 /backup/ ├── base │ ├── backup-my.cnf │ ├── ibdata1 │ ├── mysql │ ├── performance_schema │ ├── test │ ├── xtrabackup_binlog_info │ ├── xtrabackup_checkpoints │ ├── xtrabackup_info │ ├── xtrabackup_logfile │ └── yinzhengjiedb ├── inc1 │ ├── backup-my.cnf │ ├── ibdata1.delta │ ├── ibdata1.meta │ ├── mysql │ ├── performance_schema │ ├── test │ ├── xtrabackup_binlog_info │ ├── xtrabackup_checkpoints │ ├── xtrabackup_info │ ├── xtrabackup_logfile │ └── yinzhengjiedb └── inc2 11 directories, 13 files [root@node102.yinzhengjie.org.cn ~]#
3>.继续修改数据库的内容，并做第二次增量备份
MariaDB [yinzhengjiedb]> SHOW TABLES; +-------------------------+ | Tables_in_yinzhengjiedb | +-------------------------+ | custom | | employee | | students | +-------------------------+ rows in set (0.00 sec) MariaDB [yinzhengjiedb]> MariaDB [yinzhengjiedb]> MariaDB [yinzhengjiedb]> SELECT * FROM custom; Empty set (0.01 sec) MariaDB [yinzhengjiedb]> MariaDB [yinzhengjiedb]> SELECT * FROM employee; +----+---------------------+------+------+--------+-----------+ | id | name | sex | age | mobile | address | +----+---------------------+------+------+--------+-----------+ | 1 | Jason Yin | boy | 18 | 10000 | beijing | | 2 | Jay | boy | 40 | 10086 | Taiwan | | 3 | yinzhengjie | boy | 27 | NULL | shanxi | | 4 | Gloria Tang Tsz-Kei | girl | 28 | NULL | Hong Kong | +----+---------------------+------+------+--------+-----------+ rows in set (0.00 sec) MariaDB [yinzhengjiedb]> MariaDB [yinzhengjiedb]> INSERT INTO custom SELECT * FROM employee; Query OK, 4 rows affected, 1 warning (0.01 sec) Records: 4 Duplicates: 0 Warnings: 1 MariaDB [yinzhengjiedb]> MariaDB [yinzhengjiedb]> SELECT * FROM custom; +----+---------------------+------+------+--------+-----------+ | id | name | sex | age | mobile | address | +----+---------------------+------+------+--------+-----------+ | 1 | Jason Yin | boy | 18 | 10000 | beijing | | 2 | Jay | boy | 40 | 10086 | Taiwan | | 3 | yinzhengjie | boy | 27 | NULL | shanxi | | 4 | Gloria Tang Tsz-Kei | girl | 28 | NULL | Hong Kong | +----+---------------------+------+------+--------+-----------+ rows in set (0.00 sec) MariaDB [yinzhengjiedb]> MariaDB [yinzhengjiedb]> DROP TABLE students; Query OK, 0 rows affected (0.01 sec) MariaDB [yinzhengjiedb]> MariaDB [yinzhengjiedb]> SHOW TABLES; +-------------------------+ | Tables_in_yinzhengjiedb | +-------------------------+ | custom | | employee | +-------------------------+ rows in set (0.00 sec) MariaDB [yinzhengjiedb]> MariaDB [yinzhengjiedb]> CREATE DATABASE devops; Query OK, 1 row affected (0.00 sec) MariaDB [yinzhengjiedb]> MariaDB [yinzhengjiedb]> USE devops 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 [devops]> MariaDB [devops]> SHOW TABLES; +------------------+ | Tables_in_devops | +------------------+ | students | +------------------+ 1 row in set (0.00 sec) MariaDB [devops]> MariaDB [devops]> SELECT * FROM students; +----+---------------------+------+------+--------+-----------+ | id | name | sex | age | mobile | address | +----+---------------------+------+------+--------+-----------+ | 1 | Jason Yin | boy | 18 | 10000 | beijing | | 2 | Jay | boy | 40 | 10086 | Taiwan | | 3 | yinzhengjie | boy | 27 | NULL | shanxi | | 4 | Gloria Tang Tsz-Kei | girl | 28 | NULL | Hong Kong | +----+---------------------+------+------+--------+-----------+ 4 rows in set (0.00 sec) MariaDB [devops]> MariaDB [devops]>
[root@node102.yinzhengjie.org.cn ~]# xtrabackup --backup --target-dir=/backup/inc2 --incremental-basedir=/backup/inc1 xtrabackup: recognized server arguments: --log_bin=/data/logbin/mysql-bin --datadir=/var/lib/mysql xtrabackup: recognized client arguments: --log_bin=/data/logbin/mysql-bin --datadir=/var/lib/mysql --backup=1 --target-dir=/backup/inc2 --increme ntal-basedir=/backup/inc1 191107 20:58:26 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' (using password: NO). 191107 20:58:26 version_check Connected to MySQL server 191107 20:58:26 version_check Executing a version check against the server... 191107 20:58:26 version_check Done. 191107 20:58:26 Connecting to MySQL server host: localhost, user: not set, password: not set, port: not set, socket: not set Using server version 5.5.64-MariaDB xtrabackup version 2.4.12 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 170eb8c) incremental backup from 1612975 is enabled. xtrabackup: uses posix_fadvise(). xtrabackup: cd to /var/lib/mysql xtrabackup: open files limit requested 0, set to 1048576 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 191107 20:58:26 >> log scanned up to (1628213) xtrabackup: Generating a list of tablespaces xtrabackup: using the full scan for incremental backup 191107 20:58:26 [01] Copying ./ibdata1 to /backup/inc2/ibdata1.delta 191107 20:58:26 [01] ...done 191107 20:58:27 >> log scanned up to (1628213) 191107 20:58:27 Executing FLUSH NO_WRITE_TO_BINLOG TABLES... 191107 20:58:27 Executing FLUSH TABLES WITH READ LOCK... 191107 20:58:27 Starting to backup non-InnoDB tables and files 191107 20:58:27 [01] Copying ./mysql/tables_priv.frm to /backup/inc2/mysql/tables_priv.frm 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./mysql/tables_priv.MYI to /backup/inc2/mysql/tables_priv.MYI 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./mysql/tables_priv.MYD to /backup/inc2/mysql/tables_priv.MYD 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./mysql/time_zone.MYD to /backup/inc2/mysql/time_zone.MYD 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./mysql/host.MYD to /backup/inc2/mysql/host.MYD 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./mysql/ndb_binlog_index.MYI to /backup/inc2/mysql/ndb_binlog_index.MYI 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./mysql/plugin.frm to /backup/inc2/mysql/plugin.frm 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./mysql/plugin.MYD to /backup/inc2/mysql/plugin.MYD 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./mysql/proc.MYI to /backup/inc2/mysql/proc.MYI 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./mysql/procs_priv.frm to /backup/inc2/mysql/procs_priv.frm 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./mysql/procs_priv.MYD to /backup/inc2/mysql/procs_priv.MYD 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./mysql/proxies_priv.MYI to /backup/inc2/mysql/proxies_priv.MYI 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./mysql/servers.MYI to /backup/inc2/mysql/servers.MYI 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./mysql/time_zone.MYI to /backup/inc2/mysql/time_zone.MYI 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./mysql/time_zone_name.frm to /backup/inc2/mysql/time_zone_name.frm 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./mysql/time_zone_name.MYI to /backup/inc2/mysql/time_zone_name.MYI 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./mysql/time_zone_name.MYD to /backup/inc2/mysql/time_zone_name.MYD 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./mysql/time_zone_transition.frm to /backup/inc2/mysql/time_zone_transition.frm 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./mysql/time_zone_transition.MYI to /backup/inc2/mysql/time_zone_transition.MYI 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./mysql/time_zone_transition.MYD to /backup/inc2/mysql/time_zone_transition.MYD 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./mysql/time_zone_transition_type.frm to /backup/inc2/mysql/time_zone_transition_type.frm 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./mysql/time_zone_transition_type.MYI to /backup/inc2/mysql/time_zone_transition_type.MYI 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./mysql/time_zone_transition_type.MYD to /backup/inc2/mysql/time_zone_transition_type.MYD 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./mysql/user.frm to /backup/inc2/mysql/user.frm 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./mysql/user.MYI to /backup/inc2/mysql/user.MYI 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./mysql/user.MYD to /backup/inc2/mysql/user.MYD 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./mysql/time_zone_leap_second.frm to /backup/inc2/mysql/time_zone_leap_second.frm 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./mysql/time_zone_leap_second.MYI to /backup/inc2/mysql/time_zone_leap_second.MYI 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./mysql/general_log.frm to /backup/inc2/mysql/general_log.frm 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./mysql/general_log.CSM to /backup/inc2/mysql/general_log.CSM 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./mysql/general_log.CSV to /backup/inc2/mysql/general_log.CSV 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./mysql/slow_log.frm to /backup/inc2/mysql/slow_log.frm 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./mysql/slow_log.CSM to /backup/inc2/mysql/slow_log.CSM 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./mysql/slow_log.CSV to /backup/inc2/mysql/slow_log.CSV 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./mysql/servers.frm to /backup/inc2/mysql/servers.frm 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./mysql/servers.MYD to /backup/inc2/mysql/servers.MYD 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./mysql/time_zone_leap_second.MYD to /backup/inc2/mysql/time_zone_leap_second.MYD 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./mysql/columns_priv.frm to /backup/inc2/mysql/columns_priv.frm 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./mysql/columns_priv.MYI to /backup/inc2/mysql/columns_priv.MYI 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./mysql/columns_priv.MYD to /backup/inc2/mysql/columns_priv.MYD 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./mysql/db.frm to /backup/inc2/mysql/db.frm 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./mysql/db.MYI to /backup/inc2/mysql/db.MYI 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./mysql/db.MYD to /backup/inc2/mysql/db.MYD 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./mysql/event.frm to /backup/inc2/mysql/event.frm 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./mysql/event.MYI to /backup/inc2/mysql/event.MYI 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./mysql/event.MYD to /backup/inc2/mysql/event.MYD 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./mysql/func.frm to /backup/inc2/mysql/func.frm 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./mysql/func.MYI to /backup/inc2/mysql/func.MYI 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./mysql/func.MYD to /backup/inc2/mysql/func.MYD 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./mysql/help_category.frm to /backup/inc2/mysql/help_category.frm 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./mysql/help_category.MYI to /backup/inc2/mysql/help_category.MYI 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./mysql/help_category.MYD to /backup/inc2/mysql/help_category.MYD 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./mysql/help_keyword.frm to /backup/inc2/mysql/help_keyword.frm 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./mysql/help_keyword.MYI to /backup/inc2/mysql/help_keyword.MYI 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./mysql/help_keyword.MYD to /backup/inc2/mysql/help_keyword.MYD 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./mysql/help_relation.frm to /backup/inc2/mysql/help_relation.frm 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./mysql/help_relation.MYI to /backup/inc2/mysql/help_relation.MYI 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./mysql/help_relation.MYD to /backup/inc2/mysql/help_relation.MYD 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./mysql/help_topic.frm to /backup/inc2/mysql/help_topic.frm 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./mysql/help_topic.MYI to /backup/inc2/mysql/help_topic.MYI 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./mysql/help_topic.MYD to /backup/inc2/mysql/help_topic.MYD 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./mysql/host.frm to /backup/inc2/mysql/host.frm 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./mysql/host.MYI to /backup/inc2/mysql/host.MYI 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./mysql/ndb_binlog_index.frm to /backup/inc2/mysql/ndb_binlog_index.frm 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./mysql/ndb_binlog_index.MYD to /backup/inc2/mysql/ndb_binlog_index.MYD 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./mysql/plugin.MYI to /backup/inc2/mysql/plugin.MYI 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./mysql/proc.frm to /backup/inc2/mysql/proc.frm 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./mysql/proc.MYD to /backup/inc2/mysql/proc.MYD 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./mysql/procs_priv.MYI to /backup/inc2/mysql/procs_priv.MYI 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./mysql/proxies_priv.frm to /backup/inc2/mysql/proxies_priv.frm 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./mysql/proxies_priv.MYD to /backup/inc2/mysql/proxies_priv.MYD 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./mysql/time_zone.frm to /backup/inc2/mysql/time_zone.frm 191107 20:58:27 [01] ...done 191107 20:58:27 [00] Writing /backup/inc2/test/db.opt 191107 20:58:27 [00] ...done 191107 20:58:27 [01] Copying ./yinzhengjiedb/db.opt to /backup/inc2/yinzhengjiedb/db.opt 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./yinzhengjiedb/employee.frm to /backup/inc2/yinzhengjiedb/employee.frm 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./yinzhengjiedb/custom.frm to /backup/inc2/yinzhengjiedb/custom.frm 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./performance_schema/db.opt to /backup/inc2/performance_schema/db.opt 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./performance_schema/cond_instances.frm to /backup/inc2/performance_schema/cond_instances.frm 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./performance_schema/events_waits_current.frm to /backup/inc2/performance_schema/events_waits_current.frm 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./performance_schema/events_waits_history.frm to /backup/inc2/performance_schema/events_waits_history.frm 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./performance_schema/events_waits_history_long.frm to /backup/inc2/performance_schema/events_waits_history_long.frm 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./performance_schema/events_waits_summary_by_instance.frm to /backup/inc2/performance_schema/events_waits_summary_by _instance.frm191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./performance_schema/events_waits_summary_by_thread_by_event_name.frm to /backup/inc2/performance_schema/events_wait s_summary_by_thread_by_event_name.frm191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./performance_schema/events_waits_summary_global_by_event_name.frm to /backup/inc2/performance_schema/events_waits_s ummary_global_by_event_name.frm191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./performance_schema/file_instances.frm to /backup/inc2/performance_schema/file_instances.frm 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./performance_schema/file_summary_by_event_name.frm to /backup/inc2/performance_schema/file_summary_by_event_name.fr m191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./performance_schema/file_summary_by_instance.frm to /backup/inc2/performance_schema/file_summary_by_instance.frm 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./performance_schema/mutex_instances.frm to /backup/inc2/performance_schema/mutex_instances.frm 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./performance_schema/performance_timers.frm to /backup/inc2/performance_schema/performance_timers.frm 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./performance_schema/rwlock_instances.frm to /backup/inc2/performance_schema/rwlock_instances.frm 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./performance_schema/setup_consumers.frm to /backup/inc2/performance_schema/setup_consumers.frm 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./performance_schema/setup_instruments.frm to /backup/inc2/performance_schema/setup_instruments.frm 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./performance_schema/setup_timers.frm to /backup/inc2/performance_schema/setup_timers.frm 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./performance_schema/threads.frm to /backup/inc2/performance_schema/threads.frm 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./devops/db.opt to /backup/inc2/devops/db.opt 191107 20:58:27 [01] ...done 191107 20:58:27 [01] Copying ./devops/students.frm to /backup/inc2/devops/students.frm 191107 20:58:27 [01] ...done 191107 20:58:27 Finished backing up non-InnoDB tables and files 191107 20:58:27 [00] Writing /backup/inc2/xtrabackup_binlog_info 191107 20:58:27 [00] ...done 191107 20:58:27 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS... xtrabackup: The latest check point (for incremental): '1628213' xtrabackup: Stopping log copying thread. .191107 20:58:27 >> log scanned up to (1628213) 191107 20:58:27 Executing UNLOCK TABLES 191107 20:58:27 All tables unlocked 191107 20:58:27 Backup created in directory '/backup/inc2/' MySQL binlog position: filename 'mysql-bin.000006', position '1440' 191107 20:58:27 [00] Writing /backup/inc2/backup-my.cnf 191107 20:58:27 [00] ...done 191107 20:58:27 [00] Writing /backup/inc2/xtrabackup_info 191107 20:58:27 [00] ...done xtrabackup: Transaction log of lsn (1628213) to (1628213) was copied. 191107 20:58:28 completed OK! [root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]# tree -L 2 /backup/ #再次查看增量备份后目录结构变化 /backup/ ├── base │ ├── backup-my.cnf │ ├── ibdata1 │ ├── mysql │ ├── performance_schema │ ├── test │ ├── xtrabackup_binlog_info │ ├── xtrabackup_checkpoints │ ├── xtrabackup_info │ ├── xtrabackup_logfile │ └── yinzhengjiedb ├── inc1 │ ├── backup-my.cnf │ ├── ibdata1.delta │ ├── ibdata1.meta │ ├── mysql │ ├── performance_schema │ ├── test │ ├── xtrabackup_binlog_info │ ├── xtrabackup_checkpoints │ ├── xtrabackup_info │ ├── xtrabackup_logfile │ └── yinzhengjiedb └── inc2 ├── backup-my.cnf ├── devops ├── ibdata1.delta ├── ibdata1.meta ├── mysql ├── performance_schema ├── test ├── xtrabackup_binlog_info ├── xtrabackup_checkpoints ├── xtrabackup_info ├── xtrabackup_logfile └── yinzhengjiedb 16 directories, 20 files [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]# du -sh /backup/ #备份目录的总大小 23M /backup/ [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]# du -sh /backup/* 20M /backup/base #完全备份的数据 1.5M /backup/inc1 #第一次增量备份的大小 2.0M /backup/inc2 #第二次增量备份的大小 [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]#
4>.将原节点备份数据拷贝到待还原数据库节点
[root@node102.yinzhengjie.org.cn ~]# tree -L 1 /backup/ /backup/ ├── base ├── inc1 └── inc2 3 directories, 0 files [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]# cat /etc/hosts 127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 ::1 localhost localhost.localdomain localhost6 localhost6.localdomain6 172.30.1.101 node101.yinzhengjie.org.cn 172.30.1.102 node102.yinzhengjie.org.cn 172.30.1.103 node103.yinzhengjie.org.cn 172.30.1.104 node104.yinzhengjie.org.cn 172.30.1.105 node105.yinzhengjie.org.cn 172.30.1.106 node106.yinzhengjie.org.cn 172.30.1.107 node107.yinzhengjie.org.cn 172.30.1.108 node108.yinzhengjie.org.cn [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]# scp -r /backup/ node103.yinzhengjie.org.cn:/data root@node103.yinzhengjie.org.cn's password: xtrabackup_logfile 100% 2560 2.9MB/s 00:00 ibdata1 100% 18MB 94.9MB/s 00:00 tables_priv.frm 100% 8955 10.7MB/s 00:00 tables_priv.MYI 100% 4096 5.0MB/s 00:00 tables_priv.MYD 100% 0 0.0KB/s 00:00 time_zone.MYD 100% 0 0.0KB/s 00:00 host.MYD 100% 0 0.0KB/s 00:00 ndb_binlog_index.MYI 100% 1024 1.7MB/s 00:00 plugin.frm 100% 8586 12.2MB/s 00:00 plugin.MYD 100% 0 0.0KB/s 00:00 proc.MYI 100% 4096 6.4MB/s 00:00 procs_priv.frm 100% 8875 13.0MB/s 00:00 procs_priv.MYD 100% 0 0.0KB/s 00:00 proxies_priv.MYI 100% 5120 7.4MB/s 00:00 servers.MYI 100% 1024 1.9MB/s 00:00 time_zone.MYI 100% 1024 1.8MB/s 00:00 time_zone_name.frm 100% 8606 12.9MB/s 00:00 time_zone_name.MYI 100% 1024 2.0MB/s 00:00 time_zone_name.MYD 100% 0 0.0KB/s 00:00 time_zone_transition.frm 100% 8686 12.2MB/s 00:00 time_zone_transition.MYI 100% 1024 1.0MB/s 00:00 time_zone_transition.MYD 100% 0 0.0KB/s 00:00 time_zone_transition_type.frm 100% 8748 11.1MB/s 00:00 time_zone_transition_type.MYI 100% 1024 1.9MB/s 00:00 time_zone_transition_type.MYD 100% 0 0.0KB/s 00:00 user.frm 100% 10KB 13.9MB/s 00:00 user.MYI 100% 2048 2.9MB/s 00:00 user.MYD 100% 272 381.1KB/s 00:00 time_zone_leap_second.frm 100% 8624 11.8MB/s 00:00 time_zone_leap_second.MYI 100% 1024 1.7MB/s 00:00 general_log.frm 100% 8776 13.0MB/s 00:00 general_log.CSM 100% 35 63.5KB/s 00:00 general_log.CSV 100% 0 0.0KB/s 00:00 slow_log.frm 100% 8976 10.9MB/s 00:00 slow_log.CSM 100% 35 66.2KB/s 00:00 slow_log.CSV 100% 0 0.0KB/s 00:00 servers.frm 100% 8838 9.8MB/s 00:00 servers.MYD 100% 0 0.0KB/s 00:00 time_zone_leap_second.MYD 100% 0 0.0KB/s 00:00 columns_priv.frm 100% 8820 12.6MB/s 00:00 columns_priv.MYI 100% 4096 5.5MB/s 00:00 columns_priv.MYD 100% 0 0.0KB/s 00:00 db.frm 100% 9582 14.3MB/s 00:00 db.MYI 100% 5120 8.6MB/s 00:00 db.MYD 100% 880 1.1MB/s 00:00 event.frm 100% 10KB 14.1MB/s 00:00 event.MYI 100% 2048 3.3MB/s 00:00 event.MYD 100% 0 0.0KB/s 00:00 func.frm 100% 8665 12.9MB/s 00:00 func.MYI 100% 1024 1.6MB/s 00:00 func.MYD 100% 0 0.0KB/s 00:00 help_category.frm 100% 8700 11.2MB/s 00:00 help_category.MYI 100% 3072 4.5MB/s 00:00 help_category.MYD 100% 1092 2.0MB/s 00:00 help_keyword.frm 100% 8612 12.3MB/s 00:00 help_keyword.MYI 100% 16KB 21.9MB/s 00:00 help_keyword.MYD 100% 89KB 47.0MB/s 00:00 help_relation.frm 100% 8630 11.0MB/s 00:00 help_relation.MYI 100% 19KB 20.1MB/s 00:00 help_relation.MYD 100% 9252 12.5MB/s 00:00 help_topic.frm 100% 8770 12.9MB/s 00:00 help_topic.MYI 100% 20KB 26.3MB/s 00:00 help_topic.MYD 100% 440KB 80.0MB/s 00:00 host.frm 100% 9510 10.1MB/s 00:00 host.MYI 100% 2048 1.6MB/s 00:00 ndb_binlog_index.frm 100% 8778 9.9MB/s 00:00 ndb_binlog_index.MYD 100% 0 0.0KB/s 00:00 plugin.MYI 100% 1024 1.5MB/s 00:00 proc.frm 100% 10KB 12.9MB/s 00:00 proc.MYD 100% 296 438.3KB/s 00:00 procs_priv.MYI 100% 4096 3.4MB/s 00:00 proxies_priv.frm 100% 8800 10.2MB/s 00:00 proxies_priv.MYD 100% 1386 2.1MB/s 00:00 time_zone.frm 100% 8636 11.1MB/s 00:00 db.opt 100% 0 0.0KB/s 00:00 db.opt 100% 67 88.9KB/s 00:00 students.frm 100% 8723 9.1MB/s 00:00 employee.frm 100% 8723 10.9MB/s 00:00 custom.frm 100% 8723 12.3MB/s 00:00 db.opt 100% 61 67.8KB/s 00:00 cond_instances.frm 100% 8624 10.8MB/s 00:00 events_waits_current.frm 100% 9220 9.2MB/s 00:00 events_waits_history.frm 100% 9220 8.8MB/s 00:00 events_waits_history_long.frm 100% 9220 8.6MB/s 00:00 events_waits_summary_by_instance.frm 100% 8878 8.0MB/s 00:00 events_waits_summary_by_thread_by_event_name.frm 100% 8854 7.7MB/s 00:00 events_waits_summary_global_by_event_name.frm 100% 8814 9.7MB/s 00:00 file_instances.frm 100% 8654 9.7MB/s 00:00 file_summary_by_event_name.frm 100% 8800 6.2MB/s 00:00 file_summary_by_instance.frm 100% 8840 8.6MB/s 00:00 mutex_instances.frm 100% 8684 9.0MB/s 00:00 performance_timers.frm 100% 8776 9.5MB/s 00:00 rwlock_instances.frm 100% 8758 8.7MB/s 00:00 setup_consumers.frm 100% 8605 9.3MB/s 00:00 setup_instruments.frm 100% 8637 7.7MB/s 00:00 setup_timers.frm 100% 8650 7.8MB/s 00:00 threads.frm 100% 8650 7.4MB/s 00:00 xtrabackup_binlog_info 100% 21 29.1KB/s 00:00 xtrabackup_checkpoints 100% 113 172.8KB/s 00:00 backup-my.cnf 100% 431 718.9KB/s 00:00 xtrabackup_info 100% 467 723.3KB/s 00:00 xtrabackup_logfile 100% 2560 2.4MB/s 00:00 ibdata1.meta 100% 44 70.1KB/s 00:00 ibdata1.delta 100% 240KB 66.5MB/s 00:00 tables_priv.frm 100% 8955 9.4MB/s 00:00 tables_priv.MYI 100% 4096 5.3MB/s 00:00 tables_priv.MYD 100% 0 0.0KB/s 00:00 time_zone.MYD 100% 0 0.0KB/s 00:00 host.MYD 100% 0 0.0KB/s 00:00 ndb_binlog_index.MYI 100% 1024 1.6MB/s 00:00 plugin.frm 100% 8586 2.6MB/s 00:00 plugin.MYD 100% 0 0.0KB/s 00:00 proc.MYI 100% 4096 3.9MB/s 00:00 procs_priv.frm 100% 8875 6.9MB/s 00:00 procs_priv.MYD 100% 0 0.0KB/s 00:00 proxies_priv.MYI 100% 5120 6.8MB/s 00:00 servers.MYI 100% 1024 1.3MB/s 00:00 time_zone.MYI 100% 1024 1.6MB/s 00:00 time_zone_name.frm 100% 8606 12.9MB/s 00:00 time_zone_name.MYI 100% 1024 1.0MB/s 00:00 time_zone_name.MYD 100% 0 0.0KB/s 00:00 time_zone_transition.frm 100% 8686 11.6MB/s 00:00 time_zone_transition.MYI 100% 1024 1.8MB/s 00:00 time_zone_transition.MYD 100% 0 0.0KB/s 00:00 time_zone_transition_type.frm 100% 8748 11.5MB/s 00:00 time_zone_transition_type.MYI 100% 1024 1.9MB/s 00:00 time_zone_transition_type.MYD 100% 0 0.0KB/s 00:00 user.frm 100% 10KB 14.0MB/s 00:00 user.MYI 100% 2048 3.5MB/s 00:00 user.MYD 100% 272 537.3KB/s 00:00 time_zone_leap_second.frm 100% 8624 11.3MB/s 00:00 time_zone_leap_second.MYI 100% 1024 1.5MB/s 00:00 general_log.frm 100% 8776 12.2MB/s 00:00 general_log.CSM 100% 35 52.5KB/s 00:00 general_log.CSV 100% 0 0.0KB/s 00:00 slow_log.frm 100% 8976 13.3MB/s 00:00 slow_log.CSM 100% 35 65.2KB/s 00:00 slow_log.CSV 100% 0 0.0KB/s 00:00 servers.frm 100% 8838 12.4MB/s 00:00 servers.MYD 100% 0 0.0KB/s 00:00 time_zone_leap_second.MYD 100% 0 0.0KB/s 00:00 columns_priv.frm 100% 8820 10.4MB/s 00:00 columns_priv.MYI 100% 4096 5.9MB/s 00:00 columns_priv.MYD 100% 0 0.0KB/s 00:00 db.frm 100% 9582 12.3MB/s 00:00 db.MYI 100% 5120 7.4MB/s 00:00 db.MYD 100% 880 1.3MB/s 00:00 event.frm 100% 10KB 14.4MB/s 00:00 event.MYI 100% 2048 3.5MB/s 00:00 event.MYD 100% 0 0.0KB/s 00:00 func.frm 100% 8665 11.6MB/s 00:00 func.MYI 100% 1024 1.8MB/s 00:00 func.MYD 100% 0 0.0KB/s 00:00 help_category.frm 100% 8700 14.1MB/s 00:00 help_category.MYI 100% 3072 3.4MB/s 00:00 help_category.MYD 100% 1092 1.8MB/s 00:00 help_keyword.frm 100% 8612 1.6MB/s 00:00 help_keyword.MYI 100% 16KB 15.2MB/s 00:00 help_keyword.MYD 100% 89KB 50.5MB/s 00:00 help_relation.frm 100% 8630 9.0MB/s 00:00 help_relation.MYI 100% 19KB 18.4MB/s 00:00 help_relation.MYD 100% 9252 11.2MB/s 00:00 help_topic.frm 100% 8770 12.7MB/s 00:00 help_topic.MYI 100% 20KB 26.6MB/s 00:00 help_topic.MYD 100% 440KB 83.2MB/s 00:00 host.frm 100% 9510 14.4MB/s 00:00 host.MYI 100% 2048 2.6MB/s 00:00 ndb_binlog_index.frm 100% 8778 10.9MB/s 00:00 ndb_binlog_index.MYD 100% 0 0.0KB/s 00:00 plugin.MYI 100% 1024 1.5MB/s 00:00 proc.frm 100% 10KB 11.4MB/s 00:00 proc.MYD 100% 296 485.1KB/s 00:00 procs_priv.MYI 100% 4096 6.5MB/s 00:00 proxies_priv.frm 100% 8800 12.6MB/s 00:00 proxies_priv.MYD 100% 1386 1.8MB/s 00:00 time_zone.frm 100% 8636 11.7MB/s 00:00 db.opt 100% 0 0.0KB/s 00:00 db.opt 100% 67 125.1KB/s 00:00 students.frm 100% 8723 12.3MB/s 00:00 employee.frm 100% 8723 12.4MB/s 00:00 custom.frm 100% 8723 1.2MB/s 00:00 db.opt 100% 61 92.4KB/s 00:00 cond_instances.frm 100% 8624 11.8MB/s 00:00 events_waits_current.frm 100% 9220 13.7MB/s 00:00 events_waits_history.frm 100% 9220 13.9MB/s 00:00 events_waits_history_long.frm 100% 9220 13.2MB/s 00:00 events_waits_summary_by_instance.frm 100% 8878 13.3MB/s 00:00 events_waits_summary_by_thread_by_event_name.frm 100% 8854 13.5MB/s 00:00 events_waits_summary_global_by_event_name.frm 100% 8814 13.0MB/s 00:00 file_instances.frm 100% 8654 13.7MB/s 00:00 file_summary_by_event_name.frm 100% 8800 10.8MB/s 00:00 file_summary_by_instance.frm 100% 8840 10.6MB/s 00:00 mutex_instances.frm 100% 8684 12.5MB/s 00:00 performance_timers.frm 100% 8776 12.2MB/s 00:00 rwlock_instances.frm 100% 8758 12.9MB/s 00:00 setup_consumers.frm 100% 8605 13.9MB/s 00:00 setup_instruments.frm 100% 8637 13.8MB/s 00:00 setup_timers.frm 100% 8650 13.0MB/s 00:00 threads.frm 100% 8650 12.6MB/s 00:00 xtrabackup_binlog_info 100% 21 26.5KB/s 00:00 xtrabackup_checkpoints 100% 117 214.3KB/s 00:00 backup-my.cnf 100% 431 834.8KB/s 00:00 xtrabackup_info 100% 508 1.1MB/s 00:00 xtrabackup_logfile 100% 2560 4.8MB/s 00:00 ibdata1.meta 100% 44 88.4KB/s 00:00 ibdata1.delta 100% 688KB 77.2MB/s 00:00 tables_priv.frm 100% 8955 12.7MB/s 00:00 tables_priv.MYI 100% 4096 6.0MB/s 00:00 tables_priv.MYD 100% 0 0.0KB/s 00:00 time_zone.MYD 100% 0 0.0KB/s 00:00 host.MYD 100% 0 0.0KB/s 00:00 ndb_binlog_index.MYI 100% 1024 1.8MB/s 00:00 plugin.frm 100% 8586 12.5MB/s 00:00 plugin.MYD 100% 0 0.0KB/s 00:00 proc.MYI 100% 4096 5.6MB/s 00:00 procs_priv.frm 100% 8875 11.5MB/s 00:00 procs_priv.MYD 100% 0 0.0KB/s 00:00 proxies_priv.MYI 100% 5120 6.9MB/s 00:00 servers.MYI 100% 1024 1.9MB/s 00:00 time_zone.MYI 100% 1024 1.5MB/s 00:00 time_zone_name.frm 100% 8606 14.4MB/s 00:00 time_zone_name.MYI 100% 1024 1.7MB/s 00:00 time_zone_name.MYD 100% 0 0.0KB/s 00:00 time_zone_transition.frm 100% 8686 13.4MB/s 00:00 time_zone_transition.MYI 100% 1024 2.0MB/s 00:00 time_zone_transition.MYD 100% 0 0.0KB/s 00:00 time_zone_transition_type.frm 100% 8748 13.5MB/s 00:00 time_zone_transition_type.MYI 100% 1024 2.0MB/s 00:00 time_zone_transition_type.MYD 100% 0 0.0KB/s 00:00 user.frm 100% 10KB 16.9MB/s 00:00 user.MYI 100% 2048 3.8MB/s 00:00 user.MYD 100% 272 578.3KB/s 00:00 time_zone_leap_second.frm 100% 8624 14.4MB/s 00:00 time_zone_leap_second.MYI 100% 1024 1.9MB/s 00:00 general_log.frm 100% 8776 10.7MB/s 00:00 general_log.CSM 100% 35 65.7KB/s 00:00 general_log.CSV 100% 0 0.0KB/s 00:00 slow_log.frm 100% 8976 14.1MB/s 00:00 slow_log.CSM 100% 35 66.1KB/s 00:00 slow_log.CSV 100% 0 0.0KB/s 00:00 servers.frm 100% 8838 13.0MB/s 00:00 servers.MYD 100% 0 0.0KB/s 00:00 time_zone_leap_second.MYD 100% 0 0.0KB/s 00:00 columns_priv.frm 100% 8820 8.3MB/s 00:00 columns_priv.MYI 100% 4096 6.3MB/s 00:00 columns_priv.MYD 100% 0 0.0KB/s 00:00 db.frm 100% 9582 13.9MB/s 00:00 db.MYI 100% 5120 8.4MB/s 00:00 db.MYD 100% 880 1.6MB/s 00:00 event.frm 100% 10KB 15.2MB/s 00:00 event.MYI 100% 2048 3.2MB/s 00:00 event.MYD 100% 0 0.0KB/s 00:00 func.frm 100% 8665 10.1MB/s 00:00 func.MYI 100% 1024 1.5MB/s 00:00 func.MYD 100% 0 0.0KB/s 00:00 help_category.frm 100% 8700 11.1MB/s 00:00 help_category.MYI 100% 3072 4.3MB/s 00:00 help_category.MYD 100% 1092 1.6MB/s 00:00 help_keyword.frm 100% 8612 10.7MB/s 00:00 help_keyword.MYI 100% 16KB 17.6MB/s 00:00 help_keyword.MYD 100% 89KB 57.9MB/s 00:00 help_relation.frm 100% 8630 11.3MB/s 00:00 help_relation.MYI 100% 19KB 20.7MB/s 00:00 help_relation.MYD 100% 9252 13.0MB/s 00:00 help_topic.frm 100% 8770 11.1MB/s 00:00 help_topic.MYI 100% 20KB 16.9MB/s 00:00 help_topic.MYD 100% 440KB 71.5MB/s 00:00 host.frm 100% 9510 12.0MB/s 00:00 host.MYI 100% 2048 2.8MB/s 00:00 ndb_binlog_index.frm 100% 8778 12.1MB/s 00:00 ndb_binlog_index.MYD 100% 0 0.0KB/s 00:00 plugin.MYI 100% 1024 115.2KB/s 00:00 proc.frm 100% 10KB 12.3MB/s 00:00 proc.MYD 100% 296 507.8KB/s 00:00 procs_priv.MYI 100% 4096 4.1MB/s 00:00 proxies_priv.frm 100% 8800 10.9MB/s 00:00 proxies_priv.MYD 100% 1386 2.1MB/s 00:00 time_zone.frm 100% 8636 13.4MB/s 00:00 db.opt 100% 0 0.0KB/s 00:00 db.opt 100% 67 135.7KB/s 00:00 employee.frm 100% 8723 14.1MB/s 00:00 custom.frm 100% 8723 14.5MB/s 00:00 db.opt 100% 61 105.7KB/s 00:00 cond_instances.frm 100% 8624 11.8MB/s 00:00 events_waits_current.frm 100% 9220 13.6MB/s 00:00 events_waits_history.frm 100% 9220 11.7MB/s 00:00 events_waits_history_long.frm 100% 9220 13.9MB/s 00:00 events_waits_summary_by_instance.frm 100% 8878 13.4MB/s 00:00 events_waits_summary_by_thread_by_event_name.frm 100% 8854 14.5MB/s 00:00 events_waits_summary_global_by_event_name.frm 100% 8814 14.7MB/s 00:00 file_instances.frm 100% 8654 13.5MB/s 00:00 file_summary_by_event_name.frm 100% 8800 14.2MB/s 00:00 file_summary_by_instance.frm 100% 8840 14.7MB/s 00:00 mutex_instances.frm 100% 8684 14.7MB/s 00:00 performance_timers.frm 100% 8776 10.5MB/s 00:00 rwlock_instances.frm 100% 8758 13.5MB/s 00:00 setup_consumers.frm 100% 8605 13.7MB/s 00:00 setup_instruments.frm 100% 8637 13.0MB/s 00:00 setup_timers.frm 100% 8650 11.2MB/s 00:00 threads.frm 100% 8650 10.1MB/s 00:00 db.opt 100% 67 103.2KB/s 00:00 students.frm 100% 8723 12.2MB/s 00:00 xtrabackup_binlog_info 100% 22 26.6KB/s 00:00 xtrabackup_checkpoints 100% 117 182.7KB/s 00:00 backup-my.cnf 100% 431 870.4KB/s 00:00 xtrabackup_info 100% 509 904.7KB/s 00:00 [root@node102.yinzhengjie.org.cn ~]#
[root@node103.yinzhengjie.org.cn ~]# ll /data/ total 0 drwxr-xr-x 6 root root 223 Nov 7 21:38 base drwxr-xr-x 6 root root 249 Nov 7 21:38 inc1 drwxr-xr-x 7 root root 263 Nov 7 21:38 inc2 [root@node103.yinzhengjie.org.cn ~]# [root@node103.yinzhengjie.org.cn ~]# du -sh /data/ 23M /data/ [root@node103.yinzhengjie.org.cn ~]# [root@node103.yinzhengjie.org.cn ~]# du -sh /data/* #数据已经全部拷贝过来啦 20M /data/base 1.5M /data/inc1 2.0M /data/inc2 [root@node103.yinzhengjie.org.cn ~]#
5>.预准备完成备份，此选项--apply-log-only 阻止回滚未完成的事务
[root@node103.yinzhengjie.org.cn ~]# du -sh /data/* 20M /data/base 1.5M /data/inc1 2.0M /data/inc2 [root@node103.yinzhengjie.org.cn ~]# [root@node103.yinzhengjie.org.cn ~]# xtrabackup --prepare --apply-log-only --target-dir=/data/base xtrabackup: recognized server arguments: --innodb_checksum_algorithm=innodb --innodb_log_checksum_algorithm=innodb --innodb_data_file_path=ibdata 1: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: --innodb_checksum_algorithm=innodb --innodb_log_checksum_algorithm=innodb --innodb_data_file_path=ibdata 1: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 --prepare=1 --apply-log-only=1 --target-dir=/data/base xtrabackup version 2.4.12 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 170eb8c) xtrabackup: cd to /data/base/ xtrabackup: This target seems to be not prepared yet. InnoDB: Number of pools: 1 xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(1610604) 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 0 in the system tablespace does not match the log sequence number 1610604 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 1610613 InnoDB: Number of pools: 1 191107 21:42:35 completed OK! [root@node103.yinzhengjie.org.cn ~]# [root@node103.yinzhengjie.org.cn ~]# du -sh /data/* 28M /data/base 1.5M /data/inc1 2.0M /data/inc2 [root@node103.yinzhengjie.org.cn ~]#
6>.合并第1次增量备份到完全备份(即将第一次增量备份的数据整合到完全备份中)
[root@node103.yinzhengjie.org.cn ~]# du -sh /data/* 28M /data/base 1.5M /data/inc1 2.0M /data/inc2 [root@node103.yinzhengjie.org.cn ~]# [root@node103.yinzhengjie.org.cn ~]# xtrabackup --prepare --apply-log-only --target-dir=/data/base --incremental-dir=/data/inc1 xtrabackup: recognized server arguments: --innodb_checksum_algorithm=innodb --innodb_log_checksum_algorithm=innodb --innodb_data_file_path=ibdata 1: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: --innodb_checksum_algorithm=innodb --innodb_log_checksum_algorithm=innodb --innodb_data_file_path=ibdata 1: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 --prepare=1 --apply-log-only=1 --target-dir=/data/base --incremental-dir=/data/inc1 xtrabackup version 2.4.12 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 170eb8c) incremental backup from 1610604 is enabled. xtrabackup: cd to /data/base/ 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=(1612975) 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 = /data/inc1/ xtrabackup: innodb_log_files_in_group = 1 xtrabackup: innodb_log_file_size = 8388608 xtrabackup: Generating a list of tablespaces xtrabackup: page size for /data/inc1//ibdata1.delta is 16384 bytes Applying /data/inc1//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 = /data/inc1/ 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 0 in the system tablespace does not match the log sequence number 1612975 in the ib_logfiles! InnoDB: Database was not shutdown normally! InnoDB: Starting crash recovery. InnoDB: xtrabackup: Last MySQL binlog file position 663, file name /data/logbin/mysql-bin.000006 InnoDB: xtrabackup: Last MySQL binlog file position 663, file name /data/logbin/mysql-bin.000006 xtrabackup: starting shutdown with innodb_fast_shutdown = 1 InnoDB: Starting shutdown... InnoDB: Shutdown completed; log sequence number 1612984 InnoDB: Number of pools: 1 191107 21:44:08 [01] Copying /data/inc1/mysql/tables_priv.frm to ./mysql/tables_priv.frm 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/mysql/tables_priv.MYI to ./mysql/tables_priv.MYI 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/mysql/tables_priv.MYD to ./mysql/tables_priv.MYD 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/mysql/time_zone.MYD to ./mysql/time_zone.MYD 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/mysql/host.MYD to ./mysql/host.MYD 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/mysql/ndb_binlog_index.MYI to ./mysql/ndb_binlog_index.MYI 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/mysql/plugin.frm to ./mysql/plugin.frm 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/mysql/plugin.MYD to ./mysql/plugin.MYD 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/mysql/proc.MYI to ./mysql/proc.MYI 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/mysql/procs_priv.frm to ./mysql/procs_priv.frm 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/mysql/procs_priv.MYD to ./mysql/procs_priv.MYD 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/mysql/proxies_priv.MYI to ./mysql/proxies_priv.MYI 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/mysql/servers.MYI to ./mysql/servers.MYI 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/mysql/time_zone.MYI to ./mysql/time_zone.MYI 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/mysql/time_zone_name.frm to ./mysql/time_zone_name.frm 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/mysql/time_zone_name.MYI to ./mysql/time_zone_name.MYI 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/mysql/time_zone_name.MYD to ./mysql/time_zone_name.MYD 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/mysql/time_zone_transition.frm to ./mysql/time_zone_transition.frm 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/mysql/time_zone_transition.MYI to ./mysql/time_zone_transition.MYI 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/mysql/time_zone_transition.MYD to ./mysql/time_zone_transition.MYD 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/mysql/time_zone_transition_type.frm to ./mysql/time_zone_transition_type.frm 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/mysql/time_zone_transition_type.MYI to ./mysql/time_zone_transition_type.MYI 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/mysql/time_zone_transition_type.MYD to ./mysql/time_zone_transition_type.MYD 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/mysql/user.frm to ./mysql/user.frm 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/mysql/user.MYI to ./mysql/user.MYI 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/mysql/user.MYD to ./mysql/user.MYD 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/mysql/time_zone_leap_second.frm to ./mysql/time_zone_leap_second.frm 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/mysql/time_zone_leap_second.MYI to ./mysql/time_zone_leap_second.MYI 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/mysql/general_log.frm to ./mysql/general_log.frm 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/mysql/general_log.CSM to ./mysql/general_log.CSM 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/mysql/general_log.CSV to ./mysql/general_log.CSV 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/mysql/slow_log.frm to ./mysql/slow_log.frm 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/mysql/slow_log.CSM to ./mysql/slow_log.CSM 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/mysql/slow_log.CSV to ./mysql/slow_log.CSV 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/mysql/servers.frm to ./mysql/servers.frm 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/mysql/servers.MYD to ./mysql/servers.MYD 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/mysql/time_zone_leap_second.MYD to ./mysql/time_zone_leap_second.MYD 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/mysql/columns_priv.frm to ./mysql/columns_priv.frm 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/mysql/columns_priv.MYI to ./mysql/columns_priv.MYI 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/mysql/columns_priv.MYD to ./mysql/columns_priv.MYD 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/mysql/db.frm to ./mysql/db.frm 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/mysql/db.MYI to ./mysql/db.MYI 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/mysql/db.MYD to ./mysql/db.MYD 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/mysql/event.frm to ./mysql/event.frm 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/mysql/event.MYI to ./mysql/event.MYI 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/mysql/event.MYD to ./mysql/event.MYD 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/mysql/func.frm to ./mysql/func.frm 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/mysql/func.MYI to ./mysql/func.MYI 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/mysql/func.MYD to ./mysql/func.MYD 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/mysql/help_category.frm to ./mysql/help_category.frm 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/mysql/help_category.MYI to ./mysql/help_category.MYI 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/mysql/help_category.MYD to ./mysql/help_category.MYD 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/mysql/help_keyword.frm to ./mysql/help_keyword.frm 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/mysql/help_keyword.MYI to ./mysql/help_keyword.MYI 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/mysql/help_keyword.MYD to ./mysql/help_keyword.MYD 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/mysql/help_relation.frm to ./mysql/help_relation.frm 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/mysql/help_relation.MYI to ./mysql/help_relation.MYI 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/mysql/help_relation.MYD to ./mysql/help_relation.MYD 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/mysql/help_topic.frm to ./mysql/help_topic.frm 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/mysql/help_topic.MYI to ./mysql/help_topic.MYI 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/mysql/help_topic.MYD to ./mysql/help_topic.MYD 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/mysql/host.frm to ./mysql/host.frm 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/mysql/host.MYI to ./mysql/host.MYI 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/mysql/ndb_binlog_index.frm to ./mysql/ndb_binlog_index.frm 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/mysql/ndb_binlog_index.MYD to ./mysql/ndb_binlog_index.MYD 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/mysql/plugin.MYI to ./mysql/plugin.MYI 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/mysql/proc.frm to ./mysql/proc.frm 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/mysql/proc.MYD to ./mysql/proc.MYD 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/mysql/procs_priv.MYI to ./mysql/procs_priv.MYI 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/mysql/proxies_priv.frm to ./mysql/proxies_priv.frm 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/mysql/proxies_priv.MYD to ./mysql/proxies_priv.MYD 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/mysql/time_zone.frm to ./mysql/time_zone.frm 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/test/db.opt to ./test/db.opt 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/yinzhengjiedb/db.opt to ./yinzhengjiedb/db.opt 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/yinzhengjiedb/students.frm to ./yinzhengjiedb/students.frm 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/yinzhengjiedb/employee.frm to ./yinzhengjiedb/employee.frm 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/yinzhengjiedb/custom.frm to ./yinzhengjiedb/custom.frm 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/performance_schema/db.opt to ./performance_schema/db.opt 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/performance_schema/cond_instances.frm to ./performance_schema/cond_instances.frm 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/performance_schema/events_waits_current.frm to ./performance_schema/events_waits_current.frm 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/performance_schema/events_waits_history.frm to ./performance_schema/events_waits_history.frm 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/performance_schema/events_waits_history_long.frm to ./performance_schema/events_waits_history_long.frm 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/performance_schema/events_waits_summary_by_instance.frm to ./performance_schema/events_waits_summary_by_i nstance.frm191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/performance_schema/events_waits_summary_by_thread_by_event_name.frm to ./performance_schema/events_waits_ summary_by_thread_by_event_name.frm191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/performance_schema/events_waits_summary_global_by_event_name.frm to ./performance_schema/events_waits_sum mary_global_by_event_name.frm191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/performance_schema/file_instances.frm to ./performance_schema/file_instances.frm 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/performance_schema/file_summary_by_event_name.frm to ./performance_schema/file_summary_by_event_name.frm 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/performance_schema/file_summary_by_instance.frm to ./performance_schema/file_summary_by_instance.frm 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/performance_schema/mutex_instances.frm to ./performance_schema/mutex_instances.frm 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/performance_schema/performance_timers.frm to ./performance_schema/performance_timers.frm 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/performance_schema/rwlock_instances.frm to ./performance_schema/rwlock_instances.frm 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/performance_schema/setup_consumers.frm to ./performance_schema/setup_consumers.frm 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/performance_schema/setup_instruments.frm to ./performance_schema/setup_instruments.frm 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/performance_schema/setup_timers.frm to ./performance_schema/setup_timers.frm 191107 21:44:08 [01] ...done 191107 21:44:08 [01] Copying /data/inc1/performance_schema/threads.frm to ./performance_schema/threads.frm 191107 21:44:08 [01] ...done 191107 21:44:08 [00] Copying /data/inc1//xtrabackup_binlog_info to ./xtrabackup_binlog_info 191107 21:44:08 [00] ...done 191107 21:44:08 [00] Copying /data/inc1//xtrabackup_info to ./xtrabackup_info 191107 21:44:08 [00] ...done 191107 21:44:08 completed OK! [root@node103.yinzhengjie.org.cn ~]# [root@node103.yinzhengjie.org.cn ~]# du -sh /data/* 28M /data/base 9.5M /data/inc1 2.0M /data/inc2 [root@node103.yinzhengjie.org.cn ~]# [root@node103.yinzhengjie.org.cn ~]#
7>.合并第2次增量备份到完全备份：最后一次还原不需要加选项--apply-log-only
[root@node103.yinzhengjie.org.cn ~]# du -sh /data/* 28M /data/base 9.5M /data/inc1 2.0M /data/inc2 [root@node103.yinzhengjie.org.cn ~]# [root@node103.yinzhengjie.org.cn ~]# xtrabackup --prepare --target-dir=/data/base --incremental-dir=/data/inc2 xtrabackup: recognized server arguments: --innodb_checksum_algorithm=innodb --innodb_log_checksum_algorithm=innodb --innodb_data_file_path=ibdata 1: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: --innodb_checksum_algorithm=innodb --innodb_log_checksum_algorithm=innodb --innodb_data_file_path=ibdata 1: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 --prepare=1 --target-dir=/data/base --incremental-dir=/data/inc2 xtrabackup version 2.4.12 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 170eb8c) incremental backup from 1612975 is enabled. xtrabackup: cd to /data/base/ 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=(1628213) 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 = /data/inc2/ xtrabackup: innodb_log_files_in_group = 1 xtrabackup: innodb_log_file_size = 8388608 xtrabackup: Generating a list of tablespaces xtrabackup: page size for /data/inc2//ibdata1.delta is 16384 bytes Applying /data/inc2//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 = /data/inc2/ 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 0 in the system tablespace does not match the log sequence number 1628213 in the ib_logfiles! InnoDB: Database was not shutdown normally! InnoDB: Starting crash recovery. InnoDB: xtrabackup: Last MySQL binlog file position 880, file name /data/logbin/mysql-bin.000006 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.19 started; log sequence number 1628213 InnoDB: xtrabackup: Last MySQL binlog file position 880, file name /data/logbin/mysql-bin.000006 xtrabackup: starting shutdown with innodb_fast_shutdown = 1 InnoDB: FTS optimize thread exiting. InnoDB: Starting shutdown... InnoDB: Shutdown completed; log sequence number 1628403 InnoDB: Number of pools: 1 191107 21:45:31 [01] Copying /data/inc2/mysql/tables_priv.frm to ./mysql/tables_priv.frm 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/mysql/tables_priv.MYI to ./mysql/tables_priv.MYI 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/mysql/tables_priv.MYD to ./mysql/tables_priv.MYD 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/mysql/time_zone.MYD to ./mysql/time_zone.MYD 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/mysql/host.MYD to ./mysql/host.MYD 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/mysql/ndb_binlog_index.MYI to ./mysql/ndb_binlog_index.MYI 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/mysql/plugin.frm to ./mysql/plugin.frm 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/mysql/plugin.MYD to ./mysql/plugin.MYD 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/mysql/proc.MYI to ./mysql/proc.MYI 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/mysql/procs_priv.frm to ./mysql/procs_priv.frm 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/mysql/procs_priv.MYD to ./mysql/procs_priv.MYD 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/mysql/proxies_priv.MYI to ./mysql/proxies_priv.MYI 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/mysql/servers.MYI to ./mysql/servers.MYI 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/mysql/time_zone.MYI to ./mysql/time_zone.MYI 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/mysql/time_zone_name.frm to ./mysql/time_zone_name.frm 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/mysql/time_zone_name.MYI to ./mysql/time_zone_name.MYI 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/mysql/time_zone_name.MYD to ./mysql/time_zone_name.MYD 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/mysql/time_zone_transition.frm to ./mysql/time_zone_transition.frm 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/mysql/time_zone_transition.MYI to ./mysql/time_zone_transition.MYI 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/mysql/time_zone_transition.MYD to ./mysql/time_zone_transition.MYD 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/mysql/time_zone_transition_type.frm to ./mysql/time_zone_transition_type.frm 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/mysql/time_zone_transition_type.MYI to ./mysql/time_zone_transition_type.MYI 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/mysql/time_zone_transition_type.MYD to ./mysql/time_zone_transition_type.MYD 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/mysql/user.frm to ./mysql/user.frm 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/mysql/user.MYI to ./mysql/user.MYI 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/mysql/user.MYD to ./mysql/user.MYD 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/mysql/time_zone_leap_second.frm to ./mysql/time_zone_leap_second.frm 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/mysql/time_zone_leap_second.MYI to ./mysql/time_zone_leap_second.MYI 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/mysql/general_log.frm to ./mysql/general_log.frm 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/mysql/general_log.CSM to ./mysql/general_log.CSM 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/mysql/general_log.CSV to ./mysql/general_log.CSV 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/mysql/slow_log.frm to ./mysql/slow_log.frm 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/mysql/slow_log.CSM to ./mysql/slow_log.CSM 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/mysql/slow_log.CSV to ./mysql/slow_log.CSV 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/mysql/servers.frm to ./mysql/servers.frm 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/mysql/servers.MYD to ./mysql/servers.MYD 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/mysql/time_zone_leap_second.MYD to ./mysql/time_zone_leap_second.MYD 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/mysql/columns_priv.frm to ./mysql/columns_priv.frm 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/mysql/columns_priv.MYI to ./mysql/columns_priv.MYI 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/mysql/columns_priv.MYD to ./mysql/columns_priv.MYD 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/mysql/db.frm to ./mysql/db.frm 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/mysql/db.MYI to ./mysql/db.MYI 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/mysql/db.MYD to ./mysql/db.MYD 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/mysql/event.frm to ./mysql/event.frm 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/mysql/event.MYI to ./mysql/event.MYI 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/mysql/event.MYD to ./mysql/event.MYD 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/mysql/func.frm to ./mysql/func.frm 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/mysql/func.MYI to ./mysql/func.MYI 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/mysql/func.MYD to ./mysql/func.MYD 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/mysql/help_category.frm to ./mysql/help_category.frm 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/mysql/help_category.MYI to ./mysql/help_category.MYI 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/mysql/help_category.MYD to ./mysql/help_category.MYD 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/mysql/help_keyword.frm to ./mysql/help_keyword.frm 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/mysql/help_keyword.MYI to ./mysql/help_keyword.MYI 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/mysql/help_keyword.MYD to ./mysql/help_keyword.MYD 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/mysql/help_relation.frm to ./mysql/help_relation.frm 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/mysql/help_relation.MYI to ./mysql/help_relation.MYI 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/mysql/help_relation.MYD to ./mysql/help_relation.MYD 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/mysql/help_topic.frm to ./mysql/help_topic.frm 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/mysql/help_topic.MYI to ./mysql/help_topic.MYI 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/mysql/help_topic.MYD to ./mysql/help_topic.MYD 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/mysql/host.frm to ./mysql/host.frm 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/mysql/host.MYI to ./mysql/host.MYI 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/mysql/ndb_binlog_index.frm to ./mysql/ndb_binlog_index.frm 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/mysql/ndb_binlog_index.MYD to ./mysql/ndb_binlog_index.MYD 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/mysql/plugin.MYI to ./mysql/plugin.MYI 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/mysql/proc.frm to ./mysql/proc.frm 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/mysql/proc.MYD to ./mysql/proc.MYD 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/mysql/procs_priv.MYI to ./mysql/procs_priv.MYI 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/mysql/proxies_priv.frm to ./mysql/proxies_priv.frm 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/mysql/proxies_priv.MYD to ./mysql/proxies_priv.MYD 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/mysql/time_zone.frm to ./mysql/time_zone.frm 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/test/db.opt to ./test/db.opt 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/yinzhengjiedb/db.opt to ./yinzhengjiedb/db.opt 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/yinzhengjiedb/employee.frm to ./yinzhengjiedb/employee.frm 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/yinzhengjiedb/custom.frm to ./yinzhengjiedb/custom.frm 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/performance_schema/db.opt to ./performance_schema/db.opt 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/performance_schema/cond_instances.frm to ./performance_schema/cond_instances.frm 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/performance_schema/events_waits_current.frm to ./performance_schema/events_waits_current.frm 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/performance_schema/events_waits_history.frm to ./performance_schema/events_waits_history.frm 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/performance_schema/events_waits_history_long.frm to ./performance_schema/events_waits_history_long.frm 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/performance_schema/events_waits_summary_by_instance.frm to ./performance_schema/events_waits_summary_by_i nstance.frm191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/performance_schema/events_waits_summary_by_thread_by_event_name.frm to ./performance_schema/events_waits_ summary_by_thread_by_event_name.frm191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/performance_schema/events_waits_summary_global_by_event_name.frm to ./performance_schema/events_waits_sum mary_global_by_event_name.frm191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/performance_schema/file_instances.frm to ./performance_schema/file_instances.frm 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/performance_schema/file_summary_by_event_name.frm to ./performance_schema/file_summary_by_event_name.frm 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/performance_schema/file_summary_by_instance.frm to ./performance_schema/file_summary_by_instance.frm 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/performance_schema/mutex_instances.frm to ./performance_schema/mutex_instances.frm 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/performance_schema/performance_timers.frm to ./performance_schema/performance_timers.frm 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/performance_schema/rwlock_instances.frm to ./performance_schema/rwlock_instances.frm 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/performance_schema/setup_consumers.frm to ./performance_schema/setup_consumers.frm 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/performance_schema/setup_instruments.frm to ./performance_schema/setup_instruments.frm 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/performance_schema/setup_timers.frm to ./performance_schema/setup_timers.frm 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/performance_schema/threads.frm to ./performance_schema/threads.frm 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/devops/db.opt to ./devops/db.opt 191107 21:45:31 [01] ...done 191107 21:45:31 [01] Copying /data/inc2/devops/students.frm to ./devops/students.frm 191107 21:45:31 [01] ...done 191107 21:45:31 [00] Copying /data/inc2//xtrabackup_binlog_info to ./xtrabackup_binlog_info 191107 21:45:31 [00] ...done 191107 21:45:31 [00] Copying /data/inc2//xtrabackup_info to ./xtrabackup_info 191107 21:45:31 [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=1628403 InnoDB: Highest supported file format is Barracuda. InnoDB: Log scan progressed past the checkpoint lsn 1628684 InnoDB: Doing recovery: scanned up to log sequence number 1628693 (0%) InnoDB: Database was not shutdown normally! InnoDB: Starting crash recovery. InnoDB: xtrabackup: Last MySQL binlog file position 880, file name /data/logbin/mysql-bin.000006 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.19 started; log sequence number 1628693 xtrabackup: starting shutdown with innodb_fast_shutdown = 1 InnoDB: FTS optimize thread exiting. InnoDB: Starting shutdown... InnoDB: Shutdown completed; log sequence number 1628712 191107 21:45:33 completed OK! [root@node103.yinzhengjie.org.cn ~]# [root@node103.yinzhengjie.org.cn ~]# [root@node103.yinzhengjie.org.cn ~]# du -sh /data/* 50M /data/base 9.5M /data/inc1 10M /data/inc2 [root@node103.yinzhengjie.org.cn ~]# [root@node103.yinzhengjie.org.cn ~]#
8>.复制到数据库目录，注意数据库目录必须为空，MySQL服务不能启动
[root@node103.yinzhengjie.org.cn ~]# xtrabackup --copy-back --target-dir=/data/base xtrabackup: recognized server arguments: --datadir=/var/lib/mysql xtrabackup: recognized client arguments: --datadir=/var/lib/mysql --copy-back=1 --target-dir=/data/base xtrabackup version 2.4.12 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 170eb8c) 191107 21:48:01 [01] Copying ib_logfile0 to /var/lib/mysql/ib_logfile0 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ib_logfile1 to /var/lib/mysql/ib_logfile1 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ibdata1 to /var/lib/mysql/ibdata1 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./mysql/tables_priv.frm to /var/lib/mysql/mysql/tables_priv.frm 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./mysql/tables_priv.MYI to /var/lib/mysql/mysql/tables_priv.MYI 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./mysql/tables_priv.MYD to /var/lib/mysql/mysql/tables_priv.MYD 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./mysql/time_zone.MYD to /var/lib/mysql/mysql/time_zone.MYD 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./mysql/host.MYD to /var/lib/mysql/mysql/host.MYD 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./mysql/ndb_binlog_index.MYI to /var/lib/mysql/mysql/ndb_binlog_index.MYI 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./mysql/plugin.frm to /var/lib/mysql/mysql/plugin.frm 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./mysql/plugin.MYD to /var/lib/mysql/mysql/plugin.MYD 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./mysql/proc.MYI to /var/lib/mysql/mysql/proc.MYI 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./mysql/procs_priv.frm to /var/lib/mysql/mysql/procs_priv.frm 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./mysql/procs_priv.MYD to /var/lib/mysql/mysql/procs_priv.MYD 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./mysql/proxies_priv.MYI to /var/lib/mysql/mysql/proxies_priv.MYI 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./mysql/servers.MYI to /var/lib/mysql/mysql/servers.MYI 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./mysql/time_zone.MYI to /var/lib/mysql/mysql/time_zone.MYI 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./mysql/time_zone_name.frm to /var/lib/mysql/mysql/time_zone_name.frm 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./mysql/time_zone_name.MYI to /var/lib/mysql/mysql/time_zone_name.MYI 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./mysql/time_zone_name.MYD to /var/lib/mysql/mysql/time_zone_name.MYD 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./mysql/time_zone_transition.frm to /var/lib/mysql/mysql/time_zone_transition.frm 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./mysql/time_zone_transition.MYI to /var/lib/mysql/mysql/time_zone_transition.MYI 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./mysql/time_zone_transition.MYD to /var/lib/mysql/mysql/time_zone_transition.MYD 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./mysql/time_zone_transition_type.frm to /var/lib/mysql/mysql/time_zone_transition_type.frm 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./mysql/time_zone_transition_type.MYI to /var/lib/mysql/mysql/time_zone_transition_type.MYI 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./mysql/time_zone_transition_type.MYD to /var/lib/mysql/mysql/time_zone_transition_type.MYD 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./mysql/user.frm to /var/lib/mysql/mysql/user.frm 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./mysql/user.MYI to /var/lib/mysql/mysql/user.MYI 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./mysql/user.MYD to /var/lib/mysql/mysql/user.MYD 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./mysql/time_zone_leap_second.frm to /var/lib/mysql/mysql/time_zone_leap_second.frm 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./mysql/time_zone_leap_second.MYI to /var/lib/mysql/mysql/time_zone_leap_second.MYI 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./mysql/general_log.frm to /var/lib/mysql/mysql/general_log.frm 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./mysql/general_log.CSM to /var/lib/mysql/mysql/general_log.CSM 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./mysql/general_log.CSV to /var/lib/mysql/mysql/general_log.CSV 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./mysql/slow_log.frm to /var/lib/mysql/mysql/slow_log.frm 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./mysql/slow_log.CSM to /var/lib/mysql/mysql/slow_log.CSM 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./mysql/slow_log.CSV to /var/lib/mysql/mysql/slow_log.CSV 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./mysql/servers.frm to /var/lib/mysql/mysql/servers.frm 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./mysql/servers.MYD to /var/lib/mysql/mysql/servers.MYD 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./mysql/time_zone_leap_second.MYD to /var/lib/mysql/mysql/time_zone_leap_second.MYD 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./mysql/columns_priv.frm to /var/lib/mysql/mysql/columns_priv.frm 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./mysql/columns_priv.MYI to /var/lib/mysql/mysql/columns_priv.MYI 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./mysql/columns_priv.MYD to /var/lib/mysql/mysql/columns_priv.MYD 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./mysql/db.frm to /var/lib/mysql/mysql/db.frm 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./mysql/db.MYI to /var/lib/mysql/mysql/db.MYI 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./mysql/db.MYD to /var/lib/mysql/mysql/db.MYD 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./mysql/event.frm to /var/lib/mysql/mysql/event.frm 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./mysql/event.MYI to /var/lib/mysql/mysql/event.MYI 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./mysql/event.MYD to /var/lib/mysql/mysql/event.MYD 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./mysql/func.frm to /var/lib/mysql/mysql/func.frm 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./mysql/func.MYI to /var/lib/mysql/mysql/func.MYI 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./mysql/func.MYD to /var/lib/mysql/mysql/func.MYD 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./mysql/help_category.frm to /var/lib/mysql/mysql/help_category.frm 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./mysql/help_category.MYI to /var/lib/mysql/mysql/help_category.MYI 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./mysql/help_category.MYD to /var/lib/mysql/mysql/help_category.MYD 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./mysql/help_keyword.frm to /var/lib/mysql/mysql/help_keyword.frm 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./mysql/help_keyword.MYI to /var/lib/mysql/mysql/help_keyword.MYI 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./mysql/help_keyword.MYD to /var/lib/mysql/mysql/help_keyword.MYD 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./mysql/help_relation.frm to /var/lib/mysql/mysql/help_relation.frm 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./mysql/help_relation.MYI to /var/lib/mysql/mysql/help_relation.MYI 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./mysql/help_relation.MYD to /var/lib/mysql/mysql/help_relation.MYD 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./mysql/help_topic.frm to /var/lib/mysql/mysql/help_topic.frm 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./mysql/help_topic.MYI to /var/lib/mysql/mysql/help_topic.MYI 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./mysql/help_topic.MYD to /var/lib/mysql/mysql/help_topic.MYD 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./mysql/host.frm to /var/lib/mysql/mysql/host.frm 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./mysql/host.MYI to /var/lib/mysql/mysql/host.MYI 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./mysql/ndb_binlog_index.frm to /var/lib/mysql/mysql/ndb_binlog_index.frm 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./mysql/ndb_binlog_index.MYD to /var/lib/mysql/mysql/ndb_binlog_index.MYD 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./mysql/plugin.MYI to /var/lib/mysql/mysql/plugin.MYI 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./mysql/proc.frm to /var/lib/mysql/mysql/proc.frm 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./mysql/proc.MYD to /var/lib/mysql/mysql/proc.MYD 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./mysql/procs_priv.MYI to /var/lib/mysql/mysql/procs_priv.MYI 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./mysql/proxies_priv.frm to /var/lib/mysql/mysql/proxies_priv.frm 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./mysql/proxies_priv.MYD to /var/lib/mysql/mysql/proxies_priv.MYD 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./mysql/time_zone.frm to /var/lib/mysql/mysql/time_zone.frm 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./test/db.opt to /var/lib/mysql/test/db.opt 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./yinzhengjiedb/db.opt to /var/lib/mysql/yinzhengjiedb/db.opt 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./yinzhengjiedb/employee.frm to /var/lib/mysql/yinzhengjiedb/employee.frm 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./yinzhengjiedb/custom.frm to /var/lib/mysql/yinzhengjiedb/custom.frm 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./performance_schema/db.opt to /var/lib/mysql/performance_schema/db.opt 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./performance_schema/cond_instances.frm to /var/lib/mysql/performance_schema/cond_instances.frm 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./performance_schema/events_waits_current.frm to /var/lib/mysql/performance_schema/events_waits_current.frm 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./performance_schema/events_waits_history.frm to /var/lib/mysql/performance_schema/events_waits_history.frm 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./performance_schema/events_waits_history_long.frm to /var/lib/mysql/performance_schema/events_waits_history_long.fr m191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./performance_schema/events_waits_summary_by_instance.frm to /var/lib/mysql/performance_schema/events_waits_summary_ by_instance.frm191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./performance_schema/events_waits_summary_by_thread_by_event_name.frm to /var/lib/mysql/performance_schema/events_wa its_summary_by_thread_by_event_name.frm191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./performance_schema/events_waits_summary_global_by_event_name.frm to /var/lib/mysql/performance_schema/events_waits _summary_global_by_event_name.frm191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./performance_schema/file_instances.frm to /var/lib/mysql/performance_schema/file_instances.frm 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./performance_schema/file_summary_by_event_name.frm to /var/lib/mysql/performance_schema/file_summary_by_event_name. frm191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./performance_schema/file_summary_by_instance.frm to /var/lib/mysql/performance_schema/file_summary_by_instance.frm 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./performance_schema/mutex_instances.frm to /var/lib/mysql/performance_schema/mutex_instances.frm 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./performance_schema/performance_timers.frm to /var/lib/mysql/performance_schema/performance_timers.frm 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./performance_schema/rwlock_instances.frm to /var/lib/mysql/performance_schema/rwlock_instances.frm 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./performance_schema/setup_consumers.frm to /var/lib/mysql/performance_schema/setup_consumers.frm 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./performance_schema/setup_instruments.frm to /var/lib/mysql/performance_schema/setup_instruments.frm 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./performance_schema/setup_timers.frm to /var/lib/mysql/performance_schema/setup_timers.frm 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./performance_schema/threads.frm to /var/lib/mysql/performance_schema/threads.frm 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./xtrabackup_master_key_id to /var/lib/mysql/xtrabackup_master_key_id 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./xtrabackup_binlog_pos_innodb to /var/lib/mysql/xtrabackup_binlog_pos_innodb 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./devops/db.opt to /var/lib/mysql/devops/db.opt 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./devops/students.frm to /var/lib/mysql/devops/students.frm 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./xtrabackup_info to /var/lib/mysql/xtrabackup_info 191107 21:48:01 [01] ...done 191107 21:48:01 [01] Copying ./ibtmp1 to /var/lib/mysql/ibtmp1 191107 21:48:01 [01] ...done 191107 21:48:01 completed OK! [root@node103.yinzhengjie.org.cn ~]# [root@node103.yinzhengjie.org.cn ~]# [root@node103.yinzhengjie.org.cn ~]# ll /var/lib/mysql/ total 40980 drwxr-x--- 2 root root 40 Nov 7 21:48 devops -rw-r----- 1 root root 18874368 Nov 7 21:48 ibdata1 -rw-r----- 1 root root 5242880 Nov 7 21:48 ib_logfile0 -rw-r----- 1 root root 5242880 Nov 7 21:48 ib_logfile1 -rw-r----- 1 root root 12582912 Nov 7 21:48 ibtmp1 drwxr-x--- 2 root root 4096 Nov 7 21:48 mysql drwxr-x--- 2 root root 4096 Nov 7 21:48 performance_schema drwxr-x--- 2 root root 20 Nov 7 21:48 test -rw-r----- 1 root root 34 Nov 7 21:48 xtrabackup_binlog_pos_innodb -rw-r----- 1 root root 509 Nov 7 21:48 xtrabackup_info -rw-r----- 1 root root 1 Nov 7 21:48 xtrabackup_master_key_id drwxr-x--- 2 root root 58 Nov 7 21:48 yinzhengjiedb [root@node103.yinzhengjie.org.cn ~]#
9>.还原属性
[root@node103.yinzhengjie.org.cn ~]# ll /var/lib/mysql/ total 40980 drwxr-x--- 2 root root 40 Nov 7 21:48 devops -rw-r----- 1 root root 18874368 Nov 7 21:48 ibdata1 -rw-r----- 1 root root 5242880 Nov 7 21:48 ib_logfile0 -rw-r----- 1 root root 5242880 Nov 7 21:48 ib_logfile1 -rw-r----- 1 root root 12582912 Nov 7 21:48 ibtmp1 drwxr-x--- 2 root root 4096 Nov 7 21:48 mysql drwxr-x--- 2 root root 4096 Nov 7 21:48 performance_schema drwxr-x--- 2 root root 20 Nov 7 21:48 test -rw-r----- 1 root root 34 Nov 7 21:48 xtrabackup_binlog_pos_innodb -rw-r----- 1 root root 509 Nov 7 21:48 xtrabackup_info -rw-r----- 1 root root 1 Nov 7 21:48 xtrabackup_master_key_id drwxr-x--- 2 root root 58 Nov 7 21:48 yinzhengjiedb [root@node103.yinzhengjie.org.cn ~]# [root@node103.yinzhengjie.org.cn ~]# chown -R mysql:mysql /var/lib/mysql [root@node103.yinzhengjie.org.cn ~]# [root@node103.yinzhengjie.org.cn ~]# ll /var/lib/mysql/ total 40980 drwxr-x--- 2 mysql mysql 40 Nov 7 21:48 devops -rw-r----- 1 mysql mysql 18874368 Nov 7 21:48 ibdata1 -rw-r----- 1 mysql mysql 5242880 Nov 7 21:48 ib_logfile0 -rw-r----- 1 mysql mysql 5242880 Nov 7 21:48 ib_logfile1 -rw-r----- 1 mysql mysql 12582912 Nov 7 21:48 ibtmp1 drwxr-x--- 2 mysql mysql 4096 Nov 7 21:48 mysql drwxr-x--- 2 mysql mysql 4096 Nov 7 21:48 performance_schema drwxr-x--- 2 mysql mysql 20 Nov 7 21:48 test -rw-r----- 1 mysql mysql 34 Nov 7 21:48 xtrabackup_binlog_pos_innodb -rw-r----- 1 mysql mysql 509 Nov 7 21:48 xtrabackup_info -rw-r----- 1 mysql mysql 1 Nov 7 21:48 xtrabackup_master_key_id drwxr-x--- 2 mysql mysql 58 Nov 7 21:48 yinzhengjiedb [root@node103.yinzhengjie.org.cn ~]# [root@node103.yinzhengjie.org.cn ~]#
10>.启动服务
[root@node103.yinzhengjie.org.cn ~]# systemctl start mariadb [root@node103.yinzhengjie.org.cn ~]# ss -ntl State Recv-Q Send-Q Local Address:Port Peer Address:Port LISTEN 0 50 *:3306 *:* LISTEN 0 128 *:22 *:* LISTEN 0 128 :::22 :::* [root@node103.yinzhengjie.org.cn ~]#
[root@node103.yinzhengjie.org.cn ~]# mysql #很显然，使用默认的用户名和密码无法登录 ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) [root@node103.yinzhengjie.org.cn ~]# [root@node103.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie #由于我们将原节点所有数据库备份啦，因此用户名和密码也备份过来啦。 Welcome to the MariaDB monitor. Commands end with ; or g. Your MariaDB connection id is 3 Server version: 5.5.64-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. MariaDB [(none)]> MariaDB [(none)]> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | devops | | mysql | | performance_schema | | test | | yinzhengjiedb | +--------------------+ 6 rows in set (0.00 sec) MariaDB [(none)]> MariaDB [(none)]> USE devops 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 [devops]> MariaDB [devops]> SHOW TABLES; +------------------+ | Tables_in_devops | +------------------+ | students | +------------------+ 1 row in set (0.00 sec) MariaDB [devops]> SELECT * FROM students; +----+---------------------+------+------+--------+-----------+ | id | name | sex | age | mobile | address | +----+---------------------+------+------+--------+-----------+ | 1 | Jason Yin | boy | 18 | 10000 | beijing | | 2 | Jay | boy | 40 | 10086 | Taiwan | | 3 | yinzhengjie | boy | 27 | NULL | shanxi | | 4 | Gloria Tang Tsz-Kei | girl | 28 | NULL | Hong Kong | +----+---------------------+------+------+--------+-----------+ 4 rows in set (0.00 sec) MariaDB [devops]> MariaDB [devops]> MariaDB [devops]> use yinzhengjiedb 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 [yinzhengjiedb]> MariaDB [yinzhengjiedb]> MariaDB [yinzhengjiedb]> SHOW TABLES; +-------------------------+ | Tables_in_yinzhengjiedb | +-------------------------+ | custom | | employee | +-------------------------+ 2 rows in set (0.00 sec) MariaDB [yinzhengjiedb]> MariaDB [yinzhengjiedb]> SELECT * FROM custom; +----+---------------------+------+------+--------+-----------+ | id | name | sex | age | mobile | address | +----+---------------------+------+------+--------+-----------+ | 1 | Jason Yin | boy | 18 | 10000 | beijing | | 2 | Jay | boy | 40 | 10086 | Taiwan | | 3 | yinzhengjie | boy | 27 | NULL | shanxi | | 4 | Gloria Tang Tsz-Kei | girl | 28 | NULL | Hong Kong | +----+---------------------+------+------+--------+-----------+ 4 rows in set (0.00 sec) MariaDB [yinzhengjiedb]> MariaDB [yinzhengjiedb]> SELECT * FROM employee; +----+---------------------+------+------+--------+-----------+ | id | name | sex | age | mobile | address | +----+---------------------+------+------+--------+-----------+ | 1 | Jason Yin | boy | 18 | 10000 | beijing | | 2 | Jay | boy | 40 | 10086 | Taiwan | | 3 | yinzhengjie | boy | 27 | NULL | shanxi | | 4 | Gloria Tang Tsz-Kei | girl | 28 | NULL | Hong Kong | +----+---------------------+------+------+--------+-----------+ 4 rows in set (0.00 sec) MariaDB [yinzhengjiedb]> MariaDB [yinzhengjiedb]>