Mysql—服务器层日志_动联服务器中如何用mysql查询日志-CSDN博客

本文链接：https://blog.csdn.net/l_dongyang/article/details/102481815

一、日志类别

1.错误日志（error log）：服务器的启动、停止，以及运行中严重的警告或者通知信息，比如表的修复等；

2.常规日志（general log）：服务器接收到的每一个命令，包括客户端连接以及sql执行记录等；

3.慢日志（slow query log）：执行时间超过一定阈值和没有使用索引的sql，用来发现并调优一些慢sql；

4.二进制日志（binlog）：引起或可能引起数据更改的sql的日志记录用于数据还原以及主从复制功能的基础；

5.中继日志（relay log）：主从复制时产生的日志；

二、日志详解

2.1 错误日志

错误日志记录了服务器的启动、停止相关信息，以及运行过程中服务器实例发生的警告、重要变更以及错误信息。

可以通过show variables like 'log_error%'，查看当前服务器的错误日志的配置信息，如下图：

各个参数的含义及配置如下表：

参数名称	参数说明	参数示例
log-error	错误日志文件的位置	可以在配置文件中通过log-error指定文件，如果不指定则在数据目录下使用host_name.err
log-error_services	指定过滤器以及输出组件	log_filter_internal是错误过滤，log_sink_internal是格式输出。从左至右依次执行，不用的组件组合会产生不同的错误日志记录
log_error_verbosity	过滤的级别	参数影响了log_filter_internal的过滤规则；1：错误信息；2：错误和警告；3：错误、警告和注释

通过配合使用组件，以系统以及一些参数的配置决定组件的规则，将日志按照预定的格式输出到指定的日志文件中，可以以此知晓mysql服务的一些重要信息，例如启动、关闭的次数，一些警告、系统信息以及一些错误信息，以此来诊断服务是否存在问题，从而解决问题，优化服务。

下图是我在本地关闭服务在重启服务之后的error log的记录的信息，

2.2 常规日志（general log）

mysql的常规日志默认是关闭的，因为它会记录服务器接收到的每一个命令，包括客户端的连接、sql的执行等，如果操作或者访问量比较大，那么这个日志会是巨大的，IO开销也是不容忽视的。当我们进行调试的时候，可以短时间的打开以供调试诊断使用。可以通过show variables like '%general_log%'，查看当前服务器的错误日志的配置信息，如下图：

general_log是常规日志是否开启的开关，可以看到默认是‘OFF’，也就是关闭的，可以通过命令‘set global general_log = 'on''开启常规日志，可以通过关闭开启以及一些命令观察日志文件的记录，如下图：

2.3 慢日志（slow query log）

慢日志（slow query log）是mysql服务器层比较重要的日志，记录了执行时间超出了一定阈值的sql记录，因为慢sql占据着连接、资源，会拖垮整体数据库的性能，导致服务不可用，因而监控慢sql就显得尤为重要，可以快速的定位是什么sql导致性能变慢。

可以通过命令show variables like '%slow_query%';来查看慢日志功能是否开启了，下表是与慢日志相关的参数设置，

参数名称	参数说明	参数示例
slow_query_log	慢日志开启开关	ON：开启；OFF：关闭
slow_query_log_file	慢日志记录文件	慢日志的输出文件
long_query_time	时间阈值	默认10s，可以动态设置（重开客户端连接）
log_queries_not_using_indexs	没有走索引的查询也记录到慢日志	默认OFF（关闭）
log_output	日志存储方式	FILE：日志存储文件；TABLE：日志存表；可以两种都选择

2.4 二进制日志（binlog）

二进制日志记录了所有的DML、DDL语句，可以说是数据库表变更的一个记录文件，因为它记录了所有的变更操作，因此可以基于此日志文件做数据恢复、数据备份以及主从复制等，相关参数如下表：

