MySQL的日志管理

MySQL的日志简介

错误日志
常规日志
慢速查询日志
二进制日志

错误日志

作用：MySQL启动报错的错误信息记录，为了排错

默认是否开启：开启

存储位置：默认在datadir
mysql> show variables like '%log_error%';
+---------------------+--------------+
| Variable_name       | Value        |
+---------------------+--------------+
| binlog_error_action | IGNORE_ERROR |
| log_error           | ./db01.err   |
+---------------------+--------------+

## 修改方式
# 修改方式
## 修改配置文件
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
log_error=/opt/zls.txt               #这个目录必须有权限 文件也必须有权限

常规日志

# 作用：记录常规操作 记录命令相关操作
# 默认是否开启：否
# 存储位置： datadir
mysql> show variables like '%general_log%';
+------------------+---------------------------------+
| Variable_name    | Value                           |
+------------------+---------------------------------+
| general_log      | OFF                             |
| general_log_file | /app/mysql-5.6.50/data/db01.log |
+------------------+---------------------------------+

# 修改位置
## 修改配置文件
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
general_log=1
general_log_file=/app/mysql/data/db01.log

二进制日志！！

# 作用：只记录对数据发生变化的语句
记录已提交的DML事务语句，并拆分为多个事件（event）来进行记录
记录所有DDL、DCL等语句
总之，二进制日志会记录所有对数据库发生修改的操作

# 默认是否开启：否

# 存储位置
show variables like '%log_bin%';
+---------------------------------+----------------------------------------+
| Variable_name                   | Value                                  |
+---------------------------------+----------------------------------------+
| log_bin                         | ON                                     |
| log_bin_basename                | /app/mysql-5.6.50/data/mysql-bin       |
| log_bin_index                   | /app/mysql-5.6.50/data/mysql-bin.index |
| log_bin_trust_function_creators | OFF                                    |
| log_bin_use_v1_row_events       | OFF                                    |
| sql_log_bin                     | ON                                     |
+---------------------------------+----------------------------------------+

# 修改方式
## 修改配置文件 MySQL5.6
[root@db01 ~]# vim /etc/my.cnf
log-bin=mysql-bin
log-bin=/opt/zls-bin
log-bin=/app/mysql/data/mysql-bin
binlog_format=row

## 修改配置文件 MySQL5.7
[root@db01 ~]# vim /etc/my.cnf
server_id=1
log-bin=mysql-bin
log-bin=/opt/zls-bin
log-bin=/app/mysql/data/mysql-bin
binlog_format=row

二进制日志的工作模式

statement 语句模式（MySQL5.6）
优点：简单易读，占用磁盘空间小
缺点：不严谨

# 优点:
	1）日志文件较小，因为只记录 SQL 语句，而不记录具体的数据变化。
	2）在许多情况下，日志文件的体积小且适用广泛。
# 缺点:
	1）对某些特定类型的语句（如使用非确定性函数 NOW()、UUID()、RAND()、或用户自定义函数等）可能无法准确地在从服务器上重放，导致数据不一致。
	2）复杂的 SQL 语句或基于时间、随机数的操作可能会引起复制错误。

row 行级模式（MySQL5.7）
优点：严谨，记录原数据和数据的变化过程
缺点：占用磁盘空间大，不易读

# 优点:
	1）可以精确地复制数据变化，几乎不会出现数据不一致的情况。
	2）对于无法通过 SQL 语句准确描述的数据变化（例如 UUID()、CURRENT_TIMESTAMP 等），可以保证数据的正确性。
# 缺点:
	1）日志文件体积较大，特别是在执行大量批量更新时，因为每一行的变化都会被记录。
	2）在数据操作量非常大的情况下，日志的管理和维护开销较高。

mixed 混合模式

在大多数情况下，MySQL 使用 STATEMENT 模式记录日志，以减少日志文件的大小。
在特殊情况下，如涉及非确定性函数（如 UUID()、NOW()）或复杂的语句时，MySQL 则会自动切换到 ROW 模式记录，以确保复制的准确性。

