MySQL日志管理
目录
MySQL日志管理
日志简介
错误日志
mysql> show variables like 'log_error';
+---------------+------------+
| Variable_name | Value |
+---------------+------------+
| log_error | ./db01.err |
+---------------+------------+
## 错误日志日志名：
$HOSTNAME.err
## 错误日志存储位置：
$datadir 数据目录下
## 错误日志默认是否开启：
开启的
## 如何修改配置：
vim /etc/my.cnf
[mysqld]
log_error=/opt/1.txt
## 日志文件必须提前创建出来，并且针对mysql用户有写入权限
touch /opt/1.txt
chown -R mysql.mysql /opt/1.txt
一般查询日志
root@localhost [(none)] >show variables like '%general%';
+------------------+--------------------------+
| Variable_name | Value |
+------------------+--------------------------+
| general_log | OFF |
| general_log_file | /app/mysql/data/db04.log |
+------------------+--------------------------+
## 常规日志日志名：
$HOSTNAME.log
## 常规日志存储位置：
$datadir 数据目录下
## 常规日志默认是否开启：
关闭
## 如何修改配置：
vim /etc/my.cnf
[mysqld]
general_log=1
general_log_file=/opt/xxx.log
二进制日志 binlog
mysql> show variables like '%log_bin%';
+---------------------------------+---------------------------------+
| Variable_name | Value |
+---------------------------------+---------------------------------+
| log_bin | ON |
+---------------------------------+---------------------------------+
## 常规的日志名
mysql-bin.000001
mysql-bin.000002
mysql-bin.000003
...
## 常规日志存储位置：
$datadir 数据目录下
## 常规日志默认是否开启
关闭
## 如何修改配置
### 开启binlog MySQL5.6
vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
binlog_format=row
#log-bin=/opt/zls-bin
### 开启binlog MySQL5.7
vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
server_id=1
binlog_format=row
binlog的工作模式
statement 语句模式
记录MySQL的SQL语句 DDL DML DCL
## MySQL5.6 默认语句模式
mysql> show variables like 'binlog_format';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
# 优缺点
- 优点：
- 易读
- 占用磁盘空间小
- 缺点：
- 不严谨
row 行级模式
记录MySQL的SQL语句 DDL、DCL，DML记录每一行的变化过程
### ## MySQL5.7 默认行级模式
mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
# 优缺点
- 优点：
- 严谨
- 缺点
- 不易读
- 占用磁盘空间大
mixed 混合模式
statement 和 row 的混合
一般运维不用
查看binlog
## 查看语句模式
mysqlbinlog binlog名字
[root@db01 data]# mysqlbinlog mysql-bin.000001
## 查看行级模式
[root@db03 ~]# mysqlbinlog --base64-output=decode-rows -vvv /app/mysql/data/mysql-bin.000013
# 查看当前的binlog
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000016 | 120 | | | |
+------------------+----------+--------------+------------------+-------------------+
# 查看有哪些binlog，每个binlog的大小
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 622 |
| mysql-bin.000002 | 143 |
| mysql-bin.000003 | 387 |
| mysql-bin.000004 | 120 |
| mysql-bin.000005 | 120 |
| mysql-bin.000006 | 120 |
| mysql-bin.000007 | 120 |
| mysql-bin.000008 | 120 |
| mysql-bin.000009 | 120 |
| mysql-bin.000010 | 120 |
| mysql-bin.000011 | 120 |
| mysql-bin.000012 | 143 |
| mysql-bin.000013 | 143 |
| mysql-bin.000014 | 167 |
| mysql-bin.000015 | 2686 |
| mysql-bin.000016 | 120 |
+------------------+-----------+
# 查看binlog事件
mysql> show binlog events in 'mysql-bin.000015'\G
*************************** 1. row ***************************
Log_name: mysql-bin.000015
Pos: 4
Event_type: Format_desc
Server_id: 1
End_log_pos: 120
Info: Server ver: 5.6.50-log, Binlog ver: 4
*************************** 2. row ***************************
Log_name: mysql-bin.000015
Pos: 120
Event_type: Query
Server_id: 1
End_log_pos: 220
Info: create database binlog
*************************** 3. row ***************************
Log_name: mysql-bin.000015
Pos: 220
Event_type: Query
Server_id: 1
End_log_pos: 330
Info: use `binlog`; create table test_binlog(id int)
*************************** 4. row ***************************
Log_name: mysql-bin.000015
Pos: 330
Event_type: Query
Server_id: 1
End_log_pos: 404
Info: BEGIN
····等····
查看binlog内容和导出
mysqlbinlog --start-position=120 --stop-position=1035 mysql-bin.000007 > /tmp/test_binlog.sql
# -d db1 指定只查看db1库的操作
mysqlbinlog -d db1 --start-position=120 --stop-position=1035 mysql-bin.000007 > /tmp/test_binlog.sql
事件介绍
1）在binlog中最小的记录单元为event
2）一个事务会被拆分成多个事件（event）
## MySQL5.6 事件（event）特性
1）每个event都有一个开始位置（start position）和结束位置（stop position）。
2）所谓的位置就是event对整个二进制的文件的相对位置。
3）对于一个二进制日志中，前120个position是文件格式信息预留空间。
4）MySQL第一个记录的事件，都是从120开始的。
5）MySQL5.6空的binlog 143
## MySQL5.7 事件（event）特性
1）每个event都有一个开始位置（start position）和结束位置（stop position）。
2）所谓的位置就是event对整个二进制的文件的相对位置。
3）对于一个二进制日志中，前154个position是文件格式信息预留空间。
4）MySQL第一个记录的事件，都是从154开始的。
5）MySQL5.7空的binlog 177
使用binlog恢复数据案例
模拟数据
# 查看binlog信息
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000016 | 218 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
# 刷新一个新的binlog
mysql> flush logs;
Query OK, 0 rows affected (0.00 sec)
# 查看binlog信息
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000017 | 120 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
# 创建一个binlog库
mysql> create database binlog;
Query OK, 1 row affected (0.00 sec)
# 使用binlog库
mysql> use binlog
Database changed
# 创建test_binlog表
mysql> create table test_binlog(id int);
Query OK, 0 rows affected (0.00 sec)
# 插入数据1
mysql> insert into test_binlog values(1);
Query OK, 1 row affected (0.00 sec)
# 提交
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
# 查看binlog信息
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000017 | 531 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
# 插入数据2
mysql> insert into test_binlog values(2);
Query OK, 1 row affected (0.00 sec)
# 插入数据3
mysql> insert into test_binlog values(3);
Query OK, 1 row affected (0.00 sec)
# 提交
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
# 查看binlog信息
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000017 | 933 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
# 查看test_binlog表内的内容
mysql> select * from test_binlog;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
# 更改数据1为10
mysql> update test_binlog set id=10 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# 提交
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
# 查看test_binlog表内的信息
mysql> select * from test_binlog;
+------+
| id |
+------+
| 10 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
# 查看binlog信息
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000017 | 1140 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
# 删除数据3
mysql> delete from test_binlog where id=3;
Query OK, 1 row affected (0.00 sec)
# 提交
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
# 查看test_binlog表内的信息
mysql> select * from test_binlog;
+------+
| id |
+------+
| 10 |
| 2 |
+------+
2 rows in set (0.00 sec)
# 查看binlog信息
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000017 | 1341 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
# 查看库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| baixiong |
| mysql |
| performance_schema |
| test |
| xx |
+--------------------+
模拟数据故障
# 删除test_binlog表
mysql> drop table test_binlog;
Query OK, 0 rows affected (0.00 sec)
# 删除binlog库
mysql> drop database binlog;
Query OK, 0 rows affected (0.00 sec)
# 查看binlog信息
mysql> show master status;
+------------------+----------+
| File | Position |
+------------------+----------+
| mysql-bin.000017 | 1558 |
+------------------+----------+
恢复数据
# 1.查看当前使用的是哪个binlog？
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000017 | 1558 | | | |
+------------------+----------+--------------+------------------+-------------------+
# 一般binlog写入的都为最后一个
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 622 |
| mysql-bin.000002 | 143 |
| mysql-bin.000003 | 387 |
| mysql-bin.000004 | 120 |
| mysql-bin.000005 | 120 |
| mysql-bin.000006 | 120 |
| mysql-bin.000007 | 120 |
| mysql-bin.000008 | 120 |
| mysql-bin.000009 | 120 |
| mysql-bin.000010 | 120 |
| mysql-bin.000011 | 120 |
| mysql-bin.000012 | 143 |
| mysql-bin.000013 | 143 |
| mysql-bin.000014 | 167 |
| mysql-bin.000015 | 2686 |
| mysql-bin.000016 | 265 |
| mysql-bin.000017 | 1558 |
+------------------+-----------+
# 2.使用mysqlbinlog命令查看binlog内容
[root@db01 ~]# cd /app/mysql/data/
[root@db01 data]# mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000017
# 3.找到起点和结束的位置
120 和 1270
🐂例：---------------------模拟数据时未关闭自动提交。
# at 120
#230801 15:52:06 server id 1 end_log_pos 220 CRC32 0x88ddfc3c Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1690876326/*!*/;
SET @@session.pseudo_thread_id=1/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1073741824/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create database binlog
/*!*/;
# at 220
## 上面为创建一个binlog库
mysql> create database binlog;
# at 1270
#230801 15:55:12 server id 1 end_log_pos 1310 CRC32 0x2964016b Delete_rows: table id 71 flags: STMT_END_F
### DELETE FROM `binlog`.`test_binlog`
### WHERE
### @1=3 /* INT meta=0 nullable=1 is_null=0 */
# at 1310
## 上面为删除数据3
mysql> delete from test_binlog where id=3;
🐎----------------------------------------
# 4.截取binlog到文件
[root@db01 data]# mysqlbinlog --start-position=120 --stop-position=1270 mysql-bin.000017 > /tmp/2.sql
# 5.先临时关闭binlog记录
mysql> set sql_log_bin=0;
# 6.恢复数据
mysql> source /tmp/2.sql
# 7.开启binlog记录
mysql> set sql_log_bin=1;
# 8.查看数据
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| binlog |
+--------------------+
mysql> use binlog
+------------------+
| Tables_in_binlog |
+------------------+
| test_binlog |
+------------------+
mysql> select * from test_binlog;
+------+
| id |
+------+
| 10 |
| 2 |
| 3 |
+------+
binlog的刷新和删除
binlog的刷新
1)重启数据库
/etc/init.d/mysqld restart
2)执行flush logs;
3)mysqladmin
[root@db01 ~]# mysqladmin -uroot -p123 flush-log
4)自动切割，当binlog达到1G
删除binlog
# 1.根据时间删除
set global expire_logs_days = 7;
vim /etc/my.cnf
[mysqld]
expire_logs_days = 7
# 2.根据时间删除
purge binary logs before now() - interval 7 day;
# 3.根据文件名删除(1到文件名那个)
purge binary logs to 'mysql-bin.000010';
# 4.删除binlog（all）
mysql> reset master;
MySQL慢查询日志
mysql> show variables like '%slow%';
+---------------------------+-------------------------------+
| Variable_name | Value |
+---------------------------+-------------------------------+
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| slow_launch_time | 2 |
| slow_query_log | OFF |
| slow_query_log_file | /app/mysql/data/db01-slow.log |
+---------------------------+-------------------------------+
## 常规日志日志名：
$HOSTNAME-slow.log
## 常规日志存储位置：
$datadir 数据目录下
## 常规日志默认是否开启：
关闭
## 如何修改配置：
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
#指定是否开启慢查询日志
slow_query_log = 1
#指定慢日志文件存放位置（默认在data）
slow_query_log_file=/application/mysql/data/slow.log
#设定慢查询的阀值(默认10s)
long_query_time=0.05
#不使用索引的慢查询日志是否记录到索引
log_queries_not_using_indexes
#查询检查返回少于该参数指定行的SQL不被记录到慢查询日志（鸡肋）
min_examined_row_limit=100
慢查询命令
[root@db01 data]# mysqldumpslow db01-slow.log
[root@db01 data]# mysqldumpslow -s c db01-slow.log
-s：指定排序方式
c：count按照SQL语句执行次数排序
t：time时间排序
r：row行数排序
l：lock锁定时间排序
-t：top 看前几个排行
-g：接正则表达式匹配SQL语句
慢查询日志图形化
企业存在的问题
1）企业中的binlog很大，位置点不好找 grep
2）企业中所有数据不会只在一个binlog中 配合全备（mysqldump）
3）想要恢复的库，中途有其他库表操作，如何只截取指定库的
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· Manus重磅发布：全球首款通用AI代理技术深度解析与实战指南
· 被坑几百块钱后，我竟然真的恢复了删除的微信聊天记录！
· 没有Manus邀请码？试试免邀请码的MGX或者开源的OpenManus吧
· 【自荐】一款简洁、开源的在线白板工具 Drawnix
· 园子的第一款AI主题卫衣上架——"HELLO! HOW CAN I ASSIST YOU TODAY