君不见长松卧壑困风霜，时来屹立扶明堂。这篇文章主要讲述七备份恢复相关的知识，希望能为你提供帮助。
一、准备工作
cat > /etc/my.cnf < < EOF
[mysqld]
user=mysql
basedir=/data/mysql
datadir=/data/mysql/data
port=3306
socket=/tmp/mysql.sock
server_id=6
log_bin=/data/mysql/data/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
slow_query_log=1
slow_query_log_file=/data/mysql/data/slow.log
long_query_time=0.1
log_queries_not_using_indexes
#这个是设置安全目录，当使用into outfile导出时必须得设置
secure-file-priv=
[mysql]
socket=/tmp/mysql.sock
prompt=3306 [\\d]>
EOF
create database qingchen charset utf8mb4;
use qingchen
create table t1(id int);
insert into t1 values(1),(2),(3);
flush logs;
flush logs;
insert into t1 values(4);
insert into t1 values(5);
insert into t1 values(6);
show master status;
【七备份恢复】

文章图片

二、mysql备份
1、备份类型
热备 : 对于业务影响最小InnoDB在数据库正常业务时,备份数据,并且能够一致性恢复
温备 : 长时间锁表备份MyISAM锁表备份,只能查询不能修改
冷备 : 业务关闭情况下,数据库没有任何变更的情况下,进行备份数据
2、备份工具使用
(1)mysqldump
优点：
不需要下载安装
备份出来的是SQL，文本格式，可读性高,便于备份处理
压缩比较高，节省备份的磁盘空间
缺点：
依赖于数据库引擎，需要从磁盘把数据读出
然后转换成SQL进行转储，比较耗费资源，数据量大的话效率较低
建议：
100G以内的数据量级，可以使用mysqldump
超过TB以上，我们也可能选择的是mysqldump，配合分布式的系统
1EB=1024 PB =1000000 TB
(2)xtrabackup(XBK)
优点：
类似于直接cp数据文件，不需要管逻辑结构，相对来说性能较高
缺点：
可读性差
压缩比低，需要更多磁盘空间
建议：> 100G< TB
3、备份策略
备份方式：
全备:全库备份，备份所有数据
增量:备份变化的数据
逻辑备份=mysqldump+mysqlbinlog
物理备份=xtrabackup_full+xtrabackup_incr+binlog或者xtrabackup_full+binlog
备份周期:
根据数据量设计备份周期
比如：周日全备，周1-周6增量
4、基础备份
如果开了GTID,默认会在备份脚本开头备份GTID信息，--set-gtid-purged=OFF可以解决警告问题

文章图片

-A 全备
mysqldump -uroot -p\'1234\' -h 192.168.10.129 -P 3306 -A > /tmp/backup.sql
mysqldump -uroot -p\'1234\' -h 192.168.10.129 -P 3306 -A|gzip > /tmp/backup.sql.gz #同时进行压缩
压缩备份并添加时间戳
mysqldump -uroot -p1234 -A-R--triggers --master-data=https://www.songbingjia.com/android/2--single-transaction|gzip > /tmp/backup_$(date +%F).sql.gz
mysqldump -uroot -p1234 -A-R--triggers --master-data=https://www.songbingjia.com/android/2--single-transaction|gzip > /tmp/backup_$(date +%F-%T).sql.gz
-B 备份指定数据库，—B和不加的区别是：加-B会在备份脚本里面添加创建库和切换库的sql语句
mysqldump -uroot -p\'1234\' -h 192.168.10.129 -P -B 3306 qingchen > /tmp/backup.sql
库表
mysqldump -uroot -p\'1234\' -h 192.168.10.129 -P 3306 qingchen t1 > /tmp/backup.sql
注意：
(1)、mysqldump在备份和恢复时都需要mysql实例启动为前提。
(2)、一般数据量级100G以内，大约15-45分钟可以恢复，数据量级很大很大的时候（PB、EB）
(3)、mysqldump是覆盖形式恢复的方法
5、高级参数应用
-R备份存储过程及函数
--triggers备份触发器
-E备份事件
-F 在备份开始时,刷新一个新binlog日志
--master-data=https://www.songbingjia.com/android/2 以注释的形式,保存备份开始时间点的binlog的状态信息，一般配合--single-transaction使用
0默认值
1以change master to命令形式，可以用作主从复制
2以注释的形式记录，备份时刻的文件名+postion号
master-data可以自动加锁
（1）在不加--single-transaction ，启动所有表的温备份，所有表都锁定
（2）加上--single-transaction ,对innodb进行快照备份,只对非InnoDB表进行锁表备份，InnoDB表进行“热“备，实际上是实现快照备份，对非innodb表可以实现自动锁表功能
--single-transaction innodb 存储引擎开启热备(快照备份)功能
mysqldump -uroot -p -A -R -E --triggers --master-data=https://www.songbingjia.com/android/2 --single-transaction --set-gtid-purged=OFF > /tmp/backup.sql

