MySQL日志管理、备份与恢复

一.MySQL日志管理

MySQL 的日志默认保存位置为  /usr/local/mysql/data

vim /etc/my.cnf
[mysqld]
##错误日志，用来记录当MySQL启动、停止或运行时发生的错误信息，默认已开启

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

##通用查询日志，用来记录MySQL的所有连接和语句，默认是关闭的
general_log=ON

general_log_file=/usr/local/mysql/data/mysql_general.log

##二进制日志(binlog)，用来记录所有更新了数据或者已经潜在更新了数据的语句，记录了数据的更改，可用于数据恢复，默认已开启

log-bin=mysql-bin				#也可以 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秒

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..数据备份的重要性

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

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

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

造成数据丢失的原因：

• 程序错误
• 人为操作错误
• 运算错误
• 磁盘故障
• 灾难(如火灾、地震)和盗窃

2.数据库备份的分类

 （1）从物理与逻辑的角度，备份可分为

【1】物理备份:对数据库操作系统的物理文件(如数据文件:日志文件等)的备份

物理备份方法：

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

【2】逻辑备份:对数据库逻辑组件(如:表等数据库对象)的备份；以SQL

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

【1】完全备份:每次对数据库进行完整的备份

【2】差异备份:备份自从上次完全备份之后被修改过的文件

【3】增量备份:只有在上次完全备份或者增量备份后被修改的文件才会被备份

3.常见的备份方法

（1）物理冷备

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

（2）专用备份工具mysqldump或mysqlhotcopy

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

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

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

（4）第三方工具备份

• 免费的MySQL热备份软件Percona XtraBackup

备份工具：

tar压缩打包（完全备份，物理冷备        

mysqldump（完全备份，逻辑热备）  

 mysqlhotcopy（完全备份，逻辑热备，仅支持MyISAM和ARCHIVE引擎表）      

二进制日志（增量备份）          

 PXB XtraBackup innobackupex（完全备份、增量备份，物理热备）

4.MySQL完全备份

是对整个数据表、数据库结构和文件结构的备份

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

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

优点
备份与恢复操作简单方便

缺点

数据存在大量的重复

占用大量的备份空间

备份与恢复时间长

5.数据可完全备份分类

（1）物理冷备份与恢复

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

（2）mysqldump备份与恢复

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

6.MySQL物理冷备份即恢复

完全备份

物理冷备：先关闭数据库，使用 tar 命令压缩打包备份数据库的数据目录和文件 mysql/data/

192.168.10.10 

systemctl stop mysqld.service      #关闭数据库服务

cd /usr/local/mysql/data           #切换到MySQL的日志保存位置
 
tar zcf /opt/mysql_backup_$(date +%Y%m%d) data/      
#对data/目录进行压缩，并改名为mysql_backup_$(date +%Y%m%d)
                                        #$(date +%Y%m%d) 为日期

scp mysql_backup_20240619 192.168.10.60:/opt/    
#将压缩包远程传输到指定主机的指定目录

192.168.10.60

systemctl stop mysqld.service   #关闭数据库服务

tar xf mysql_backup_20240619    #对压缩包进行解压会出现data/目录

cd /usr/local/mysql/            #切换到当前主机的mysql目录下

mv data/ data.bak        #将当前主机的data/目录 改名为data.bak

mv /opt/data/ /usr/local/mysql/   #将解压后出现的data目录移动到当前主机的mysql目录下

systemctl restart mysqld       #启动数据库服务

mysql -u root -pabc123          #登录数据库

之后就能查看物理冷备份过来的数据库信息

7.mysqldump 备份数据库

mysqldump -u root -pabc123 mydb > /opt/backup/mydb1.sql

mysqldump -u root -pabc123 --databases mydb > /opt/backup/mydb2.sql

mysqldump -u root -pabc123 --databases mydb mysql > /opt/backup/mydb2_mysql.sql