# 优点:
	1）在保持较小日志文件大小的同时，保证了数据的准确性。
	2）自动处理某些特殊情况，不需要用户手动干预。
# 缺点:
	1）尽管比纯 ROW 模式效率更高，但在某些情况下仍然可能产生较大的日志文件。
	2）理解和调试时，可能需要关注日志模式的切换行为。

#### 查询方式
show variables like '%binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+

二进制日志的查看方式

### 对于 statement 语句模式（MySQL5.6）
mysqlbinlog /app/mysql/data/mysql-bin.000027

查询指定时间范围内的事件
mysqlbinlog --start-datetime="2024-08-20 10:00:00" --stop-datetime="2024-08-20 12:00:00" /tmp/mysql-bin.000001  

查询指定范围内的事件
mysqlbinlog --start-position=120 --stop-position=500 /tmp/mysql-bin.000001

### 对于 row 行级模式（MySQL5.7）
mysqlbinlog --base64-output=decode-rows -vvv  /app/mysql/data/mysql-bin.000001

### SQL 语句
show binary logs;    # 查询有多少二进制日志文件
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       167 |
| mysql-bin.000002 |      1565 |
+------------------+-----------+

show master status;  # 查询二进制的记录点
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |     1565 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

show binlog events in 'mysql-bin.000001'; # 查询一个二进制日志所有事件，不适合row看行模式
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                  |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| mysql-bin.000001 |   4 | Format_desc |         1 |         120 | Server ver: 5.6.50-log, Binlog ver: 4 |
| mysql-bin.000001 | 120 | Rotate      |         1 |         167 | mysql-bin.000002;pos=4                |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+

二进制的解读

BEGIN
/*!*/;
# at 1213
#240820 11:18:44 server id 1  end_log_pos 1270 CRC32 0x37e0195c         Table_map: `binlog`.`binlog_table` mapped to number 70
# at 1270
#240820 11:18:44 server id 1  end_log_pos 1316 CRC32 0x4ec5c63e         Update_rows: table id 70 flags: STMT_END_F
### UPDATE `binlog`.`binlog_table`
### WHERE
###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
### SET
###   @1=22 /* INT meta=0 nullable=1 is_null=0 */
# at 1316
#240820 11:18:44 server id 1  end_log_pos 1347 CRC32 0x0e73bbf6         Xid = 29
COMMIT/*!*/;

BEGIN 
/*!*/;
事务开始的标志，表明接下来的一系列操作是在同一个事务中执行的。


# at 1213
表示日志事件在二进制日志文件中的位置偏移量，从字节 1213 开始。

#240820 11:18:44 server id 1 end_log_pos 1270 CRC32 0x37e0195c Table_map: binlog.binlog_table mapped to number 70
时间戳：240820 11:18:44 表示操作发生的时间，即2024年8月20日上午11:18:44。
server id 1：标识该事件来源于 server-id 为 1 的服务器。
end_log_pos 1270：该事件结束时在日志中的位置是 1270。
CRC32 0x37e0195c：该事件的 CRC32 校验码，用于数据完整性校验。
Table_map: 表示这条日志映射了 binlog.binlog_table 到内部表 ID 70。

#240820 11:18:44 server id 1 end_log_pos 1316 CRC32 0x4ec5c63e Update_rows: table id 70 flags: STMT_END_F
描述了一条 UPDATE 操作。
end_log_pos 1316：该事件结束时在日志中的位置是 1316。
Update_rows: 表示这是一次 UPDATE 操作，作用于表 ID 70

### UPDATE binlog.binlog_table
标识更新操作是针对表 binlog.binlog_table 进行的。

### WHERE
表示 UPDATE 操作的条件部分。

###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
### SET
###   @1=22 /* INT meta=0 nullable=1 is_null=0 */
@1=22：将表的第一列的值从 2 更新为 22。

# at 1316
再次显示了当前日志的偏移位置。

