MySQL的各种日志

错误⽇志：

记录了mysql在执⾏过程中的错误信息，但是不仅仅记录错误信息，还有其他信息：mysql执⾏过程总的错误信息mysql执⾏过程中的警告信息

events scheduler运⾏时产⽣的信息

mysql启动和停⽌过程中输出的信息，未必是错误信息主从复制中，从服务器IO复制线程的启动信息mysql> show variables like '%log_error%';

+---------------------+-------------------------------------------------+| Variable_name | Value |+---------------------+-------------------------------------------------+

| binlog_error_action | ABORT_SERVER |

| log_error | /data1/mysql_root/data/20143/TENCENT.site.err |+---------------------+-------------------------------------------------+

之前说过log_warnings变量的值也与错误⽇志有关，那么log_warnings代表什么意思呢？log_warnings的值为0：表⽰不记录警告信息

log_warnings的值为1：警告记录⼀并记录到错误⽇志中

log_warnings的值⼤于1：表⽰‘失败的连接’的信息和创建新连接时‘拒绝访问’类的错误会记录到错误⽇志中。mysql> show variables like '%log_warnings%';+---------------+-------+

| Variable_name | Value |+---------------+-------+| log_warnings | 1 |+---------------+-------+

如果想要⽤就修改该参数，需要在配置⽂件中加⼊：log_warnings=2重启mysql

查询⽇志：

查询⽇志在mysql中被称作‘通⽤⽇志’，不要错误的以为仅仅记录select的记录，查询⽇志记录了mysql执⾏的所有命令，不管执⾏的语句是否正确，都会被记录！

由于mysql操作指令可能⾮常多⽽且操作频繁，因为开启了查询⽇志后可能需要不停的写⼊查询⽇志，这样会增⼤服务器的IO压⼒，增加系统开销，因此默认情况下，查询⽇志是没有开启的！

如果开启查询⽇志，我们可以通过三种⽅式存储查询⽇志：1：存放在指定的⽂件中

2：存放于mysql.general_log表中3：同时存放于上⾯的两个位置看下查询⽇志的具体参数：

mysql> show variables where variable_name like '%general_log%' or variable_name=\"log_output\";+------------------+--------------------------------------------+| Variable_name | Value |+------------------+--------------------------------------------+| general_log | OFF |

| general_log_file | /data1/mysql_root/data/20143/TENCENT.log || log_output | FILE |

+------------------+--------------------------------------------+general_log ：表⽰查询⽇志是否开启

我们尝试修改下改参数看看效果：mysql> set global general_log=ON;Query OK, 0 rows affected (0.00 sec)

mysql> show variables where variable_name like '%general_log%' or variable_name=\"log_output\";+------------------+-----------------------------+| Variable_name | Value |+------------------+-----------------------------+| general_log | ON |

| general_log_file | /var/lib/mysql/VM-75-68.log || log_output | FILE |+------------------+-----------------------------+

mysql> set global log_output='FILE,TABLE';Query OK, 0 rows affected (0.00 sec)

mysql> show variables where variable_name like '%general_log%' or variable_name=\"log_output\";+------------------+-----------------------------+| Variable_name | Value |+------------------+-----------------------------+| general_log | ON |

| general_log_file | /var/lib/mysql/VM-75-68.log || log_output | FILE,TABLE |+------------------+-----------------------------+

OK，参数修改好了。mysql> use relay;

Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -A

Database changedmysql> show tables;+-----------------+| Tables_in_relay |+-----------------+| account00 |+-----------------+

1 row in set (0.00 sec)

mysql> select * from account00;

+-------------+--------------+-----------+--------------+

| AccountHash | AccountID | ZoneID | RoleID |+-------------+--------------+-----------+--------------+

| 21323211 | 787492757373 | 314342427 | 9997979799 || 21323212 | 787492757374 | 314342426 | 9997979798 || 21323213 | 787492757375 | 314342425 | 9997979797 |+-------------+--------------+-----------+--------------+3 rows in set (0.00 sec)

