MySQL的日志文件及配置

对MySQL中的6种日志文件，以及配置做简单的记录。

版本：MySQL 5.7

二进制日志（binlog）

记录所有引起数据变化的操作，用于备份和还原，使用主从复制时也需要开启binlog；
默认存放在datadir目录下，在刷新和重启数据库是会滚动二进制文件，产生新的binlog；

[mysql@localhost mysql]$  vi /etc/my.cnf

修改[mysqld]下的配置

#以下为 my.cnf 部分配置
[mysqld]
basedir=/home/mysql/mysql-5.7/
datadir=/home/mysql/mysql-5.7/data/
#序列号
server_id=1
#不指定log文件，默认mysqld-bin.00000* 作为文件名称；
log-bin=mysql-bin
#基于SQL语句的复制(statement-based replication, SBR)，基于行的复制(row-based replication, RBR)，混合模式复制(mixed-based replication, MBR)
binlog_format=Row
#binlog每个日志文件大小
max-binlog-size = 500M
#设置binlog清理时间
expire_logs_days = 7
#binlog缓存大小
binlog_cache_size = 4m
#最大binlog缓存大小
max_binlog_cache_size = 512m

登录MySQL查看相关参数

mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------------------------+
| Variable_name                   | Value                                      |
+---------------------------------+--------------------------------------------+
| log_bin                         | ON                                         |
| log_bin_basename                | /home/mysql/mysql-5.7/data/mysql-bin       |
| log_bin_index                   | /home/mysql/mysql-5.7/data/mysql-bin.index |
| log_bin_trust_function_creators | OFF                                        |
| log_bin_use_v1_row_events       | OFF                                        |
| sql_log_bin                     | ON                                         |
+---------------------------------+--------------------------------------------+
6 rows in set (0.00 sec)

mysql> show variables like '%binlog%';
+--------------------------------------------+----------------------+
| Variable_name                              | Value                |
+--------------------------------------------+----------------------+
| binlog_cache_size                          | 4194304              |
| binlog_checksum                            | CRC32                |
| binlog_direct_non_transactional_updates    | OFF                  |
| binlog_error_action                        | ABORT_SERVER         |
| binlog_format                              | ROW                  |
| binlog_group_commit_sync_delay             | 0                    |
| binlog_group_commit_sync_no_delay_count    | 0                    |
| binlog_gtid_simple_recovery                | ON                   |
| binlog_max_flush_queue_time                | 0                    |
| binlog_order_commits                       | ON                   |
| binlog_row_image                           | FULL                 |
| binlog_rows_query_log_events               | OFF                  |
| binlog_stmt_cache_size                     | 32768                |
| binlog_transaction_dependency_history_size | 25000                |
| binlog_transaction_dependency_tracking     | COMMIT_ORDER         |
| innodb_api_enable_binlog                   | OFF                  |
| innodb_locks_unsafe_for_binlog             | OFF                  |
| log_statements_unsafe_for_binlog           | ON                   |
| max_binlog_cache_size                      | 536870912            |
| max_binlog_size                            | 524288000            |
| max_binlog_stmt_cache_size                 | 18446744073709547520 |
| sync_binlog                                | 1                    |
+--------------------------------------------+----------------------+
22 rows in set (0.00 sec)

事务日志（redo log / undo log ）

innodb的事务日志包括redo log重做日志，提供前滚操作，undo log回滚日志，提供回滚操作；
保证事务一致性；其中innodb_flush_log_at_trx_commit的配置可控制commit是否刷新log buffer是否刷新到磁盘

当设置为1的时候，事务每次提交都会将log buffer中的日志写入os buffer并调用fsync()刷到log file on disk中。这种方式即使系统崩溃也不会丢失任何数据，但是因为每次提交都写入磁盘，IO的性能较差。

当设置为0的时候，事务提交时不会将log buffer中日志写入到os buffer，而是每秒写入os buffer并调用fsync()写入到log file on disk中。也就是说设置为0时是(大约)每秒刷新写入到磁盘中的，当系统崩溃，会丢失1秒钟的数据。

当设置为2的时候，每次提交都仅写入到os buffer，然后是每秒调用fsync()将os buffer中的日志写入到log file on disk。

