MySQL之备份与恢复

配置文件开启关闭日志的位置
vim /etc/my.cnf

#错误日志,用来记录当MySQL启动、停止或运行时发生的错误信息
log-error=/usr/local/mysql/data/mysql_error.log   #指定日志的保存位置和文件名
#通用查询日志，用来记录MySQL的所有连接和语句，不建议开
general_log=ON
general_log_file=/usr/local/mysql/data/mysql_general.log
#二进制日志，用来记录所有更新了数据或者已经潜在更新了数据的语句，记录了数据的更改，可用于数据恢复
log-bin=mysql-bin
#慢查询日志，用来记录所有执行时间超过long_query_time秒的语句，可以找到哪些查询语句执行时间常，以便提醒优化
slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
long_query_time=5    #设置超过5秒执行的语句被记录，缺省时为10秒

3.日志配置文件查询

#登入mysql

#查看通用查询日志是否开启
show variables like 'general%';	

#查看二进制日志是否开启
show variables like 'log_bin%';

#查看慢查询日志是否开启
show variables like '%slow%';

#查看慢查询时间设置
show variables like 'long_query_time';

#在数据库中设置开启慢查询的方法
set global slow_query_log=ON;

查看通用日志是否开启

查看二进制日志是否开启

查看慢查询日志是否开启，时间设置为多少

二、备份的类型

数据库可以物理备份和逻辑备份

物理备份时对数据库操作系统的物理文件（如数据文件、日志文件等）的备份，这种类型的备份适用于在出现问题的时候需要快速恢复的大型重要数据库

1.物理备份

物理备份又可以分为冷备份（脱机备份）、热备份（连接备份）、温备份

冷备份

是在关闭数据库的时候进行的备份，可以直接（tar）

关闭MySQL数据库
使用tar命令直接打包数据库文件夹
直接替换现有mysql目录即可

热备份

数据库处于运行状态，依赖于数据库的日志文件（mysqlhotcopy mysqlbackup）

将指定的库、表处为sql脚本
使用命令mysql带入备份的数据

2.逻辑备份

逻辑备份时对数据库逻辑组件的备份，表示为逻辑数据库结

这种类型的备份适用于可以编辑数据值或表结构

从数据库的备份策略角度来看，备份又可分为完全备份、差异备份和增量备份

完全备份

每次对数据进行完整备份，即对整个数据库、数据库结构和文件结构的备份，保存的是备份完成时刻的数据库，是差异备份与增量备份的基础，完全备份的备份与恢复操作都非常简单方便，但是数据存在大量的重复并且会占用大量的磁盘空间，备份的时间也很长

优势：备份恢复操作简单
劣势：随着备份的次数越来越多，占用的磁盘空间和资源也较多，数据存在大量的重复

差异备份

备份那些自从上次完全备份之后被修改过的所有文件，备份的时间节点是从上次完整备份起，备份数据量会越来越大。恢复数据时只需要恢复上次的完全备份与最佳的一次差异备份

优势：恢复时只需要恢复全备文件和就近一次备份文件
劣势：数据会越来越大

增量备份

只有那些在上次完全备份或者增量备份后被修改的文件才会被备份，以上次完整备份或上次增量备份的时间为时间点，仅备份期间内的数据变化，因而备份的数据量小，占用空间小，备份速度快。但恢复时，需要从上一次的完整备份开始到最后一次增量备份之间的所有增量一次恢复，如中间某次的备份数据损坏，将导致数据的丢失

优势：没有重复的备份数据，备份数据量不大，所需时间短
劣势：数据恢复比较麻烦，中间任何一个备份的数据都不能损坏，不然导致数据丢失

3.如何选择逻辑备份的策略

合理值区间
完全备份：3天一次的全备，全备的时间需要在不提供业务的时间区间进行，PM10:00~AM5:00之间进行全备
增量备份：3天\2天\2天一次增量备份
差异备份：选择特定的场景进行备份

三、常见的备份方法

1.物理冷备

备份时数据库处于关闭状态，直接打包数据库文件（tar）
备份速度快，恢复时也是最简单的

2.专用备份工具

