mysql日志

发表于 | 分类于 | 浏览 次

mysql日志记录了mysql的日常操作和错误信息，通过日志，我们可以知道mysql内部发生的事情，可以为mysql的管理和优化提供必要的信息。
mysql日志主要有：二进制日志、通用查询日志、慢查询日志、错误日志、事务日志。

二进制日志

二进制日志用来记录操作mysql数据库中的写入性操作(包括增删改，不包括查询)，操作语句以事件的形式进行保存，描述数据更改。
二进制日志的主要作用有两个：
1.复制，配置了主从复制的时候，主服务器会将其产生的二进制日志发送给slave，slave会利用这个二进制日志的信息在本地重做，实现主从同步。
2.恢复，因为二进制日志包含了备份以后的所有更新，因此可以用于最大限度地恢复数据库。因此，建议二进制日志单独保存到一个磁盘上，以便磁盘损坏后进行数据恢复。

查看二进制日志是否打开

使用show variables like ‘log_bin’;可以查看二进制日志是否打开(二进制日志是否启用)。
下面的返回表明二进制日志未打开。

1
2
3
4
5
6
7
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

下面是在尚未打开二进制日志的情况下，查看当前日志和所生成的二进制文件的结果。

1
2
3
4
5
6
7
# 查看当前日志
mysql> show master status;
Empty set (0.00 sec)

# 查看日志（所生成的全部日志文件）
mysql> show binary logs;
ERROR 1381 (HY000): You are not using binary logging

配置二进制日志

ubuntu下mysql安装布局

/usr/bin 客户端程序和mysql_install_db
/var/lib/mysql 数据库和日志文件
/var/run/mysqld 服务器
/etc/mysql 配置文件my.cnf
/usr/share/mysql 字符集，基准程序和错误消息
/etc/init.d/mysql 启动mysql服务器

修改/etc/mysql/conf.d/mysql.cnf，添加以下内容，将二进制日志打开：

1
2
3
4
[mysqld]
server_id = 1
log-bin
expire-logs-days=7

server_id而不是server-id：https://stackoverflow.com/questions/33328652/setting-server-id-variable-in-mysql-doesnt-work

二进制日志内容

二进制日志打开后：
查看生成的日志

1
2
3
4
5
6
7
mysql> show binary logs;
+-----------------+-----------+
| Log_name        | File_size |
+-----------------+-----------+
| ivic-bin.000001 |       658 |
+-----------------+-----------+
1 row in set (0.00 sec)

通过mysqlbinlog语句查看二进制日志的内容，二进制日志文件名：/var/lib/mysql/ivic-bin.00001

1
2
3
4
5
6
7
8
9
10
11
12
 sudo mysqlbinlog -vv /var/lib/mysql/ivic-bin.000001
 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#191002 10:00:40 server id 1  end_log_pos 123 CRC32 0x7e402a57 	Start: binlog v 4, server v 5.7.27-0ubuntu0.18.04.1-log created 191002 10:00:40 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
此处省略多行
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

通过show binlog events命令查看binlog内记录的事件：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> show binlog events;
+-----------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------+
| Log_name        | Pos | Event_type     | Server_id | End_log_pos | Info                                                                                                          |
+-----------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------+
| ivic-bin.000001 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.27-0ubuntu0.18.04.1-log, Binlog ver: 4                                                        |
| ivic-bin.000001 | 123 | Previous_gtids |         1 |         154 |                                                                                                               |
| ivic-bin.000001 | 154 | Anonymous_Gtid |         1 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                          |
| ivic-bin.000001 | 219 | Query          |         1 |         390 | use `mydb`; create table test_tbl(id INT NOT NULL AUTO_INCREMENT, name varchar(10) not null, primary key(id)) |
| ivic-bin.000001 | 390 | Anonymous_Gtid |         1 |         455 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                          |
| ivic-bin.000001 | 455 | Query          |         1 |         527 | BEGIN                                                                                                         |
| ivic-bin.000001 | 527 | Table_map      |         1 |         581 | table_id: 112 (mydb.test_tbl)                                                                                 |
| ivic-bin.000001 | 581 | Write_rows     |         1 |         627 | table_id: 112 flags: STMT_END_F                                                                               |
| ivic-bin.000001 | 627 | Xid            |         1 |         658 | COMMIT /* xid=29 */                                                                                           |
+-----------------+-----+----------------+-----------+-------------+----------------------------------------------------------------------------------

各字段含义：

字段名 含义
Log_name 此条log存在的那个文件，这些log存在于文件ivic-bin.000001中
Pos log在bin-log中的开始位置
Event_type log的类型信息
Server_id 可以查看配置中的server_id，表示产生此log的mysql服务器，server_id可以在mysql.cnf中配置
End_log_pos log在bin-log中的结束位置
Info log的一些备注信息，可以直观地看出进行了什么操作

