阅读 29

MySQL日志文件

MySQL日志文件可以帮助我们程序员对MySQL数据库的运行状态进行诊断,从而更好的进行数据库层面及代码层面的优化,我说说我所了解的一些MySQL日志文件。

1.错误日志

错误日志文件对MySQL的启动、运行、关闭过程都进行了记录,它不仅记录了所有的错误信息,也记录了一些警告信息和正确的信息。它能过很好的帮助我们定位问题。

我们通过命令SHOW VARIABLES LIKE 'log_error'命令找到该文件的路径和文件名(以下全部使用我的本机MySQL为例)

在默认情况下错误文件的文件名为服务器的主机名。这是我自己的笔记本上面的MySQL。

2.慢查询日志

在面试的经常被问到sql优化,可以聊聊慢查询日志。(在默认情况下不会开启慢查询日志)开启慢查询日志后,MySQL会将运行时间超过long_query_time所设置的参数(默认为10)的所有sql语句都记录到慢查询日志文件中去。

(1)使用set global slow_query_log=1开启了慢查询日志只对当前数据库生效,如果MySQL重启后则会失效。如果要永久生效,就必须修改配置文件。

(2)可以通过命令SHOW VARIABLES LIKE 'slow_query_log'来查看是否开启,我的已经开启了。(如下图)

(3)可以通过命令SHOW VARIABLES LIKE 'long_query_time'来查看这个时间阈值(如下图)。set global long_query_time=1命令设置阈值为1s。

(4)可以通过命令SHOW VARIABLES LIKE 'slow_query_log_file'来查看慢查询日志位置。

(5)用本机测试:首先将阈值设置为1,执行sql语句SELECT * FROM test;用了1s多,然后查询慢查询文件已经产生了该条记录(如下图)

(6)还有两个和慢查询日志有关的比较重要的参数。一个是log_queries_not_using_indexes,意为如果运行的sql语句没有使用索引,则数据库同样会将这条sql记录到慢查询日志里面去;另一个是log_throttle_queries_not_using_indexes,意为每分钟运行记录到slow log的且未使用索引的sql语句的次数。具体用法和上面类似,首先要开启相关功能,具体不再赘述。

后面随着sql的增多,不可能慢慢查看日志,而这时MySQL提供了mysqldumpslow命令,可以很好的帮助我们解决问题,具体的用法请问度娘。

注意:MySQL数据库会记录运行时间超过阈值的所有sql语句,但是运行时间正好等于long_query_time的情况是不会被记录下。

3.查询日志

查询日志记录了所有对MySQL数据库的请求信息,不管这些请求是否得到了正确的执行。默认情况下,通用查询日志不会被启用,需要自己在配置文件设置,我现在通过命令set global general_log=on;进行启用查询日志(如下图)。

用本机测试:首先使用了一些查询语句,发现这些正常的请求,是可以看到日志的。又故意输入错误密码连接MySQL,发现查询日志甚至记录了对Access denied的请求(如下图)。

4.二进制日志

二进制文件记录了对MySQL数据库执行更改的所有操作,但是不包括对数据本身没有进行修改的操作,如select和show这类操作。虽然如此,但是二进制文件还是有许多其他重要的作用。 恢复:某些数据的恢复需要二进制文件; 复制:其原理类似于恢复,通过复制和执行二进制日志使一台远程的MySQL数据库与另一台MySQL数据库进行实时同步(MySQL主从同步); 审计:用户可以通过二进制日志中的信息来进行审计,判断是否有对数据库进行注入的攻击。

二进制日志文件在默认情况下并没有启动,需要我们自己配置,通过配置参数log-bin[=name]可以启动二进制日志。(下面全部以我的线上应用为例,线上为mysql主从配置)输入show slave status命令可以看到如下信息(如下图)mysql-bin.000001即为二进制日志文件。

一些重要的配置文件的参数: (1)max_binlog_size:单个二进制日志文件的最大值,如果超过了该值,则生成一个新的二进制日志文件。输入show VARIABLES like 'max_binlog_size'命令,可以看出我的配置为默认1G(如下图)

(2)binlog_cache_size:当使用事务的表存储引擎时,所有未提交的二进制日志会被记录到一个缓存中去,等该事务提交时才将缓存中的二进制日志写入二进制日志文件中,而该缓存的大小则由该参数决定。输入show VARIABLES like 'binlog_cache_size'命令,可以看出我的配置为默认32k(如下图)。那有人会问为什么只有这么小呢?那是因为该参数是基于会话的,当一个线程开启一个事务的时候,MySQL会自动分配该参数大小的缓存,如果设置过大,你懂的!

(3)sync_binlog:表示每写缓存多少次就同步到磁盘里面去。输入show VARIABLES like 'sync_binlog'命令,可以看出我的是采用同步写磁盘的方式来写二进制日志(如下图)

(4)binlog-do-db、binlog-ignore-db:分别表示需要写入或者忽略写入哪些库的日志,默认是空的,表示需要同步所有库的日志到二进制日志中去,我的默认也是空的。

(5)log-slave-update:如果当前数据库是复制中的slave角色,它是不会从master取得并去执行二进制日志写入到自己的二进制文件中去。如果要写,则需要设置log-slave-update,因为我自己的应用配置是采用一主一从的,所以执行show VARIABLES like 'log-slave-update'命令后,是没有值的(如下图)

(6)binlog_format:该参数影响了记录二进制日志的格式。其有三值,分别为STATEMENT、ROW、MIXED。 STATEMENT:记录的是日志的逻辑sql语句。 ROW:记录表的行更改情况。 MIXED:会根据执行的每一条具体的sql语句来区分对待记录的日志形式,也就是在STATEMENT和ROW之间选择一种。我的配置就采用这个。

注意:①如果设置了ROW,可以将InnoDB的事务隔离级别设置为Read-Committed,以获得更好的并发行;②bing_format参数是有存储引擎的限制,具体的请问度娘;③ROW所产生的二进制文件会比STATEMENT大。


如有描述不对的地方,望指正!

关注下面的标签,发现更多相似文章
评论