mysqldump -u root -pabc123 --all-databases > /opt/backup/all-databases.sql

8.mysqldump 备份数据表

mysqldump可针对库内特定的表进行备份

musqldump -u 用户名 -p 密码 -[选项] 数据库名 表名 > /备份路径/备份文件名

9.恢复数据库

（1）使用mysqldump导出的脚本，可使用导入的方法

• source命令
• mysql命令

（2）使用source恢复数据库的步骤

• 登录到MySQL数据库
• 执行source 备份sql脚本的路径

source /opt/backup/mydb2.sql

source 备份sql脚本的路径

第一种： 

第二种：

10.恢复表的操作

恢复表时同样可以使用source或者mysql命令

source恢复表的操作与恢复库的操作相同

当备份文件中只包含表的备份，而不包括创建库的语句时，必须指定库名，且目标库必须存在

mysql -u 用户名 -p [密码] < 表备份脚本的路径

mysql -u root -p mysql < /backup/mysql-user.sql

三.MySQL增量备份

1.增量备份介绍

（1）使用mysqldump进行完全备份存在的问题

• 备份数据中有重复数据
• 备份时间与恢复时间过长

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

（2）特点：

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

（3）MySQL没有提供直接的增量备份方法

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

（5）MySQL二进制日志对备份的意义

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

2.MySQL增量备份

（1）开启二进制日志功能

vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
binlog_format = MIXED	#可选，指定二进制日志(binlog)的记录格式为 MIXED
server-id = 1

（2）二进制日志(binlog)有3种不同的记录格式：

STATEMENT(基于SQL语句)：记录速度快，省空间，但是在高并发情况下，可能会导致记录顺序

有误，导致数据恢复时，数据有偏差。

ROW(基于行)：基于数据内容行进行记录，不仅记录执行的命令语句，还会记录命令影响的相关数据行。

MIXED(混合模式)：一般情况下，基于sql语句形式记录二进制形式；在高并发情况下，无法保证二进制文件准确性，会使用row格式去记录，保证二进制文件的准确性。

通过刷新二进制日志实现增量备份

mysqladmin -u root -p密码 flush-logs

查看二进制日志内容

mysqlbinlog --no-defaults --base64-output=decode-rows -v 二进制文件路径

3.MySQL增量恢复

（1）一般恢复

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

mysqlbinlog [--no-defaults] 增量备份文件 | mysql -u 用户名 -p

实验步骤

#创建表格，并在里面插入数据
create database test01;
use test01;
create table xy101 (id int, name varchar(20), age smallint, sex char(2));
insert into xy101 values (1, '张三', 28, '男');
insert into xy101 values (2, '李四', 26, '男');
insert into xy101 values (3, '王五', 25, '男');
insert into xy101 values (4, '赵六', 24, '男');

#创建放置数据库、表保存的目录和每天保存的目录
mkdir backup-all
mkdir backup-everyday

#将数据库和数据库的表进行保存到放置数据库和表保存的目录
mysqldump -u root -pabc123 --databases test01 > /root/backup-all/test01_$(date +%Y%m%d).sql
mysqldump -u root -pabc123 test01 xy101 > /root/backup-all/test01_xy101_$(date +%Y%m%d).sql

#刷新二进制文件，将今天的二进制文件进行保存到每天保存的目录
mysqladmin -u root -pabc123 flush-logs
cp mysql_bin.000003 /root/backup-everyday/mysql_bin.$(date +%Y%m%d)

#第二天，在表中插入两条数据
insert into xy101 values (5, '刘二', 22, '男');
insert into xy101 values (6, '孙一', 22, '男');

#刷新二进制文件，将第二天的二进制文件进行保存到每天保存的目录
mysqladmin -u root -pabc123 flush-logs
cp mysql_bin.000004 /root/backup-everyday/mysql_bin.20240620

#第三天，在表中插入两条数据
insert into xy101 values (7, '胡七', 22, '男');
insert into xy101 values (8, '史八', 22, '男');