参数名称	参数说明	参数示例
log_bin	binlog开关	ON：开，OFF：关闭
log_bin_basename	binlog日志文件名	eg：binlog.000040
log_bin_index	binlog索引文件名	该文件记录了所有binlog文件名字
binlog_expire_logs_seconds	binlog文件过期时间	默认30天。expire_logs_days在8版本默认是0，两个参数精度不同，优先以秒级参数为准。
binlog_format	binlog日志文件格式	statement：row：mixed：
max_binlog_size	binlog文件大小	binlog文件大小限制；有可能超出大小，因为最后一个事务日志超出其大小，事务日志一起写入，则事务日志会超出
binlog_cache	binlog缓存大小	默认 32768，也就是32k，是每一个线程所分配的缓冲大小
max_binlog_cache	最大binlog缓存大小	如果一个事务的日志超过缓冲最大值，会报错
syn_binlog	binlog缓存刷盘规则	0：提交事务写入文件缓存，不刷盘；1：每次提交事务，写入缓存，并刷盘；N：每次提交事务都写入缓存，N次事务刷盘

binlog的写入是以事务为单位的，也就是说一次写入就是一个事务的日志，不管事务多大，都是不可分割的，因为这个原因导致一个binlog文件可能会超出max_binlog_size的大小。当一个事务开始之后，binlog记录于线程的binlog cache中，在事务最终提交之前，会将缓存的binlog日志提交刷盘，刷盘的规则根据syn_binlog参数而定。

binlog记录格式？mysql中提供了三种binlog的格式，可以通过配置参数binlog_format来决定。下表描述了三种方式的大概情况，可以根据具体业务情况来选择使用：

binlog_format取值	优点	缺点
statement：记录所有修改数据的sql	只需要记录改变数据的sql，不需要记录每条更改记录，日志量比较少，减少磁盘io（当涉及到批量更改数据时，比较节省空间，提高性能，但是如果只是改变一条记录，可能日志量比row要高，取决于业务情况）	涉及到主从复制时，要保证slave执行和master一样的结果，可能会需要额外记录信息；一些函数功能无法实现，比如now() 等。
row：记录改变的每条数据	清晰的记录了每一条修改记录的内容，不需要记录执行sql的上下文信息，结构清晰名了。不会出现一些函数例如now()的不确定性。	如果数据量更改比较大的时候，比如更新一定条件下的数据记录，或者更改表的结构使得表中数据改变，则日志量会巨大。
mixed：两种方式的结合	两种模式根据适合的情况使用。比如一般的语句会使用statement模式，如果涉及到一些函数影响主从复制，则会使用row模式。高版本的mysql对row进行了优化，当遇到表结构改变时，会使用statement记录，但是update/delete等会使用row。

binlog的记录格式，推荐使用mixed方式，这种方式可以根据sql选择合适的方式进行记录。当然还是要结合我们的业务情况进行选择。

查看binlog日志内容？可以通过两种方式查看binlog日志内容，首先mysql提供了mysqlbinlog命令，可以通过此命令查看，mysqlbinlog binlog.000002：

上面这种命令查看可能不是很方便，还可以通过命令show binlog events in 'binlog.000002' \G;查看：

binlog文件组成？binlog包含两种文件，xxx.index是binlog索引文件，记录了在使用的binlog文件，host_name.序号是binlog日志文件，记录了binlog的具体内容，本机binlog文件列表,以及binlog索引文件内容如下：

查看binlog文件情况？通过show binary logs；查看使用的binlog文件列表，以及每个文件的大小：

通过show master status；查看当前正在使用日志文件的情况，写入的位置等信息，如下图，正在使用binlog.000002文件，写入位置是4738字节处，

如何使用binlog恢复？binlog日志最重要的作用之一便是数据恢复，当我们误操作导致数据删除或者更改时候，可以选择使用binlog进行数据恢复，加入我新增了一条数据，之后误操作将数据全部清除了，我只需要恢复我插入的那条数据即可，可按照如下步骤：

id为14的记录是后插入记录，只需要恢复到这条记录即可。

1.首先我们要确认我们开启了binlog日志，即通过参数log_bin启用来确认；