mysqldump：常用的逻辑备份工具
mysqlhotcopy：仅拥有备份MyISAM和ARCHIVE表

3.启用二进制文件进行增量备份

进行增量备份，需要刷新二进制日志
MySQL支持增量备份，进行增量备份时必须启用二进制日志，二进制日志文件为用户提供复制对执行备份点后进行的数据库更改所需的信息进行恢复，如果进行增量备份（包含上次完全备份或增量备份以来发生的数据修改），需要刷新二进制日志

4.第三方工具备份

免费的MySQL热备份软件Percona XtraBackup

四、MySQL完全备份与恢复

InnoDB存储引擎的数据库在磁盘上存储成三个文件：db.opt（表属性文件）、表名.frm（表结构文件）、表名.ibd（表数据文件）

1.物理冷备份与恢复

将数据库文件进行压缩到别的目录备份，恢复时，直接解压回以前的目录即可

关闭数据库，下载xz工具

systemctl stop mysqld
yum -y install xz

压缩备份

tar Jcf /opt/mysql_all_$(date +%F).tar.xz /usr/local/mysql/data/
mv /usr/local/mysql/data/ /opt/

解压恢复

mkdir /usr/local/mysql/data
tar Jxf /opt/mysql_all_2022-09-19.tar.xz -C /usr/local/mysql/data/
cd /usr/local/mysql/data/usr/local/mysql/data
mv ./* /usr/local/mysql/data
chown -R mysql:mysql /usr/local/mysql/data
systemctl start mysqld

2.mysqldump备份与恢复（温备份）

首先我们创建两个数据库（wu1、test），在每个数据库中插入一张表，并插入数据

完全备份和恢复一个或多个完整的库（包括其中所有的表）

备份一个库或多个库

格式：mysqldump -uroot -p[密码] --databases 库名1 [库名2] ... > /备份路径/备份文件名.sql
#备份一个库或多个库
格式：mysqldump -uroot -p[密码] --all-databases > /备份路径/备份文件名.sql
#备份所有库

恢复一个或多个库

恢复有两种方法

如果是使用musqldump备份的文件，那么可以使用mysql导入的方法恢复
还有一种方法就是使用source命令在数据库中加载备份的目录即可

使用mysql命令进行导入恢复

mysql -uroot -pabc123 -e 'show databases;'
#查看所有的库
mysql -uroot -pabc123 -e 'drop database test;'
#使用-e的方法进入数据库执行删除数据库的操作
mysql -uroot pabc123 -e 'show databases;'
#再次查看所有的库，发现ngs库被删除
mysql -uroot pabc123 < /opt/wxj.sql
#使用mysql命令恢复数据库ngs
mysql -uroot pabc123 -e 'show databases;'
#查看数据库是否恢复成功

使用source进行恢复数据库

mysql -uroot -pabc123 -e 'drop database ngs;'
#删除数据库ngs
mysql -uroot -pabc123 -e 'drop database sen;'
#删除数据库sen
mysql -uroot -pabc123 -e 'show databases;'
#查看mysql中ngs库和sen库是否被删除
mysql -uroot -pabc123 -e 'source /opt/wu1.sql;'
#使用source进行恢复

完全备份和恢复指定库中的部分表

备份数据表的话，其中也有是否加-d选项的区别

加了-d表示之备份数据表的结构
不加-d表示备份数据表的结构以及数据

格式：mysqldump -uroot -p[密码] 库名 [表名1] [表名2] ... > /备份路径/备份文件名.sql

mysqldump -uroot -pabc123 test class > /opt/test1.sql
#备份ngs数据库中的info表（表的数据也会备份）
mysqldump -uroot -pabc123 -d test class > opt/test2.sql
#备份ngs数据库中的info表（加了-d选项，表示只备份info表的表结构）

grep -v "^--" /opt/test1.sql |grep -v "^/" |grep -v "^$"
#查看备份文件

不加-d选项备份

加-d选项备份

恢复指定表中的部份表

恢复同样和恢复库文件一样，可以使用两种方法进行恢复

使用mysql命令进行恢复

mysql -uroot -pabc123 -e 'drop table test.class;'
#删除ngs库中的info表
mysql -uroot -pabc123 -e 'desc test.class;'
#查看ngs库中的表
mysql -uroot -pabc123 test < /opt/test1.sql
#将ngs库中的info表进行恢复
mysql -uroot -pabc123 -e 'desc test.class;'
#再次查看

使用source恢复

mysql -uroot -pabc123
#登录数据库
use test
show tables;
#查看ngs数据库中的所有表
drop table class;
#删除info表
source /opt/test1.sql
#使用source执行恢复

五、MySQL增量备份与恢复

MySQL数据库增量恢复有：一般恢复、基于位置恢复、基于时间点恢复

一般恢复

将所有备份的二进制日志内容全部恢复

基于位置恢复

数据库在某一时间点可能既有错误的操作也有正确操作
可以基于精确的位置跳过错误的操作
发生错误节点之前的一个节点，上一次正确操作的位置点停止

基于时间点恢复

跳过某个发生错误的时间点实现数据恢复
在错误时间点停止，在下一个正确时间点开始

1.二进制文件介绍

二进制文件有三种不同的记录格式

1、STTATEMENT（基于sql语句）

每一条涉及到被修改的sql都会记录在binlog中

缺点：日志量过大、如sleep（）函数，last_insert_id()>（聚合函数）,以及user-definedfuctions(udf)、主从复制等架构记录日志时会出现问题
总结
增删改查通过sql语句来实现记录，如果用高并发可能会出错，可能时间差异或者延迟，可能不是我们想像的恢复可能你先删除或在修改，可能会倒过来。准确率低。

2、ROW（基于行）

只记录变动的记录，不记录sql语句的上下文环境

缺点
如果遇到update…set…where true 那么binlog的数据量会越来越大
总结
update、delete多行数据其作用，来用行记录下来，值计量变动的记录，不记录sql的上下文环境，比如sql语句记录一行，但是ROW就可能记录10行，但是准确性高，高并发的时候由于操作量能变低，所以记录都记下来。

MIXED（混合模式）推荐使用

上面两种的版本混合，都进行记录
一般的语句使用statement，函数使用ROW方式存储

3.开启二进制功能

vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
binlog_format = MIXED
#二进制日志有三种不同的记录格式：STATEMENT（基于SQL语句）、ROW（基于行）、MIXED（混合模式），默认为STATEMENT
server-id = 1
#server-id 为服务的序号，在MySQL主备、高可用中需要指定服务的序号

systemctl resart mysqld
#改为配置文件需要重启服务

4. 查看二进制文件的内容

开启了二进制文件，会在/data目录下生成二进制文件，但是直接使用cat查看的时候出现乱码，所以需要指定编码格式和解码进行查看

mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql-bin.000001 >/opt/mysql-bin.000001
#可以将日志文件解码至/opt目录下导出为txt格式查看

或

cp /usr/local/mysql/data/mysql-bin.000001 /opt
mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000001
#--no-defaults：默认字符集（不加会报UTF-8错误）
#--base64-output=decode-rows：使用64位编码机制去解码（decode）并按行读取（rows）
-v：显示详细内容
mysqladmin -uroot -pabc123 flush-logs
#刷新二进制日志文件（为了不重启，进行刷新，这样二进制文件会重新生成一个，相当于增量备份）

2.增量备份（完备+增备）

增量备份是基于完全备份或增量备份的，所以我们直接完全备份数据库

对表进行完全备份

mysqldump -uroot -pabc123 test class > /opt/test-$(date +%F).sql
#使用mysqldump对ngs库中的info表进行完全备份数据库
mysqladmin -u root -p flush-logs

在表中增加数据再进行增量备份

在表中插入新的数据

use test
select * from class;
insert into class values(3,'wangwu',21,'music');
select * from class;

进行增量备份
mysqladmin -uroot -p flush-logs
#刷新日志文件，相当于一次增量备份

3.增量恢复

增量恢复之一般恢复

mysql -uroot -pabc123 -e 'drop table test.class;'
#模拟ngs库中的info表中的数据全部丢失
mysql -uroot -pabc123 -e 'use test;show tables;'
现在需要还原info表，且还要恢复里面的两条数据
mysql -uroot -pabc123 test < /opt/test-2022-09-19.sql
#先进行完全恢复，（但完全恢复的文件中，info表中只有两条数据）
mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000003 | mysql -uroot -pabc123
#基于mysql-bin.000003日志文件恢复（恢复test.class表中的另一条数据）

进行完全恢复

进行增量恢复

增量恢复之断点恢复

mysqldump -uroot -pabc123 test class > /opt/test3.sql
#完全备份info表（表中有3条数据）
mysqladmin -uroot -pabc123 flush-logs
#刷新二进制日志文件

现在进行增量备份新插入2条数据的日志文件

现在有人不小心删除了class表中相关的数据，但现在客户只想恢复前4条数据，最后一条数据不想要了

基于位置恢复-at

mysql -uroot -pabc123 test < /opt/test3.sql
#完全恢复前三条数据
mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000005 > /opt/mysql-bin.000005
#将日志文件重定向到/opt目录下，方便查看，查看到第4条数据的位置点
vim /opt/mysql-bin.000005

先进行完全备份恢复到两条数据

mysql -uroot -pabc123 -e 'drop table test.class;'

mysql -uroot -pabc123 -e 'use test;select * from class;'

mysql -uroot -pabc123 test < /opt/test3.sql

mysql -uroot -pabc123 -e 'use test;select * from class;'

再基于位置节点进行恢复

mysqlbinlog --no-defaults --stop-position='492' mysql-bin.000005 | mysql -uroot -pabc123

mysql -uroot -pabc123 -e 'use test;select * from class;'

总结

1、备份的类型

物理备份、逻辑备份

2、物理备份：冷备、温备、热备

①冷备：需要关闭mysql服务，或者确保mysql服务在进行tar备份时，没有客户端的写入操作

②温备： mysqldump，这个是mysql自带的备份工具

特性：逐表备份，每备份一张表时，会先drop删除，然后重新create创建表结构，然后再解锁表

（仅锁定写的操作，但可读），进行insert语句的备份，备份完成后，进行unlock解锁，然后继续备份下一个。

③热备： xtrabackup或mysqlhotcopy等热备工具（第三方），在mysql正常运行时，进行备份。

3、逻辑备份：全量备份、增量备份、差异备份

①全量备份： tar压缩、mysqldump -u -p --all-databases

②增量备份： 主要使用bin-log，来舒心生成新的增备的日志文件，可以通过：mysqladmin -u -p fiush-logs 来刷新生成新的增备的日志文件，同时可以结合crontan，完成自动刷新。

注意：再进行基于二进制文件的备份恢复时，有必要的话，需要先回复完备的数据，再逐个恢复增备的数据，直到恢复至我们需要恢复的数据为止。

③差异备份：主要备份一次完备，后面修改的数据全部基于完备进行恢复。（相当于快照）

4、日志的保存类型

①混合模式Mixed（建议使用）：记录行和sql

②基于行ROWS：只记录被修改的行的记录

③基于sql（默认）：记录修改内容的执行语句

5、mysql恢复的方式

①基于冷备（tar）形式：t ar zxvf解压打包的/usr/local/mysql/data数据下的内容

②基于自带的温备工具： 使用mysqldump进行备份后，可使用两种方式进行恢复

mysql -u -p 库名 </opt/mysql_all.sql（备份文件的路径）
mysql -u -p -e ’ source /opt/mysql_all.sql’ 直接使用source进行恢复
③基于日志：bin-log

首先开启二进制日志的配置log-bin=mysql-bin ；log_format=MIXED

然后再/usr/local/mysql/data 目录下，会生成mysql-bin.00000x的二进制文件进行恢复

接着使用mysqlbinlog --no-defaults 二进制日志路径|mysql -u -p 基于完整的二进制日志文件进行恢复。

基于位置点和时间点进行恢复

使用mysqlbinlog --no-defaults --base64-output-decode-rows -v 二进制日志文件路径，查看正确和需要跳过的错误操作的at（position位置带点）和datetime（时间点）来进行恢复

啊啊啊啊啊建

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录