MYSQL日志管理、备份恢复

一.数据库备份

1.数据备份的重要性

• 备份的主要目的是灾难恢复

• 在生产环境中，数据的安全性至关重要

• 任何数据的丢失都可能产生严重的后果

5
造成数据丢失的原因 ♢程序错误
♢人为操作错误 ♢运算错误
♢磁盘故障 ♢不可控因素

2.数据库备份的方法
（1）从物理与逻辑的角度，备份分为（物理备份、逻辑备份）
物理备份： 对数据库操作系统的物理文件（如数据文件、日志文件等）的备份

逻辑备份：对数据库逻辑组件（如：表等数据库对象）的备份

物理备份方法
冷备份（脱机备份）：是在关闭数据库的时候进行的
热备份（联机备份）：数据库处于运行状态，依赖于数据库的日志文件
温备份：数据库锁定表格（不可写入但可读）的状态下进行备份操作

(2)从数据库的备份策略角度，备份可分为

• 完全备份：每次对数据库进行完整的备份

• 差异备份：备份自从上次完全备份之后被修改过的文件

• 增量备份：只有在上次完全备份或者增量备份后被修改的文件才会被备份

(3)常见的备份方法
物理冷备

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

专用备份工具mydump或mysqlhotcopy

• myaqldump常用的逻辑备份工具
• mysqlhotcopy仅拥有备份MyISM和ARCHIVE表

启用二进制日志进行增量备份

• 进行增量备份，需要刷新二进制日志

第三方工具备份

• 免费MySQL热备份软件Percona XtraBackup

3.MySQL完全备份
(1)了解MySQL完全备份
是对整个数据库，数据库结构和文件结构的备份

保存的是备份完成时刻的数据库

是差异备份与增量备份的基础

优点

• 备份与恢复操作简单方便

缺点

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

（2）数据库完全备份分类
物理冷备份与恢复

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

mysqldump备份与恢复

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

4.MySQL增量备份
1.使用mysqldump进行完全备份存在的问题
• 备份数据中有重复数据
• 备份时间与恢复时间过长

2.是自上一次备份后增加/变化的文件或者内容

3.特点
• 没有重复数据，备份不大，时间短
• 恢复需要上次完全备份及完全备份之后所有的增量备份才能恢复，且要对所有增备份进行逐个反推恢复

4.MySQL没有提供直接的增量备份方法

5.可通过MySQL提供的二进制日志间接实现增量备份

6.MySQL二进制日志对备份的意义

• 二进制日志保存了所有更新或者可能更新数据库的操作
• 二进制日志在启动MySQL服务器后开始记录，并在文件达到max_ binlog_ size所设 置的大小或者接收到flush logs命令后重新创建新的日志文件
• 只需定时执行flush logs方法重新创建新的日志，生成二进制文件序列，并及时把这些日志保存到安全的地方就完成了-一个时间段的增量备份

5.MySQL数据库增量恢复
一般恢复

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

基于位置恢复

• 数据库在某一时间点可能既有错误的操作也有正确的操作

• 可以基于精准的位置跳过错误的操作

基于时间点恢复

• 跳过某个发生错误的时间点实现数据恢复

二.日志管理

MySQL常用日志类型及开启
日志通常保存在 etc/my.cnf文件中

1.错误日志
指定日志的保存位置和文件名
log-error=/usr/local/mysql/data/mysql_error.log

#用来记录当MySQL启动、停止或运行时发生的错误信息，默认已开启

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

#用来记录MySQL的所有连接和语句，默认是关闭的

3、二进制日志（binlog)
log-bin=mysql-bin
或
log_bin=mysql-bin

#用来记录所有当MySQL启动、停止或运行时发送的错误信息，默认是关闭的

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

systemctl restart mysqld

#用来记录所有执行时间超过long_query_time秒的语句，可以找到哪些查询语句执行时间长，以便于优化，


查看日志状态
1、查看通用查询日志是否开启

2、查看二进制日志是否开启
show variables like ‘log_bin%’;
3、查看慢查询日功能是否开启
show variables like ‘%slow%’;

4.查看慢查询时间设置

5.在数据库中设置开启慢查询的方法
set global slow_query_log=ON;
该方法重启服务失效

三.MySQL备份、恢复相关操作

物理备份
热备份（Hot Backup）：热备份也称为在线备份（Online Backup），是指在数据库运行的过程中进行备份，对生产环境中的数据库运行没有任何影响。常见的热备方案是利用mysqldump、XtraBackup等工具进行备份。

冷备份（Cold Backup）：冷备份也称为离线备份（Offline Backup），是指在数据库关闭的情况下进行备份，这种备份非常简单，只需要关闭数据库，复制相关的物理文件即可。目前，线上数据库一般很少能够接受关闭数据库，所以该备份方式很少使用。