2.为了不影响恢复的操作日志进入恢复点的binlog，我们使用flush logs使binlog日志刷新，恢复之后的操作都在新的binlog中，这样不会和之前的binlog冗余，安全且清晰；

3.首先我们需要确认我们要恢复的日志段，可以通过结合mysqlbinlog和show binlog events命令来结合看应该恢复的时间段和日志的偏移位置，比如我想恢复position617-770的数据更新，需要把该位置段的binlog导出为sql执行文件：

4.导出sql文件之后，在数据中执行该文件，使用命令：source e:\\change.sql,如下：

5. 可以看到恢复了id=14的记录，恢复操作结束：

后续的恢复操作日志都记录在了新的binlog.000044日志文件中，不会和binlog.000043文件信息混合，是比较清晰的。

binlog主从同步？使用binlog完成数据库的主从复制也是最重要的功能之一,因为binlog记录了数据库的改变，可以通过在从库回放这些命令，保持和主库做相同的改变。

主从复制原因？第一，可以做数据备份，也就是容灾，如果主节点发生宕机，可以使用从节点顶替；第二，可以实现读写分离，主库用作写，从库用作读，写的开销是比读大的，操作隔离，可以分别提高性能；第三，高可用性，分布式的读写分离提高可用性；第四，高扩展性，可以动态的扩展从节点，这样可以减少单机的IO次数，从而提高没台机器库的性能，提高整体的性能。

主从复制模式？一般现实生产中用的比较多的形式是一主一从或者一主多从，也就是一个主库，配置一个或者多个从库。除此之外还有多主一从，多台主机使用一个性能比较好从库，双主复制，两个主机互相为主从，这样两个主库都保持相同的数据，级联复制，特殊之处是从库可能连的是从库，而不是主库，这种方式可以缓解从库比较多的情况下主库的压力，因为从库过多，就会有过多的log dump thread，弊端就是从库连接从库，会导致越距离主库远，延迟就会越大，可以根据业务情况来选取模式。

binlog复制过程线程？一共涉及到三个线程，master binlog dump thread，将binlog信息发送至从节点；slave IO thread将master发来的binlog日志放入relay log中；slave sql thread回放relay log中的binlog日志命令，实现和主库数据保持一致功能。

binlog日志主从复制，有一张比较经典的图，如下所示：

复制大大致过程如下：

前提是主从配置完毕，binlog功能打开，客户端新的变更命令执行，数据改变，事务提交，记录binlog日志，都正常；

1.从库开启主从复制开关，命令start slave执行后，从库会创建两个线程，slave io thread、slave sql thread。

2.slave io thread通过读取并拿着从库中配置的主库的信息（binlog position、主库IP等），尝试连接主服务器，以长连接的方式保持连接（超过参数slave_net_timeout时间，就会断开连接，然后进行重连）；

3.主库的binlog dump thread校验slave io thread传过来的信息，如果正确，就拿着传过来的old binlog position和当前的最新的binlog的position作比较，如果相等，则保持等待状态；

4.如果不一致，说明binlog有更新，则binlog dump thread首先给正在使用的binlog文件上锁，然后读取其中old binlog positon之后的sql以及最新的binlog position，然后释放锁，将读取信息一起传给slave io thread；

5.slave io thread获取到最新的binlog日志内容之后，会将其复制到本地的relay log之中，并将最新的binlog position更新记录，以便下次请求主库的binlog使用，然后继续执行2步骤；

6.slave sql thread监听到relay log有变更之后，就会通过获取新的sql进行回放，完成这一次主从操作。

2.5 中继日志（relay log）

上面提到了relay log是主从复制的时候，从节点的io thread将主节点传输过来的binlog存储的本地文件，里边记录了主节点增量变更的命令日志，在从库本地文件中，可以看到mysql-relay-bin开头的文件就是relay log文件，其中也包括一个index索引文件和若干个日志文件，可以看出和binlog的组织结构很相似，当relay log文件发生变更时，监听该文件的sql thread就会执行相应的增量sql，使从库和主库保持数据一致性。