Mysql之备份与恢复

Mysql之备份与恢复

1mysql日志概述

mysql的日志默认保存位置为：/usr/local/mysql/data 下面

1.1日志类型与作用

1.1.1redo （重做日志）

达到事务一致性（每次重启都会重做）

作用：确保日志的持久性，防止在发生故障，脏页未写入磁盘。重启数据库会进行 redo log 执行重做，达到事务一致性。

1.1.2undo（回滚日志）

作用：保证数据的原子性，记录事务发生之前的一个版本，用于回滚，innodb事务可重复和读取已提交隔离级别就是通过mvcc+undo实现。

1.1.3 errorlog（错误日志）

作用：mysql本身启动，停止，运行期间发生的错误信息

1.1.4slow query log（慢查询日志）

作用：记录执行时间过长的sql语句，时间默认值为10s，可以配置，只记录执行成功，超过设置时间的sql语句，都会被存在到慢查询日志中。

还可以提醒优化，对于查询慢的语句进行操作（比如是网络问题，还是未添加索引等）

1.1.5 bin log （二进制日志）

作用：用于基于日志形式的数据恢复。用于主从复制，实现主从同步，记录的内容是：数据库中执行的sql语句

1.1.6relay log（中继日志）

作用：用于数据库主从同步，将主库发来的bin log保存在本地，然后从库进行回放。

1.1.7 general log（普通日志）

记录数据库的操作明细，默认关闭，开启后会降低数据库的性能

2日志配置文件

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

#错误日志
log-error=/usr/local/mysql/data/mysql_error.log

#通用查询日志
general_log=ON
general_log_file=/usr/local/mysql/data/mysql_general.log

#二进制日志
log-bin=mysql-bin

#慢查询日志
slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
long_query_time=5

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-OMFSh4eu-1663639287270)(C:\Users\lin\AppData\Roaming\Typora\typora-user-images\image-20220919135144153.png)]

2.1、修改完成需要重新mysql服务

systemctl restart mysqld.service

3日志配置文件的查询

#登入mysql
mysql -u root -p[密码]

#查看通用查询日志是否开启
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;

查看二进制日志是否开启

show variables like ‘long_query_time’;

4备份的类型

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

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

4.1冷备份（脱机备份）

是在关闭数据库的时候进行的备份，可以直接（tar）当正常关闭时会提供给我们一个完整的数据库。对于备份Oracle信息而言，冷备份是最快和最安全的方法。

冷备份的优点

使用tar命令直接打包数据库文件夹

直接替换现有mysql目录即可（/usr/local/mysql/data 数据目录）

是非常快速的备份方法（只需拷文件）

轻易归档（简单拷贝即可）

轻易恢复到某个时间点上（只需将文件再拷贝回去）

能与归档方法相结合，做数据库“最佳状态”的恢复。

低度维护，高度安全。

冷备份也有如下不足

① 单独使用时，只能提供到“某一时间点上”的恢复。
② 再实施备份的全过程中，数据库必须要作备份而不能作其他工作。也就是说，在冷备份过程中，数据库必须是关闭状态。
③ 若磁盘空间有限，只能拷贝到磁带等其他外部存储设备上，速度会很慢。
④ 不能按表或按用户恢复。

4.2热备份 (联机备份)

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

4.3温备份

数据库锁定表格(不可写入但可读)的状态下进行备份操作（mysqldump）

物理备份的优点及使用场景：

优点：速度快，效率高。

场景：可用于停机维护及数据库物理迁移场景中

4.4逻辑备份

逻辑备份是对数据库逻辑组件的备份.表示为逻辑数据库结构
这种类型的备份适用于可以编辑数据值或表结构
从数据库的备份策略角度来看，备份又可分为完全备份、差异备份和增量备份

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

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

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

