MySQL数据库日志管理+备份

大家好，欢迎来到IT知识分享网。

一、MySQL日志管理

1.1 日志分类

• 事务日志：transaction log

  事务日志的写入类型为”追加”，因此其操作为”顺序IO”；通常也被称为：预写式日志 write ahead logging

  事务日志文件： ib_logfile0， ib_logfile1

• 错误日志 error log
• 通用日志 general log
• 慢查询日志 slow query log
• 二进制日志 binary log （备份时使用的）
• 中继日志 reley log，在主从复制架构中，从服务器用于保存从主服务器的二进制日志中读取的事件

1.2 日志管理

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秒 systemctl restart mysqld 

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; #在数据库中设置开启慢查询的方法 

二、备份

2.1 数据备份的必要性

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

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

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

2.2 数据备份分类

2.2.1物理备份

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

物理备份的方法 ：

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

2.2.2 逻辑备份

对数据库的表或者对象进行备份。

2.3 数据库备份策略

完全备份：整个数据集，每次都备份完整的数据库。

差异备份：仅备份最近一次完全备份后变化的数据，备份较慢，还原简单

增量备份：仅备份最近一次完全备份或增量备份（如果存在增量）以来变化的数据，备份较快，还原复杂.

2.4 常用的备份方法和工具

备份速度快，恢复时也是最简单的。

专用备份工具mysqldump或mysqlhotcopy

mysqldump常用的逻辑备份工具（逻辑热备）

备份速度较慢（如果数据库超过100G，一般不建议使用mysqldump)

mysqlhotcopy仅拥有备份MyISAM和ARCHIVE表

archive

XtraBackup(物理热备)

2.5 数据库上云迁移

冷迁移

热迁移

三、MySQL完全备份

3.1 简介

• 完全备份是对整个数据库、数据库结构和文件结构的备份
• 保存的是备份完成时刻的数据库
• 是差异备份与增量备份的基础

3.2 物理冷备份与恢复

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

3.2.1 物理冷备份

#先关闭数据库 systemctl stop mysqld #建立备份文件存放目录 mkdir /opt/bak #压缩备份 tar zcvf /opt/bak/mysql_full_$(date +%F).tar.gz /usr/local/mysql/data/* $(date -d "-1 day" +%Y%m%d) 

3.2.2 解压恢复

主机1：192.168.10.20

主机2：192.168.10.30

备份到另一台主机

#先关闭数据库 systemctl stop mysqld scp 192.168.10.20:/opt/bak/* /opt #将主机1的备份包远程传输到主机2 

#先备份数据，防止恢复失败 mv /usr/local/mysql/data /usr/local/mysql/data.bak 

cd /opt tar xf mysql_full_2024-07-03.tar.gz cp -a usr/local/mysql/data /usr/local/mysql/data #不用-a 权限不足，无法启动 

systemctl start mysqld mysql -u root -p 密码 #登录到数据库 

3.3 mysqldump备份与恢复

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

mysqldump -u root -p[密码] --databases 库名1 [库名2] … > /备份路径/备份文件名.sql #导出的就是数据库脚本文件 如果不加 --databases ,只备份库中的表，不会备份库 

#举个例子 #单个数据库 mysqldump -u root -p -B hellodb > /opt/bak/hellodb.sql #多个数据库 mysqldump -u root -p -B hellodb mysql > /opt/bak/hd.sql 

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

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

#举个例子 mysqldump -u root -p --all-databases > /opt/bak/all.sql cat /opt/all.sql | grep "^CREATE DATABASES" 

3）完全备份指定库中的部分表

mysqldump -u root -p[密码] 库名 [表名1] [表名2] … > /备份路径/备份文件名.sql 

举个例子

#备份hellodb的students表 mysqldump -u root -p [-d] hellodb students > /opt/bak/hellodb-students.sql #使用“-d”选项，说明只保存数据库的表结构 #不使用“-d”选项，说明表数据也进行备份 

4）查看备份文件

cat xx.sql|grep -v "^--"| grep -v "^/" | grep -v "^$" grep -E -v "^--|^/\*|^$" #或运行符 

#举个例子 cat hellodb-students.sql|grep -v "^--" | grep -v "^/" | grep -v "^$" 

四、MySQL 完全恢复

4.1 恢复数据库

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

在生产环境中，可以使用Shell脚本自动实现定时备份。

4.1.1 通过source命令恢复

先使用mysqldump工具，生成备份sql脚本；

然后登录到MySQL数据库；

再执行source 备份sql脚本的路径

MySQL[(none)]> source /backup/all-data.sql 

举个例子

先备份目标数据库，以hellodb为例 mysqldump -uroot -p --databases hellodb > /opt/bak/hellodb.sql 

模拟数据库被删除 mysql -uroot -p -e "drop database hellodb;" mysql -uroot -p -e "show databases;" 

source命令执行sql脚本 #先登录到数据库 mysql -u root -p #执行脚本 source /opt/bak/hellodb.sql; 