#刷新二进制文件，将第三天的二进制文件进行保存到每天保存的目录
mysqladmin -u root -pabc123 flush-logs
cp mysql_bin.000005 /root/backup-everyday/mysql_bin.20240621

#模拟误删除数据库
drop database test01;

#退出系统，去到保存数据库和表的目录，进行恢复数据库，并查看是否成功
quit
mysql -u root -pabc123 < /root/backup-all/test01_20240619.sql
mysql -u root -pabc123 -e 'show databases;
mysql -u root -pabc123 -e 'show tables from test01;
mysql -u root -pabc123 -e 'select * from test01.xy101;

#切换到保存每天保持的目录
cd /root/backup-everyday/

#查看第二天的二进制文件
mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql_bin.20240620

#对第二天的数据进行恢复
mysqlbinlog --no-defaults mysql_bin.20240620 | mysql -u root -pabc123
mysql -u root -pabc123 -e 'select * from test01.xy101;

#对第三天的数据进行恢复
mysqlbinlog --no-defaults mysql_bin.20240621 | mysql -u root -pabc123
mysql -u root -pabc123 -e 'select * from test01.xy101;

（2）断点恢复

【1】基于位置恢复

• 数据库在某一时间点可能既有错误的操作也有正确的操作
• 可以基于精准的位置跳过错误的操作

mysqlbinlog --no-defaults --start-position='开始位置点' --stop-position='结束位置点'  二进制文件路径 | mysql -u root -p密码

恢复数据到指定位置

mysqlbinlog --stop-position='操作 id'  二进制日志 | mysql -u 用户名 -p 密码

从指定的位置开始恢复数据

mysqlbinlog --start-position='操作 id' 二进制日志 | mysql -u 用户名 -p 密码

mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql_bin.20240621

#查看二进制文件

只恢复id为8的数据

mysqlbinlog --no-defaults --start-position='608' ./mysql_bin.20240621 | mysql -u root -pabc123

                                           #608是id为8的数据开始的位置

#mysqlbinlog --no-defaults --start-position='608' ./mysql_bin.20240621

#指定数据文件从608开始的地方往下读到结束

mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql_bin.20240621

#查看二进制文件

只恢复id为7的数据

mysqlbinlog --no-defaults --stop-position='460' ./mysql_bin.20240621 | mysql -u root -pabc123

                                           #462是id为7的数据commit保存的位置

#mysqlbinlog --no-defaults --stop-position='460' ./mysql_bin.20240621

#指定数据文件读到460的地方结束

读取两个数据中间的数据

mysqlbinlog --no-defaults --start-position='xxx' --stop-position='xxx' ./mysql_bin.20240621 | mysql -u root -pabc123

#第一个xxx是中间数据开始的地方；第二个xxx是中间数据commit保存位置结束的地方

【2】基于时间点恢复

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

mysqlbinlog --no-defaults --start-datetime='YYYY-mm-dd HH:MM:SS' --stop-datetime='YYYY-mm-dd HH:MM:SS'  二进制文件路径 | mysql -u root -p密码

#仅恢复到 16∶41∶24 之前的数据，即不恢复“user4”的数据
mysqlbinlog --no-defaults --stop-datetime='2020-11-22 16:41:24' /opt/mysql-bin.000002 |mysql -uroot -p

#仅恢复“user4”的数据，跳过“user3”的数据恢复
mysqlbinlog --no-defaults --start-datetime='2020-11-22 16:41:24' /opt/mysql-bin.000002 |mysql -uroot -p

（3） 断点恢复的原则：

如果要恢复到某条sql语句之前的所有数据，就stop在这个语句的位置点或时间点之前

如果要恢复某条sql语句及其之后的所有数据，就从这个语句的位置点或时间点开始start

拓展：编写完全备份的脚本和增量备份的脚本

完全备份的脚本

增量备份的脚本