备份方式	完全备份	差异备份	增量备份
完全备份时的状态	表1、表2	表1、表2	表1、表2
第1次添加内容	创建表3	创建表3	创建表3
备份内容	表1、表2、表3	表3	表3
第2次添加内容	创建表4	创建表4	创建表4
备份内容	表1、表2、表3、表4	表3、表4	表4

4.5逻辑备份的策略（增、全、差异）

如何选择逻辑备份策略（频率）
合理值区间 ☆
完全备份：3天一次的全备，全备的时间需要在不提供业务的时间区间进行 PM 10点 AM 5:00之间进行全备
增量备份：3天/2天/1天一次增量备份
差异备份：选择特定的场景进行备份
一个处理（NAS 就是采用nfs技术）提供额外空间给与mysql 服务器用

4.6常见的备份方法

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

2、专用备份工具 mysqldump 或 mysqlhotcopy
mysqldump 常用的逻辑备份工具
mysqlhotcopy 仅拥有备份 MyISAM 和 ARCHIVE 表

3、启用二进制日志进行增量备份
进行增量备份，需要刷新二进制日志

4、第三方工具备份
免费的MySQL 热备份软件 Percona XtraBackup mysqlbackup

5MYsql完全备份与恢复

是对整个数据库、数据库结构和文件结构的备份
保存的是备份完成时刻的数据库
是差异备份与增量备份的基础
优点：
备份与恢复操作简单方便

缺点：
数据存在大量的重复
占用大量的备份空间
备份与恢复时间长

5.1物理冷备份与恢复（tar）

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

systemctl stop mysqld.service

yum -y install xz 下载更新压缩工具

xz是一个使用 LZMA压缩算法的无损数据压缩文件格式。和gzip与bzip2一样，同样支持多文件压缩，但是约定不能将多于一个的目标文件压缩进同一个档案文件。相反，xz通常作为一种归档文件自身的压缩格式。但是其压缩比比gzip和bzip2高。
官网链接： https://tukaani.org/xz/
官网介绍到： 压缩后的文件大小 gzip 小30％，比bzip2小15％。

xz -z 文件名 不保留原文件压缩

xz -zk 文件名 保留原文件压缩

xz -d 文件名 不保留原文件解压

xz -dk 文件名 保留原文件解压

tar Jcvf /mnt/mysql_all$(date +%F).tar.xz ./data
ls /mnt

模拟数丢失并恢复