文章图片

--set-gtid-purged=auto
auto , on
off
使用场景:
1. --set-gtid-purged=OFF,可以使用在日常备份参数中.
mysqldump -uroot -p -A -R -E --triggers --master-data=https://www.songbingjia.com/android/2--single-transaction --set-gtid-purged=OFF > /data/backup.sql
2. auto , on:在构建主从复制环境时需要的参数配置
mysqldump -uroot -p -A -R -E --triggers --master-data=https://www.songbingjia.com/android/2--single-transaction --set-gtid-purged=ON > /data/backup.sql
mysqldump -uroot -p -A -R -E --triggers --master-data=https://www.songbingjia.com/android/2--single-transaction --set-gtid-purged=OFF --max-allowed-packet=256M > /data/backup.sql
--max-allowed-packet=#从服务器发送或接收的最大数据包长度
6、实现所有表的单独备份
select concat("mysqldump -uroot -p123 ",table_schema," ",table_name," --master-data=https://www.songbingjia.com/android/2 --single-transaction --set-gtid-purged=OFF-R -E --triggers> /backup/",
table_schema,"_",table_name,".sql") from information_schema.tables where table_schema not in (\'sys\',\'information_schema\',\'performance_schema\');
7、模拟物理磁盘损坏导致数据目录丢失
故障模拟及恢复：
(1)模拟全备
mysqldump -uroot -p -A -R -E --triggers --master-data=https://www.songbingjia.com/android/2 --single-transaction --set-gtid-purged=OFF > /tmp/backup.sql
(2) 模拟数据变化
use qingchen
create table t2(name varchar(10));
insert into t2 values (\'aa\');
insert into t2 values (\'bb\');
insert into t2 values (\'cc\');
模拟磁盘损坏：千万别把二进制日志删了
pkill mysqld
rm -rf /data/mysql/qingchen
rm -rf /data/mysql/mysql
(3) 恢复思路
检查备份可用性
从备份中获取二进制日志位置

文章图片

根据日志位置截取需要的二进制日志
mysqlbinlog --skip-gtids --start-position=194 --stop-position=1160 /data/mysql/data/mysql-bin.000005 > /tmp/bin.sql
(4)初始化数据库,并启动
(5)恢复全备
set sql_log_bin=0;
source backup.sql;
(6)恢复二进制日志
source bin.sql
set sql_log_bin=1;
8.、备份时优化参数:
(1) max_allowed_packet最大的数据包大小
mysqldump -uroot -p123 -A-R--triggers --set-gtid-purged=OFF --master-data=https://www.songbingjia.com/android/2 max_allowed_packet=128M--single-transaction|gzip > /tmp/backup_$(date +%F).sql.gz
(2) 增加key_buffer_size(临时表有关)
(3) 分库分表并发备份
(4) 架构分离,分别备份(架构拆分,分布式备份)
三、percona-xtrabackup
1、安装
安装依赖
wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo
yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL libev perl-Digest-MD5
下载软件并安装
wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.23/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.23-1.el7.x86_64.rpm
rpm -ivh percona-xtrabackup-24-2.4.23-1.el7.x86_64.rpm
2、备份命令介绍:
xtrabackup
innobackupex******
查看版本信息
innobackupex --version

文章图片

自动读取了/etc/my.cnf的配置文件
3、备份方式——物理备份
(1)对于非Innodb表（比如 myisam）是，锁表cp数据文件，属于一种温备份。
(2)对于Innodb的表（支持事务的），不锁表，拷贝数据页，最终以数据文件的方式保存下来，把一部分redo和undo一并备走，属于热备方式。
面试题： xbk 在innodb表备份恢复的流程
(1)、xbk备份执行的瞬间,立即触发ckpt,已提交的数据脏页,从内存刷写到磁盘,并记录此时的LSN号
(2)、备份时，拷贝磁盘数据页，并且记录备份过程中产生的redo和undo一起拷贝走,也就是checkpoint LSN之后的日志
(3)、在恢复之前，模拟Innodb“自动故障恢复”的过程，将redo（前滚）与undo（回滚）进行应用
(4)、恢复过程是cp 备份到原来数据目录下
4、innobackupex使用
全备
innobackupex --user=root --password=123 /data/backup/full

文章图片

在/etc/my.cnf添加以下内容
[client]
socket=/tmp/mysql.sock

###最好重启一下mysql
innobackupex --user=root --password=123 /data/backup/full

文章图片

默认会生成时间戳文件夹
innobackupex --user=root --password=123 --no-timestamp /data/backup/full

文章图片

#直接是对应文件
多出来四个文件

文章图片

xtrabackup_binlog_info ：（备份时刻的binlog位置）

文章图片

记录的是备份时刻，binlog的文件名字和当时的结束的position，可以用来作为截取binlog时的起点。
xtrabackup_checkpoints ：
backup_type = full-backuped
from_lsn = 0上次所到达的LSN号(对于全备就是从0开始,对于增量有别的显示方法)
to_lsn = 7373331备份开始时间(ckpt)点数据页的LSN
last_lsn = 7373340备份结束后，redo日志最终的LSN
compact = 0
recover_binlog_info = 0
flushed_lsn = 7373340
XBK备份的时候会占用9个lsn号，如果备份期间没数据变化，to_lsn和last_lsn相差9
(1)备份时刻，立即将已经commit过的，内存中的数据页刷新到磁盘(CKPT).开始备份数据，数据文件的LSN会停留在to_lsn位置。
(2)备份时刻有可能会有其他的数据写入，已备走的数据文件就不会再发生变化了。
(3)在备份过程中，备份软件会一直监控着redo的undo，如果一旦有变化会将日志也一并备走，并记录LSN到last_lsn。
5、全备的恢复
将redo进行重做，已提交的写到数据文件，未提交的使用undo回滚掉。模拟了CSR的过程
innobackupex --apply-log/data/backup/full
恢复备份
前提：
1、被恢复的目录是空
2、被恢复的数据库的实例是关闭
pkill mysqld
创建新目录
mkdir /data/mysql/data1
恢复备份
cp -R /data/backup/full/* /data/mysql/data1/
数据授权
chown -R mysql.mysql /data/mysql/data1/
修改配置文件
vim /etc/my.cnf
datadir=/data/mysql/data1
启动数据库
mysqld --defaults-file=/etc/my.cnf &
6、innobackupex 增量备份(incremental)
增量备份的方式，是基于上一次备份进行增量。
增量备份无法单独恢复。必须基于全备进行恢复。
所有增量必须要按顺序合并到全备中。
增量备份命令
(1)全备
innobackupex --user=root --password=123 --no-timestamp /data/backup/full > & /data/xbk_full.log
(2)模拟第一次数据变化
use qingchen
create table t3(id int);
insert into t3 values (1),(2),(3);
(3)第一次增量备份
innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/data/backup/full/data/backup/inc1 & > /data/inc1.log
(4)模拟第二次数据变化
use qingchen
create table t4(id int);
insert into t4 values (1),(2),(3);
(5)第二次增量备份
innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/data/backup/inc1/data/backup/inc2& > /data/inc2.log
(6)模拟第三次数据变化
use qingchen
create table t5(id int);
insert into t5 values (1);
insert into t5 values (2);
insert into t5 values (3);
drop database qingchen;
恢复到第三次数据变化误drop之前的数据状态
恢复思路：
查看完binlog信息，暂停数据库，防止继续脏数据写入
检查备份：第一次full+第二次inc1+第三次inc2，第三次的完整二进制日志
进行备份整理（细节），截取关键的二进制日志（从备份误删除之前）
测试库进行备份恢复及日志恢复
7、恢复过程
(1)二进制日志进行截取
cd /data/backup/inc2
cat xtrabackup_binlog_info

文章图片


文章图片


文章图片
‘
根据观察主要截取GTID5到8
mysqlbinlog --skip-gtids --include-gtids=\'21f9f0dd-067b-11ec-a8e9-000c2924e11c:5-8\' /data/mysql/data/mysql-bin.000013 > /data/backup/bin.sql
(2) 全备的整理
--redo-only如果不加这个参数，数据整理的过程中会执行undo操作，我们只需要最后一次日志合并的时候或者没有日志合并的时候执行undo
innobackupex --apply-log --redo-only /data/backup/full

文章图片

合并inc1到full中

文章图片

innobackupex --apply-log --redo-only --incremental-dir=/data/backup/inc1 /data/backup/full

文章图片

合并inc2到full中

文章图片

innobackupex --apply-log--incremental-dir=/data/backup/inc2 /data/backup/full

文章图片

最后一次整理全备
innobackupex --apply-log/data/backup/full
(3)进行恢复
mkdir /data/mysql/data2 -p
cp -R /data/backup/full/* /data/mysql/data2/
chown -R mysql.mysql /data/mysql/data2/
vim /etc/my.cnf
datadir=/data/mysql/data2
启动数据库
mysqld --defaults-file=/etc/my.cnf &


文章图片

set sql_log_bin=0;
source /data/backup/bin.sql;
set sql_log_bin=1;

文章图片

(4)误删一张表的恢复过程
show create table t4;

文章图片

将t4删除
drop table t4;
重新创建t5表
CREATE TABLE `t4` (`id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
将t4表脱离表空间
alter table t4 discard tablespace;
cp /data/backup/full/qingchen/t4.ibd /data/mysql/data2/qingchen/
chown -R mysql.mysql /data/mysql/data2/qingchen/t4.ibd
alter table t4 importtablespace;

文章图片

四、mysql5.6升级到5.7
tar -xvf mysql-5.6.35-linux-glibc2.5-x86_64.tar.gz
mv mysql-5.6.35-linux-glibc2.5-x86_64 mysql5.6
mv mysql5.6/ /data/
mkdir /data/mysql5.6/data1
yum install -y libaio-devel
yum remove mariadb-libs
chown -R mysql.mysql /data/mysql5.6/
/data/mysql5.6/scripts/mysql_install_db --user=mysql --basedir=/data/mysql5.6 --datadir=/data/mysql5.6/data1
cat > /etc/my.cnf < < EOF
[mysqld]
user=mysql
basedir=/data/mysql5.6
datadir=/data/mysql5.6/data1
port=3306
socket=/tmp/mysql.sock
server_id=99
log_bin=/data/mysql5.6/data1/mysql-bin
binlog_format=row
[mysql]
socket=/tmp/mysql.sock
prompt=3306 [\\d]>
EOF
/data/mysql5.6/bin/mysqld --defaults-file=/etc/my.cnf &
准备数据
create database qingchen charset utf8mb4;
use qingchen
create table test1(id int,name varchar(10));
insert into test1 values (1,\'zs\');
insert into test1 values (2,\'ls\');
insert into test1 values (3,\'ww\');
insert into test1 values (4,\'zl\');
备份数据
/data/mysql5.6/bin/mysqldump -uroot -p -A --master-data=https://www.songbingjia.com/android/2 --single-transaction -R -E --triggers > /tmp/full.sql
mysql5.7和mysql5.6差异
mysql5.7
information_schema 有61张表
performance_schema 有87张表
mysql.user用户表没有password字段
mysql5.6
information_schema 有59张表
performance_schema 有52张表
mysql.user用户表有password字段
将数据倒入mysql5.7
source /tmp/full.sql;
再用mysql5.7提供的更新命令更新一下
mysql_upgrade -uroot -p #此时的密码是5.6数据库的密码
五、从mysqldump 全备中获取库和表的备份
1、获得表结构
sed -e\'/./{H; $!d; }\' -e \'x; /CREATE TABLE `city`/!d; q\'full.sql> createtable.sql
2、获得INSERT INTO 语句，用于数据的恢复
grep -i \'INSERT INTO `city`\'full.sqll > data.sql &
3.获取单库的备份
sed -n \'/^-- Current Database: `world`/,/^-- Current Database: `/p\' all.sql > world

推荐阅读

• 

  mysql实现排名 mysqlsql排名函数怎么用
• 

  大豆蛋白被是什么材料
• 

  金庸的武侠代表作品是什么
• 

  技嘉主板超频内存 技嘉Z390主板超频指南
• 

  2017年出的关于现代校园恋爱的动漫,有没有什么推荐？
• 

  新鲜桂花怎么处理和保存
• 

  为什么iPhone手机不踏足折叠屏市场？或许离不开这五点原因
• 

  SQL|SQL 导出表结构到Excel
• 

  N95 12月25日起赣榆区投放8万个医用防护口罩
• 

  什么条件下才能领取失业保险金?不领取还是领好?
• 

  出现菲斯曼锅炉显示0a故障怎么办,故障原因说明与3种解决方法
• 

  定金合同在什么情况下无效 有哪些情况定金合同无效
• 

  大金空调打台风后不制冷故障排除图解,主要还是受气温的影响
• 

  40岁自学go语言 零基础学go语言多久
• 

  办理加油卡有什么好处 加油卡怎么办理划算
• 

  这样搭配更具有视觉美观性 浅蓝色t恤配什么颜色裤子好看
• 

  夏天什么时候种白菜 什么时候种白菜
• 

  Win11如何禁用资源管理器历史搜索？Win11禁用资源管理器历史搜索的方法
• 

  特仑苏纯牛奶多少钱一瓶 「特仑苏纯牛奶两种包装」
• 

  三菱空调定时闪烁不制冷是什么原因,辅助加热

• Linux(程序设计):26---字符集与字符编码概述（附Unicode字符集实现原理）
• Linux文件权限三（特殊权限SUID SGID SBIT）
• Linux(程序设计):66---简略版的线程池设计
• 八mysql主从复制基础
• RHCSA评分结果
• Linux/Windows下MySQL数据库的备份与还原（mysqldump）
• Flutter Chanel通信流程
• Linux(程序设计):12---选项查询(sysconfpathconffpathconf)
• Linux(内核剖析):14---内核数据结构之链表（struct list_head）