mysql> ni hao ya;

ERROR 10 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ni hao ya' at line 1我们执⾏了⼀些操作，下⾯看下⽇志的记录情况。mysql> select * from mysql.general_log;

+---------------------+---------------------------+-----------+-----------+--------------+---------------------------------------------------------------------------------------+| event_time | user_host | thread_id | server_id | command_type | argument |+---------------------+---------------------------+-----------+-----------+--------------+---------------------------------------------------------------------------------------+

| 2019-10-10 10:17:57 | root[root] @ localhost [] | 9 | 2 | Query | show variables where variable_name like '%general_log%' or variable_name=\"log_output\" || 2019-10-10 10:18:54 | root[root] @ localhost [] | 9 | 2 | Query | show databases |

| 2019-10-10 10:19:00 | root[root] @ localhost [] | 9 | 2 | Query | SELECT DATABASE() || 2019-10-10 10:19:00 | root[root] @ localhost [] | 9 | 2 | Init DB | relay |

| 2019-10-10 10:19:00 | root[root] @ localhost [] | 9 | 2 | Query | show databases || 2019-10-10 10:19:00 | root[root] @ localhost [] | 9 | 2 | Query | show tables || 2019-10-10 10:19:00 | root[root] @ localhost [] | 9 | 2 | Field List | account00 || 2019-10-10 10:19:02 | root[root] @ localhost [] | 9 | 2 | Query | show tables |

| 2019-10-10 10:19:10 | root[root] @ localhost [] | 9 | 2 | Query | select * from account00 |

| 2019-10-10 10:19:22 | root[root] @ localhost [] | 9 | 2 | Query | show variables where variable_name like '%general_log%' or variable_name=\"log_output\" || 2019-10-10 10:20:10 | root[root] @ localhost [] | 9 | 2 | Query | select * from mysql.general_log |+---------------------+---------------------------+-----------+-----------+--------------+---------------------------------------------------------------------------------------+

上⾯是表的记录情况，下⾯看看⽇志⽂件的记录情况：[root@VM-75-68 mysql]# cat VM-75-68.log

/usr/local/mysql/bin/mysqld, Version: 5.6.41-log (MySQL Community Server (GPL)). started with:Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sockTime Id Command Argument

191010 10:17:32 9 Query show variables where variable_name like '%general_log%' or variable_name=\"log_output\"191010 10:17:55 9 Query set global log_output='FILE,TABLE'

191010 10:17:57 9 Query show variables where variable_name like '%general_log%' or variable_name=\"log_output\"191010 10:18:54 9 Query show databases

191010 10:19:00 9 Query SELECT DATABASE() 9 Init DB relay

9 Query show databases 9 Query show tables 9 Field List account00

191010 10:19:02 9 Query show tables

191010 10:19:10 9 Query select * from account00

191010 10:19:22 9 Query show variables where variable_name like '%general_log%' or variable_name=\"log_output\"191010 10:20:10 9 Query select * from mysql.general_log191010 10:20:45 9 Query select * from mysql.general_log191010 10:21:17 9 Query delete from account00

191010 10:21:19 9 Query select * from mysql.general_log191010 10:22:17 9 Quit[root@VM-75-68 mysql]# pwd/var/lib/mysql

OK，也有了！

这⾥查询⽇志的功能搞清楚了，但是要记住，⼀般线上咱们是不开启这个参数的！

慢查询⽇志：

某些sql的执⾏完毕时间花费得⽐较长，我们将这种响应⽐较慢的语句记录在慢查询⽇志中，不要被名字误导，除了查询语句，其他所有的更新语句只要超过定义的慢查询时间，都算作慢查询！

默认情况下，慢查询⽇志也是不开启的，开启之后，还需要定义超时时间，⾄于定义为多少，就需要看具体业务了！mysql> show variables like '%slow%log%';