温备份（Warm Backup）：温备份也是在数据库运行的过程中进行备份，但是备份会对数据库操作有所影响。该备份利用锁表的原理备份数据库，由于影响了数据库的操作，故该备份方式也很少使用。

优点：

备份和恢复操作都比较简单，能够跨mysql的版本，恢复速度快，属于文件系统级别的。

冷备份与恢复
[root@localhost ~]# systemctl stop mysqld.service
[root@localhost ~]# mkdir /backup
[root@localhost ~]# tar zcf /backup/mysql_all_$(date +%F).tar.XZ /usr/local/mysql/data/
tar: 从成员名中删除开头的“/”
###关闭数据库，之后打包

[root@localhost ~]# mkdir bak
[root@localhost ~]# mv /usr/local/mysql/data/ /bak/
[root@localhost ~]# mkdir restore
[root@localhost ~]# tar zxf /backup/mysql_all_2022-06-06.tar.XZ -C restore/
[root@localhost ~]# mv restore/usr/local/mysql/data/ /usr/local/mysql/
[root@localhost ~]# systemctl start mysqld.service
恢复数据库，采用将备份数据mv成线上库文件夹的方式
逻辑备份是对数据库逻辑组件（如：表等数据库对象）的备份

逻辑备份是以文件为基本单位将数据从主机复制到备机
指对数据库逻辑组件（如表等数据库对象）的备份
从数据库的备份策略角度来看，备份又可分为完全备份、差异备份和增量备份
优点：

恢复简单。备份的结果为ASCII文件，可以编辑。与存储引擎无关。可以通过网络备份和恢复。

缺点：

备份或恢复都需要mysql服务器进程参与。备份结果占据更多的空间，浮点数可能会丢失精度。还原之后，缩影需要重建。

四、MySQL 完全备份与恢复

1. 数据备份的重要性
   备份的主要目的是灾难恢复

在生产环境中，数据的安全性至关重要

任何数据的丢失都可能产生严重的后果

造成数据丢失的原因
程序错误

人为操作错误.

运算错误

磁盘故障

灾难(如火灾、地震)和盗窃

3.数据库备份的分类

1. 从物理和逻辑角度分
   物理备份

对数据库操作系统的物理文件(如数据文件、日志文件等)的备份
物理备份的方法 ：

冷备份(脱机备份) :是在关闭数据库的时候进行的；

热备份(联机备份) :数据库处于运行状态，依赖于数据库的日志文件；

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

逻辑备份

对数据库逻辑组件(如:表等数据库对象)的备份
完全备份:每次对数据库进行完整的备份
是对整个数据库、数据库结构和文件结构的备份

保存的是备份完成时刻的数据库

是差异备份与增量备份的基础

优点：备份与恢复操作简单方便 缺点：数据存在大量的重复、占用大量的备份空间及备份与恢复时间长
差异备份:备份自从上次完全备份之后被修改过的文件
增量备份:只有在上次完全备份或者增量备份后被修改的文件才会被备份

五、Mysqldump完全备份与恢复

1、备份文件
[root@localhost /]# systemctl stop mysqld.service
#关闭mysql服务
[root@localhost /]# mkdir /backup/
#创建备份目录
[root@localhost /]# rpm -q xz
#查看xz是否安装
[root@localhost /]# yum install xz -y
#安装xz工具
[root@localhost /]# tar Jcf /backup/mysql_all_$(date +%F).tar.xz /usr/local/mysql/data
#打包数据文件并以日期为文件名
[root@localhost /]# cd /backup/
[root@localhost backup]# ls
2、完全恢复

[root@localhost backup]# scp /backup/mysql_all_2022-06-06.tar.xz 192.168.13.10:/opt
#scp 远程连接发送文件
#########主机2###########
[root@localhost opt]# systemctl stop mysqld.service
cd[root@localhost opt]# cd /opt
[root@localhost opt]# ls
[root@localhost opt]# mkdir /opt/bak/
[root@localhost opt]# tar jxf mysql_all_2022-06-06.tar.xz -C /opt/bak/
[root@localhost bak]# mysql -u root -p
Enter password:
#设置的密码
mysql> show databases;
#查看库
mysql>use wangbao;
#进入库
mysql> show tables ;
#查看表
mysql> select * from qqq;
#表内容

逻辑备份
mysqldump是常用的逻辑备份工具。
mysqldump可以将指定的库、表导出为sql脚本。

1.完全备份一个或多个库
mysqldump -uroot -p密码] --databases 库名1 [库名2] >备份路径/备份文件名.sql
#导出后就是数据库脚本文件

