mysql logfile_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的分布式日志收集方案的搭建教程。