+---------------------+---------------------------------------------+| Variable_name | Value |+---------------------+---------------------------------------------+| slow_query_log | ON |

| slow_query_log_file | /data1/mysql_root/data/20125/slow_query.log |+---------------------+---------------------------------------------+2 rows in set (0.00 sec)

mysql> show variables like '%long_query%';+-----------------+-----------+| Variable_name | Value |+-----------------+-----------+

| long_query_time | 10.000000 |+-----------------+-----------+

我们修改相关参数：

mysql> set global long_query_time=3;Query OK, 0 rows affected (0.00 sec)mysql> set global slow_query_log=ON;Query OK, 0 rows affected (0.00 sec)

mysql> select sleep(5); #⼿动执⾏超时语句！+----------+| sleep(5) |+----------+| 0 |+----------+

1 row in set (4.99 sec)

看下记录情况：

mysql> select * from mysql.slow_log;

+---------------------+---------------------------+------------+-----------+-----------+---------------+----+----------------+-----------+-----------+-----------------+-----------+

| start_time | user_host | query_time | lock_time | rows_sent | rows_examined | db | last_insert_id | insert_id | server_id | sql_text | thread_id |+---------------------+---------------------------+------------+-----------+-----------+---------------+----+----------------+-----------+-----------+-----------------+-----------+| 2019-10-10 10:35:29 | root[root] @ localhost [] | 00:00:05 | 00:00:00 | 1 | 0 | | 0 | 0 | 2 | select sleep(5) | 12 || 2019-10-10 10:35:49 | root[root] @ localhost [] | 00:00:05 | 00:00:00 | 1 | 0 | | 0 | 0 | 2 | select sleep(5) | 12 || 2019-10-10 10:35:55 | root[root] @ localhost [] | 00:00:04 | 00:00:00 | 1 | 0 | | 0 | 0 | 2 | select sleep(4) | 12 |+---------------------+---------------------------+------------+-----------+-----------+---------------+----+----------------+-----------+-----------+-----------------+-----------+

我们还能够使⽤下⾯的语句查看从mysql进程启动到当前的时间⼀共记录了多少条慢查询⽇志：mysql> show global status like '%slow_queries%'; +---------------+-------+

| Variable_name | Value |+---------------+-------+| Slow_queries | 3 |+---------------+-------+1 row in set (0.00 sec)

注意：当mysql重启后，这⾥的数据会被清零，但是慢查询⽇志和slow_log表中的⽇志不会被清除。

下⾯看下慢查询⽇志的⽂件内容：

[root@VM-75-68 mysql]# cat VM-75-68-slow.log

/usr/local/mysql/bin/mysqld, Version: 5.6.41-log (MySQL Community Server (GPL)). started with:Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sockTime Id Command Argument# Time: 191010 10:35:29

# User@Host: root[root] @ localhost [] Id: 12

# Query_time: 5.000320 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0SET timestamp=1570674929;select sleep(5);

# Time: 191010 10:35:49

# User@Host: root[root] @ localhost [] Id: 12

# Query_time: 5.000206 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0SET timestamp=1570674949;select sleep(5);

# Time: 191010 10:35:55

# User@Host: root[root] @ localhost [] Id: 12

# Query_time: 4.000201 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0SET timestamp=1570674955;select sleep(4);

[root@VM-75-68 mysql]# pwd/var/lib/mysql

同时，mysql还⾃带了慢查询⽇志的统计分析⼯具：mysqldumpslow

[root@VM-75-68 mysql]# /usr/local/mysql/bin/mysqldumpslow -s l VM-75-68-slow.log

Reading mysql slow query log from VM-75-68-slow.log

Count: 3 Time=4.67s (14s) Lock=0.00s (0s) Rows=1.0 (3), root[root]@localhost select sleep(N)

其中，‘-s’表⽰排序，根据什么排序呢？看‘-s’后⾯的参数：c：执⾏计数，执⾏次数多的语句排在前⾯。l：锁定时间r：返回记录t：执⾏时间

