MySQL日志功能详解
作者：尹正杰
版权声明：原创作品，谢绝转载！否则将追究法律责任。
一.查询日志
它是用来保存所有跟查询相关的日志，这种日志类型默认是关闭状态的，因为MySQL的用户有很多，如果将每个用户的查询操作都记录下来的话，对服务器的资源开销也是一件令人烦恼的事情。查询日志常见的几个参数：
1 mysql> show global variables like 'log'; #查看是否记录所有语句的日志信息于一般查询日志文件（general_log），默认是关闭状态。 2 +---------------+-------+ 3 | Variable_name | Value | 4 +---------------+-------+ 5 | log | OFF | 6 +---------------+-------+ 7 1 row in set (0.00 sec) 8 9 mysql> show global variables like 'log_output'; #它有三个值，即{TABLE|FILE|NONE}，分别表示记录在表中（table），文件（file）中或是不记录（none）。注意，只有og_output的值等于 FILE时，general_log_file的参数才会有意义。且 table和file 可以同时出现，用逗号分隔即可 10 +---------------+-------+ 11 | Variable_name | Value | 12 +---------------+-------+ 13 | log_output | FILE | 14 +---------------+-------+ 15 1 row in set (0.00 sec) 16 17 mysql> show global variables like 'general_log'; #查看是否启用查询日志功能 18 +---------------+-------+ 19 | Variable_name | Value | 20 +---------------+-------+ 21 | general_log | OFF | 22 +---------------+-------+ 23 1 row in set (0.00 sec) 24 25 mysql> show global variables like 'general_log_file'; #定义了一般查询日志保存的文件 26 +------------------+----------------------------+ 27 | Variable_name | Value | 28 +------------------+----------------------------+ 29 | general_log_file | /var/run/mysqld/mysqld.log | 30 +------------------+----------------------------+ 31 1 row in set (0.00 sec) 32 33 mysql>
1 mysql> set global log='on'; 2 Query OK, 0 rows affected, 1 warning (0.00 sec) 3 4 mysql> set global log_file='on'; 5 ERROR 1193 (HY000): Unknown system variable 'log_file' 6 mysql> 7 mysql> set global log_output='table'; 8 Query OK, 0 rows affected (0.00 sec) 9 10 mysql> show tables from mysql; 11 +---------------------------+ 12 | Tables_in_mysql | 13 +---------------------------+ 14 | columns_priv | 15 | db | 16 | event | 17 | func | 18 | general_log | 19 | help_category | 20 | help_keyword | 21 | help_relation | 22 | help_topic | 23 | host | 24 | ndb_binlog_index | 25 | plugin | 26 | proc | 27 | procs_priv | 28 | servers | 29 | slow_log | 30 | tables_priv | 31 | time_zone | 32 | time_zone_leap_second | 33 | time_zone_name | 34 | time_zone_transition | 35 | time_zone_transition_type | 36 | user | 37 +---------------------------+ 38 23 rows in set (0.05 sec) 39 40 mysql> select * from mysql.genral_log; 41 ERROR 1146 (42S02): Table 'mysql.genral_log' doesn't exist 42 mysql> 43 mysql> 44 mysql> 45 mysql> select * from mysql.general_log; 46 +---------------------+---------------------------+-----------+-----------+--------------+---------------------------------+ 47 | event_time | user_host | thread_id | server_id | command_type | argument | 48 +---------------------+---------------------------+-----------+-----------+--------------+---------------------------------+ 49 | 2017-11-23 07:15:45 | root[root] @ localhost [] | 34 | 0 | Query | show tables from mysql | 50 | 2017-11-23 07:16:10 | root[root] @ localhost [] | 34 | 0 | Query | select * from mysql.genral_log | 51 | 2017-11-23 07:16:49 | root[root] @ localhost [] | 34 | 0 | Query | select * from mysql.general_log | 52 +---------------------+---------------------------+-----------+-----------+--------------+---------------------------------+ 53 3 rows in set (0.00 sec) 54 55 mysql>
二.慢查询日志
查询执行时长超过指定时长的查询，即为慢查询。这里的慢不一定是查询语句存在问题，可能是因为访问你的资源当时不被许可访问，就好比你将一个一个MySQL库中的一个表添加写锁，那么别人就没有办法去查询这个表的内容啦，等到你将这个表锁解开之后，访问这张表的查询语句才会被执行。
慢查询日志是我们通常拿来定位系统上查询操作执行速度过慢时常用到的一个评估工具，所以在生产环境中很有必要启用慢查询日志功能的哟！它默认情况下也是没有启用的哟。慢查询常见的几个参数：
1 mysql> show global variables like 'slow_query_log'; #是否启用慢查询日志，它的输出位置也取决log_output={table|file|none}。 2 +----------------+-------+ 3 | Variable_name | Value | 4 +----------------+-------+ 5 | slow_query_log | OFF | 6 +----------------+-------+ 7 1 row in set (0.01 sec) 8 9 mysql> show global variables like 'slow_query_log_file'; #查看定义慢查询日志的文件 10 +---------------------+---------------------------------+ 11 | Variable_name | Value | 12 +---------------------+---------------------------------+ 13 | slow_query_log_file | /var/run/mysqld/mysqld-slow.log | 14 +---------------------+---------------------------------+ 15 1 row in set (0.02 sec) 16 17 mysql> show global variables like 'long_query_time'; #慢查询的时长，超出这个时长的都被记录为慢查询。 18 +-----------------+-----------+ 19 | Variable_name | Value | 20 +-----------------+-----------+ 21 | long_query_time | 10.000000 | 22 +-----------------+-----------+ 23 1 row in set (0.00 sec) 24 25 mysql>
1 mysql> show global variables like 'slow_query_log'; 2 +----------------+-------+ 3 | Variable_name | Value | 4 +----------------+-------+ 5 | slow_query_log | OFF | 6 +----------------+-------+ 7 1 row in set (0.00 sec) 8 9 mysql> set global slow_query_log=1; 10 Query OK, 0 rows affected (0.00 sec) 11 12 mysql> set global log_output='FILE,TABLE'; 13 Query OK, 0 rows affected (0.00 sec) 14 15 mysql> show global variables like 'slow_query_log'; 16 +----------------+-------+ 17 | Variable_name | Value | 18 +----------------+-------+ 19 | slow_query_log | ON | 20 +----------------+-------+ 21 1 row in set (0.00 sec) 22 23 mysql> show global variables like 'log_output'; 24 +---------------+------------+ 25 | Variable_name | Value | 26 +---------------+------------+ 27 | log_output | FILE,TABLE | 28 +---------------+------------+ 29 1 row in set (0.00 sec) 30 31 mysql> select database(); 32 +------------+ 33 | database() | 34 +------------+ 35 | NULL | 36 +------------+ 37 1 row in set (0.00 sec) 38 39 mysql> use yinzhengjie 40 Reading table information for completion of table and column names 41 You can turn off this feature to get a quicker startup with -A 42 43 Database changed 44 mysql> show tables; 45 +-----------------------+ 46 | Tables_in_yinzhengjie | 47 +-----------------------+ 48 | Classes | 49 | students | 50 | tearchers | 51 +-----------------------+ 52 3 rows in set (0.00 sec) 53 54 mysql> lock tables students write; 55 Query OK, 0 rows affected (0.00 sec) 56 57 mysql> select * from mysql.general_log; 58 ERROR 1100 (HY000): Table 'general_log' was not locked with LOCK TABLES 59 mysql> unlock tables; 60 Query OK, 0 rows affected (0.00 sec) 61 62 mysql> select * from mysql.general_log; 63 +---------------------+---------------------------------------------+-----------+-----------+--------------+--------------------------------------------------+ 64 | event_time | user_host | thread_id | server_id | command_type | argument | 65 +---------------------+---------------------------------------------+-----------+-----------+--------------+--------------------------------------------------+ 66 | 2017-11-23 07:15:45 | root[root] @ localhost [] | 34 | 0 | Query | show tables from mysql | 67 | 2017-11-23 07:16:10 | root[root] @ localhost [] | 34 | 0 | Query | select * from mysql.genral_log | 68 | 2017-11-23 07:16:49 | root[root] @ localhost [] | 34 | 0 | Query | select * from mysql.general_log | 69 | 2017-11-23 07:17:11 | yzj[yzj] @ www.yinzhengjie.com [10.0.0.115] | 31 | 0 | Quit | | 70 | 2017-11-23 07:18:36 | root[root] @ localhost [] | 34 | 0 | Quit | | 71 | 2017-11-23 07:18:45 | root[root] @ localhost [] | 35 | 0 | Connect | root@localhost on | 72 | 2017-11-23 07:18:45 | root[root] @ localhost [] | 35 | 0 | Query | select @@version_comment limit 1 | 73 | 2017-11-23 07:19:56 | root[root] @ localhost [] | 35 | 0 | Query | show global variables like 'slow_query_log' | 74 | 2017-11-23 07:20:11 | root[root] @ localhost [] | 35 | 0 | Query | show global variables like 'slow_query_log_file' | 75 | 2017-11-23 07:20:26 | root[root] @ localhost [] | 35 | 0 | Query | show global variables like 'long_query_time' | 76 | 2017-11-23 07:20:38 | root[root] @ localhost [] | 35 | 0 | Query | show global variables like 'long_slow_filter' | 77 | 2017-11-23 07:24:50 | root[root] @ localhost [] | 35 | 0 | Query | show global variables like 'slow_query_log' | 78 | 2017-11-23 07:24:55 | root[root] @ localhost [] | 35 | 0 | Query | set global slow_query_log=1 | 79 | 2017-11-23 07:25:57 | root[root] @ localhost [] | 35 | 0 | Query | set global log_output='FILE,TABLE' | 80 | 2017-11-23 07:26:10 | root[root] @ localhost [] | 35 | 0 | Query | show global variables like 'slow_query_log' | 81 | 2017-11-23 07:26:21 | root[root] @ localhost [] | 35 | 0 | Query | show global variables like 'log_output' | 82 | 2017-11-23 07:27:09 | root[root] @ localhost [] | 35 | 0 | Query | select database() | 83 | 2017-11-23 07:27:14 | root[root] @ localhost [] | 35 | 0 | Query | SELECT DATABASE() | 84 | 2017-11-23 07:27:14 | root[root] @ localhost [] | 35 | 0 | Init DB | yinzhengjie | 85 | 2017-11-23 07:27:14 | root[root] @ localhost [] | 35 | 0 | Query | show databases | 86 | 2017-11-23 07:27:14 | root[root] @ localhost [] | 35 | 0 | Query | show tables | 87 | 2017-11-23 07:27:14 | root[root] @ localhost [] | 35 | 0 | Field List | Classes | 88 | 2017-11-23 07:27:14 | root[root] @ localhost [] | 35 | 0 | Field List | students | 89 | 2017-11-23 07:27:14 | root[root] @ localhost [] | 35 | 0 | Field List | tearchers | 90 | 2017-11-23 07:27:27 | root[root] @ localhost [] | 35 | 0 | Query | show tables | 91 | 2017-11-23 07:27:41 | root[root] @ localhost [] | 35 | 0 | Query | lock tables students write | 92 | 2017-11-23 07:28:00 | root[root] @ localhost [] | 36 | 0 | Connect | root@localhost on | 93 | 2017-11-23 07:28:00 | root[root] @ localhost [] | 36 | 0 | Query | select @@version_comment limit 1 | 94 | 2017-11-23 07:28:10 | root[root] @ localhost [] | 36 | 0 | Query | select * from yinzhengjie.students | 95 | 2017-11-23 07:29:28 | root[root] @ localhost [] | 35 | 0 | Query | select * from mysql.general_log | 96 | 2017-11-23 07:29:53 | root[root] @ localhost [] | 35 | 0 | Query | unlock tables | 97 | 2017-11-23 07:29:55 | root[root] @ localhost [] | 35 | 0 | Query | select * from mysql.general_log | 98 +---------------------+---------------------------------------------+-----------+-----------+--------------+--------------------------------------------------+ 99 32 rows in set (0.00 sec) 100 101 mysql>
三.错误日志
顾名思义，这是用来记录错误的日志，但是不仅仅是记录错误信息，还包括MySQL启动，关闭，复制线程（指的是从服务器）的信息哟。错误日志默认是开启的。它主要记录的信息如下：
1>.服务器启动和关闭过程中的信息；
2>.服务器运行过程中的错误信息
3>.事件调度器运行一个事件时产生的信息
4>.在复制架构中的从服务器上启动从服务器线程时产生的信息
常见参数如下：
1 mysql> show global variables like 'log_error'; #指定错误日志文件位置。 2 +---------------+---------------------+ 3 | Variable_name | Value | 4 +---------------+---------------------+ 5 | log_error | /var/log/mysqld.log | 6 +---------------+---------------------+ 7 1 row in set (0.00 sec) 8 9 mysql> 10 mysql> show global variables like 'log_warnings'; #是否将经过日志也记录在错误日志文件中去。 11 +---------------+-------+ 12 | Variable_name | Value | 13 +---------------+-------+ 14 | log_warnings | 1 | 15 +---------------+-------+ 16 1 row in set (0.00 sec) 17 18 mysql>
四.二进制日志
1>.什么是二进制文件
只记录修改相关的操作，记录了当前服务器的数据修改和有潜在可能性影响数据修改的语句。它用来实现复制的基本凭据。也就是说，你可以将生成环境中的MySQL的二进制文件拿到线下的服务器上运行一下，理论上你会拿到和生成环境中一样的数据哟，因此，二进制日志也叫复制日志。二进制日志文件默认在数据目录下，通常情况下为mysql-bin#（例如：mysql-bin.000001，mysql-bin000002）。
2>.查看MySQL日志文件
由于二进制文件格式是二进制类型的，我们不能用cat等查看普通文本类命令去查看这些二进制日志，我们可以通过mysqlbinlog来查看。注意“show master status; ”查看当前使用的二进制日志和下一个事件开始时的基于的位置。
1 MariaDB [(none)]> show binlog eventsG #查看mysql的日志 2 *************************** 1. row *************************** 3 Log_name: mysql-bin.000001 4 Pos: 4 5 Event_type: Format_desc #事件类型 6 Server_id: 1 #指定服务器的唯一标识。 7 End_log_pos: 245 8 Info: Server ver: 5.5.36-MariaDB-log, Binlog ver: 4
3>.日志滚动
为了避免一个文件过大，我们可以适当的将文件的内容分开存储，这就是日志滚动，比如：当超过1G，日志会滚动。当然，你也可以按照文件大小自定义， 时间定义。想要手动滚动日志，执行“flush logs;”即可。
4>.查看当前正在使用的log日志
1 MariaDB [(none)]> show binary logs; 2 +------------------+-----------+ 3 | Log_name | File_size | 4 +------------------+-----------+ 5 | mysql-bin.000001 | 1043 | 6 | mysql-bin.000002 | 245 | 7 +------------------+-----------+ 8 2 rows in set (0.00 sec)
5>.二进制的主要两个功能是：
时间点恢复;(它的功能不亚于事务日志哟！)
复制;
6>.清除日志
不要手动去删除，而是用专业的工具去干这件事情，即purge工具
1 mysql> help PURGE 2 Name: 'PURGE BINARY LOGS' 3 Description: 4 Syntax: 5 PURGE { BINARY | MASTER } LOGS 6 { TO 'log_name' | BEFORE datetime_expr （某个时间之前） } 7 mysql>
7>.Mysql 记录二进制日志的格式
基于语句（statement）：
只把语句服务器执行的SQL语句记录下来，但是可能存在不精准的情况。例如：“INSERT INTO t1 value(current_date())”，明锐的你可能发现有"current_date()"这个参数，如果从服务器执行了想用的语句，如果存在网络延迟的情况，就会导致主从同步存在误差！
基于行(row)：
可以更精确的记录数据，但是会面临记录的数据量过大的情况，可能一个语句的操作，匹配了1w多行，那么这1w多行数据的修改都会记录在这个二进制文件中去的。
混合模式（mixed）：
既有了statement的模型，也用了mixed的特点。这种模式据反映不是很好，建议还是用基于行的模式，因为它能够保证数据更加精确，换来更加精确的同时，可能对你的存储空间和I/O的使用率会有所提高哟。
8>.指定从那个位置开始读取
1 mysqlbinlog的常用的[options]：
2 a>--start-time #起始时间
3 b>.--stop-time #结束时间
4 c>.--start-position #基于起始位置来显示信息
5 d>.--stop-position #指定结束位置来显示
6 命令行查询方式如下（可以将读取的内容保存下来，在另一台服务器上可以情景再现）：
7 [root@yinzhengjie ~]# mysqlbinlog --start-position=153 mysql-bin.000001 >/yinzhengjie/backup.sql
8 SQL命令查询方法如下：
9 MariaDB [(none)]> show binlog events in 'mysql-bin.000001' from 153G
9>.二进制日志文件内容格式
1 事件发生的日期和时间（会在关键字“at”）
2 服务器ID（server id）
3 事件结束位置（end_log_pos）
4 事件的类型（如：Query，Stop等等）
5 原服务器生成此事件时的线程ID号（thead_id，可以通过“show processlist;”进行查询）
6 语句时间戳和写入二进制文件的时间差，单位为秒（exec_time，表示记录日志所用的时间戳，当他等于0时表示没有用到1秒钟。）
7 错误代码，0表示正常执行（error_code,排查方法就得查看官方文档。）
8 事件内容（修改的SQL语句）
9 事件位置（相当于下一事件的开始位置，还是用“at”关键字标志）
10>.二进制日志文件常用的相关参数详解
1 mysql> show global variables like 'log_bin'; #查看是否开启二进制日志功能，当然我们可以在MySQL的配置文件（my.cnf）中指定文件路径（如：log_bin=/yinzhengjie/log/mysql-bin）； 2 +---------------+-------+ 3 +---------------+-------+ 4 | Variable_name | Value | 5 +---------------+-------+ 6 | log_bin | OFF | 7 +---------------+-------+ 8 1 row in set (0.01 sec) 9 10 mysql> 11 mysql> show global variables like 'log_bin_trust_function_creators'; #不阻止任何存储函数，存在一定风险，默认关闭即可； 12 +---------------------------------+-------+ 13 | Variable_name | Value | 14 +---------------------------------+-------+ 15 | log_bin_trust_function_creators | OFF | 16 +---------------------------------+-------+ 17 1 row in set (0.00 sec) 18 19 mysql> 20 mysql> show global variables like 'sql_log_bin'; #当前会话是否将二进制文件进入进二进制文件，默认为ON； 21 +---------------+-------+ 22 | Variable_name | Value | 23 +---------------+-------+ 24 | sql_log_bin | ON | 25 +---------------+-------+ 26 1 row in set (0.02 sec) 27 28 mysql> 29 mysql> show global variables like 'sql_log_off'; #是否将一般查询日志记入查询日志 30 +---------------+-------+ 31 | Variable_name | Value | 32 +---------------+-------+ 33 | sql_log_off | OFF | 34 +---------------+-------+ 35 1 row in set (0.00 sec) 36 37 mysql> 38 mysql> show global variables like 'sync_binlog'; #同步缓冲中的二进制到硬盘的时间，0不基于时间同步，只在事件提交时同步 39 +---------------+-------+ 40 | Variable_name | Value | 41 +---------------+-------+ 42 | sync_binlog | 0 | 43 +---------------+-------+ 44 1 row in set (0.00 sec) 45 46 mysql> 47 mysql> show global variables like 'binlog_format'; #指定记录二进制日志的格式 有三种格式：基于语句（statement），基于行（row），混合模式（mixed） 48 49 +---------------+-----------+ 50 | Variable_name | Value | 51 +---------------+-----------+ 52 | binlog_format | STATEMENT | 53 +---------------+-----------+ 54 1 row in set (0.00 sec) 55 56 mysql> 57 mysql> show global variables like 'max_binlog_cache_size'; #mysql二进制日志的缓冲区大小，仅用于缓存事务类的语句 58 +-----------------------+----------------------+ 59 | Variable_name | Value | 60 +-----------------------+----------------------+ 61 | max_binlog_cache_size | 18446744073709547520 | 62 +-----------------------+----------------------+ 63 1 row in set (0.01 sec) 64 65 mysql> 66 mysql> show global variables like 'max_binlog_size'; #二进制日志文件的上限，单位为字节 67 +-----------------+------------+ 68 | Variable_name | Value | 69 +-----------------+------------+ 70 | max_binlog_size | 1073741824 | 71 +-----------------+------------+ 72 1 row in set (0.00 sec) 73 74 mysql>
友情提示：
MySQL的很多默认设置并不适合生成环境，我们需要调整很多东西。给出两点提示：
a>.切勿将二进制日志与数据文件放在同一设备；
b>.可以临时通过sql_log_bin来控制二进制的写入；
五.中继日志
1 从服务器上的二进制日志。说白了中继日志其实就是从主服务器上的二进制日志中取数据，然后写入中级之日里面，在从服务器上，执行中继日志的sql信息，这样从服务器就会得到和主服务器一样的内容，与此同时每次执行之后从服务器的二进制日志也会记录，聪明的你可能也会想到，这个从服务器的二进制日志内容应该是和主服务器是一致的，所以我们通常采取的操作就是将从服务器的二进制日志关闭掉。
2 对于非从服务器的中继日志并没有启用，可能会用到以下两个参数：
3 relay_log_purge = {ON|OFF} # 是否自动清理不在需要的中继日志
4 relay_log_space_limit #中继（空间）大小是否限制
六.事务日志.
1 先暂存事物提交的数据而后在同步到数据文件中去的一种日志。它的主要目的是将随机I/O转换为顺序I/O并保证事物的兼容性的。（顺序I/O是早期提升写入的速度一个不错的解决方案，它比随机I/O的性能可能会高出100倍呢！不过后来固态硬盘的出现顺序I/O起到的作用就不是很明显啦。）
2 事务日志我们也称之为日志文件组，它至少要存在两个日志文件以实现轮询。我们知道，MySQL的innodb是支持事物的，当启动一个事务时，修改的数据是存储在innodb的缓存（innodb_buffer）中的,当这个缓存存储不下之后，它就会将数据写入到日志文件组（事务日志）中的一个文件，当其中的一个文件写满之后，又开始写第二个文件，与此同时，第一个文件的内容开始网磁盘中写，已达事务持久化的特性之一。这也就意味着当事务回滚时，很可能会将已经写入磁盘中的数据进行删除操作，这样性能就会降低，而如果事务较小的话，也就不会存在将数据写入到磁盘中，甚至不用写入事务日志中，直接在innodb的缓存中就将问题解决，因此，尽可能使用小事务来替代大事务来提升事务引擎的性能。
3
友情提示：
当存储事务日志的磁盘坏掉是，数据是无法恢复的哟！因此选择一个可靠的磁盘还是相当有必要的，比如我们可以给我们的数据做raid10或者raid1（推荐使用raid10）来提供这种保障。事务日志不能帮助我们恢复数据，它的作用在于当操作系统崩溃时（比如异常断电）它能够保障已经提交的事物不丢失，而未提交的事物能回滚。如果想要恢复日志还得依赖于二进制日志。