MySQL的日志

MySQL的日志分为事务日志、错误日志、通用日志、慢查询日志、以及二进制日志，此处主要讲解前4种日志的配置及使用方法。二进制日志将在下一节中单独讲解。

事务日志(transaction log)

事务日志默认的存放位置为MySQL的数据库目录下的ib_logfile0、ib_logfile1

生产环境中建议将事务日志存放在单独的分区中，防止产生IO的争用。

Innodb事务日志相关配置：

1
2
3
4
5
6
7
8
#innodb事务日志相关配置可以使用以下命令进行查看
show variables like '%innodb_log';
#查询出的结果
innodb_log_block_size
innodb_log_file_size   5242880   每个日志文件大小，生产环境建议将此数值改大到几百兆，5M数值太小
innodb_log_files_in_group  2      日志组成员个数  
innodb_log_group_home_dir  ./   事务文件路径  
innodb_flush_log_at_trx_commit  默认为1

innodb_flush_log_at_trx_commit

0: 提交时没有任何操作; 而是每秒执行一次日志缓冲区写入和刷新。这样可以提供更好的性能，但服务器崩溃可以清除最后一秒的事务

1: 默认情况下，日志缓冲区将写入日志文件，并在每次事务后执行刷新到磁盘。这是完全遵守ACID特性

2: 每次提交后都会写入日志缓冲区，但每秒都会进行一次刷新。性能比0略好一些，但操作系统或停电可能导致最后一秒的交易丢失

3: 模拟MariaDB 5.5组提交（每组提交3个同步），此项MariaDB 10.0支持

此参数修改方法/etc/my.cnf文件中进行修改。

事务日志和数据分开存放

在生产中建议将事务日志存放到一个独立的分区中，带来的好处是性能更优。如果事务日志和数据库存放在一个磁盘上，既要写数据库又要写事务日志，这时候两个写操作都往同一磁盘内写数据必然会涉及到IO争用问题。如果将其存放到不同的磁盘上，这时候数据库和事务日志各写各的，理论上效率更高。

1.创建事务日志目录

1
2
[root@localhost ~]# mkdir /data/tlog
[root@localhost ~]# chown -R mysql.mysql /data/tlog

2.修改配置文件指定事务日志的存放位置

1
2
3
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
innodb_log_group_home_dir=/data/tlog

3.重启MySQL服务

1
2
3
4
5
6
[root@localhost ~]# service mysqld restart
Restarting mysqld (via systemctl):                         [  OK  ]
[root@localhost ~]# ll /data/tlog/
total 98304
-rw-rw---- 1 mysql mysql 50331648 May  5 04:35 ib_logfile0
-rw-rw---- 1 mysql mysql 50331648 May  5 04:35 ib_logfile1

错误日志

mysqld启动和关闭过程中输出的事件信息

mysqld运行中产生的错误信息

event scheduler运行一个event时产生的日志信息

在主从复制架构中的从服务器上启动从服务器线程时产生的信息

错误日志相关配置：

1
2
log_error=/PATH/TO/LOG_ERROR_FILE    #错误日志的存放位置
log_warnings=1|0 默认值1             #是否记录警告信息至错误日志文件，1为记录警告信息，0为不记录警告信息只记录错误信息。

通用日志

通用日志：记录对数据库的通用操作，包括错误的SQL语句，数据库的通用日志默认没有启用。

通用日志相关设置，在配置文件中修改

1
2
3
4
#在配置文件/etc/my.cnf中添加以下三行信息即可开启通用日志
general_log=ON|OFF                  #通用日志默认为关闭
general_log_file=HOSTNAME.log       #通用日志的名字默认为主机名.log
log_output=TABLE|FILE|NONE          #通用日志可以存放在文件中，也可以存放在表中，表放在MySQL库中general_log

慢查询日志

慢查询日志：记录执行查询时长超出指定时长的操作，其配置在配置文件中进行修改

慢查询日志相关配置：

1
2
3
4
5
6
7
slow_query_log=ON|OFF       #开启或关闭慢查询
long_query_time=N           #慢查询的阀值，单位秒
slow_query_log_file=HOSTNAME-slow.log         #慢查询日志文件
log_slow_filter = admin,filesort,filesort_on_disk,full_join,full_scan, query_cache,query_cache_miss,tmp_table,tmp_table_on_disk    #上述查询类型且查询时长超过long_query_time，则记录日志
log_queries_not_using_indexes=ON               #不使用索引或使用全索引扫描，不论是否达到慢查询阀值的语句是否记录日志，默认OFF，即不记录
log_slow_rate_limit = 1                 #多少次查询才记录，mariadb特有
log_slow_verbosity= Query_plan,explain       #记录内容