al：平均锁定时间ar：平均返回记录数at：平均执⾏时间

我们还能使⽤-t选项指定读取前⼏条信息：

[root@VM-75-68 mysql]# /usr/local/mysql/bin/mysqldumpslow -s t -t 2 VM-75-68-slow.log

Reading mysql slow query log from VM-75-68-slow.log

Count: 3 Time=4.67s (14s) Lock=0.00s (0s) Rows=1.0 (3), root[root]@localhost select sleep(N)

Died at /usr/local/mysql/bin/mysqldumpslow line 161, <> chunk 3.

只读取前两条信息，但是这⾥只有⼀条，仅仅展⽰语句的⽤法！

⼆进制⽇志：

⼆进制⽇志有三种记录⽅式：

这⾥我看了下，腾讯的线上云DB采⽤的是MIXED⽅式：mysql> show variables like '%binlog_format%';+---------------+-------+

| Variable_name | Value |+---------------+-------+

| binlog_format | MIXED |+---------------+-------+

相关参数：

mysql> show variables like '%log_bin%';

+---------------------------------+--------------------------------------------+| Variable_name | Value |

+---------------------------------+--------------------------------------------+| log_bin | ON |

| log_bin_basename | /data/mysql_root/log/20125/mysql-bin || log_bin_index | /data/mysql_root/log/20125/mysql-bin.index |

| log_bin_trust_function_creators | ON || log_bin_use_v1_row_events | OFF || sql_log_bin | ON |

+---------------------------------+--------------------------------------------+mysql> show variables like '%binlog%';

+-----------------------------------------+----------------------+| Variable_name | Value |+-----------------------------------------+----------------------+

| max_binlog_cache_size | 18446744073709547520 || max_binlog_size | 1073741824 || sync_binlog | 0 |

+-----------------------------------------+----------------------+其中：

log_bin：⽤于控制是否开启⼆进制⽇志，默认情况下可能这个值为OFF，如果想要开启只需要把这个值设置为⼆进制⽇志的⽂件名即可，但需要注意的是，这⾥不能在交互式界⾯直接set，需要在my.cnf中定义：log_bin=mybinlog，重启mysql就能在mysql的数据⽬录下⽣成mybinlog为前缀的⼆进制⽇志⽂件，如果想要禁⽤，在配置⽂件⾥删除这条参数即可；

sql_log_bin：⽤于标识当前会话的操作是否会被记录在⼆进制⽇志中，若该值为ON，标识当前会话的操作会被记录在⼆进制⽇志中，所以即使开启了⼆进制⽇志功能，如果这个值为OFF，当前会话的操作也不会被记录在⽇志中，同样，主从结构中的从db也就不会收到重演指令；该变量是会话级别变量，只能使⽤set，⽽不能使⽤set global，同时也不能存在于my.cnf中，否则mysql没法启动！binlog_format：上⾯解释过

max_binlog_size：单个⼆进制⽂件的最⼤⼤⼩，单位是字节，这⾥显⽰的是1G，超过这个值，就会⾃动滚动。sync_binlog：之前总结的‘双⼀’模式中的其中⼀个值，由于⼆进制⽇志⼀开始存在于内存中；

该值为1：每次事务提交之后都会⽴即将内存中的⼆进制⽇志同步到磁盘中的⼆进制⽇志⽂件中，安全性最⾼，性能最差；

该值为0：表⽰当事务提交后，不会⽴即将内存中的同步到磁盘中的bbinlog，⽽是由⽂件系统决定什么时候刷写，取决于⽂件系统的缓存机制，此时⼀旦宕机，将丢失未从内存中同步到磁盘中的binlog，因此安全性最差，但是性能最⾼；

该值为N：在有N次事务提交之后，将binlog从内存中刷写到磁盘中，该值越⼤，可能丢失的⽇志就越多，同时性能也就越好！如果设置0或N,推荐为操作系统准备带有备⽤电源的缓存！

