4.★★★★备份和恢复★★★★
4.1备份恢复概述
4.1.1为什么要备份
灾难恢复：硬件故障、软件故障、自然灾害、黑客攻击、误操作测试等数据丢失场景
参考链接: https://www.toutiao.com/a6939518201961251359/
4.1.2备份类型
1、完全备份，部分备份
完全备份：整个数据集
部分备份：只备份数据子集，如部分库或表
2、完全备份、增量备份、差异备份
增量备份：仅备份最近一次完全备份或增量备份（如果存在增量）以来变化的数据，备份较快，还原复杂
差异备份：仅备份最近一次完全备份以来变化的数据，备份较慢，还原简单
3、冷、温、热备份
冷备：读、写操作均不可进行，数据库停止服务
温备：读操作可执行；但写操作不可执行
热备：读、写操作均可执行
MyISAM：温备，不支持热备
InnoDB：都支持
4、物理和逻辑备份
物理备份：直接复制数据文件进行备份，与存储引擎有关，占用较多的空间，速度快
逻辑备份：从数据库中”导出”数据另存而进行的备份，与存储引擎无关，占用空间少，速度慢，可能丢失精度
4.1.3备份什么
- 数据
- 二进制日志、InnoDB的事务日志
- 用户帐号，权限设置，程序代码（存储过程、函数、触发器、事件调度器）
- 服务器的配置文件
4.1.4 备份注意要点
- 能容忍最多丢失多少数据
- 备份产生的负载
- 备份过程的时长
- 温备的持锁多久
- 恢复数据需要在多长时间内完成
- 需要备份和恢复哪些数据
4.1.5 还原要点
- 做还原测试，用于测试备份的可用性（血的教训备份不测试，真的需要还原了备份数据有问题服务器起不来）
- 还原演练，写成规范的技术文档
4.1.6 备份工具
- cp, tar等复制归档工具：物理备份工具，适用所有存储引擎；只支持冷备；完全和部分备份
- LVM的快照：先加读锁，做快照后解锁，几乎热备；借助文件系统工具进行备份
- mysqldump：逻辑备份工具，适用所有存储引擎，对MyISAM存储引擎进行温备；支持完全或部分备份；对InnoDB存储引擎支持热备，结合binlog的增量备份
- xtrabackup：由Percona提供支持对InnoDB做热备(物理备份)的工具，支持完全备份、增量备份
- MariaDB Backup： 从MariaDB 10.1.26开始集成，基于Percona XtraBackup 2.3.8实现
- mysqlbackup：热备份， MySQL Enterprise Edition 组件
- mysqlhotcopy：PERL 语言实现，几乎冷备，仅适用于MyISAM存储引擎，使用LOCK TABLES、FLUSH TABLES和cp或scp来快速备份数据库
4.1.7实战案例：数据库冷备份和还原
MySQL8.0
#备份过程
[root@centos8 ~]#systemctl stop mysqld
#备份数据
[root@centos8 ~]#rsync -a /var/lib/mysql 10.0.0.28:/data/
#如果配置及二进制文件相关有特殊设置也需要备份
#还原
[root@centos8 ~]#yum -y install mysql-server
[root@centos8 ~]#cp -a /data/mysql/* /var/lib/mysql/
[root@centos8 ~]#systemctl start mysqld
MariaDB10.3
#数据库服务器为10.0.0.8，备份服务器为10.0.0.11
#查看数据库
MariaDB [hellodb]> show databases;
+--------------------+
| Database |
+--------------------+
| hellodb |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.000 sec)
#对hellodb中的teacher表进行修改
MariaDB [hellodb]> insert teachers values(null,'test',66,'M');
Query OK, 1 row affected (0.001 sec)
MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | test | 66 | M |
+-----+---------------+-----+--------+
5 rows in set (0.000 sec)
#停服务
[root@localhost ~]#systemctl stop mariadb.service
#复制相关文件
[root@localhost ~]#scp -r /var/lib/mysql/* 10.0.0.11:/var/lib/mysql/
[root@localhost ~]#scp /etc/my.cnf.d/mariadb-server.cnf 10.0.0.11:/etc/my.cnf.d/
[root@localhost ~]#scp -r /data/mysql/ 10.0.0.11:/data/ #10.0.0.11须事先存在/data/目录
#复制相关文件并保留属性：可以用rsync
[root@localhost ~]#rsync /etc/my.cnf.d/mariadb-server.cnf 10.0.0.18:/etc/my.cnf.d/
[root@localhost ~]#rsync -av /var/lib/mysql/ 10.0.0.18:/var/lib/mysql/
[root@localhost ~]#rsync -av/data/logbin/ 10.0.0.18:/data/ #10.0.0.11 须事先存在/data/目录
#在目标主机（10.0.0.11）执行
[root@localhost ~]#chown -R mysql.mysql /var/lib/mysql/
[root@localhost ~]#chown -R mysql.mysql /data/logbin/
[root@localhost ~]#systemctl start mariadb
#测试服务器进行测试
#启动服务
[root@localhost ~]#systemctl start mariadb
[root@localhost ~]#mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.28-MariaDB-log 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 |
+--------------------+
| hellodb |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.001 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 teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | test | 66 | M |
+-----+---------------+-----+--------+
5 rows in set (0.000 sec)
数据成功备份
4.2mysqldump备份工具
4.2.1mysqldump说明
逻辑备份工具：
mysqldump, mydumper, phpMyAdmin
Schema和数据存储在一起、巨大的SQL语句、单个巨大的备份文件
mysqldump是MySQL的客户端命令，通过mysql协议连接至mysql服务器进行备份
命令格式:
mysqldump [OPTIONS] database [tables] #支持指定数据库和指定多表的备份，但数据库本身定义不备份
mysqldump [OPTIONS] -B DB1 [DB2 DB3...] #支持指定数据库备份，包含数据库本身定义也会备份
mysqldump [OPTIONS] -A [OPTIONS] #备份所有数据库，包含数据库本身定义也会备份
mysqldump参考：
https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html
mysqldump 常见通用选项：
-A, --all-databases #备份所有数据库，含create database
-B, --databases db_name… #指定备份的数据库，包括create database语句
-E, --events： #备份相关的所有event scheduler
-R, --routines： #备份所有存储过程和自定义函数
--triggers： #备份表相关触发器，默认启用,用--skip-triggers，不备份触发器
--default-character-set=utf8 #指定字符集
--master-data[=#]： #此选项须启用二进制日志★★★★★★（在5.2.3.3最后有介绍此命令的好处）
#1：所备份的数据之前加一条记录为CHANGE MASTER TO语句，非注释，不指定#，默认为1，适合于主从复制多机使用
#2：记录为被注释的#CHANGE MASTER TO语句，适合于单机使用,适用于备份还原
#此选项会自动关闭--lock-tables功能，自动打开-x | --lock-all-tables功能（除非开启--single-transaction）
-F, --flush-logs #备份前滚动日志，锁定表完成后，执行flush logs命令,生成新的二进制日志文件，配合-A 或 -B 选项时，会导致刷新多次数据库。建议在同一时刻执行转储和日志刷新，可通过和--single-transaction或-x，--master-data 一起使用实现，此时只刷新一次二进制日志
--compact #去掉注释，适合调试，节约备份占用的空间,生产不使用
-d, --no-data #只备份表结构,不备份数据,即只备份create table
-t, --no-create-info #只备份数据,不备份表结构,即不备份create table
-n,--no-create-db #不备份create database，可被-A或-B覆盖
--flush-privileges #备份mysql或相关时需要使用
-f, --force #忽略SQL错误，继续执行
--hex-blob #使用十六进制符号转储二进制列，当有包括BINARY， VARBINARY，BLOB，BIT的数据类型的列时使用，避免乱码
-q, --quick #不缓存查询，直接输出，加快备份速度
mysqldump的MyISAM存储引擎相关的备份选项：
MyISAM不支持事务，只能支持温备；不支持热备，所以必须先锁定要备份的库，而后启动备份操作
-x,--lock-all-tables #加全局读锁，锁定所有库的所有表，同时加--single-transaction或--lock-tables选项会关闭此选项功能，注意：数据量大时，可能会导致长时间无法并发访问数据库
-l,--lock-tables #对于需要备份的每个数据库，在启动备份之前分别锁定其所有表，默认为on,--skip-lock-tables选项可禁用,对备份MyISAM的多个库,可能会造成数据不一致
#注：以上选项对InnoDB表一样生效，实现温备，但不推荐使用
mysqldump的InnoDB存储引擎相关的备份选项：
InnoDB 存储引擎支持事务,可以利用事务的相应的隔离级别,实现热备，也可以实现温备但不建议用
--single-transaction
#此选项Innodb中推荐使用，不适用MyISAM，此选项会开始备份前，先执行START TRANSACTION指令开启事务
#此选项通过在单个事务中转储所有表来创建一致的快照。 仅适用于存储在支持多版本控制的存储引擎中的表（目前只有InnoDB可以）; 转储不保证与其他存储引擎保持一致。 在进行单事务转储时，要确保有效的转储文件（正确的表内容和二进制日志位置），没有其他连接应该使用以下语句：ALTER TABLE，DROP TABLE，RENAME TABLE，TRUNCATE TABLE,此选项和--lock-tables（此选项隐含提交挂起的事务）选项是相互排斥,备份大型表时，建议将--single-transaction选项和--quick结合一起使用
4.2.2生产环境实战备份策略
InnoDB建议备份策略
mysqldump -uroot -p -A -F -E -R --triggers --single-transaction --master-data=1 --flush-privileges --default-character-set=utf8 --hex-blob >${BACKUP}/fullbak_${BACKUP_TIME}.sql
MyISAM建议备份策略
mysqldump -uroot -p -A -F -E -R -x --master-data=1 --flush-privileges --triggers --default-character-set=utf8 --hex-blob >${BACKUP}/fullbak_${BACKUP_TIME}.sql
4.2.3mysqldump 备份还原实战案例
5.2.3.1 实战案例：特定数据库的备份（方式一，不推荐）
#备份hellodb数据库
[root@localhost ~]#mysqldump -uroot -p'sunxiang' hellodb > /data/hellodb.sql
#查看一下备份文件
[root@localhost ~]#grep -i create /data/hellodb.sql
CREATE TABLE `classes` (
CREATE TABLE `coc` (
CREATE TABLE `courses` (
CREATE TABLE `scores` (
CREATE TABLE `students` (
CREATE TABLE `teachers` (
CREATE TABLE `toc` (
##发现没有关于生成数据库的命令，都是创建表的信息
#删除数据库
MariaDB [(none)]> drop database hellodb;
Query OK, 7 rows affected (0.020 sec)
#通过备份文件导入数据库
MariaDB [(none)]> source /data/hellodb.sql; #粘贴了一部分但是总的来说就是有报错无法成功导入
Query OK, 0 rows affected (0.000 sec)
ERROR 1046 (3D000) at line 191 in file: '/data/hellodb.sql': No database selected
ERROR 1046 (3D000) at line 192 in file: '/data/hellodb.sql': No database selected
ERROR 1046 (3D000) at line 193 in file: '/data/hellodb.sql': No database selected
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.000 sec)
#手动创建数据库（可以与原理不同名）
MariaDB [(none)]> create database hellodbtest;
Query OK, 1 row affected (0.000 sec)
#进入数据库中
MariaDB [(none)]> use hellodbtest
Database changed
#导数数据
MariaDB [hellodbtest]> source /data/hellodb.sql;
Query OK, 0 rows affected (0.000 sec)
Query OK, 0 rows affected (0.000 sec)
Query OK, 0 rows affected (0.000 sec)
#查看表信息
MariaDB [hellodbtest]> show tables;
+-----------------------+
| Tables_in_hellodbtest |
+-----------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-----------------------+
7 rows in set (0.000 sec)
MariaDB [hellodbtest]> select *from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | test | 66 | M |
+-----+---------------+-----+--------+
5 rows in set (0.000 sec)
##貌似没有什么问题，但是手工创建数据库的时候我们不知道原始数据指定的规则字符集等，此时如果手动创建数据库导入数据很可能出现问题所以在生产中尽量避免使用
5.2.3.2 实战案例：特定数据库的备份（方式二，推荐）
#通过-B选项备份特定数据库
[root@localhost ~]#mysqldump -uroot -p'sunxiang' -B hellodbtest > /data/hellodb_B.sql
#查看备份文件中关于创建的信息
[root@localhost ~]#grep -i create /data/hellodb_B.sql
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `hellodbtest` /*!40100 DEFAULT CHARACTER SET latin1 */;
CREATE TABLE `classes` (
CREATE TABLE `coc` (
CREATE TABLE `courses` (
CREATE TABLE `scores` (
CREATE TABLE `students` (
CREATE TABLE `teachers` (
CREATE TABLE `toc` (
##此时备份文件中有创建数据库的信息无需我们手动创建了
#删除数据库
[root@localhost ~]#mysql -uroot -p'sunxiang' -e 'drop database hellodbtest'
[root@localhost ~]#mysql -uroot -p'sunxiang' -e 'show databases'
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
#倒入备份的数据库
[root@localhost ~]#mysql -uroot -p'sunxiang' < /data/hellodb_B.sql
[root@localhost ~]#mysql -uroot -p'sunxiang' -e 'show databases'
+--------------------+
| Database |
+--------------------+
| hellodbtest |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
[root@localhost ~]#
##成功还原
[root@localhost ~]#mysql -uroot -p'sunxiang' -e 'use hellodbtest;select *from teachers'
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | test | 66 | M |
+-----+---------------+-----+--------+
5.2.3.3 实战案例：分库备份并压缩
[root@centos8 ~]#for db in `mysql -uroot -e 'show databases'|grep -Ev '^(Database|information_schema|performance_schema)$'`;do mysqldump -B $db | gzip > /backup/$db.sql.gz;done
[root@centos8 ~]#mysql -uroot -e 'show databases'|grep -Ev '^(Database|information_schema|performance_schema)$'|while read db;do mysqldump -B $db | gzip > /backup/$db.sql.gz;done
[root@centos8 ~]#mysql -uroot -e 'show databases'|grep -Ev '^(Database|information_schema|performance_schema)$' | sed -rn 's#(.*)#mysqldump -B \1 | gzip > /backup/\1.sql.gz#p' |bash
[root@centos8 ~]#mysql -uroot -e 'show databases'|sed -rn '/^(Database|information_schema|performance_schema)$/!s#(.*)#mysqldump -B \1 | gzip > /backup/\1.sql.gz#p' |bash
5.2.3.4 实战案例：所有数据库的备份
#备份所有数据库
[root@localhost ~]#mysqldump -uroot -p'sunxiang' -A > /data/all.sql
#查看备份文件中关于创建数据库的信息
[root@localhost ~]#grep -i "^create database" /data/all.sql
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `hellodbtest` /*!40100 DEFAULT CHARACTER SET latin1 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysql` /*!40100 DEFAULT CHARACTER SET latin1 */;
#查看服务器上存在的数据库
[root@localhost ~]#mysql -uroot -p'sunxiang' -e 'show databases;'
+--------------------+
| Database |
+--------------------+
| hellodbtest |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
##发现实际存在四个库，实际只备份了两个库，一个是数据库，一个是存放用户信息的库。
#停服务
[root@localhost ~]#systemctl stop mariadb.service
#删库
[root@localhost ~]#rm -rf /var/lib/mysql/*
#重启服务（类比重装服务然后启动）
[root@localhost ~]#systemctl restart mariadb.service
#导入备份数据库（发现指定账号密码反而会报错）
[root@localhost ~]#mysql -uroot -p'sunxiang' < /data/all.sql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@localhost ~]#mysql < /data/all.sql
#连接数据库（输入用户名密码报错）
[root@localhost ~]#mysql -uroot -p'sunxiang'
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@localhost ~]#mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 10.3.28-MariaDB-log 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 |
+--------------------+
| hellodbtest |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.000 sec)
#查看user表也有用户信息，但是就是无法正常通过用户名密码登录
MariaDB [(none)]> select user,host,password from mysql.user;
+------+------------------------+-------------------------------------------+
| user | host | password |
+------+------------------------+-------------------------------------------+
| root | localhost | *366428608497F2CF536AE0D5612599507BDB5AFB |
| root | localhost.localdomain | |
| root | 127.0.0.1 | |
| root | ::1 | |
| test | 10.0.0.0/255.255.255.0 | *366428608497F2CF536AE0D5612599507BDB5AFB |
+------+------------------------+-------------------------------------------+
5 rows in set (0.000 sec)
#刷新权限即可解决
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> Bye
[root@localhost ~]#mysql -utest -p'sunxiang' -h'10.0.0.8'
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 13
Server version: 10.3.28-MariaDB-log 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 |
+--------------------+
| hellodbtest |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.001 sec)
MariaDB [(none)]>
#也从侧面反映了一个问题，在恢复数据库的时候初次登录时免密码的，这就存在安全隐患了，所以可以在恢复的时候在配置文件中添加--skip-networking此字段使得数据库只能由指定的主机进行连接
目前看着好像很nice，但是有一个问题就是数据库的备份不可能每分每秒都在备份，如果在凌晨一点进行备份，上午十点删库了，这九个小时的数据就会丢失，因为完全备份并没有这部分的内容，此时就需要用到之前说的二进制日志了，通过mysqldump的--master-data参数可以为我们备份的数据库打上标签，通过这个标签就可以知道我们备份时的二进制日志的时间，之后就可以通过二进制日志加上记录的时间点通过mysqlbinlog将时间点之后的日志导出来然后就可以进行恢复了
5.2.3.5实战案例：利用二进制日志，恢复到当前时间数据库的最新状态
#备份数据库加上 --master-data=2参数（2用于数据恢复）
[root@localhost ~]#mysqldump -uroot -p'sunxiang' -A --master-data=2 > /data/all.sql
[root@localhost ~]#vim /data/all.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=490677;
#备份完成后在添加一条数据
MariaDB [hellodbtest]>insert teachers values(null,'haha',77,'F');
MariaDB [hellodbtest]> select *from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | test | 66 | M |
| 6 | haha | 77 | F |
+-----+---------------+-----+--------+
6 rows in set (0.001 sec)
#模拟删库
[root@localhost ~]#systemctl stop mariadb.service
[root@localhost ~]#rm -rf /var/lib/mysql/*
#将二进制日志时间点之后的内容导出（注意此时不要启动数据库！！）
[root@localhost /data/mysql]#mysqlbinlog --start-position=490677 mysql-bin.000006 > /data/mysql/binlog.sql
#查看导出后的文件
[root@localhost /data/mysql]#vim binlog.sql
insert teachers values(null,'haha',77,'F') #之前添加用户信息的记录
#开启服务，登录数据库
[root@localhost /data/mysql]#systemctl start mariadb.service
[root@localhost ~]#mysql
#关闭二进制日志，防止在恢复的过程中记录二进制日志
MariaDB [(none)]> select @@sql_log_bin;
+---------------+
| @@sql_log_bin |
+---------------+
| 1 |
+---------------+
1 row in set (0.000 sec)
MariaDB [(none)]> set sql_log_bin=0;
Query OK, 0 rows affected (0.000 sec)
#数据恢复
#导入完全备份的文件
MariaDB [(none)]> source /data/all.sql;
#查看数据库信息
MariaDB [mysql]> use hellodbtest;select *from teachers;
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
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | test | 66 | M |
+-----+---------------+-----+--------+
5 rows in set (0.000 sec)
#导入二进制文件的备份数据
MariaDB [hellodbtest]> source /data/mysql/binlog.sql
#再次查看数据库的信息
MariaDB [hellodbtest]> select *from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | test | 66 | M |
| 6 | haha | 77 | F |
+-----+---------------+-----+--------+
6 rows in set (0.000 sec)
##成功还原到最新状态
#刷新一下权限
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.001 sec)
#恢复完成之后开启二进制日志
MariaDB [hellodbtest]> set sql_log_bin=1;
Query OK, 0 rows affected (0.000 sec)
5.2.3.6 实战案例：mysqldump 和二进制日志结合实现差异(增量)备份
[root@centos8 ~]#mysqldump -uroot -p -A -F --single-transaction --flush-privileges --master-data=2 |gzip > /backup/all-`date +%F`.sql.gz
#观察上面备份文件中记录的二进制文件和位置，定期将其之后生成的所有二进制日志进行复制备份
[root@centos8 ~]#cp /var/lib/mysql/mariadb-bin.000003 /backup #假设mariadb-bin.000003是后续生成的二进制日志
[root@centos8 ~]#mysqlbinlog backup/mariadb-bin.000003 > /backup/inc.sql
5.2.3.7 实战案例：恢复误删除的表
案例说明：每天2：30做完全备份，早上10：00误删除了表teachers，10：10才发现故障，现需要将数
据库还原到10：10的状态，且恢复被删除的students表
#完全备份
[root@localhost /data/mysql]#mysqldump -uroot -p'sunxiang' -A -F --single-transaction --flush-privileges --master-data=1 | gzip > /data/all-`date +%F`.sql.gz
[root@localhost ~]#ll /data/all-2021-12-10.sql.gz
-rw-r--r-- 1 root root 137832 Dec 10 09:50 /data/all-2021-12-10.sql.gz
#修改teachers表，并删库（模拟十点）
MariaDB [hellodbtest]> insert teachers values(null,'lala',80,'F');
Query OK, 1 row affected (0.003 sec)
MariaDB [hellodbtest]> select *from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | test | 66 | M |
| 6 | haha | 77 | F |
| 7 | lala | 80 | F |
+-----+---------------+-----+--------+
7 rows in set (0.000 sec)
MariaDB [hellodbtest]> drop table teachers;
Query OK, 0 rows affected (0.002 sec)
#修改students表（模拟十点十分）
MariaDB [hellodbtest]> insert students values(null,'test','18','F',1,8);
Query OK, 1 row affected (0.001 sec)
MariaDB [hellodbtest]> select *from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
| 26 | test | 18 | F | 1 | 8 |
+-------+---------------+-----+--------+---------+-----------+
26 rows in set (0.000 sec)
##发现teachers表被删除了，进行恢复
#停服务（模拟发通知进行维护）
[root@localhost ~]#systemctl stop mariadb.service
#解压压缩的完全备份文件
[root@localhost ~]#gzip -d /data/all-2021-12-10.sql.gz
#将二进制日志文件导出 因为加了-F参数所以会在备份之后重新生成二进制日志文件，所以10及其之后的都要进行导出，这里只有10
[root@localhost ~]#mysqlbinlog /data/mysql/mysql-bin.000010 > /data/mysql/binlog1.sql
#注意：真实的生产环境中用vim之前要三思，防止文件过大直接搞死机了，所以看看文件有多上号
[root@localhost ~]#wc -l /data/mysql/binlog1.sql
90 /data/mysql/binlog1.sql
#可以通过vim进去修改（我这里使用直接删除）
[root@localhost ~]#vim /data/mysql/binlog1.sql
#过滤一下drop的语句（发现有一条删表的操作）
[root@localhost ~]#grep -i 'drop table' /data/mysql/binlog1.sql
DROP TABLE `teachers` /* generated by server */
#删除掉drop语句
[root@localhost ~]#sed -i.bak '/^DROP TABLE/d' /data/mysql/binlog1.sql
#再次过滤查看确认一下
[root@localhost ~]#grep -i 'drop table' /data/mysql/binlog1.sql
#启动数据库服务
[root@localhost ~]#systemctl start mariadb.service
#登录数据库
[root@localhost ~]#mysql -utest -p'sunxiang' -h'10.0.0.8'
#关闭二进制日志
MariaDB [hellodbtest]> set sql_log_bin=0; #会话下关闭，别的客户端或者退出之后再次登录还是开启的
Query OK, 0 rows affected (0.000 sec)
#导入完全备份的数据
MariaDB [hellodbtest]> source /data/all-2021-12-10.sql;
#老师表恢复
MariaDB [hellodbtest]> select *from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | test | 66 | M |
| 6 | haha | 77 | F |
| 7 | lala | 80 | F |
+-----+---------------+-----+--------+
7 rows in set (0.000 sec)
#学生表添加的信息还没有同步
MariaDB [hellodbtest]> select *from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.000 sec)
#导入二进制日志
MariaDB [hellodbtest]> source /data/mysql/binlog1.sql
#查看学生表，学生表已经恢复
MariaDB [hellodbtest]> select *from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
| 26 | test | 18 | F | 1 | 8 |
+-------+---------------+-----+--------+---------+-----------+
26 rows in set (0.000 sec)
##至此恢复完成，通知用户维护完成
4.3xtrabackup 备份工具
4.3.1 xtrabackup 工具介绍
Percona 公司
官网：www.percona.com
percona-server
InnoDB –> XtraDB
Xtrabackup备份工具
percona提供的mysql数据库备份工具，惟一开源的能够对innodb和xtradb数据库进行热备的工具
手册：https://www.percona.com/doc/percona-xtrabackup/LATEST/index.html
下载: https://www.percona.com/downloads/
xtrabackup 特点：
- 备份还原过程快速、可靠
- 备份过程不会打断正在执行的事务
- 能够基于压缩等功能节约磁盘空间和流量
- 自动实现备份检验
- 开源，免费
xtrabackup工具文件组成
Xtrabackup2.2 版之前包括4个可执行文件：
- innobackupex: Perl 脚本
- xtrabackup: C/C++，编译的二进制程序
- xbcrypt: 加解密
- xbstream: 支持并发写的流文件格式
说明:
xtrabackup 是用来备份 InnoDB 表的，不能备份非 InnoDB 表，和 MySQL Server 没有交互
innobackupex 脚本用来备份非 InnoDB 表，同时会调用 xtrabackup 命令来备份 InnoDB 表，还会和MySQL Server 发送命令进行交互，如加全局读锁（FTWRL）、获取位点（SHOW SLAVE STATUS）等。即innobackupex是在 xtrabackup 之上做了一层封装实现的
xtrabackup的新版变化
xtrabackup版本升级到2.4后，相比之前的2.1有了比较大的变化：innobackupex 功能全部集成到xtrabackup 里面，只有一个 binary程序，另外为了兼容考虑，innobackupex作为 xtrabackup 的软链接，即xtrabackup现在支持非Innodb表备份，并且 Innobackupex 在下一版本中移除，建议通过xtrabackup替换innobackupex
xtrabackup备份过程
参考文章https://zhuanlan.zhihu.com/p/415703617
备份生成的相关文件
使用innobackupex备份时，其会调用xtrabackup备份所有的InnoDB表，复制所有关于表结构定义的相关文件(.frm)、以及MyISAM、MERGE、CSV和ARCHIVE表的相关文件，同时还会备份触发器和数据库配置信息相关的文件。这些文件会被保存至一个以时间命名的目录中,在备份时，innobackupex还会在备份目录中创建如下文件：
- xtrabackup_info：文本文件，innobackupex工具执行时的相关信息，包括版本，备份选项，备份时长，备份LSN(log sequence number日志序列号)，BINLOG的位置
- xtrabackup_checkpoints：文本文件，备份类型（如完全或增量）、备份状态（如是否已经为prepared状态）和LSN范围信息,每个InnoDB页(通常为16k大小)都会包含一个日志序列号LSN。LSN是整个数据库系统的系统版本号，每个页面相关的LSN能够表明此页面最近是如何发生改变的
- xtrabackup_binlog_info：文本文件，MySQL服务器当前正在使用的二进制日志文件及至备份这一刻为止二进制日志事件的位置，可利用实现基于binlog的恢复
- backup-my.cnf：文本文件，备份命令用到的配置选项信息
- xtrabackup_logfile：备份生成的二进制日志文件
4.3.2xtrabackup 工具安装
在Centos7中的EPEL源中有提供但是版本较老，在Centos8中直接就没有提供
yum install percona-xtrabackup
[root@centos7 ~]#yum info percona-xtrabackupAvailable PackagesName : percona-xtrabackupArch : x86_64Version : 2.3.6Release : 1.el7Size : 4.6 MRepo : epel/7/x86_64Summary : Online backup for InnoDB/XtraDB in MySQL, Percona Server and MariaDBURL : http://www.percona.com/software/percona-xtrabackup/License : GPLv2Description : Online backup for InnoDB/XtraDB in MySQL, MariaDB and Percona Server.
官网下载rpm包本地安装
https://www.percona.com/downloads/XtraBackup/LATEST/ #最新版本https://www.percona.com/downloads #各个版本
[root@localhost ~]#wget https://downloads.percona.com/downloads/Percona-XtraBackup-LATEST/Percona-XtraBackup-8.0.26-18/binary/redhat/8/x86_64/percona-xtrabackup-80-8.0.26-18.1.el8.x86_64.rpm--2021-12-10 20:27:30-- https://downloads.percona.com/downloads/Percona-XtraBackup-LATEST/Percona-XtraBackup-8.0.26-18/binary/redhat/8/x86_64/percona-xtrabackup-80-8.0.26-18.1.el8.x86_64.rpmResolving downloads.percona.com (downloads.percona.com)... 162.220.4.222, 162.220.4.221, 74.121.199.231Connecting to downloads.percona.com (downloads.percona.com)|162.220.4.222|:443... connected.HTTP request sent, awaiting response... 200 OKLength: 14840344 (14M) [application/octet-stream]Saving to: ‘percona-xtrabackup-80-8.0.26-18.1.el8.x86_64.rpm.1’percona-xtrabackup-80- 97%[===========================> ] 13.78M 76.2KB/s in 3m 51s 2021-12-10 20:31:23 (61.0 KB/s) - Connection closed at byte 14452737. Retrying.--2021-12-10 20:31:24-- (try: 2) https://downloads.percona.com/downloads/Percona-XtraBackup-LATEST/Percona-XtraBackup-8.0.26-18/binary/redhat/8/x86_64/percona-xtrabackup-80-8.0.26-18.1.el8.x86_64.rpmConnecting to downloads.percona.com (downloads.percona.com)|162.220.4.222|:443... connected.HTTP request sent, awaiting response... 206 Partial ContentLength: 14840344 (14M), 387607 (379K) remaining [application/octet-stream]Saving to: ‘percona-xtrabackup-80-8.0.26-18.1.el8.x86_64.rpm.1’percona-xtrabackup-80- 100%[++++++++++++++++++++++++++++>] 14.15M 58.6KB/s in 6.5s 2021-12-10 20:31:32 (58.6 KB/s) - ‘percona-xtrabackup-80-8.0.26-18.1.el8.x86_64.rpm.1’ saved [14840344/14840344][root@localhost ~]#yum install -y percona-xtrabackup-80-8.0.26-18.1.el8.x86_64.rpmLast metadata expiration check: 0:05:28 ago on Fri 10 Dec 2021 08:26:46 PM CST.Dependencies resolved.============================================================================================ Package Architecture Version Repository Size============================================================================================Installing: percona-xtrabackup-80 x86_64 8.0.26-18.1.el8 @commandline 14 MTransaction Summary============================================================================================Install 1 PackageTotal size: 14 MInstalled size: 62 MDownloading Packages:Running transaction checkTransaction check succeeded.Running transaction testTransaction test succeeded.Running transaction Preparing : 1/1 Installing : percona-xtrabackup-80-8.0.26-18.1.el8.x86_64 1/1 Running scriptlet: percona-xtrabackup-80-8.0.26-18.1.el8.x86_64 1/1 Verifying : percona-xtrabackup-80-8.0.26-18.1.el8.x86_64 1/1 Installed products updated.Installed: percona-xtrabackup-80-8.0.26-18.1.el8.x86_64 Complete![root@localhost ~]#rpm -ql percona-xtrabackup-80-8.0.26-18.1.el8.x86_64 /usr/bin/xbcloud/usr/bin/xbcloud_osenv/usr/bin/xbcrypt/usr/bin/xbstream/usr/bin/xtrabackup/usr/lib/.build-id/usr/lib/.build-id/36/usr/lib/.build-id/36/ed258dd40f202b4c5911d0530c90c0462d5bf8/usr/lib/.build-id/74/usr/lib/.build-id/74/f9cfb42a16ea80a4ff14fb7c7399d60160c0eb/usr/lib/.build-id/7e/usr/lib/.build-id/7e/b128304d20088d35e71c569cb9e6ef866ef962/usr/lib/.build-id/9f/usr/lib/.build-id/9f/395d87678b2ca7473e08acf9cf76f5dec8153e/usr/lib/.build-id/b2/usr/lib/.build-id/b2/12bfbe1e4c4f8a2eac6c27825b4c10e72ca0c7/usr/lib/.build-id/b3/usr/lib/.build-id/b3/548ee561fcf4874d98d23922e0bf575145f80e/usr/lib/.build-id/ee/usr/lib/.build-id/ee/29e5044bfbd1137d539092671bd16b917d3d85/usr/lib64/xtrabackup/plugin/component_keyring_file.so/usr/lib64/xtrabackup/plugin/keyring_file.so/usr/lib64/xtrabackup/plugin/keyring_vault.so/usr/share/doc/percona-xtrabackup-80/usr/share/doc/percona-xtrabackup-80/LICENSE/usr/share/man/man1/xbcrypt.1.gz/usr/share/man/man1/xbstream.1.gz/usr/share/man/man1/xtrabackup.1.gz
4.3.3xtrabackup 用法
xtrabackup工具备份和还原，需要三步实现
- 备份：对数据库做完全或增量备份
-
预准备： 还原前，先对备份的数据，整理至一个临时目录
-
还原：将整理好的数据，复制回数据库目录中
xtrabackup 选项参考：
https://www.percona.com/doc/percona-xtrabackup/LATEST/genindex.html
备份：
innobackupex [option] BACKUP-ROOT-DIR
选项说明：
--user： #该选项表示备份账号--password： #该选项表示备份的密码--host： #该选项表示备份数据库的地址--databases： #该选项接受的参数为数据库名，如果要指定多个数据库，彼此间需要以空格隔开；如："xtra_test dba_test"，同时，在指定某数据库时，也可以只指定其中的某张表。如："mydatabase.mytable"。该选项对innodb引擎表无效，还是会备份所有innodb表--defaults-file： #该选项指定从哪个文件读取MySQL配置，必须放在命令行第一个选项位置--incremental： #该选项表示创建一个增量备份，需要指定--incremental-basedir--incremental-basedir：#该选项指定为前一次全备份或增量备份的目录，与--incremental同时使用--incremental-dir： #该选项表示还原时增量备份的目录--include=name： #指定表名，格式：databasename.tablename
Prepare预准备：
innobackupex --apply-log [option] BACKUP-DIR
选项说明：
--apply-log：#一般情况下,在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处理不一致状态。此选项作用是通过回滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态--use-memory：#和--apply-log选项一起使用，当prepare 备份时，做crash recovery分配的内存大小，单位字节，也可1MB,1M,1G,1GB等，推荐1G--export：#表示开启可导出单独的表之后再导入其他Mysql中--redo-only：#此选项在prepare base full backup，往其中合并增量备份时候使用，但不包括对最后一个增量备份的合并
还原：
innobackupex --copy-back [选项] BACKUP-DIRinnobackupex --move-back [选项] [--defaults-group=GROUP-NAME] BACKUP-DIR
选项说明：
--copy-back：#做数据恢复时将备份数据文件拷贝到MySQL服务器的datadir--move-back：#这个选项与--copy-back相似，唯一的区别是它不拷贝文件，而是移动文件到目的地。这个选项移除backup文件，用时候必须小心。使用场景：没有足够的磁盘空间同事保留数据文件和Backup副本--force-non-empty-directories #指定该参数时候，使得innobackupex --copy-back或--move-back选项转移文件到非空目录，已存在的文件不会被覆盖。如果--copy-back和--move-back文件需要从备份目录拷贝一个在datadir已经存在的文件，会报错失败
还原注意事项：
- datadir 目录必须为空。除非指定innobackupex –force-non-empty-directorires选项指定，否则–copy-back选项不会覆盖
- 在restore之前,必须shutdown MySQL实例，不能将一个运行中的实例restore到datadir目录中
- 由于文件属性会被保留，大部分情况下需要在启动实例之前将文件的属主改为mysql，这些文件将属于创建备份的用户, 执行chown -R mysql:mysql /data/mysql,以上需要在用户调用innobackupex之前完成
4.3.4 实战案例：利用 xtrabackup 实现完全备份及还原
注意：percona-xtrabackup-24-2.4.18-1.el8.x86_64.rpm不支持CentOS 8上的mariadb-10.3版本
4.3.4.1案例: 利用xtrabackup8.0 完全备份和还原MySQL8.0
原始服务器ip为10.0.0.11，目标服务器为10.0.0.8
##在原始主机上操作1）安装xtrabackup[root@localhost ~]#yum install -y percona-xtrabackup-80-8.0.26-18.1.el8.x86_64.rpm2）准备存放备份文件的目录[root@localhost ~]#mkdir /backup #父目录需要手动创建，子目录会自动shengc3）做完全备份[root@localhost ~]#xtrabackup -uroot -p'sunxiang' --backup --target-dir=/backup/all[root@localhost ~]#ll /backup/all/total 69680-rw-r----- 1 root root 475 Dec 10 21:29 backup-my.cnfdrwxr-x--- 2 root root 132 Dec 10 21:29 hellodb-rw-r----- 1 root root 3603 Dec 10 21:29 ib_buffer_pool-rw-r----- 1 root root 12582912 Dec 10 21:29 ibdata1drwxr-x--- 2 root root 143 Dec 10 21:29 mysql-rw-r----- 1 root root 156 Dec 10 21:29 mysql-bin.000002-rw-r----- 1 root root 29 Dec 10 21:29 mysql-bin.index-rw-r----- 1 root root 25165824 Dec 10 21:29 mysql.ibddrwxr-x--- 2 root root 8192 Dec 10 21:29 performance_schemadrwxr-x--- 2 root root 28 Dec 10 21:29 sys-rw-r----- 1 root root 16777216 Dec 10 21:29 undo_001-rw-r----- 1 root root 16777216 Dec 10 21:29 undo_002-rw-r----- 1 root root 21 Dec 10 21:29 xtrabackup_binlog_info-rw-r----- 1 root root 102 Dec 10 21:29 xtrabackup_checkpoints-rw-r----- 1 root root 471 Dec 10 21:29 xtrabackup_info-rw-r----- 1 root root 2560 Dec 10 21:29 xtrabackup_logfile-rw-r----- 1 root root 39 Dec 10 21:29 xtrabackup_tablespaces4）传输至目标服务器 #目标主机无需创建/backup目录,直接复制目录本身[root@localhost ~]#scp -r /backup/ 10.0.0.8:/backup##在目标主机上操作1）安装xtrabackup[root@localhost ~]#yum install -y percona-xtrabackup-80-8.0.26-18.1.el8.x86_64.rpm2）预准备[root@localhost ~]#xtrabackup --prepare --target-dir=/backup/all3）复制到数据库目录 #注意：数据库目录必须为空，MySQL服务不能启动[root@localhost ~]#xtrabackup --copy-back --target-dir=/backup/all4）还原属性[root@localhost ~]#chown -R mysql.mysql /var/lib/mysql5）启动服务[root@localhost ~]#systemctl restart mysqld.service 6）登录查看[root@localhost ~]#mysql -uroot -p'sunxiang'mysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 9Server version: 8.0.26 Source distributionCopyright (c) 2000, 2021, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show databases;+--------------------+| Database |+--------------------+| hellodb || information_schema || mysql || performance_schema || sys |+--------------------+5 rows in set (0.00 sec)
注意：两边的xtrabackup版本需要一致，尝试过不同版本直接GG
4.3.4.2案例：利用xtrabackup2.4完全备份及还原MySQL5.5和Mariadb5.5
1 安装xtrabackup包#先安装MySQL5.7和xtrabackup包[root@centos8 ~]#yum -y install percona-xtrabackup-24-2.4.20-1.el8.x86_64.rpm2 在原主机做完全备份到/backup[root@centos8 ~]#mkdir /backup[root@centos8 ~]#xtrabackup -uroot -pmagedu --backup --target-dir=/backup/base#目标主机无需创建/backup目录,直接复制目录本身[root@centos8 ~]#scp -r /backup/ 目标主机:/3 在目标主机上还原1）预准备：确保数据一致，提交完成的事务，回滚未完成的事务[root@centos8 ~]#yum -y install percona-xtrabackup-24-2.4.20-1.el8.x86_64.rpm[root@centos8 ~]#xtrabackup --prepare --target-dir=/backup/base2）复制到数据库目录注意：数据库目录必须为空，MySQL服务不能启动[root@centos8 ~]#xtrabackup --copy-back --target-dir=/backup/base3）还原属性[root@centos8 ~]#chown -R mysql:mysql /var/lib/mysql4）启动服务[root@centos8 ~]#service mysqld start
4.3.5实战案例：利用xtrabackup完全，增量备份及还原
4.3.5.1案例: 利用xtrabackup8.0 完全，增量备份及还原MySQL8.0
##原始服务器
#备份过程
1）安装xtrabackup
[root@localhost ~]#yum install -y percona-xtrabackup-80-8.0.26-18.1.el8.x86_64.rpm
2）准备存放备份文件的目录
[root@localhost ~]#mkdir /backup #父目录需要手动创建，子目录会自动shengc
3）做完全备份
[root@localhost ~]#xtrabackup -uroot -p'sunxiang' --backup --target-dir=/backup/all
4）修改数据
mysql> insert teachers values(null,'zhangsan',55,'M');
Query OK, 1 row affected (0.00 sec)
mysql> insert teachers values(null,'lisi',66,'M');
Query OK, 1 row affected (0.00 sec)
5）第一次增量备份
[root@localhost ~]#xtrabackup -uroot -p'sunxiang' --backup --target-dir=/backup/add1 --incremental-basedir=/backup/all #--target-dir=/backup/add1增量备份存放路径，--incremental-basedir=/backup/all上一次备份的路径
6）修改数据
mysql> insert teachers values(null,'wangmazi',77,'M');
Query OK, 1 row affected (0.02 sec)
7）第二次增量备份
[root@localhost ~]#xtrabackup -uroot -p'sunxiang' --backup --target-dir=/backup/add2 --incremental-basedir=/backup/add1 #-backup --target-dir=/backup/add2增量备份存放的路径， --incremental-basedir=/backup/add1上一次备份的路径
8）上传备份文件
[root@localhost ~]#scp -r /backup/* 10.0.0.8:/backup/
##目标服务器
#还原过程
1）预准备完全备份 #注意：最后一次增量备份之前的备份的还原都需要添加--apply-log-only 阻止回滚未完成的事务
[root@localhost ~]#xtrabackup --prepare --apply-log-only --target-dir=/backup/all
2）合并第一次增量备份到完全备份
[root@localhost ~]#xtrabackup --prepare --apply-log-only --target-dir=/backup/all --incremental-dir=/backup/add1
3）合并第二次增量备份到完全备份 #注意：这是最后一次增量备份还原所以需要开启回滚未完成的事务
[root@localhost ~]#xtrabackup --prepare --target-dir=/backup/all --incremental-dir=/backup/add2
4）复制到数据库目录 #注意：数据库目录必须为空。MySQL服务不能启动
[root@localhost ~]#xtrabackup --copy-back --target-dir=/backup/all
5）还原属性
[root@localhost ~]#chown -R mysql.mysql /var/lib/mysql
6）启动服务
[root@localhost ~]#systemctl restart mysqld.service
7）登录验证
[root@localhost ~]#mysql -uroot -p'sunxiang'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.26 Source distribution
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> 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
mysql> select *from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | zhangsan | 55 | M |
| 6 | lisi | 66 | M |
| 7 | wangmazi | 77 | M |
+-----+---------------+-----+--------+
7 rows in set (0.01 sec)
4.3.6实战案例：xtrabackup单表导出和导入
#导出
1 单表备份
innobackupex -uroot -pmagedu --include='hellodb.students' /backup
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;