MySQL日志配置

作为开发人员的，都知道日志的重要性，通过查看日志可以优化项目的性能，在出现错误的时候，一般第一步就是要查看错误日志。
这里我们介绍Mysql相关日志的一些配置。

慢查询日志

1、运行时开起慢查询日志

set global slow_query_log = 1;
set global slow_query_log_file = '/var/log/mysql-slow.log';

2、在my.cnf中永久设置

slow-query-log=1
slow-query-log-file=/var/log/mysql-slow.log

MySQL二进制日志

配置文件中

log-bin = /path/to/log

额外配置：

expire-logs-days = 14
max-binlog-size  = 500M
server-id        = 1

错误日志

默认会记录到syslog里面，如果不想记录到syslog，那么官方建议的方式

[mysqld_safe]
...
log_error=/var/log/mysql/mysql_error.log
 
[mysqld]
...
log_error=/var/log/mysql/mysql_error.log

查看配置

MariaDB [(none)]> show variables like '%log%';
+-------------------------------------------+--------------------------------------------------------------------------------------------------------------+
| Variable_name               | Value                                                                                                        |
+-------------------------------------------+--------------------------------------------------------------------------------------------------------------+
| aria_checkpoint_log_activity        | 1048576                                                                                                      |
| aria_log_file_size             | 1073741824                                                                                                   |
| aria_log_purge_type             | immediate                                                                                                    |
| aria_sync_log_dir              | NEWFILE                                                                                                      |
| back_log                  | 80                                                                                                           |
| binlog_annotate_row_events         | OFF                                                                                                          |
| binlog_cache_size              | 32768                                                                                                        |
| binlog_checksum                | NONE                                                                                                         |
| binlog_commit_wait_count           | 0                                                                                                            |
| binlog_commit_wait_usec           | 100000                                                                                                       |
| binlog_direct_non_transactional_updates    | OFF                                                                                                          |
| binlog_format                | STATEMENT                                                                                                    |
| binlog_optimize_thread_scheduling      | ON                                                                                                           |
| binlog_row_image               | FULL                                                                                                         |
| binlog_stmt_cache_size            | 32768                                                                                                        |
| encrypt_binlog                | OFF                                                                                                          |
...

参考：

• How and When To Enable MySQL Logs
       

• MySQL官方文档