4.1.2 通过mysql命令

输入重定向

#先模拟数据库删除，这里不再赘述 #恢复数据库 mysql -u root -p < /opt/bak/hellodb.sql mysql -u root -p -e 'show databases;' 

使用管道符

cat hellodb.sql | mysql -uroot -p 

4.2 恢复数据表

当备份文件中只包含表的备份，而不包含创建的库的语句时，执行导入操作时必须指定库名，且目标库必须存在。

#备份byyb的student表 mysqldump -u root -p [-d] hellodb students > /opt/bak/hellodb-students.sql 

#模拟删除表 mysql -u root -p -e 'drop table hellodb.students;' mysql -u root -p -e 'show tables from hellodb;' 

#恢复表数据 mysql -uroot -p hellodb < /opt/bak/hellodb-students.sql #查看表数据，验证是否成功恢复 mysql -u root -p -e 'show tables from hellodb;' 

五、MySQL增量备份与恢复

5.1 MySQL增量备份

5.1.1 开启二进制日志功能

vim /etc/my.cnf [mysqld] log-bin=mysql-bin binlog_format = MIXED #可选，指定二进制日志(binlog)的记录格式为 MIXED server-id = 1 #二进制日志(binlog)有3种不同的记录格式：STATEMENT(基于SQL语句)、ROW(基于行)、MIXED(混合模式)，默认格式是STATEMENT 

systemctl start mysqld ls -l /usr/local/mysql/data/mysql-bin.* 

5.1.2 通过刷新二进制日志文件间接实现增量备份

mysqladmin -uroot -p[密码] flush-logs 

5.1.3 查看二进制日志文件的内容

mysqlbinlog --no-defaults --base64-output=decode-rows -v 二进制文件（绝对路径） #--base64-output=decode-rows：使用64位编码机制去解码并按行读取 #-v：显示详细内容 

举个例子

#举个例子 cp /usr/local/mysql/data/mysql-bin.000001 /opt mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000001 

5.2 MySQL增量恢复

MySQL没有提供直接的增量备份方法，可通过MySQL提供的二进制日志间接实现增量备份。

5.2.1 一般恢复

1）模拟丢失更改的数据的恢复步骤

use hellodb; delete from students where stuid=24; mysqlbinlog --no-defaults /data/mysql-bin.000001 | mysql -u root -p 

use hellodb; drop table students; mysql -u root -p kgc < /opt/hello_students_2024-07-04.sql mysqlbinlog --no-defaults /opt/mysql-bin.000002 | mysql -u root -p

5.2.2 断点恢复

#查看二进制文件 mysqlbinlog --no-defaults --base64-output=decode-rows -v /data/mysql-bin.000002 

1）基于位置恢复

mysqlbinlog --no-defaults --start-position='位置点' --stop-position='位置点' 二进制日志文件（绝对路径） | mysql -uroot -p[密码] 

#仅恢复到操作 ID 为“623”之前的数据，即不恢复“user4”的数据 mysqlbinlog --no-defaults --stop-position='623' /data/mysql-bin.000002 | mysql -uroot -p #仅恢复“user4”的数据，跳过“user3”的数据恢复 mysqlbinlog --no-defaults --start-position='623' /data/mysql-bin.000002 | mysql -uroot -p 

2）基于时间点恢复

mysqlbinlog --no-defaults --start-datetime='时间点' --stop-datetime='时间点' 二进制日志文件（绝对路径） | mysql -uroot -p[密码] 

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

5.3 实战案例

每天2：30做完全备份，早上10：00误删除了表students，10：10才发现故障，现需要将数据库还原到10：10的状态，且恢复被删除的students表

#开启二进制日志 vim /etc/my.cnf log_bin=/data/mysql-bin server-id = 1 chown mysql.mysql /data/ -R systemctl restart mysqld 2:30 执行全备 mysqldump -uroot -pabc123 -A -F --single-transaction --master-data=2 > /opt/all.sql #完全备份后数据更新 insert students (name,age,gender) values('rose',20,'f'); insert students (name,age,gender) values('jack',20,'f'); 10:00 误删除了一个students的表 drop table students; 后续其余的表继续更新 insert teachers (name,age,gender)values('test',30,'M'); insert teachers (name,age,gender)values('test1',30,'M'); 10点10分发现进行还原 #停止数据库访问 [root@localhost ~]#grep '\-\- CHANGE MASTER TO' /opt/all.sql #从完全备份中，找到二进制位置 -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=154; #备份 完全备份后的二进制日志 mysqlbinlog --start-position=154 /data/mysql-bin.000002 > /opt/inc.sql #找到 删除的语句 [root@localhost ~]#grep -i "^drop table" /opt/inc.sql DROP TABLE `students` /* generated by server */ #删除 删表的那一行 sed -i.bak '/^DROP TABLE/d' /opt/inc.sql #登录数据库还原 set sql_log_bin=0; #先关闭二进制日志 临时关闭 set sql_log_bin=1; source /opt/inc.sql