查询日志

查询日志能够存放到一个文本文件或表中，全部连接或者表中，全部连接和语句都被记录到该日志文件或表，默认不开启该日志。

错误日志

mysql的错误日志记录了mysqld启动和停止时，以及服务器在运行过程中出现的错误。

慢查询日志

记录所有执行时间超过long_query_time秒的所有查询或不使用索引的查询。慢查询日志记录的是查询比较慢的sql语句的日志，可用于对执行时间较长、执行效率较低的sql语句的性能优化。

临时开启慢查询日志:

1
set global slow_query_log = on;

如果想关闭慢查询日志，只需要执行:

1
set global slow_query_log = off;

临时设置慢查询时间临界点。查询时间高于这个临界点的都会被记录到慢查询日志中。所有执行时间超过1秒的sql都将被记录到慢查询日志：

1
set long_query_time = 1;

设置慢查询存储的方式

1
set globle log_output = file;

这里设置为了file,就是说慢查询日志是通过file保存,默认是none,我们还可以设置为table,如果是table则慢查询信息会保存到mysql库下的slow_log表中。

查询慢查询日志的开启状态和慢查询日志储存的位置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
mysql> mysql> show variables like '%query%';
+------------------------------+------------------------------+
| Variable_name                | Value                        |
+------------------------------+------------------------------+
| binlog_rows_query_log_events | OFF                          |
| ft_query_expansion_limit     | 20                           |
| have_query_cache             | YES                          |
| long_query_time              | 10.000000                    |
| query_alloc_block_size       | 8192                         |
| query_cache_limit            | 1048576                      |
| query_cache_min_res_unit     | 4096                         |
| query_cache_size             | 16777216                     |
| query_cache_type             | OFF                          |
| query_cache_wlock_invalidate | OFF                          |
| query_prealloc_size          | 8192                         |
| slow_query_log               | ON                           |
| slow_query_log_file          | /var/lib/mysql/ivic-slow.log |
+------------------------------+------------------------------+
13 rows in set (0.00 sec)

可以看到最后两行显示的就是慢查询日志的开启状态和存储信息。

事务日志

事务日志是InnoDB引擎特有的日志，可以帮助提高事务的效率。
使用事务日志，存储引擎在修改表的数据时只需要修改其内存拷贝，再把该修改行为记录到持久在磁盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘。事务日志采用追加的方式，因此写日志的操作是磁盘上一小块区域内的顺序I/O，而不像随机I/O需要在磁盘的多个地方移动磁头，所以采用事务日志的方式相对来说要快得多。事务日志持久化以后，内存中被修改的数据可以在后台慢慢地刷回到磁盘。目前大多数的存储引擎都是这样实现的，我们通常称之为预写式日志，修改数据需要写两次磁盘。
如果数据的修改已经记录到事务日志并持久化，但数据本身还没有写回磁盘，此时系统崩溃，存储引擎在重启时能够自动恢复这部分修改的数据。具体的恢复方式则视存储引擎而定。
查看日志参数：

1
2
3
4
5
6
7
8
mysql> show global variables like '%log%';
+--------------------------------------------+-------------------------------------+
| Variable_name                              | Value                               |
+--------------------------------------------+-------------------------------------+
| back_log                                   | 80                                  |
| binlog_cache_size                          | 32768                               |
此处省略很多行
.........

查看事务日志参数：

1
2
3
4
5
6
7
8
9
10
11
12
mysql> show global variables like 'innodb_log%';
+-----------------------------+----------+
| Variable_name               | Value    |
+-----------------------------+----------+
| innodb_log_buffer_size      | 16777216 |
| innodb_log_checksums        | ON       |
| innodb_log_compressed_pages | ON       |
| innodb_log_file_size        | 50331648 |
| innodb_log_files_in_group   | 2        |
| innodb_log_group_home_dir   | ./       |
| innodb_log_write_ahead_size | 8192     |
+-----------------------------+----------+

总结

mysql日志主要有：错误日志、通用查询日志、慢查询日志、事务日志、二进制日志
mysql日志的输出位置一般有三种方式：file(文件)、table(表)、none(不保存)
mysql日志主要是用于：异常监控、性能优化、数据恢复、主从同步

参考文献

[1] mysql日志类型：https://mp.weixin.qq.com/s/zX9rs0SxY68Pmm5hEj1zdw
[2]ubuntu下mysql的安装布局：https://www.cnblogs.com/jiangfeilong/p/10527562.html
[3]log-bin的配置：https://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html#option_mysqld_log-bin
[4]linux mysql的二进制日志：https://blog.csdn.net/sunny1660/article/details/77505985
[6]高性能mysql