#240820 11:18:44 server id 1 end_log_pos 1347 CRC32 0x0e73bbf6 Xid = 29
时间戳：240820 11:18:44。
end_log_pos 1347：该事务结束的位置是 1347。
Xid = 29：事务的内部标识符 Xid 为 29。
CRC32 0x0e73bbf6：该事件的 CRC32 校验码。

COMMIT /*!*/;
事务的提交，表明事务中的所有操作已经成功并永久写入数据库。

二级制日志文件的更新，删除

## binlog 刷新
1.重启MySQL数据库
2.flush logs;
3.mysqladmin -uroot -p123 flush-log
4.当binlog大小1G会自动刷新

## binlog删除

1.根据时间删除
#临时生效
SET GLOBAL expire_logs_days = 7;
#永久生效
[root@db01 data]# vim /etc/my.cnf
[mysqld]
expire_logs_days = 7

2.根据时间删除
PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day;

3.根据文件名删除（常用）
PURGE BINARY LOGS TO 'mysql-bin.000020';

4.企业级优化 （清空所有二进制日志）
reset master;

慢查询日志设置

# 作用：记录执行的比较慢的SQL语句
# 默认是否开启：否
# 存储位置：datadir
 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-5.6.50/data/db01-slow.log |
+---------------------------+--------------------------------------+

# 修改方式
## 修改配置文件
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
#指定是否开启慢查询日志
slow_query_log=1
#指定慢日志文件存放位置（默认在data）
slow_query_log_file=/app/mysql/data/db01-slow.log
#设定慢查询的阀值(默认10s)
long_query_time=0.05
#不使用索引的慢查询语句是否记录到日志
log_queries_not_using_indexes=1
#查询检查返回少于该参数指定行的SQL不被记录到慢查询日志
min_examined_row_limit=100（鸡肋）

模拟慢查询日志

create table l_city select * from city;  ## 创建一个相同的表结构  不含索引

insert into l_city select * from l_city;  ## 向里面插入相同的数据

慢查询日志的查询分析

[root@db01 ~]# mysqldumpslow
-s：指定排序顺序
	c：Count 执行次数
	t：Time 执行时间 总时间
	r：Rows 结果行数 总行数
	l：Lock 锁表时间 总锁表时间

	at：平均时间
	ar：平均行数
	al：平均锁表时间
	
-t 或 --top
	用于指定显示前 N 条记录。例如，-t 10 会显示前 10 条查询
	
-g 或 --grep
	只显示匹配给定正则表达式的查询。正则表达式不区分大小写。
	
-r 或 --reverse
	反转排序顺序。例如，如果按执行时间排序，使用 -r 可以将结果从最小的开始排序。

第三方慢查询日志

使用percona公司提供的pt-query-digest工具分析慢查询日志
yum install -y https://downloads.percona.com/downloads/perconatoolkit/3.6.0/binary/redhat/7/x86_64/percona-toolkit-3.6.0-1.el7.x86_64.rpm

pt-query-digest /application/mysql/data/mysql-db01-slow.log

有能力的可以做成可视化界面：
Anemometer基于pt-query-digest将MySQL慢查询可视化
https://www.percona.com/downloads/percona-toolkit/LATEST/ 慢日志分析工具下载
https://github.com/box/Anemometer 可视化代码下载

可视化慢查询日志

pt-query-digest --user=root --password=123  --review h=127.0.0.1,D=slow_query_log,t=global_query_review  --history h=127.0.0.1,D=slow_query_log,t=global_query_review_history  --no-report --limit=0% --filter=" \$event->{Bytes} = length(\$event->{arg}) and \$event->{hostname}=\"$HOSTNAME\""  /app/mysql/data/db01-slow.log

ALTER TABLE slow_query_log.global_query_review MODIFY checksum CHAR(32);

MySQL的日志管理

MySQL的日志简介

错误日志

常规日志

二进制日志 ！！

二进制日志的工作模式

二进制日志的查看方式

二进制的解读

二级制日志文件的更新，删除

慢查询日志设置

模拟慢查询日志

慢查询日志的查询分析

第三方慢查询日志

可视化慢查询日志

二进制日志！！