从上⾯参数看到这⾥mysql已经开启了⼆进制⽇志：[root@VM-75-68 mysql]# pwd/var/lib/mysql

[root@VM-75-68 mysql]# lltotal 628432

-rw-rw----. 1 mysql mysql 56 May 17 20:46 auto.cnf-rw-rw----. 1 mysql mysql 79691776 Oct 10 10:21 ibdata1-rw-rw----. 1 mysql mysql 268435456 Oct 10 10:21 ib_logfile0-rw-rw----. 1 mysql mysql 268435456 May 17 20:46 ib_logfile1-rw-rw----. 1 mysql mysql 132 Oct 10 13:29 master.infodrwx------. 2 mysql mysql 4096 May 17 20:46 mysql

-rw-rw----. 1 mysql mysql 2160 May 21 03:53 mysql-bin.000001-rw-rw----. 1 mysql mysql 430 Jul 24 01:42 mysql-bin.000002-rw-rw----. 1 mysql mysql 3909 Jul 25 00:19 mysql-bin.000003-rw-rw----. 1 mysql mysql 190 Sep 11 20:35 mysql-bin.index除了⼀些⼆进制⽂件，还有⼀个index⽂件，这⾥后⾯再看。

查看⼆进制⽂件列表：mysql> show binary logs;+------------------+-----------+| Log_name | File_size |+------------------+-----------+

| mysql-bin.000001 | 2160 || mysql-bin.000002 | 430 || mysql-bin.000003 | 3909 |+------------------+-----------+或者：

mysql> show master logs;+------------------+-----------+| Log_name | File_size |+------------------+-----------+

| mysql-bin.000001 | 2160 || mysql-bin.000002 | 430 || mysql-bin.000003 | 3909 |+------------------+-----------+

查看具体binlog内容：

mysql> show binlog events in 'mysql-bin.000010';

+------------------+-----+-------------+-----------+-------------+---------------------------------------+

| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |+------------------+-----+-------------+-----------+-------------+---------------------------------------+

| mysql-bin.000010 | 4 | Format_desc | 2 | 120 | Server ver: 5.6.41-log, Binlog ver: 4 || mysql-bin.000010 | 120 | Query | 2 | 201 | BEGIN |

| mysql-bin.000010 | 201 | Query | 2 | 298 | use `relay`; delete from account00 || mysql-bin.000010 | 298 | Xid | 2 | 329 | COMMIT /* xid=58 */ |+------------------+-----+-------------+-----------+-------------+---------------------------------------+Pos：表⽰时间开始的position

End_log_pos：表⽰时间结束的position

此外，我们还能够从指定的位置查看⼆进制⽇志：

这⾥的from 317，就是制定的位置！我们还能够控制显⽰⽇志的数量：

我们还能够使⽤下⾯的指令查看当前系统正在使⽤那个⼆进制，以及⼆进制⽇志记录到哪个位置了：

在⼆进制⽇志到达指定⼤⼩之后会刷新，同时，我们也能⼿动刷新⼆进制⽇志：mysql> flush logs;

Query OK, 0 rows affected (0.00 sec)mysql> show master logs; +------------------+-----------+| Log_name | File_size |+------------------+-----------+

| mysql-bin.000001 | 2160 || mysql-bin.000002 | 430 || mysql-bin.000003 | 3909 || mysql-bin.000004 | 143 || mysql-bin.000005 | 143 || mysql-bin.000006 | 2694 || mysql-bin.000007 | 9 || mysql-bin.000008 | 9134 || mysql-bin.000009 | 787 || mysql-bin.000010 | 376 || mysql-bin.000011 | 120 |+------------------+-----------+

