Mysql各种日志管理

flytalei

已于 2024-03-30 18:26:29 修改

阅读量1.2k

点赞数 26

分类专栏： mysql删库到跑路文章标签： mysql 数据库

于 2024-03-27 18:28:34 首次发布

本文链接：https://blog.csdn.net/flytalei/article/details/137076842

版权

mysql删库到跑路专栏收录该内容

9 篇文章 0 订阅

订阅专栏

文章目录

事务日志
错误日志
二进制日志(备份)
通用日志
慢查询日志

Mysql日志记录着数据库在运行过程中的各种操作，它支持丰富的日志类型，帮助管理员定位查找问题。

事务日志

事务日志记录着所有事务的操作过程。

事务日志(Transaction log)
   事务日志的写入类型为“追加”，因此其操作为“顺序IO”;通常也称为：预写式日志write ahead logging(操作数据之前先写日志)

事务日志文件：ib_logfile0,  ib_logfile1

事务日志的记录过程

事务日志记录有四个步骤：
  1.读取数据库文件
  2.将数据库文件加载到内存中
  3.将数据库的操作记录到事务日志文件中
  4.再将内存中的操作写回到数据库文件中
如果第四步时突然断电了，别慌，日志文件已经记录了所有的内存操作，来电后直接在读取日志文件中的记录即可

事务日志类型

redolog和undolog

redo log: 记录某数据块被修改后的值，数据更新前先记录redo log，可以用来恢复未写入data file已成功事务更新的数据的
undo log：保存与执行的操作相反的操作，即记录某数据被修改前的值，可以用来在事务失败时进行回滚rollback。

事务型存储引擎自行管理和使用，建议和数据文件分开存放。

事务日志的相关变量

mysql> show variables like '%innodb_log%';
+------------------------------------+----------+
| Variable_name                      | Value    |
+------------------------------------+----------+
| innodb_log_buffer_size             | 16777216 |
| innodb_log_checksums               | ON       |
| innodb_log_compressed_pages        | ON       |
| innodb_log_file_size               | 50331648 |
| innodb_log_files_in_group          | 2        |
| innodb_log_group_home_dir          | ./       |
| innodb_log_spin_cpu_abs_lwm        | 80       |
| innodb_log_spin_cpu_pct_hwm        | 50       |
| innodb_log_wait_for_flush_spin_hwm | 400      |
| innodb_log_write_ahead_size        | 8192     |
| innodb_log_writer_threads          | ON       |
+------------------------------------+----------+
11 rows in set (0.00 sec)

错误日志

错误日志是MySQL中最重要的日志之一，它记录了当mysqld启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。
当数据库出现任何故障导致无法正常使用时，建议首先查看此日志。

错误日志
  1.mysqld启动和关闭过程中输出的事件信息
  2.mysqld运行中产生的错误信息
  3.event scheduler运行一个event时产生的日志信息
  4.在主从复制架构中的从服务器上启动从服务器线程时产生的信息

该日志默认是开启的，默认存放目录/var/log/,默认的日志文件名为mysqld.log

//查看日志位置
mysql> show variables like '%log_error%';
+----------------------------+----------------------------------------+
| Variable_name              | Value                                  |
+----------------------------+----------------------------------------+
| binlog_error_action        | ABORT_SERVER                           |
| log_error                  | /var/log/mysqld.log                    |
| log_error_services         | log_filter_internal; log_sink_internal |
| log_error_suppression_list |                                        |
| log_error_verbosity        | 2                                      |
+----------------------------+----------------------------------------+
5 rows in set (0.03 sec)

二进制日志(备份)

二进制日志(binlog)
   1.记录了所有DDL(数据定义语言)语句和DML(数据操纵语言)语句，但不包括数据查询(select,show...)语句;
   2.记录已经提交的日志
   3.不依赖于存储引擎类型

功能作用

功能：通过“重放”日志文件中的事件来生成数据副本

二进制日志的作用： mysql8版本中，二进制日志默认开启的，
  1.灾难时的数据恢复；
  2.MySQL的主从复制；

二进制日志的相关变量

sql_log_bin=ON|OFF                  #是否记录二进制日志，默认ON,支持动态修改，系统变量，而非服务器选项
log_bin=/path/bin_log_file          #指定文件位置，默认OFF,表示不启用二进制日志功能，上述两项都开启才可以
binlog_format=statement|row|mixed   #二进制日志记录格式

mysql> show variables like '%log_bin%';
+---------------------------------+-----------------------------+
| Variable_name                   | Value                       |
+---------------------------------+-----------------------------+
| log_bin                         | ON                          |
| log_bin_basename                | /var/lib/mysql/binlog       |
| log_bin_index                   | /var/lib/mysql/binlog.index |
| log_bin_trust_function_creators | OFF                         |
| log_bin_use_v1_row_events       | OFF                         |
| sql_log_bin                     | ON                          |
+---------------------------------+-----------------------------+
6 rows in set (0.01 sec)

查看二进制文件

mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |       157 | No        |
| binlog.000002 |       157 | No        |
+---------------+-----------+-----------+
2 rows in set (0.00 sec)

查看二进制日志是否开启和所处的位置

