本文详细介绍了Percona Xtrabackup工具在MySQL数据库增量备份中的应用，包括安装、全量备份、增量备份及恢复流程。Xtrabackup支持快速备份和还原，适用于大规模数据场景。
一：Xtrabackup介绍：
MySQL冷备、mysqldump、MySQL热拷贝都无法实现对数据库进行增量备份。在实际生产环境中增量备份是非常实用的，如果数据大于50G或100G，存储空间足够的情况下，可以每天进行完整备份，如果每天产生的数据量较大，需要定制数据备份策略。例如每周实用完整备份，周一到周六实用增量备份。而Percona-Xtrabackup就是为了实现增量备份而出现的一款主流备份工具，xtrabakackup有2个工具，分别是xtrabakup、innobakupe。
Percona-xtrabackup是
Percona公司开发的一个用于MySQL数据库物理热备的备份工具，支持MySQL、Percona
server和MariaDB，开源免费，是目前较为受欢迎的主流备份工具。xtrabackup只能备份innoDB和xtraDB两种数据引擎的表，而不能备份MyISAM数据表。
二：Xtrabackup的优点：
（1）备份速度快，物理备份可靠
（2）备份过程不会打断正在执行的事务（无需锁表）
（3）能够基于压缩等功能节约磁盘空间和流量
（4）自动备份校验
（5）还原速度快
（6）可以流传将备份传输到另外一台机器上
（7）在不增加服务器负载的情况备份数据
三：Xtrabackup的安装：
**
yum install xtrabackup //依赖epel源，请先安装epel源
Xtrabackup中主要包含两个工具：
xtrabackup：是用于热备innodb，xtradb表中数据的工具，不能备份其他类型的表，也不能备份数据表结构；
innobackupex：是将xtrabackup进行封装的perl脚本，提供了备份myisam表的能力。
常用选项:
--host 指定主机
--user 指定用户名
--password 指定密码
--port 指定端口
--databases 指定数据库
--incremental 创建增量备份
--incremental-basedir 指定包含完全备份的目录
--incremental-dir 指定包含增量备份的目录
--apply-log 对备份进行预处理操作
一般情况下，在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处理不一致状态。“准备”的主要作用正是通过回滚未提交的事务及同步已经提交的事务至数据文件也使得数据文件处于一致性状态。
--redo-only 不回滚未提交事务
--copy-back 恢复备份目录
**
使用innobackupex备份时，其会调用xtrabackup备份所有的InnoDB表，复制所有关于表结构定义的相关文件(.frm)、以及MyISAM、MERGE、CSV和ARCHIVE表的相关文件，同时还会备份触发器和数据库配置信息相关的文件，这些文件会被保存到一个以时间命名的目录当中。在备份的同时，innobackupex还会在备份目录中创建如下文件：
(1)xtrabackup_checkpoints -- 备份类型(如完全或增量)、备份状态(如是否已经为prepared状态)和LSN(日志序列号)范围信息：
每个InnoDB页(通常为16k大小)
都会包含一个日志序列号，即LSN，LSN是整个数据库系统的系统版本号，每个页面相关的LSN能够表明此页面最近是如何发生改变的。
(2)xtrabackup_binlog_info -- mysql服务器当前正在使用的二进制日志文件及备份这一刻位置二进制日志时间的位置。
(3)xtrabackup_binlog_pos_innodb -- 二进制日志文件及用于InnoDB或XtraDB表的二进制日志文件的当前position。
(4)xtrabackup_binary -- 备份中用到的xtrabackup的可执行文件；
(5)backup-my.cnf -- 备份命令用到的配置选项信息：
在使用innobackupex进行备份时，还可以使用--no-timestamp选项来阻止命令自动创建一个以时间命名的目录：如此一来，innobackupex命令将会创建一个BACKUP-DIR目录来存储备份数据
。
第一种备份：使用全量备份；
| 主数据库 | 备份数据库 |
|---|---|
| 10.5.100.183 | 10.5.100.146 |
| node3 | node4 |
备份过程：
[root@node3 ~]# yum install xtrabackup -y //安装所需软件包
[root@node3 ~]# inno
innobackupex innochecksum //两个备份工具
[root@node3 ~]# mkdir /backups //用于保存备份信息的目录
[root@node3 ~]# vim /etc/my.cnf
添加此项：innodb_file_per_table = 1 //表示每个表使用单独的表空间。
[root@node3 ~]# mysql < hellodb.sql //导入一些数据进入到主数据库中
[root@node3 ~]# systemctl restart mariadb
> [root@node3 ~]# innobackupex --user=root /backups/
--user：指明备份时以那个用户身份备份
191202 06:44:26 Executing UNLOCK TABLES
191202 06:44:26 All tables unlocked
191202 06:44:26 Backup created in directory '/backups//2019-12-02_06-44-24'
MySQL binlog position: filename 'mysql-bin.000004', position '748'
191202 06:44:26 [00] Writing backup-my.cnf
191202 06:44:26 [00] ...done
191202 06:44:26 [00] Writing xtrabackup_info
191202 06:44:26 [00] ...done
xtrabackup: Transaction log of lsn (1597945) to (1597945) was copied.
191202 06:44:26 completed OK!
显示ok即可
[root@node3 ~]# cd /backups/
[root@node3 backups]# ls
2019-12-02_06-44-24
[root@node3 backups]# scp -r /backups/2019-12-02_06-44-24/ 10.5.100.146:/root/
将备份的数据信息传送到备份数据库上进行恢复。
file_summary_by_instance.frm 100% 8840
mutex_instances.frm 100% 8684
performance_timers.frm 100% 8776
rwlock_instances.frm 100% 8758
setup_consumers.frm 100% 8605
setup_instruments.frm 100% 8637
setup_timers.frm 100% 8650
threads.frm 100% 8650
db.opt 100% 65
classes.frm 100% 8636
classes.MYI 100% 1024
classes.MYD 100% 212
courses.frm 100% 8602
courses.MYI 100% 1024
courses.MYD 100% 160
students.frm 100% 8730
students.MYI 100% 2048
students.MYD 100% 764
teachers.frm 100% 8650
teachers.MYI 100% 1024
teachers.MYD 100% 112
xtrabackup_binlog_info 100% 21
xtrabackup_checkpoints 100% 113
backup-my.cnf 100% 386
xtrabackup_info
在备份库中进行恢复数据；
从库也需安装xtrbackups软件包
[root@node4 ~]# ls
2019-12-02_06-44-24 anaconda-ks.cfg mha4mysql-node-0.56-0.el6.noarch.rpm
[root@node4 ~]# mkdir /backups
[root@node4 ~]# cd /backups/
[root@node4 backups]# ls
[root@node4 backups]# cd
[root@node4 ~]#
[root@node4 ~]# mv 2019-12-02_06-44-24/ /backups/
[root@node4 ~]# innobackupex --apply-log /backups/2019-12-02_06-44-24/
这一项表示整理合并数据文件和事务日志文件。
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=1597971
InnoDB: Highest supported file format is Barracuda.
InnoDB: 128 rollback segment(s) are active.
InnoDB: Waiting for purge to start
InnoDB: 5.6.24 started; log sequence number 1598476
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1598486
191202 15:49:32 completed OK!
删除备份数据库中原有的数据文件，进行主库数据文件恢复。注意恢复时要关闭数据库。
[root@node4 ~]# systemctl stop mariadb
[root@node4 ~]# rm -rf /var/lib/mysql/*
[root@node4 ~]# innobackupex --copy-back /backups/2019-12-02_06-44-24/
191202 15:52:01 [01] ...done
191202 15:52:01 [01] Copying ./hellodb/students.frm to /var/lib/mysql/hellodb/students.frm
191202 15:52:01 [01] ...done
191202 15:52:01 [01] Copying ./hellodb/students.MYI to /var/lib/mysql/hellodb/students.MYI
191202 15:52:01 [01] ...done
191202 15:52:01 [01] Copying ./hellodb/students.MYD to /var/lib/mysql/hellodb/students.MYD
191202 15:52:01 [01] ...done
191202 15:52:01 [01] Copying ./hellodb/teachers.frm to /var/lib/mysql/hellodb/teachers.frm
191202 15:52:01 [01] ...done
191202 15:52:01 [01] Copying ./hellodb/teachers.MYI to /var/lib/mysql/hellodb/teachers.MYI
191202 15:52:01 [01] ...done
191202 15:52:01 [01] Copying ./hellodb/teachers.MYD to /var/lib/mysql/hellodb/teachers.MYD
191202 15:52:01 [01] ...done
191202 15:52:01 [01] Copying ./xtrabackup_info to /var/lib/mysql/xtrabackup_info
191202 15:52:01 [01] ...done
191202 15:52:01 completed OK!
[root@node4 mysql]# chown -R mysql:mysql ./*
因为我以root用户执行，所以要将数据文件的属主和属组修改成mysql用户。
[root@node4 mysql]# ll
total 28684
drwx------. 2 mysql mysql 254 Dec 2 15:52 hellodb
-rw-r-----. 1 mysql mysql 18874368 Dec 2 15:51 ibdata1
-rw-r-----. 1 mysql mysql 5242880 Dec 2 15:51 ib_logfile0
-rw-r-----. 1 mysql mysql 5242880 Dec 2 15:51 ib_logfile1
drwx------. 2 mysql mysql 4096 Dec 2 15:52 mysql
drwx------. 2 mysql mysql 4096 Dec 2 15:52 performance_schema
drwx------. 2 mysql mysql 20 Dec 2 15:52 test
-rw-r-----. 1 mysql mysql 454 Dec 2 15:52 xtrabackup_info
[root@node4 mysql]# systemctl restart mariadb
启动数据库进行验证。
第二种备份机制：完全+增量备份。
两台主机：
| 主数据库 | 备份数据库 |
|---|---|
| 10.5.100.207 | 10.5.100.208 |
主数据库节点：
[root@rhel7 ~]# vim /etc/my.cnf
innodb_file_per_table=ON
[root@rhel7 ~]# systemctl restart mariadb
[root@rhel7 ~]# mysql < hellodb.sql
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
MariaDB [(none)]> use hellodb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [hellodb]> select * from students;
+-------+---------------+------+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+------+--------+---------+-----------+
| 1 | shi zhongyu | 22 | M | 2 | 3 |
| 2 | shi potian | 22 | M | 1 | 7 |
| 3 | xie yangke | 53 | M | 2 | 16 |
| 4 | ding dian | 32 | M | 4 | 4 |
| 5 | yu yutong | 26 | M | 3 | 1 |
| 6 | shi qing | 46 | M | 5 | 5 |
| 7 | xie ren | 19 | F | 3 | NULL |
| 8 | lin daiyu | 17 | F | 7 | NULL |
| 9 | ren yingying | 20 | F | 6 | NULL |
| 10 | yue lingshan | 19 | F | 3 | NULL |
| 11 | yuan chengzhi | 23 | M | 6 | NULL |
| 12 | wei qingqing | 19 | F | 1 | NULL |
| 13 | tian boguang | 33 | M | 2 | NULL |
| 14 | lu wushuang | 17 | F | 3 | NULL |
| 15 | duan yu | 19 | M | 4 | NULL |
| 16 | xu zhu | 21 | M | 1 | NULL |
| 17 | lin chong | 25 | M | 4 | NULL |
| 18 | hua rong | 23 | M | 7 | NULL |
| 19 | xue baochai | 18 | F | 6 | NULL |
| 20 | huang yueying | 19 | F | 7 | NULL |
| 21 | diao chan | 22 | F | 6 | NULL |
| 22 | xiao qiao | 20 | F | 1 | NULL |
| 23 | ma chao | 23 | M | 4 | NULL |
| 24 | xu xian | 27 | M | NULL | NULL |
| 25 | su dasheng | 101 | M | NULL | NULL |
+-------+---------------+------+--------+---------+-----------+
25 rows in set (0.00 sec)
二：进行全量备份;
[root@rhel7 ~]# innobackupex --user=root /backups/
191202 00:37:09 [00] ...done
191202 00:37:09 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '1623336'
xtrabackup: Stopping log copying thread.
.191202 00:37:09 >> log scanned up to (1623336)
191202 00:37:09 Executing UNLOCK TABLES
191202 00:37:09 All tables unlocked
191202 00:37:09 Backup created in directory '/backups//2019-12-02_00-37-08'
MySQL binlog position: filename 'mysql-bin.000003', position '4773'
191202 00:37:09 [00] Writing backup-my.cnf
191202 00:37:09 [00] ...done
191202 00:37:09 [00] Writing xtrabackup_info
191202 00:37:09 [00] ...done
xtrabackup: Transaction log of lsn (1623336) to (1623336) was copied.
191202 00:37:09 completed OK!
[root@rhel7 ~]# cd /backups/
[root@rhel7 backups]# ls
2019-12-02_00-37-08
三：修改数据库实现增量
[root@rhel7 ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 6
Server version: 5.5.41-MariaDB-log MariaDB Server
Copyright (c) 2000, 2014, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> use hellodb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [hellodb]> select * from students;
+-------+---------------+------+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+------+--------+---------+-----------+
| 1 | shi zhongyu | 22 | M | 2 | 3 |
| 2 | shi potian | 22 | M | 1 | 7 |
| 3 | xie yangke | 53 | M | 2 | 16 |
| 4 | ding dian | 32 | M | 4 | 4 |
| 5 | yu yutong | 26 | M | 3 | 1 |
| 6 | shi qing | 46 | M | 5 | 5 |
| 7 | xie ren | 19 | F | 3 | NULL |
| 8 | lin daiyu | 17 | F | 7 | NULL |
| 9 | ren yingying | 20 | F | 6 | NULL |
| 10 | yue lingshan | 19 | F | 3 | NULL |
| 11 | yuan chengzhi | 23 | M | 6 | NULL |
| 12 | wei qingqing | 19 | F | 1 | NULL |
| 13 | tian boguang | 33 | M | 2 | NULL |
| 14 | lu wushuang | 17 | F | 3 | NULL |
| 15 | duan yu | 19 | M | 4 | NULL |
| 16 | xu zhu | 21 | M | 1 | NULL |
| 17 | lin chong | 25 | M | 4 | NULL |
| 18 | hua rong | 23 | M | 7 | NULL |
| 19 | xue baochai | 18 | F | 6 | NULL |
| 20 | huang yueying | 19 | F | 7 | NULL |
| 21 | diao chan | 22 | F | 6 | NULL |
| 22 | xiao qiao | 20 | F | 1 | NULL |
| 23 | ma chao | 23 | M | 4 | NULL |
| 24 | xu xian | 27 | M | NULL | NULL |
| 25 | su dasheng | 101 | M | NULL | NULL |
+-------+---------------+------+--------+---------+-----------+
25 rows in set (0.00 sec)
MariaDB [hellodb]> update students set Age=200 where StuID=25;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [hellodb]> select * from students;
+-------+---------------+------+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+------+--------+---------+-----------+
| 1 | shi zhongyu | 22 | M | 2 | 3 |
| 2 | shi potian | 22 | M | 1 | 7 |
| 3 | xie yangke | 53 | M | 2 | 16 |
| 4 | ding dian | 32 | M | 4 | 4 |
| 5 | yu yutong | 26 | M | 3 | 1 |
| 6 | shi qing | 46 | M | 5 | 5 |
| 7 | xie ren | 19 | F | 3 | NULL |
| 8 | lin daiyu | 17 | F | 7 | NULL |
| 9 | ren yingying | 20 | F | 6 | NULL |
| 10 | yue lingshan | 19 | F | 3 | NULL |
| 11 | yuan chengzhi | 23 | M | 6 | NULL |
| 12 | wei qingqing | 19 | F | 1 | NULL |
| 13 | tian boguang | 33 | M | 2 | NULL |
| 14 | lu wushuang | 17 | F | 3 | NULL |
| 15 | duan yu | 19 | M | 4 | NULL |
| 16 | xu zhu | 21 | M | 1 | NULL |
| 17 | lin chong | 25 | M | 4 | NULL |
| 18 | hua rong | 23 | M | 7 | NULL |
| 19 | xue baochai | 18 | F | 6 | NULL |
| 20 | huang yueying | 19 | F | 7 | NULL |
| 21 | diao chan | 22 | F | 6 | NULL |
| 22 | xiao qiao | 20 | F | 1 | NULL |
| 23 | ma chao | 23 | M | 4 | NULL |
| 24 | xu xian | 27 | M | NULL | NULL |
| 25 | su dasheng | 200 | M | NULL | NULL |
+-------+---------------+------+--------+---------+-----------+
四：备份增量数据库，指明从那个备份位置文件增量的数据
[root@rhel7 backups]# innobackupex --incremental /backups/ --incremental-basedir=/backups/2019-12-02_00-37-08
91202 00:45:13 [01] Copying ./hellodb/teachers.frm to /backups//2019-12-02_00-45-12/hellodb/teachers.frm
191202 00:45:13 [01] ...done
191202 00:45:13 Finished backing up non-InnoDB tables and files
191202 00:45:13 [00] Writing xtrabackup_binlog_info
191202 00:45:13 [00] ...done
191202 00:45:13 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '1624540'
xtrabackup: Stopping log copying thread.
.191202 00:45:13 >> log scanned up to (1624540)
191202 00:45:14 Executing UNLOCK TABLES
191202 00:45:14 All tables unlocked
191202 00:45:14 Backup created in directory '/backups//2019-12-02_00-45-12'
MySQL binlog position: filename 'mysql-bin.000003', position '4979'
191202 00:45:14 [00] Writing backup-my.cnf
191202 00:45:14 [00] ...done
191202 00:45:14 [00] Writing xtrabackup_info
191202 00:45:14 [00] ...done
xtrabackup: Transaction log of lsn (1624540) to (1624540) was copied.
191202 00:45:14 completed OK!
[root@rhel7 backups]# ls
2019-12-02_00-37-08 2019-12-02_00-45-12
[root@rhel7 backups]#
五：暂停数据库，没有语句再写入；
[root@rhel7 backups]# systemctl stop mariadb
[root@rhel7 backups]#
[root@rhel7 backups]# scp -r 2019-12-02_00-37-08/ 10.5.100.208:/backups/
将备份数据的信息传送到从数据库中。
file_summary_by_event_name.frm 100% 8800 8.6KB/s 00:00
file_summary_by_instance.frm 100% 8840 8.6KB/s 00:00
mutex_instances.frm 100% 8684 8.5KB/s 00:00
performance_timers.frm 100% 8776 8.6KB/s 00:00
rwlock_instances.frm 100% 8758 8.6KB/s 00:00
setup_consumers.frm 100% 8605 8.4KB/s 00:00
setup_instruments.frm 100% 8637 8.4KB/s 00:00
setup_timers.frm 100% 8650 8.5KB/s 00:00
threads.frm 100% 8650 8.5KB/s 00:00
xtrabackup_binlog_info 100% 22 0.0KB/s 00:00
xtrabackup_checkpoints 100% 113 0.1KB/s 00:00
backup-my.cnf 100% 386 0.4KB/s 00:00
xtrabackup_info 100% 459 0.5KB/s 00:00
[root@rhel7 backups]# scp -r 2019-12-02_00-45-12/ 10.5.100.208:/backups/
将增量备份的数据信息也传送到备份数据库中
file_instances.frm 100% 8654 8.5KB/s 00:00
file_summary_by_event_name.frm 100% 8800 8.6KB/s 00:00
file_summary_by_instance.frm 100% 8840 8.6KB/s 00:00
mutex_instances.frm 100% 8684 8.5KB/s 00:00
performance_timers.frm 100% 8776 8.6KB/s 00:00
rwlock_instances.frm 100% 8758 8.6KB/s 00:00
setup_consumers.frm 100% 8605 8.4KB/s 00:00
setup_instruments.frm 100% 8637 8.4KB/s 00:00
setup_timers.frm 100% 8650 8.5KB/s 00:00
threads.frm 100% 8650 8.5KB/s 00:00
xtrabackup_binlog_info 100% 22 0.0KB/s 00:00
xtrabackup_checkpoints 100% 117 0.1KB/s 00:00
backup-my.cnf 100% 386 0.4KB/s 00:00
xtrabackup_info 100% 518 0.5KB/s 00:00
备份数据库，进行恢复。注意先关掉数据库。
[root@centos7 mysql]# rm -rf *
[root@centos7 mysql]# ls
[root@centos7 mysql]# systemctl stop mariadb
[root@centos7 ~]# cat /backups/2019-12-02_00-37-08/xtrabackup_checkpoints
查看备份数据集的所在位置。
backup_type = log-applied
from_lsn = 0
to_lsn = 1623336
last_lsn = 1623336
compact = 0
recover_binlog_info = 0
[root@centos7 ~]# cat /backups/2019-12-02_00-45-12/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 1623336
to_lsn = 1624540
last_lsn = 1624540
compact = 0
recover_binlog_info = 0
[root@centos7 ~]# innobackupex --apply-log --redo-only /backups/2019-12-02_00-37-08/
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, size = 100.0M
InnoDB: Completed initialization of buffer pool
InnoDB: Highest supported file format is Barracuda.
InnoDB: The log sequence numbers 0 and 0 in ibdata files do not match the log sequence number 1623336 in the ib_logfiles!
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages
InnoDB: from the doublewrite buffer...
xtrabackup: Last MySQL binlog file position 4661, file name ./mysql-bin.000003
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1623336
191203 01:59:10 completed OK!
[root@centos7 ~]# innobackupex --apply-log --redo-only /backups/2019-12-02_00-37-08/ --incremental-dir=/backups/2019-12-02_00-45-12/
将增量备份的信息，整理到全量备份中，进行全部恢复至数据文件。
191203 02:18:20 [01] ...done
191203 02:18:20 [01] Copying /backups/2019-12-02_00-45-12/performance_schema/mutex_instances.frm to ./performance_schema/mutex_instances.frm
191203 02:18:20 [01] ...done
191203 02:18:20 [01] Copying /backups/2019-12-02_00-45-12/performance_schema/performance_timers.frm to ./performance_schema/performance_timers.frm
191203 02:18:20 [01] ...done
191203 02:18:20 [01] Copying /backups/2019-12-02_00-45-12/performance_schema/rwlock_instances.frm to ./performance_schema/rwlock_instances.frm
191203 02:18:20 [01] ...done
191203 02:18:20 [01] Copying /backups/2019-12-02_00-45-12/performance_schema/setup_consumers.frm to ./performance_schema/setup_consumers.frm
191203 02:18:20 [01] ...done
191203 02:18:20 [01] Copying /backups/2019-12-02_00-45-12/performance_schema/setup_instruments.frm to ./performance_schema/setup_instruments.frm
191203 02:18:20 [01] ...done
191203 02:18:20 [01] Copying /backups/2019-12-02_00-45-12/performance_schema/setup_timers.frm to ./performance_schema/setup_timers.frm
191203 02:18:20 [01] ...done
191203 02:18:20 [01] Copying /backups/2019-12-02_00-45-12/performance_schema/threads.frm to ./performance_schema/threads.frm
191203 02:18:20 [01] ...done
191203 02:18:20 [00] Copying /backups/2019-12-02_00-45-12//xtrabackup_binlog_info to ./xtrabackup_binlog_info
191203 02:18:20 [00] ...done
191203 02:18:20 [00] Copying /backups/2019-12-02_00-45-12//xtrabackup_info to ./xtrabackup_info
191203 02:18:20 [00] ...done
191203 02:18:20 completed OK!
在从库进行数据文件恢复
[root@centos7 ~]# cat /backups/2019-12-02_00-37-08/xtrabackup_checkpoints
现在查看全量备份数据已经赶上增量了的数值了
backup_type = log-applied
from_lsn = 0
to_lsn = 1624540
last_lsn = 1624540
compact = 0
recover_binlog_info = 0
[root@centos7 ~]# cat /backups/2019-12-02_00-45-12/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 1623336
to_lsn = 1624540
last_lsn = 1624540
compact = 0
recover_binlog_info = 0
[root@centos7 backups]# innobackupex --copy-back /backups/2019-12-02_00-37-08/
还原全量备份。切记恢复时关闭数据库，删除原有数据文件
191203 02:38:00 innobackupex: Starting the copy-back operation
IMPORTANT: Please check that the copy-back run completes successfully.
At the end of a successful copy-back run innobackupex
prints "completed OK!".
innobackupex version 2.3.6 based on MySQL server 5.6.24 Linux (x86_64) (revision id: )
Original data directory /var/lib/mysql is not empty!
[root@centos7 mysql]# ll
total 18448
drwx------ 2 root root 176 Dec 3 02:39 hellodb
-rw-r----- 1 root root 18874368 Dec 3 02:39 ibdata1
drwx------ 2 root root 4096 Dec 3 02:39 mysql
drwx------ 2 root root 4096 Dec 3 02:39 performance_schema
drwx------ 2 root root 20 Dec 3 02:39 test
-rw-r----- 1 root root 24 Dec 3 02:39 xtrabackup_binlog_pos_innodb
-rw-r----- 1 root root 518 Dec 3 02:39 xtrabackup_info
[root@centos7 mysql]#
[root@centos7 mysql]# chown -R mysql:mysql ./* 修改成mysql用户，否则数据库启动不了
[root@centos7 mysql]# ll
total 18448
drwx------ 2 mysql mysql 176 Dec 3 02:39 hellodb
-rw-r----- 1 mysql mysql 18874368 Dec 3 02:39 ibdata1
drwx------ 2 mysql mysql 4096 Dec 3 02:39 mysql
drwx------ 2 mysql mysql 4096 Dec 3 02:39 performance_schema
drwx------ 2 mysql mysql 20 Dec 3 02:39 test
-rw-r----- 1 mysql mysql 24 Dec 3 02:39 xtrabackup_binlog_pos_innodb
-rw-r----- 1 mysql mysql 518 Dec 3 02:39 xtrabackup_info
[root@centos7 mysql]#
[root@centos7 mysql]# systemctl restart mariadb
fs[root@centos7 mysql]#
[root@centos7 mysql]#
[root@centos7 mysql]#
[root@centos7 mysql]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
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)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
MariaDB [(none)]> use hellodb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [hellodb]> select * from students;
+-------+---------------+------+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+------+--------+---------+-----------+
| 1 | shi zhongyu | 22 | M | 2 | 3 |
| 2 | shi potian | 22 | M | 1 | 7 |
| 3 | xie yangke | 53 | M | 2 | 16 |
| 4 | ding dian | 32 | M | 4 | 4 |
| 5 | yu yutong | 26 | M | 3 | 1 |
| 6 | shi qing | 46 | M | 5 | 5 |
| 7 | xie ren | 19 | F | 3 | NULL |
| 8 | lin daiyu | 17 | F | 7 | NULL |
| 9 | ren yingying | 20 | F | 6 | NULL |
| 10 | yue lingshan | 19 | F | 3 | NULL |
| 11 | yuan chengzhi | 23 | M | 6 | NULL |
| 12 | wei qingqing | 19 | F | 1 | NULL |
| 13 | tian boguang | 33 | M | 2 | NULL |
| 14 | lu wushuang | 17 | F | 3 | NULL |
| 15 | duan yu | 19 | M | 4 | NULL |
| 16 | xu zhu | 21 | M | 1 | NULL |
| 17 | lin chong | 25 | M | 4 | NULL |
| 18 | hua rong | 23 | M | 7 | NULL |
| 19 | xue baochai | 18 | F | 6 | NULL |
| 20 | huang yueying | 19 | F | 7 | NULL |
| 21 | diao chan | 22 | F | 6 | NULL |
| 22 | xiao qiao | 20 | F | 1 | NULL |
| 23 | ma chao | 23 | M | 4 | NULL |
| 24 | xu xian | 27 | M | NULL | NULL |
| 25 | su dasheng | 200 | M | NULL | NULL |
+-------+---------------+------+--------+---------+-----------+
25 rows in set (0.00 sec)
查看数据信息已经恢复完成了
扫一扫
2368
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
