【MySQL数据库】mysql日志管理、备份与恢复

---

MySQL数据库备份及日志

在生产环境中，数据的安全性是至关重要的，任何数据的丢失都可能产生严重的后果。造成数据丢失的原因

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

所以数据库需要备份。这里以mysql5.7为例。

一、数据库备份分类：

从物理与逻辑的角度，备份可分为物理备份和逻辑备份。
（1）物理备份：对数据库操作系统的物理文件（如数据文件、日志文件等）的备份。又可以分为冷备份和热备份。

• 冷备份：关闭数据库时进行的备份操作

• 热备份：在数据库运行状态中进行备份操作，这种备份方法依赖于数据库的日志文件。

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

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

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

（1）完全备份：每次对数据库进行完整的备份。可以备份单个数据库，多个数据库，所有数据库，也可以备份数据
库中的单个表，多个表。

（2）差异备份：备份那些自从上次完全备份之后被修改过的文件，只备份数据库部分内容，但是存储和恢复速度快

（3）增量备份：只有那些在上次完全备份或者增量备份后被修改的文件才会被备份。

如何选择逻辑备份策略 (频率)

合理值区间
一周一次的全备，全备的时间需要在不提供业务的时间区间进行 晚上2-4点之间进行全备
增量:3天/2天/1天一次增量备份
差异:选择特定的场景进行备份
一个处理(NFS) 提供额外空间给与mysql 服务器用

完全备份与恢复

备份

使用mysqldump工具可以灵活的控制备份的内容，比如某几个表或库都可以单独备份。

对单个库进行完全备份。命令格式如下：

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

对多个库进行完全备份。命令格式如下：

mysqldump -u用户名 -p[密码] [选项] --databases 库名1 库名2 ... > /备份路径/备份文件名

对所有库进行完全备份。命令格式如下：

mysqldump -u用户名 -p[密码] --opt --all-databases > /备份路径/备份文件名

对表结构进行完全备份。命令格式如下：

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

对表进行完全备份。命令格式如下：

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

恢复

登录mysql，使用source命令恢复库。命令格式如下：

source 库备份脚本的路径

在不登录MySQL的情况下，使用mysql命令直接恢复整库。命令格式如下：

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

增量备份与恢复

MySQL没有提供直接的增量办法，但是可以通过对MySQL的二进制日志间接实现增量备份。二进制日志保存了所有更新或者可能更新数据库的操作。

特点：

没有重复数据，备份量不大，时间短。

需要上次完全备份及完全备份之后所有的增量备份才能恢复，而且要进行逐个反推恢复，操作繁琐。

实现增量备份

要进行MySQL增量备份，首先要开启二进制日志功能。
（1）在mysql的配置文件的[mysqld]选项中加入log-bin=mysql-bin，然后重启服务。

 vim /etc/my.cnf
      [mysqld]
      log-bin=mysql-bin

然后重启mysql

（2）使用mysqldump完全备份school库。

 mysqldump -u root -p123456 xcz1 > /bak/mysql_bak/$(date +%F).sql 

（3）使用mysqladmin的选项flush-logs生成新的二进制文件，这样在插入新的数据后，新的二进制文件对应的就是数据库的变化的内容。

mysqladmin -uroot -p123456 flush-logs 

（4）插入一条新的数据，以模拟数据的增加或变更。

此时的数据库变化保存在编号2 的二进制文件中，使用mysqlbinlog命令可以查看二进制文件的内容，里面保存了插入数据的语句。

mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000002

（5）再次执行flush- logs操作生成新的二进制文件，而新的二进制文件会保存之后的数据操作。

mysqladmin -uroot -p123456 flush-logs

基于时间点与位置恢复

基于位置恢复
使用基于时间点的恢复可能会出现在一个时间点里既同时存在正确的操作又存在错误的操作，基于位置是一种更为精确的恢复方式。

mysqlbinlog --no-defaults --start-position='1560' --stop-position='1825'
/opt/mysql-bin.000006 | mysql -uroot -pabc123

基于时间点恢复

mysqlbinlog [--no-defaults] --start-datetime='年-月-日 小时:分钟:秒' --stopdatetime='
年-月-日小时:分钟:秒' 二进制日志 | mysql -u 用户名 -p 密码
#例如：
mysqlbinlog --no-defaults --stop-datetime='2023-07-17 23:04:18' /opt/mysqlbin.
000006 | mysql -uroot -pabc123

二.MySQL日志管理

MySQL日志管理
MySQL的默认日志保存位置为/usr/local/mysql/data
yum安装为 /var/lib/mysql
日志开启方式有两种：通过配置文件或者是通过命令
通过命令修改开启的日志是临时的，关闭或重启服务后就会关闭

可在 /etc/my.cnf 配置文件中的 [mysqld] 中进行日志的路径修改、开启、关闭等操作
①、错误日志
用于记录 mysql 启动、停止或运行时产生的错误信息
可通过一下字段进行更新
log-error=/usr/local/mysql/data/mysql_error.log （指定日志的保存位置和文件名）
②、二进制日志
二进制日志，用来记录所有更新的数据或者已经潜在更新了数据的语句，记录了数据的更改，可用于数据恢复
开启方式：

log-bin=mysql-bin #或者 
log_bin=mysql-bin

③、中继日志
一般情况下，它在 mysql 主从同步（复制）、读写分离集群的从节点上才开启。
主节点一般不需要这个日志。
④、慢查询日志
慢查询日志，用来记录所有执行时间超过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 '%log_bin%';

②、查看慢查询日志功能是否开启

show variables like '%slow%';

③、查看慢查询时间设置

show variables like 'long_query_time';

查看通用查询日志是否开启

#重新mysql服务
systemctl restart mysqld.service
mysql -uroot -pabc123

show variables like 'general%';