mysql> select @@log_bin;
+-----------+
| @@log_bin |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)

mysql> select @@sql_log_bin;
+---------------+
| @@sql_log_bin |
+---------------+
|             1 |
+---------------+
1 row in set (0.00 sec)

mysql> show master logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000013 |       157 | No        |
| binlog.000014 |       201 | No        |
| binlog.000015 |       157 | No        |
| binlog.000016 |       157 | No        |
| binlog.000017 |       201 | No        |
| binlog.000018 |       157 | No        |
| binlog.000019 |       201 | No        |
| binlog.000020 |     11060 | No        |
+---------------+-----------+-----------+
8 rows in set (0.01 sec)

文件的构成

二进制日志文件的构成

由两类文件
  1.日志文件：/var/lib/mysql/binlog 文件名后缀，二进制格式，会有多个文件，文件名数字递增。
  2.索引文件：/var/lib/mysql/binlog.index 文本格式，记录当前已有的二进制日志文件列表。

在这里插入图片描述
binlog.index记录当前已有的二进制日志文件列表。

日志三种格式

MySQL服务器中提供了多种格式来记录二进制日志

日志格式	含义
statement(语句记录)	statement，记录的是sql语句，对数据进行修改的sql都会记录在该日志文件中，日志量较少。
row(行记录)	基于行的日志记录，记录的是每一行的数据变更，生成中建议使用的格式，Mysql8.0默认的格式
mixed(混合模式)	混合了statement和row两种格式，默认采用statement，在某些特殊情况下会自动切换为row进行记录。

查看日志格式(默认)

mysql> show variables like '%binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.01 sec)

查看日志

由于二进制日志是以二进制方式存储的，不能直接读取，所以需要通过二进制日志查询工具mysqlbinlog来查看，语法如下：

mysqlbinlog [参数选项] logfilename

参数选项：
   -d    指定数据库名称，只列出指定的数据快相关操作。
   -o    忽略掉日志中的前n行命令
   -v    将行事件(数据变更)重构为sql语句
   -w    将行事件(数据变更)重构为sql语句，并输出注释信息

删除日志

对于比较繁忙的业务系统，每天生成的binlog数据巨大，如果长时间不清楚，将会占用大量磁盘空间。可以通过一下几种方式清理日志

指令	含义
reset master	删除全部binlog日志，删除之后，日志编号从binlog。000001重新开始
purge master logs to ‘binlog.******’	删除******编号之前的所有日志
purge master logs before ‘yyyy-mm-dd hh24:mi:ss’	删除日志为’yyyy-mm-dd24:mi:ss之前的所有日志’

也可以在mysql的配置文件中配置二进制日志的过期时间，设置了之后，二进制日志过期会自动删除。

查看默认的过期时间，单位2592000秒(30天)

show variables like '%binlog_expire_logs_seconds%';

mysql> show variables like '%binlog_expire_logs_seconds%';
+----------------------------+---------+
| Variable_name              | Value   |
+----------------------------+---------+
| binlog_expire_logs_seconds | 2592000 |
+----------------------------+---------+
1 row in set (0.00 sec)

通用日志

通用日志中记录了客户端的所有操作语句，而二进制日志不包含查询数据的sql语句。默认情况下，查询日志是未开启的。

如果需要开启通用日志，可以设置一下配置

mysql> show variables like '%general%';
+------------------+------------------------------+
| Variable_name    | Value                        |
+------------------+------------------------------+
| general_log      | OFF                          |
| general_log_file | /var/lib/mysql/localhost.log |
+------------------+------------------------------+
2 rows in set (0.02 sec)

修改MySQL的配置文件/etc/my.cnf文件，添加如下内容：

#该选项用来开启查询日志，可选项：0或者1；0代表关闭，1代表开启
general_log=1

#设置日志的文件名，如果没有指定，默认的文件名为host_name.log
general_log_file=mysql_query.log

慢查询日志

慢查询日志记录了所有执行时间超过参数long_query_time设置的值并且扫描记录数不小于min_examined_row_limit的所有的sql语句的日志，默认未开启。

慢查询相关变量

slow_query_log=ON|OFF                  #开启或关闭慢查询，支持全局和会话，只有全局设置才会生成慢查询文件
long_query_time=N                      #慢查询的阈值，单位秒，默认为10s，精度可以到微妙。
slow_query_log_file=hostname-slow.log  #慢查询日志文件,默认文件名前缀是你的主机名。
log_queries_not_using_indexes=ON       #记录执行较慢的未使用索引的语句，默认OFF,即不记录。
log_slow_admin_statements=ON

默认情况下，慢查询日志不会记录管理语句，也不会记录不使用索引进行查找的的查询。可以使用log_slow_admin_statements和更改次行为为log_queries_not_using_indexes,如下所述

查询慢查询日志的默认配置

mysql> show variables like '%slow_query%';
+---------------------+-----------------------------------+
| Variable_name       | Value                             |
+---------------------+-----------------------------------+
| slow_query_log      | OFF                               |
| slow_query_log_file | /var/lib/mysql/localhost-slow.log |
+---------------------+-----------------------------------+
2 rows in set (0.01 sec)