mysql> show variables like '%innodb%log%';
+----------------------------------+------------+
| Variable_name                    | Value      |
+----------------------------------+------------+
| innodb_api_enable_binlog         | OFF        |
| innodb_flush_log_at_timeout      | 1          |
| innodb_flush_log_at_trx_commit   | 1          |
| innodb_locks_unsafe_for_binlog   | OFF        |
| innodb_log_buffer_size           | 33554432   |
| innodb_log_checksums             | ON         |
| innodb_log_compressed_pages      | ON         |
| innodb_log_file_size             | 134217728  |
| innodb_log_files_in_group        | 2          |
| innodb_log_group_home_dir        | ./         |
| innodb_log_write_ahead_size      | 8192       |
| innodb_max_undo_log_size         | 1073741824 |
| innodb_online_alter_log_max_size | 134217728  |
| innodb_undo_log_truncate         | OFF        |
| innodb_undo_logs                 | 128        |
+----------------------------------+------------+
15 rows in set (0.00 sec)

中继日志（relay-log）

从服务器I/O线程将主服务器的二进制日志读取过来记录到从服务器本地文件；
SQL线程读取relay-log日志的内容并应用到从服务器，从而使从服务器和主服务器的数据保持一致；

mysql> show variables like '%relay_log%';
+---------------------------+------------------------------------------------------+
| Variable_name             | Value                                                |
+---------------------------+------------------------------------------------------+
| max_relay_log_size        | 0                                                    |
| relay_log                 |                                                      |
| relay_log_basename        | /home/mysql/mysql-5.7/data/localhost-relay-bin       |
| relay_log_index           | /home/mysql/mysql-5.7/data/localhost-relay-bin.index |
| relay_log_info_file       | relay-log.info                                       |
| relay_log_info_repository | FILE                                                 |
| relay_log_purge           | ON                                                   |
| relay_log_recovery        | OFF                                                  |
| relay_log_space_limit     | 0                                                    |
| sync_relay_log            | 10000                                                |
| sync_relay_log_info       | 10000                                                |
+---------------------------+------------------------------------------------------+
11 rows in set (0.00 sec)

错误日志（mysql_error）

MySQL服务启动和关闭过程中的信息以及其它运行中的错误和警告信息；
log_error_verbosity：1 错误信息；2 错误信息、告警信息； 3：错误信息、告警信息、通知信息；
修改[mysqld]下的配置

#以下为 my.cnf 部分配置
[mysqld]
#错误日志目录配置
log-error=/home/mysql/mysql-5.7/logs/mysql_error.log

mysql> show variables like 'log_error%';
+---------------------+--------------------------------------------+
| Variable_name       | Value                                      |
+---------------------+--------------------------------------------+
| log_error           | /home/mysql/mysql-5.7/logs/mysql_error.log |
| log_error_verbosity | 3                                          |
+---------------------+--------------------------------------------+
2 rows in set (0.00 sec)

一般日志（general_log）

记录SQL操作的 DDL / DML 日志，记录信息非常简单，但包括完整的SQL语句；
开启general_log会产生非常庞大的日质量，一般不建议开启；

#以下为 my.cnf 部分配置
[mysqld]
#一般日志开启 默认关闭
general_log=on
#一般日志文件路径
general_log_file=/tmp/mariadb_general_log.log

mysql> show variables like '%general%';
+------------------+------------------------------+
| Variable_name    | Value                        |
+------------------+------------------------------+
| general_log      | ON                           |
| general_log_file | /tmp/mariadb_general_log.log |
+------------------+------------------------------+
2 rows in set (0.00 sec)

慢查询日志（slow_query_log）

记录所有执行超过long_query_time设置的SQL语句；
可以通过slow_query_log分析出系统中SQL语句的存在的问题，方便我们进行优化；

#以下为 my.cnf 部分配置
[mysqld]
#慢查询SQL时间设置(秒)
long_query_time=1
#开启慢查询SQL日志 默认 OFF
slow-query-log=ON
#慢查询日志文件路径
slow-query-log-file=/home/mysql/mysql-5.7/logs/mysql-slow.log

mysql> show variables like '%slow_query_log%';
+---------------------+-------------------------------------------+
| Variable_name       | Value                                     |
+---------------------+-------------------------------------------+
| slow_query_log      | ON                                        |
| slow_query_log_file | /home/mysql/mysql-5.7/logs/mysql-slow.log |
+---------------------+-------------------------------------------+
2 rows in set (0.01 sec)

结尾

登录服务器查询相关日志还是比较麻烦的，所以可以使用ELK+Filebeat对一般日志、错误日志、慢查询日志进行收集，从而进行分析。后续我会更新ELK7+Beat的分布式日志收集方案的搭建教程。