mysql原理（四）日志文件
2021-05-13 本文已影响0人
我犟不过你
日志文件记录了Mysql的各种类型活动，常见的日志文件有：
1）错误日志（error log）
2）二进制日志 （binlog）
3）慢查询日志（slow query log）
4）查询日志（log）
一、错误日志
错误日志对mysql启动、运行、停止过程进行了记录。该日志不仅记录了错误信息，也记录了一些警告和正确的信息。可以通过以下参数查看日志地址：
在mysql5.6中，我这里使用windows版的，看下参数值，记录的是日志地址。
log_error
而在mysql8中，错误日志有了一个全新的改革，同样使用命令查看，默认情况下错误日志输出到终端标准输出。
mysql> show variables like 'log_error';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| log_error | stderr |
+---------------+--------+
1 row in set (0.00 sec)
在mysql8.0的版本当中，错误日志接受了来自社区的批评，并作出了修改：
1）采用组件架构，通过不同的组件执行日志的写入和过滤功能
2）写入错误日志的全部信息都具有唯一的错误代码从10000开始
3）增加了一个新的消息分类“system”用于在错误日志中始终可见的非错误但服务器状态更改事件的消息
4）增加了额外的附加信息，例如关机时的版本信息，谁发起的关机等等
5）两种过滤方式，Internal和Dragnet
6）三种写入形式，经典、JSON和syseventlog
使用以下命令查看错误日志相关内容：
mysql> show variables like 'log_error%';
+----------------------------+----------------------------------------+
| Variable_name | Value |
+----------------------------+----------------------------------------+
| log_error | stderr |
| log_error_services | log_filter_internal; log_sink_internal |
| log_error_suppression_list | |
| log_error_verbosity | 2 |
+----------------------------+----------------------------------------+
5 rows in set (0.01 sec)
mysql>
错误日志配置存储在全局 log_error_services 和 log_error_verbosity 变量中，这些变量都存储在 global_variables 表中。错误日志变量都是以“log_error_”为前缀。
组件可以细分为两种类型：filter 和 sink。
1）filter 组件完成针对错误日志事件的筛选。如果没有启用 filter 组件，就不会进行筛选。否则，任何已启用的 filter 组件仅影响 log_error_services 变量中列出的组件的日志事件。
2）错误日志 sink 组件是写入器，负责错误日志输出。如果没有启用 sink 组件，就不会有任何日志输出。某些 sink 组件描述是指默认的错误日志目标。这是由 log_error 系统变量指示的控制台或文件。
有四个可用的日志组件。它们存储在 lib/plugins 目录中，扩展名为“.so”：
component_log_filter_dragnet.so
component_log_sink_json.so
component_log_sink_syseventlog.so
component_log_sink_test.so
如果要使用，按照如下方式安装：
mysql> INSTALL COMPONENT 'file://component_log_sink_json';
Query OK, 0 rows affected (0.00 sec)
mysql> SET GLOBAL log_error_services = 'log_filter_internal; log_sink_internal; log_sink_json';
mysql> show variables like 'log_error%';
+----------------------------+--------------------------------------------------------+
| Variable_name | Value |
+----------------------------+--------------------------------------------------------+
| log_error | stderr |
| log_error_services | log_filter_internal; log_sink_internal; log_sink_json |
| log_error_suppression_list | |
| log_error_verbosity | 2 |
+----------------------------+--------------------------------------------------------+
4 rows in set (0.00 sec)
查看安装好的插件：
mysql> select * from mysql.component;
+--------------+--------------------+--------------------------------+
| component_id | component_group_id | component_urn |
+--------------+--------------------+--------------------------------+
| 1 | 1 | file://component_log_sink_json |
+--------------+--------------------+--------------------------------+
1 row in set (0.00 sec)
log_error_verbosity参数：
它有三个可选值， 分别对应：1 错误信息；2 错误信息和告警信息； 3：错误信息、告警信息和通知信息。
二、慢日志查询
帮助我们定位存在问题的sql语句，使用如下语句可以查看相关参数：
mysql> show variables like 'slow_query%';
+---------------------+-------------------------------------------------+
| Variable_name | Value |
+---------------------+-------------------------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | /var/lib/mysql/iZ2ze7sn66bchxncut8rgsZ-slow.log |
+---------------------+-------------------------------------------------+
2 rows in set (0.00 sec)
默认情况下慢日志是关闭的，需要手动开启：
mysql> set global slow_query_log=on;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'slow_query%';
+---------------------+-------------------------------------------------+
| Variable_name | Value |
+---------------------+-------------------------------------------------+
| slow_query_log | ON |
| slow_query_log_file | /var/lib/mysql/iZ2ze7sn66bchxncut8rgsZ-slow.log |
+---------------------+-------------------------------------------------+
2 rows in set (0.00 sec)
使用下面的参数查看默认的慢日志执行时间阈值：
mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)
另外有一个参数，可以设置慢查询日志是否记录不使用索引的查询：
mysql> show variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF |
+-------------------------------+-------+
1 row in set (0.00 sec)
慢查询在这个文件中查看就可以了：
/var/lib/mysql/iZ2ze7sn66bchxncut8rgsZ-slow.log
另外，在mysql5.1之后，mysql提供了slow_log表，用于记录慢日志，开启方式使用下面的参数：
mysql> show variables like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output | FILE |
+---------------+-------+
1 row in set (0.01 sec)
修改参数：
mysql> set global log_output = 'table';
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output | TABLE |
+---------------+-------+
1 row in set (0.00 sec)
查询：
select * from mysql.slow_log;
三、查询日志
查询日志记录了所有对mysql的请求。默认文件名为：主机名.log。
同样的，在mysql5.1之后，也可用通过如下方式查询:
select * from mysql.general_log;
开启方式与慢查询日志相同。
四、二进制日志
记录了所有对数据库执行更改的操作。不包括Select 和show等操作命令，想要查看只能通过查询日志。
通过如下方式可以查看binlog：
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000026 | 716 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> show binlog events in 'binlog.000026';
+---------------+-----+----------------+-----------+-------------+-----------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+-----+----------------+-----------+-------------+-----------------------------------+
| binlog.000026 | 4 | Format_desc | 1 | 125 | Server ver: 8.0.21, Binlog ver: 4 |
| binlog.000026 | 125 | Previous_gtids | 1 | 156 | |
+---------------+-----+----------------+-----------+-------------+-----------------------------------+
2 rows in set (0.00 sec)
现在更新一条数据看看变化：
update bssp_sys_menu set label = '用户中心1' where label = '用户中心';
mysql> show binlog events in 'binlog.000026';
+---------------+-----+----------------+-----------+-------------+--------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+-----+----------------+-----------+-------------+--------------------------------------+
| binlog.000026 | 4 | Format_desc | 1 | 125 | Server ver: 8.0.21, Binlog ver: 4 |
| binlog.000026 | 125 | Previous_gtids | 1 | 156 | |
| binlog.000026 | 156 | Anonymous_Gtid | 1 | 235 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000026 | 235 | Query | 1 | 319 | BEGIN |
| binlog.000026 | 319 | Table_map | 1 | 408 | table_id: 84 (bssp.bssp_sys_menu) |
| binlog.000026 | 408 | Update_rows | 1 | 685 | table_id: 84 flags: STMT_END_F |
| binlog.000026 | 685 | Xid | 1 | 716 | COMMIT /* xid=74 */ |
+---------------+-----+----------------+-----------+-------------+--------------------------------------+
7 rows in set (0.00 sec)
如上所示，我们刚在的update操作被详细的记录了。
binlog的作用：
1）恢复（recovery）：某些数据的恢复需要binlog。可以进行point-in-time的恢复。
2）复制（replication）：数据同步，多用于主从服务器的数据同步。
3）审计（audit）：通过binlog中记录的信息进行审计，来判断是否有注入的攻击。
使用以下命令查看binlog的相关信息：
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 |
+---------------------------------+-----------------------------+
5 rows in set (0.00 sec)
可以使用以下命令找到binlog的位置：
mysql> show variables like 'datadir';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| datadir | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.01 sec)
mysql> system ls -lh /var/lib/mysql/;
总用量 174M
-rw-r----- 1 mysql mysql 56 11月 26 15:02 auto.cnf
-rw-r----- 1 mysql mysql 29K 5月 11 11:17 binlog.000009
-rw-r----- 1 mysql mysql 179 5月 11 11:22 binlog.000010
-rw-r----- 1 mysql mysql 179 5月 11 11:27 binlog.000011
-rw-r----- 1 mysql mysql 179 5月 11 11:28 binlog.000012
-rw-r----- 1 mysql mysql 179 5月 11 11:30 binlog.000013
-rw-r----- 1 mysql mysql 179 5月 13 10:01 binlog.000014
-rw-r----- 1 mysql mysql 179 5月 13 10:16 binlog.000015
-rw-r----- 1 mysql mysql 179 5月 13 10:16 binlog.000016
-rw-r----- 1 mysql mysql 179 5月 13 10:28 binlog.000017
-rw-r----- 1 mysql mysql 179 5月 13 10:29 binlog.000018
-rw-r----- 1 mysql mysql 179 5月 13 10:34 binlog.000019
-rw-r----- 1 mysql mysql 179 5月 13 11:07 binlog.000020
-rw-r----- 1 mysql mysql 200 5月 13 11:12 binlog.000021
-rw-r----- 1 mysql mysql 179 5月 13 11:16 binlog.000022
-rw-r----- 1 mysql mysql 179 5月 13 12:26 binlog.000023
-rw-r----- 1 mysql mysql 179 5月 13 12:30 binlog.000024
-rw-r----- 1 mysql mysql 179 5月 13 12:32 binlog.000025
-rw-r----- 1 mysql mysql 716 5月 13 13:00 binlog.000026
-rw-r----- 1 mysql mysql 288 5月 13 12:32 binlog.index
如上所示就是binlog的日志文件，后面的数字是索引文件，不建议自己修改。另外开启binlog会有微乎其微的性能下降，大概1%左右。但是考虑到其复制和恢复的能力，这些损失是完全被接受的。
下面看下与binlog相关的参数：
mysql> show variables like '%binlog%';
+------------------------------------------------+----------------------+-------------------+
| Variable_name | Value | 描述 |
+------------------------------------------------+----------------------+-------------------+
| binlog_cache_size | 32768 | 未提交的binlog日志记录在这里，当提交后会更新到binlog文件，默认32k。
| binlog_checksum | CRC32 | binlog文件头中做检验的参数，4个字节。
| binlog_direct_non_transactional_updates | OFF | |
| binlog_encryption | OFF | |
| binlog_error_action | ABORT_SERVER |保证正确的写入binary log日志，默认值为 ABORT_SERVER，当出现错误的时候会使 MySQL 在写 binlog 遇到严重错误时直接退出( 即 Crash 动作)，当binlog_error_action设置为IGNORE_ERROR时，如果服务器遇到这样的错误，它将继续正在进行的事务，记录错误，然后停止日志记录，并继续执行更新。要恢复二进制日志记录，必须再次启用log_bin，这需要重新启动服务器。
| binlog_expire_logs_seconds | 2592000 | binlog的超时日期，默认30天
| binlog_format | ROW | 二进制日志的格式，值有statement（sql信息），row（表的行更改情况，前面的例子就是这种，文件大小会更大），mixed（默认是statement，特殊情况row）
| binlog_group_commit_sync_delay | 0 | 控制二进制日志提交在将二进制日志文件同步到磁盘之前等待多少微秒，默认是0；可使更多事务一次同步到磁盘上。可是使从服务器并行同步数据，要考虑从服务器的吞吐量。增加客户端事务的延迟。
| binlog_group_commit_sync_no_delay_count | 0 |延迟组提交时间截止前的最大事务数，默认0无效。
| binlog_gtid_simple_recovery | ON | |
| binlog_max_flush_queue_time | 0 | |
| binlog_order_commits | ON | 事务顺序提交，多线程可能一位某个线程阻塞导致整体吞吐量下降
| binlog_rotate_encryption_master_key_at_startup | OFF | |
| binlog_row_event_max_size | 8192 | 将超大的事务拆分成大小为8192的事件
| binlog_row_image | FULL | |
| binlog_row_metadata | MINIMAL | |
| binlog_row_value_options | | |
| binlog_rows_query_log_events | OFF | |
| binlog_stmt_cache_size | 32768 | |
| binlog_transaction_compression | OFF | |
| binlog_transaction_compression_level_zstd | 3 | |
| binlog_transaction_dependency_history_size | 25000 | |
| binlog_transaction_dependency_tracking | COMMIT_ORDER | |
| innodb_api_enable_binlog | OFF | |
| log_statements_unsafe_for_binlog | ON | |
| max_binlog_cache_size | 18446744073709547520 | |
| max_binlog_size | 1073741824 | 单个binlog的最大文件，默认1G，查过就生成新的文件，索引+1
| max_binlog_stmt_cache_size | 18446744073709547520 | |
| sync_binlog | 1 |二进制不是每次写都同步到磁盘（缓冲写），改参数是每缓冲写的次数，达到就同步binlog到磁盘 ，1表示不使用缓冲，直接同步磁盘
+------------------------------------------------+----------------------+------------------+
29 rows in set (0.00 sec)
重点参数详细说明：
binlog_cache_size：该属性是基于客户端session的，也就是会为每个客户端分配一个缓存，所以不能分配的太大。当事务的记录大于缓冲的大小，会生成一个临时文件，所以该值也不能太小。使用下面参数查看,Binlog_cache_use记录使用缓存的次数，Binlog_cache_disk_use记录使用临时文件的次数。当Binlog_cache_disk_use次数多了证明32k的大小可能不够使用了，要适当的进行优化。
mysql> show global status like '%binlog_cache%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 1 |
+-----------------------+-------+
2 rows in set (0.01 sec)