可以看到多了⼀个‘mysql-bin.000011’，同时我们看下‘mysql-bin.index’这个⽂件，可以直接cat：[root@VM-75-68 mysql]# cat mysql-bin.index./mysql-bin.000001./mysql-bin.000002./mysql-bin.000003./mysql-bin.000004./mysql-bin.000005./mysql-bin.000006./mysql-bin.000007./mysql-bin.000008./mysql-bin.000009./mysql-bin.000010./mysql-bin.000011

这个⽂件仅仅是记录了有哪些⼆进制⽇志⽂件⽽已。

设置当前会话不会别记录到binlog：

mysql> set sql_log_bin=OFF; ##关闭当前会话的binlog记录功能Query OK, 0 rows affected (0.00 sec)执⾏⼀些操作：mysql> use RW

Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -Amysql> select * from T;

此时再次查看binlog的情况：mysql> show master logs;+------------------+-----------+| Log_name | File_size |+------------------+-----------+

| mysql-bin.000001 | 2160 || mysql-bin.000002 | 430 || mysql-bin.000003 | 3909 || mysql-bin.000004 | 143 || mysql-bin.000005 | 143 || mysql-bin.000006 | 2694 || mysql-bin.000007 | 9 || mysql-bin.000008 | 9134 || mysql-bin.000009 | 787 || mysql-bin.000010 | 376 || mysql-bin.000011 | 120 |+------------------+-----------+

我们看到mysql-bin.000011的⼤⼩没有变化，也就是说，上⾯这些步骤的sql都没有被记录在binlog中！

除了能够在mysql交互界⾯使⽤‘show binlog events’指令查看binlog外，还能⽤通过mysqlbinlog指令读取⼆进制⽇志⽂件：[root@VM-75-68 mysql]# /usr/local/mysql/bin/mysqlbinlog --no-defaults mysql-bin.000010/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;/*!40019 SET @@session.max_insert_delayed_threads=0*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER /*!*/;# at 4

#190911 20:35:31 server id 2 end_log_pos 120 CRC32 0x5cd581ef Start: binlog v 4, server v 5.6.41-log created 190911 20:35:31BINLOG '

E+p4XQ8CAAAAdAAAAHgAAAAAAAQANS42LjQxLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAe+B1Vw='/*!*/;# at 120

#191010 10:21:17 server id 2 end_log_pos 201 CRC32 0xecd3cb40 Query thread_id=9 exec_time=0 error_code=0SET TIMESTAMP=1570674077/*!*/;

SET @@session.pseudo_thread_id=9/*!*/;

SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;SET @@session.sql_mode=10758376/*!*/;

SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;/*!\\C utf8 *//*!*/;

SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;SET @@session.lc_time_names=0/*!*/;

SET @@session.collation_database=DEFAULT/*!*/;BEGIN/*!*/;# at 201

#191010 10:21:17 server id 2 end_log_pos 298 CRC32 0x57e2097b Query thread_id=9 exec_time=0 error_code=0use `relay`/*!*/;

SET TIMESTAMP=1570674077/*!*/;delete from account00/*!*/;# at 298

#191010 10:21:17 server id 2 end_log_pos 329 CRC32 0x22972e8a Xid = 58COMMIT/*!*/;# at 329

#191010 14:05:01 server id 2 end_log_pos 376 CRC32 0xb582eebc Rotate to mysql-bin.000011 pos: 4DELIMITER ;# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

我们把上⾯的部分拆开来看：第⼀部分：

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;/*!40019 SET @@session.max_insert_delayed_threads=0*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER /*!*/;# at 4

#190911 20:35:31 server id 2 end_log_pos 120 CRC32 0x5cd581ef Start: binlog v 4, server v 5.6.41-log created 190911 20:35:31BINLOG '

E+p4XQ8CAAAAdAAAAHgAAAAAAAQANS42LjQxLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAe+B1Vw='/*!*/;

以上部分，是⼆进制⽇志的版本信息和属性信息，我们不⽤在意他们。

以上就是MySQL的⼀些相关⽇志，共勉！