rm -rf /usr/local/mysql/data/* //删除mysql的数据
systemctl start mysqld.service 开启数据库

mysql -uroot -p123456 登录失败

ls /usr/local/mysql/data/ 查看文件发现生成新的数据库文件

5.2mysqldump备份与恢复（温备份）

MySQL自带的备份工具，可方便实现对MySQL的备份
可以将指定的库、表导出为SQL 脚本
使用命令mysq|导入备份的数据

1、完全备份一个或多个完整的库（包含其中所有的表）
完全备份一个或多个完整的库 (包括其中所有的表)

mysqldump -u root -p[密码] --databases 库名1 [库名2] … > /备份路径/备份文件名.sql #导出的就是数据库脚本文件

完全备份 MySQL 服务器中所有的库

mysqldump -u root -p[密码] --all-databases > /备份路径/备份文件

5.2.1恢复一个库或多个库

如果是使用mysqldump备份的文件，那么可以使用mysql导入的方法恢复

还有一种方法就是使用source命令在数据库中加载备份的目录即可

使用mysql命令进行导入恢复

mysql -uroot -p123456 -e ‘drop database gang1’;
#使用-e的方法进入数据库执行删除数据库的操作
mysql -uroot -p123456 -e ‘show databases’;
#使用-e的方法进行显示数据库的数量

6mysql增量备份与恢复

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

一般恢复

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

基于位置恢复

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

基于时间点恢复

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

6.1二进制文件介绍及开启

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

① STATEMENT（基于SQL语句）:
每一条涉及到被修改的sql 都会记录在binlog中
缺点：日志量过大，如sleep()函数，last_insert_id()>，以及user-defined fuctions（udf）、主从复制等架构记录日志时会出现问题

② ROW(基于行)
只记录变动的记录，不记录sql的上下文环境
缺点:如果遇到update…set…where true 那么binlog的数据量会越来越大
create table info
insert into info values(1,‘zhangsan’);
insert into info values(2,‘lisi’);
delete id=zhangsan

③ MIXED 推荐使用
一般的语句使用statement，函数使用ROW方式存储。

6.2查看二进制文件的内容

cp /usr/local/mysql/data/mysql-bin.000005
#把日志文件复制到/mnt目录下慢慢看,可以直接使用cat查看

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 -p123456 flush-logs;
#刷新二进制日志文件(为了不重启，进行刷新，这样二进制文件会重新生成一个，相当于增量备份)

二进制日志中需要关注的部分
1、at ：开始的位置点
2、end_log_pos：结束的位置 position （位置点）
3、时间戳： 220917 17:05:04
4、SQL语句

mysqlbinlog --no-defaults --base64-output=decode-rows -v /mnt/mysql-bin.000001

6.3增量备份（完备+增备）

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

对表进行完全备份

mysqldump -uroot -p123456 lin school> /mnt/lin_school-$(date +%F).sql
#使用mysqldump进行完全备份数据库

mysqladmin -u root -p flush-logs
#可每天进行增量备份，生成新的二进制日志文件(例如:mysql-bin.00002)

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

在表中添加数据

select * from school;

insert into school values(2,‘英雄’,45,98);

select * from school;

增量备份

mysqladmin -u root -p flush-logs
#可每天进行增量备份，生成新的二进制日志文件(例如:mysql-bin.00002)

6.5增量恢复

mysql -uroot -p123456 -e ‘drop table lin.school’;
#模拟lin.school 表中的所有数据全部丢失。

mysql -uroot -p123456 -e ‘use lin;show tables’;

现在需要还原lin.school表，且还要恢复里面的两条数据

mysql -uroot -p123456 lin < /mnt/gang1_school-2022-09-17.sql
#先进行完会恢复，(但是完全恢复的文件中，lin表中只有一条数据)

mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000002 | mysql -uroot -p123456
#基于mysql-bin.000002日志文件恢复 (恢复 gang1.school 表中的另一条数据)

6.6增量恢复之断点恢复实验

mysqldump -uroot -p123456 gang1 school > /mnt/gang1-school-2.sql 完全备份2条数据

表中插入新数据

insert into school values(3,‘牛人’,45,45.2)；

insert into school values(4,‘国人’,24,75.6);

mysqladmin -uroot -p123456 flush-logs

mysql-bin.000002 增加的内容保存在这个文件

6.7基于时间恢复

基于时间恢复和基于位置恢复是基本类似。现在使用基于时间用来恢复第三条语句

mysql -uroot -p123456 lin < /mnt/lin-school-2.sql 恢复前2条信息

mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000002 > /opt/mysql-bin.000002 翻译并重定向

vim /opt/mysql-bin.000002 查看第3条信息时间

6.8 基于时间跳点恢复

mysqlbinlog [–no-defaults] --start-datetime=‘年-月-日 小时:分钟:秒’ --stop-datetime=‘年-月-日小时:分钟:秒’ 二进制日志 | mysql -u 用户名 -p 密码

#仅恢复到16:41:24 之前的数据，即不恢复"user4"的数据
mysqlbinlog --no-defaults --stop-datetime=‘2020-11-22 16:41:24’ /usr/local/mysql/data/mysql-bin.000002 | mysql -uroot -p123456

#仅恢复"user4"的数据，跳过"user3"的数据恢复
mysqlbinlog --no-defaults --start-datetime=‘2020-11-2216:41:24’ /usr/local/mysql/data/mysql-bin.000002 | mysql -uroot -p7

7总结

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（时间点）来进行恢复
于自带的温备工具： 使用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（时间点）来进行恢复