1. 数据库的日志管理
1.1 日志管理分类
- 通用日志： 记录登录数据库用户的操作行为日志 （审计日志）
- 错误日志： 记录数据库服务运行情况信息 （排查故障）
- 二进制日志： 记录数据库用户DML DDL DCL操作语句信息（不包含查询语句信息） （恢复数据 主从同步）
- 慢查询日志： 记录数据库用户查询语句信息 （时间长 没有走索引的查询语句信息 -- 优化调整 -- select * from t1）
消耗磁盘资源多 影响数据库并发业务 影响数据库主从数据一致性
1.2 通用日志管理
- 创建生产日志
vim /etc/my.cnf
general_log=ON
general_log_file=/data/3306/log/general.log
- 查看日志信息
[root@db-01 ~ 11:05]# tail -f /data/3306/log/general.log
2024-12-13T03:05:39.568135Z 8 Connect root@localhost on using Socket
2024-12-13T03:05:39.568568Z 8 Query select @@version_comment limit 1
2024-12-13T03:06:13.463763Z 8 Query select @@version_comment
2024-12-13T03:06:45.285118Z 8 Query show databases
2024-12-13T03:07:02.174836Z 8 Query SELECT DATABASE()
2024-12-13T03:07:02.175060Z 8 Init DB mysql
2024-12-13T03:07:02.175909Z 8 Query show databases
2024-12-13T03:07:02.176637Z 8 Query show tables
1.3 错误日志管理
- 创建生成日志
vi /etc/my.cnf
log_error=/data/3306/log/error.log
touch /data/3306/log/error.log
chown mysql.mysql /data/3306/log/error.log
- 查看日志信息
cat /data/3306/log/error.log
2024-12-13T03:29:42.035930Z 0 [System] [MY-010116] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.32) starting as process 2995
2024-12-13T03:29:42.043548Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-12-13T03:29:42.335614Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-12-13T03:29:42.536109Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2024-12-13T03:29:42.536155Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2024-12-13T03:29:42.562485Z 0 [System] [MY-010931] [Server] /usr/local/mysql/bin/mysqld: ready for connections. Version: '8.0.32' socket: '/tmp/mysql.sock' port: 3306 MySQL Community Server - GPL.
2024-12-13T03:29:42.562455Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /tmp/mysqlx.sock
##1.4 二进制日志（binlog）管理配置应用
1.4.1 二进制日志配置参数
- log_bin -- 开启二进制日志，并设置保存路径信息（新版数据库默认开启 旧版数据库默认关闭）
- sql_log_bin -- 是否临时禁用二进制日志记录功能 （临时设置--会话级别设置） 1-- 表示记录日志 0 -- 表示关闭记录功能
- sync_binlog -- 二进制日志记录方式（数据库双一配置）
-- 参数信息配置0：表示由操作系统缓存自己决定，什么时候刷新日志到磁盘中； 性能考虑设置
-- 参数信息配置1：表示每次事务提交，立即刷新日志到磁盘中；(此方式配置更安全) 安全考虑设置
-- 参数信息配置N：表示每组事务提交，按照组的事务次数定义，确定刷新日志到磁盘中的频次；（可以有效减少IO性能损耗）
-- 参数官方资料链接：https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html - binlog_format -- 设置日志格式信息
SBR -- 语句格式记录binlog信息 可以清楚看到DML相关语句操作信息 update insert delete 恢复数据会出现不易
RBR -- 行格式记录binlog信息 不能清楚看到DML相关语句操作信息 将行的数据信息编码处理记录 恢复数据可以保证一致性
- 查看日志信息
# ll /data/3306/log/bin-log.*
-rw-r----- 1 mysql mysql 157 Dec 13 12:15 /data/3306/log/bin-log.000001
-rw-r----- 1 mysql mysql 30 Dec 13 12:15 /data/3306/log/bin-log.index
- 开启二进制日志
vi /etc/my.cnf
log_bin=/data/3306/log/bin-log
1.4.2 二进制日志查看方法
- 在数据库中查看
mysql> show binary logs;
+----------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+----------------+-----------+-----------+
| bin-log.000001 | 157 | No |
| bin-log.000002 | 202 | No |
| bin-log.000003 | 180 | No |
| bin-log.000004 | 157 | No |
+----------------+-----------+-----------+
-- 查看生成binlog日志信息，会显示生成过的binlog名称和大小信息
mysql> show master status;
+----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| bin-log.000004 | 157 | | | |
+----------------+----------+--------------+------------------+-------------------+
-- 显示最新binlog，以及大小信息或者记录位置点信息
-- Binlog_Do_DB 若开启数据库binlog日志白名单功能，会显示哪个数据库的操作将记录到binlog
-- Binlog_Ignore_DB 若开启数据库binlog日志黑名单功能，会将指定数据库的操作不会记录到binlog
-- Executed_Gtid_Set 将每个数据库的操作行为设置一个唯一编号信息
create database test01 --- 157 - 170 唯一编号 0004157170 bin-log.000004 0004157170
create database test02 --- 157 - 170 唯一编号 0005157170 bin-log.000005
drop database test01
157 - 170 create database test01
mysql> show binlog events in 'bin-log.000004';
+----------------+------+----------------+-----------+-------------+-------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+----------------+------+----------------+-----------+-------------+-------------------------------------------------------------------------------+
| bin-log.000004 | 4 | Format_desc | 1 | 126 | Server ver: 8.0.32, Binlog ver: 4 |
| bin-log.000004 | 126 | Previous_gtids | 1 | 157 | |
| bin-log.000004 | 157 | Anonymous_Gtid | 1 | 234 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| bin-log.000004 | 234 | Query | 1 | 344 | drop database test01 /* xid=9 */
- DDL操作语句记录binlog：会记录时间编号信息（需要开启gtid） 以及DDL操作语句信息（可读）
- DML操作语句记录binlog：会记录时间编号信息（需要开启gtid） 以及DML操作语句信息（不可读-ROW格式） 还会生成记录begin... commit信息
- begin ... commit / begin .. rollback -- 是否将内存中的改动信息/创建信息/删除信息，写入到磁盘（内存信息落盘）
-- 实现可以确认或反悔的机制称为数据库事务机制
- 在命令行中查看
mysqlbinlog bin-log.000004
mysqlbinlog --base64-output=decode-rows -vvv bin-log.000004
### INSERT INTO `test01`.`t1` INSERT INTO `test01`.`t1` set @1（第一列）=1（设置的信息）
### SET
### @1=1 /* INT meta=0 nullable=1 is_null=0 */
### INSERT INTO `test01`.`t2` INSERT INTO `test01`.`t2` SET @1=1 @2='xiaoA' @3=20
### SET
### @1=1 /* INT meta=0 nullable=1 is_null=0 */
### @2='xiaoA' /* STRING(40) meta=65064 nullable=1 is_null=0 */
### @3=20 /* TINYINT meta=0 nullable=1 is_null=0 */
### INSERT INTO `test01`.`t2` INSERT INTO `test01`.`t2` SET @1=2 @2='xiaoB' @3=21
### SET
### @1=2 /* INT meta=0 nullable=1 is_null=0 */
### @2='xiaoB' /* STRING(40) meta=65064 nullable=1 is_null=0 */
### @3=21 /* TINYINT meta=0 nullable=1 is_null=0 */
- sql_log_bin 临时关闭binlog功能
sql_log_bin -- 是否需要临时关闭binlog日志记录功能
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
mysql> create database test03;
Query OK, 1 row affected (0.00 sec)
mysql> create database test04;
Query OK, 1 row affected (0.00 sec)
mysql> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)
mysql> create database test05;
Query OK, 1 row affected (0.01 sec)
| bin-log.000004 | 1810 | Anonymous_Gtid | 1 | 1887 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| bin-log.000004 | 1887 | Query | 1 | 2001 | create database test02 /* xid=35 */ |
| bin-log.000004 | 2001 | Anonymous_Gtid | 1 | 2078 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| bin-log.000004 | 2078 | Query | 1 | 2192 | create database test05 /* xid=42 */
-- 在进行数据恢复时，恢复数据的语句就可以不用再记录到binlog了；
- binlog_format 加密与可读性
- row （RBR） -- 加载恢复binlog日志可以保证数据一致性，但是阅读性差 （DML操作语句）
- statement （SBR） -- 加载恢复binlog日志无法保证数据一致性，但是阅读性强 （DML操作语句）
- mixed （MBR） -- 数据库服务自动选择识别binlog信息，自动显示dml信息 （明文信息 看不明白-编码信息）
mysql> set global binlog_format='statement'; -- 临时全局设置（可以永久设置）
- 其他函数
- sync_binlog -- 影响binlog日志写入到文件的过程方式
- sync_binlog=0 -- binlog日志文件中具有信息记录，由系统决定
- sync_binlog=1 -- 每操作一次commit，都会及时将事件信息同步到磁盘binlog文件
- sync_binlog=3 -- 每操作三次commit，才会将对应事件信息同步到磁盘binlog文件
1.4.3 二进制日志过滤查看
-
方式一：在数据库中利用语句功能过滤
- pager less -- 开启binlog过滤功能
- pager grep "drop database" -- 定义需要过滤显示的binlog内容信息
-
方式二：在命令行中利用命令功能过滤 （grep sed awk）
- mysqlbinlog bin-log.000004|grep/sed/awk
1.4.4 二进制日志恢复数据
数据库服务需要进行数据恢复：
情况一：数据库数据出现物理损坏情况 --- 数据库服务器宕机 磁盘故障 文件系统异常 数据库目录有文件损害 ....
如何恢复数据：使用备份文件 使用从数据库
情况二：数据库数据出现逻辑损坏情况 --- 误删除 误修改 误插入
如何恢复数据：使用备份文件 使用二进制文件
案例
0-01 9:00 创建一个新的数据库
create database test06;
10-01 10:00 创建一个新的数据表
use test06;
create table t1 (id int,name char(5));
10-01 11:00 在新的表中插入数据
insert into t1 values (1,'xiaoA');
insert into t1 values (2,'xiaoB'); 00001 update 00002 delete 0003 insert 0004 ... 005 ... 0006
insert into t1 values (3,'xiaoC');
10-01 12:00 误将数据表信息清空了
delete from t1;
解决恢复方案
10-01 12:00+ 恢复数据
步骤一：确认需要恢复的数据信息
pager grep "t1"
步骤二：获取恢复数据的位置信息
起始位置点：719
结束位置点：1484
步骤三：利用mysqlbinlog命令将需要的数据信息进行截取，并利用截取后的数据文件恢复数据
--start-position：719
--stop-position： 1484
mysqlbinlog --start-position=719 --stop-position=1484 bin-log.000005
mysqlbinlog --start-position=719 --stop-position=1484 bin-log.000005 >/tmp/t1.sql
mysql -uroot -p123456 </tmp/t1.sql
1.4.5 企业实际情况
企业恢复数据情况01：有些需要恢复数据的binlog文件被清理了
企业恢复数据情况02：需要恢复的数据信息分不在不同binlog文件中 备份数据恢复
企业恢复数据情况03：只是误操作影响了部分行的数据 1000w行 误操作影响4行数据，如何将误操作的数据修改
delete from t1 where name in ('李四'，'张三','王五')
1.4.6 二进制日志切割设置
- 方式一：利用命令切割：（手工）
mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)
mysql> show binary logs;
+----------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+----------------+-----------+-----------+
| bin-log.000001 | 157 | No |
| bin-log.000002 | 202 | No |
| bin-log.000003 | 180 | No |
| bin-log.000004 | 3520 | No |
| bin-log.000005 | 3413 | No |
| bin-log.000006 | 202 | No |
| bin-log.000007 | 157 | No |
+----------------+-----------+-----------+
#命令行
mysqladmin -uroot -p123456 flush-logs
- 方式二：实现自动切割
编写切割脚本文件 -- 定时任务中
mysqladmin -uroot -p123456 flush-logs
实现根据日志量进行自动切割
select @@max_binlog_size;
set global max_binlog_size=1048576; -- 设置为1M
1.4.7 二进制日志清理设置
- 方式一：进行日志信息自动清理
- binlog_expire_logs_seconds -- 按照秒为单位，进行历史切割日志的清理 （30天）
- expire_logs_days -- 按照天为单位，进行历史切割日志的清理 （默认关闭）
- set global -- 临时设置，进行配置项功能测试；
- 方式二：进行日志信息手工清理
mysql> show binary logs;
+----------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+----------------+-----------+-----------+
| bin-log.000009 | 202442 | No |
| bin-log.000010 | 202 | No |
| bin-log.000011 | 202 | No |
| bin-log.000012 | 202 | No |
| bin-log.000013 | 202 | No |
| bin-log.000014 | 157 | No |
+----------------+-----------+-----------+
6 rows in set (0.00 sec)
mysql> purge binary logs to 'bin-log.000013'; -- 根据日志名称进行手工删除日志
Query OK, 0 rows affected (0.00 sec)
mysql> show binary logs;
+----------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+----------------+-----------+-----------+
| bin-log.000013 | 202 | No |
| bin-log.000014 | 157 | No |
+----------------+-----------+-----------+
2 rows in set (0.00 sec)
mysql> PURGE BINARY LOGS BEFORE '2019-04-02 22:46:26'; -- 根据日志切割后出现的时间进行删除日志
**注意：一定不能采用rm方式清理日志信息 **
1.4.8 二进制日志备份管理 (MHA数据库高可用软件 -- 加载日志备份服务器中备份日志信息)
- 步骤一：需要创建生成日志备份服务器，并安装数据库程序（二进制包解压）
mysql -V
mysql Ver 8.0.32 for Linux on x86_64 (MySQL Community Server - GPL)
- 步骤二：创建不同数据库实例的数据目录
db01 - 3356 3357 3380 全网备份 web01 web02 web03 nfs ... backup/10.0.0.51 backup/10.0.0.52
db02 - 3306 3307 3308
...
mkdir /backup/10.0.0.51_3306 -p
cd /backup/10.0.0.51_3306/
- 步骤三：进行日志信息的备份 （实时备份）
cd /backup/10.0.0.51_3306/
mysqlbinlog -R --host=10.0.0.51 --user=root --password=123 --raw --stop-never bin-log.000017 &
[root@manager 10.0.0.51_3306 15:47]# ll
total 16
-rw-r----- 1 root root 202 Dec 16 15:44 bin-log.000017
-rw-r----- 1 root root 202 Dec 16 15:44 bin-log.000018
-rw-r----- 1 root root 202 Dec 16 15:44 bin-log.000019
-rw-r----- 1 root root 348 Dec 16 15:46 bin-log.000020
1.5 慢查询日志管理配置应用 （可以应用日志信息做数据库语句优化）
1.5.1 创建生成慢查询日志
-
slow_query_log --- 是否生成慢查询日志 0-默认禁用慢查询日志 1-激活慢查询日志功能
-
slow_query_log_file --- 指定慢查询日志存储路径以及名称信息
-
long_query_time --- 指定慢查询日志参考时间，超过设定时间完成的查询操作，都成为慢查询会保存到日志中
(企业环境一般不超过5s~10s，超过5s语句可以考虑进行优化) -
log_queries_not_using_indexes --- 将没有走索引的语句需要记录到慢查询日志
-
永久开启
vi /etc/my.cnf
slow_query_log=ON
slow_query_log_file=/data/3306/log/slow.log
long_query_time=0.01
log_queries_not_using_indexes=1;
1.5.2 查看慢查询日志信息
Time Id Command Argument
# Time: 2025-01-19T17:00:29.297572Z
# User@Host: root[root] @ localhost [] Id: 8
# Query_time: 0.025341 Lock_time: 0.000003 Rows_sent: 1 Rows_examined: 0
use world;
SET timestamp=1737306029;
select count(*) from city;
2. 数据库备份恢复管理
2.1 物理备份恢复
2.1.1 含义及特点
物理备份恢复：将数据目录中数据信息做整体备份
优势特点：在进行数据备份时，当数据量大的时候，备份效率快
缺陷说明：需要进行停止数据库服务备份/恢复 备份数据的灵活性不高（全备）
PS：可以使用第三方软件工具，实现物理备份的热备操作；
2.1.2 物理备份数据/恢复数据
- 步骤一：需要停止数据库服务*
/etc/init.d/mysql.server stop
- 步骤二：将数据库数据目录做备份
cp -a /data/3306/data/ /backup/
- 步骤三：恢复数据库服务/以及恢复原有数据
rm -rf /data/3306/data/
ps -ef|grep mysql -- 确认数据库服务关闭状态
cp -a /backup/data/ /data/3306/
- 步骤四：恢复启动数据库服务
/etc/init.d/mysql.server start
2.2 逻辑备份恢复
2.2.1 含义及特点
逻辑备份恢复：将数据库服务的数据信息转换成SQL语句进行备份
优势特点：进行备份/恢复数据时，可以实现不停止数据库服务操作备份恢复 备份数据的灵活性较高（分库分表）
缺陷说明：在备份大量数据或恢复大量数据信息时，时间成本较高
PS：在企业环境中，如果备份的数据量超过50G，建议采用物理备份数据，若不超过50G，建议采用逻辑备份
2.2.2 逻辑备份数据/恢复数据： mysqldump
- 方式一： 全备操作
· 备份数据
mysqldump -uroot -p123456 -S /tmp/mysql.sock -A >/backup/all.sql -- 本地全备
mysqldump -uroot -p123456 -h 10.0.0.51 -P3306 -A >/backup/all.sql -- 远程全备（云主机数据库服务做备份-RDS）
· 恢复数据
mysql -uroot -p123456 -S /tmp/mysql.sock </backup/all.sql -- 本地恢复
mysql -uroot -p-p123456 -h 10.0.0.51 -P3306 </backup/all.sql -- 远程恢复
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
source /backup/all.sql -- 数据恢复
mysql> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)
- 方式二： 分库备份
· 备份数据
mysqldump -uroot -p123456 -S /tmp/mysql.sock -B world oldboy >/backup/world_oldboy.sql
· 恢复数据
mysql -uroot -p123456 -S /tmp/mysql.sock </backup/world_oldboy.sql -- 本地恢复
mysql -uroot -p-p123456 -h 10.0.0.51 -P3306 </backup/world_oldboy.sql -- 远程恢复
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
source /backup/world_oldboy.sql -- 数据恢复
mysql> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)
PS：https://blog.csdn.net/shenql_/article/details/131968148 --如何利用脚本实现分库备份 （每天/每周）
- 方式三： 分表备份
· 备份数据
mysqldump -uroot -p123456 -S /tmp/mysql.sock world city >/backup/city.sql
-- 分表备份文件中，不含有创建库的语句，以及切换数据库的语句
· 恢复数据
mysql -uroot -p123456 -S /tmp/mysql.sock world </backup/city.sql -- 本地恢复
mysql -uroot -p-p123456 -h 10.0.0.51 -P3306 world </backup/city.sql -- 远程恢复
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
use world
source /backup/city.sql -- 数据恢复
mysql> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)