[root@localhost bak]# mysqldump -uroot -p --databases wangbao >/opt/mysql.sql
#完全备份一个库
[root@localhost bak]# mysqldump -uroot -p --databases wangbao yan >/opt/mysql.sql
#完全备份多个库

2.完全备份mysql服务器中的所有的库（包括库中的表）
mysqldump -u root -p --all-databases > /备份路径/备份文件名.sql
[root@localhost bak]# mysqldump -u root -p --all–databases >/opt/all.sql
3.完全备份指定库中的部分表
mysqldump -u root -p [密码] [-d] 库名 表名1 [表名2] … > /备份路径/备份文件名.sql
[root@localhost opt]# mysqldump -u root -p wanbao qqq >/opt/qqq.sql

4.查看备份文件

[root@localhost opt]# cat qqq.sql |grep -v “^–” |grep -v “^/” |grep -v “^$”
#过滤-- / 以及空行为开头的行
5.mysqldump完全恢复

启动mysql
[root@localhost opt]# systemctl start mysqld
[root@localhost opt]# mysql -u root -p -e ‘drop database wangbao’;
#"-e"选项，用于指定连接mysql后执行命令，命令执行完后自动退出
[root@localhost opt]# mysql -u root -p -e ‘SHOW DATABASES’;
###恢复########
[root@localhost opt]# mysql -u root -p < /opt/mysql.sql
[root@localhost opt]# mysql -u root -p ‘SHOW DATABASES’
6.恢复数据表
#####备份表####
[root@localhost opt]# mysqldump -u root -p wangbao qqq > /opt/mysql/wb_qqq.sql
[root@localhost opt]# mysql -u root -p -e ‘drop table wangbao.qqq;’
[root@localhost opt]# mysql -u root -p wangbao < /opt/mysql/wb_qqq.sql

六、MySQL增量备份与恢复

1.mysql增量备份特点
特点：通过二进制日志间接实现（flush logs刷新日志），没有冗余、备份时间短、恢复时间长
2.备份类别
现在/etc/my.cnf中[mysqld]子配置中设置log-cin=mysql-bin开启二进制日志
然后再：binlog_format=MIXED指定二进制的记录格式为混合模式
STAEMENT 基于sql语句备份，较快，高并发是容易出错（默认是这个类型）
ROW 基于数据库行内容备份，较慢，安全准确
MIXED 混合模式，兼有两者优点，生产环境一般都用这个
3.备份命令
mysqladmin -u root -p[密码] flush-logs
#备份日志
mysqladmin --no-defaults --base64 -output=decode-rows -v 备份路径
#查看备份日志

[root@localhost ~]# vim /etc/my.cnf
[root@localhost ~]# systemctl restart mysqld.service
[root@localhost ~]# ls -l /usr/local/mysql/data/mysql-bin.*
[root@localhost ~]# mysqladmin -u root -p flush-logs
Enter password:
##备份日志文件
[root@localhost ~]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql-bin.000002
####查看备份日志
/!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1/;
/!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0/;
DELIMITER /!/;
#at 4
#220607 14:44:30 server id 1 end_log_pos 123 CRC32 0xc90356af Start: binlog v 4, server v 5.7.20-log created 220607 14:44:30
#Warning: this binlog is either in use or was not closed properly.
#at 123
#220607 14:44:30 server id 1 end_log_pos 154 CRC32 0xc0200398 Previous-GTIDs
#[empty]
SET @@SESSION.GTID_NEXT= ‘AUTOMATIC’ /* added by mysqlbinlog / /!/;
DELIMITER ;
#End of log file
/!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0/;

4.恢复类别
类别 命令
一般恢复 mysqlbinlog --no-defaults 备份路径
位置恢复 mysqlbinlog --no-defaults --start-position=‘位置点’ --stop-position=‘位置点’ 备份路径
时间恢复 mysqlbinlog --no-defaults --start-datetime=‘时间点’ --stop-datetime=‘时间点’ 备份路径
一般恢复
mysql> mysql> insert into qqq values(‘saddsa’, ‘男’,’ ') ;
Query OK, 1 row affected (0.00 sec)

mysql> insert into qqq values(‘sgagasgsa’, ‘男’,’ ') ;
Query OK, 1 row affected (0.00 sec)

##删除数据
[root@localhost ~]# mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000002
###日志恢复###
mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000001 | mysql -u root -p
###日志会保存在上一份日志中
位置恢复
[root@localhost ~]# mysqlbinlog --no-defaults --stop-datetime=‘25955’ /usr/local/mysql/data/mysql-bin.000002 | mysql -u root -p

时间恢复

[root@localhost ~]# mysqlbinlog --no-defaults --start-datetime=‘2022-6-7 16:04:04’ --stop-datetime=‘2022-6-7 16:09:02’ /usr/local/mysql/data/mysql-bin.000002 |mysql -u root -p