本文详细介绍了MySQL数据库中的四种日志类型：错误日志、常规日志、慢查询日志和二进制日志。错误日志记录启动、停止信息及运行错误，常规日志记录所有用户操作，慢查询日志记录执行时间超过阈值的SQL，二进制日志用于主从复制和数据恢复。文章还阐述了各日志的参数设置、查看与维护方法，以及如何开启和关闭这些日志功能。
MySQL数据库中根据日志文件的不同，可分为以下几种，下面将分别介绍。
一 错误日志
1 描述
记录MySQL的启动、停止信息以及在MySQL运行过程中的错误信息，类似于Oracle数据库的alert log，默认是开启。错误日志文件一般存放在数据目录下，以error.log作为文件名的结尾。
错误日志不光记录着是错误的信息，在MySQL 5.7初始化数据库中，加上--initialize参数，会生成一个临时的数据库初始密码，记录在log-error中。
2 参数
[mysqld]中的参数：
1)log_err(默认开启)
log_error=[path/[file_name]]，如果不指定文件名，则默认hostname.err，修改后重启生效；
mysql> show variables like 'log_error';
+---------------+-------------------------+
| Variable_name | Value |
+---------------+-------------------------+
| log_error | ./strong.oracle.com.err |
+---------------+-------------------------+
1 row in set (0.02 sec)
2)log_warnings
该参数在5.7.2之后被不建议使用，取而代之的是log_error_verbosity；
3)log_error_verbosity
该参数有以下几种值选项，分别为：
1：仅仅记录错误；
2：记录错误和警告；
3：默认值，记录错误、警告和普通日志；
3 试验
1)查看错误日志
mysql> show variables like 'log_error';
+---------------+-------------------------+
| Variable_name | Value |
+---------------+-------------------------+
| log_error | ./strong.oracle.com.err |
+---------------+-------------------------+
1 row in set (0.01 sec)
2)错误日志维护
可以直接删除错误日志文件，删除后，错误日志不会自动重建，需要flush logs或者重启数据库。
[root@strong mydb]# ll strong.oracle.com.err
-rw-r-----. 1 mysql mysql 79058 May 11 10:14 strong.oracle.com.err
[root@strong mydb]# rm strong.oracle.com.err
rm: remove regular file `strong.oracle.com.err'? y
[root@strong mydb]# ll strong.oracle.com.err
ls: cannot access strong.oracle.com.err: No such file or directory
[root@strong mydb]# mysqladmin -uroot -pmysql -S /u01/data/mydb/3306.sock flush-logs;
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@strong mydb]# ll strong.oracle.com.err
-rw-r-----. 1 mysql mysql 0 May 11 10:39 strong.oracle.com.err
注：也可以使用mysql> flush logs;重建错误日志。
二 常规日志
1 描述
由log moudle写入，记录MySQL的所有用户操作，包括启动和关闭服务、执行查询和更新语句等。包括错误的记录，mysqld的所有动作，默认关闭。
平时不开启常规日志，出现问题时候再开启，防止大量日志充满空间。
2 参数
[mysqld]中的参数：
1)general_log
默认关闭，{0-关闭，1-开启}，可以在线打开和关闭；
2)general_log_file
指定日志文件名称和路径；
3)log_output
该参数有如下选项，分别为：
TABLE：便于统计写入一个CSV引擎的表mysql.general_log中；
FIFLE：默认值，存储在文件；
TABLE,FILE：同时存放在表和文件中；
NONE：不存放日志；注：如果log_output=NONE，但是general_log为1，则默认不写常规日志；
4)sql_log_off
默认值为OFF，设置该值为ON，则不写常规日志；
3 试验
1)查看常规日志设置
mysql> show variables like 'general%';
+------------------+---------------------------+
| Variable_name | Value |
+------------------+---------------------------+
| general_log | OFF |
| general_log_file | /u01/data/mydb/strong.log |
+------------------+---------------------------+
2 rows in set (0.01 sec)
mysql> show variables like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output | FILE |
+---------------+-------+
1 row in set (0.02 sec)
2)打开常规日志
mysql> set global general_log='ON';
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'general%';
+------------------+---------------------------+
| Variable_name | Value |
+------------------+---------------------------+
| general_log | ON |
| general_log_file | /u01/data/mydb/strong.log |
+------------------+---------------------------+
2 rows in set (0.02 sec)
3)查看常规日志内容
[root@strong mydb]# more strong.log
/usr/local/mysql/bin/mysqld, Version: 5.7.25 (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /u01/data/mydb/3306.sock
Time Id Command Argument
2019-05-11T02:57:09.481561Z 2 Query show variables like 'general%'
2019-05-11T02:58:34.571239Z 2 Query show databases
2019-05-11T02:58:48.788272Z 2 Query SELECT DATABASE()
2019-05-11T02:58:48.788990Z 2 Init DB mysql
2019-05-11T02:58:52.107128Z 2 Query show tables
4)常规日志维护
[root@strong mydb]# rm strong.log
rm: remove regular file `strong.log'? y
[root@strong mydb]# mysqladmin -uroot -pmysql -S /u01/data/mydb/3306.sock flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@strong mydb]# ls strong.log
strong.log
注：如果设置为常规日志存储在TABLE，也可从表general_log查看常规日志。
三 慢查询日志
1 描述
MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阈值(long_query_time、min_examined_row_limit、log_slow_admin_statements和log_queries_not_using_indexes)的语句，默认关闭。
注意：慢查询日志不会计算语句在开始执行之前等待锁的时间(initial slow query)，mysqld只会从语句等待的锁释放掉，并开始执行开始计算时间(执行开始后的锁定时间会记录)。所以慢查询日志中的语句顺序和语句的发出顺序或者general log的顺序不同。
2 参数
[mysqld]中的参数：
1)slow_query_log
默认关闭，{0-关闭，1-开启}，可以在线打开和关闭；
2)slow_query_log_file
指定慢查询日志的名称和位置；
3)long_query_time
设置SQL执行的阈值；
4)log_output
该参数有如下选项，分别为：
TABLE：便于统计写入一个CSV引擎的表mysql.slow_log中；
FIFLE：默认值，存储在文件；
TABLE,FILE：同时存放在表和文件中；
NONE：不存放日志；注：如果log_output=NONE，但是slow_query_log为1，则默认不写慢查询日志；
5)log_queries_not_using_indexes
默认值为OFF，记录没有用索引的查询；
6)log_throttle_queries_not_using_indexes
记录没有使用索引查询的语句条数在一分钟内记录的条数；
注：如何消除上线后未使用索引查询的隐患，线上开启5秒即可。
7)min_examined_row_limit
要检查的行数大于等于N时才记录为慢查询，前提是必须满足long_query_time和log_queries_not_using_indexes约束；
8)log_slow_admin_statements
管理语句是否记录，比如:ALTER TABLE、ANALYZE TABLE、CHECK TABLE、CREATE INDEX、DROP INDEX、OPTIMIZE TABLE和REPAIR TABLE。
9)log_slow_slave_statements
从库的语句执行是否记录；
3 试验
1)开启慢查询
mysql> set global slow_query_log='ON';
Query OK, 0 rows affected (0.00 sec)
2)查看慢查询日志
注：慢查询日志可以使用percona-toolkit工具的pt-query-digest查看。
3)慢查询日志维护
[root@strong mydb]# rm strong-slow.log
rm: remove regular file `strong-slow.log'? y
[root@strong mydb]# ls strong-slow.log
ls: cannot access strong-slow.log: No such file or directory
[root@strong mydb]# mysqladmin -uroot -pmysql -S /u01/data/mydb/3306.sock flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@strong mydb]# ls strong-slow.log
strong-slow.log
四 二进制日志
1 描述
以“事件”的形式保存，描述数据的更改，包含关于每个更新数据库的语句的执行时间。
二进制日志的主要作用：
主从复制：在主服务器上把所有修改数据的操作记录到binlog中，通过网络发送给从服务器，从而达到主从同步；
恢复操作：数据可以通过binlog日志，使用mysqlbinlog命令，实现基于时间点和位置的恢复操作；
二进制日志记录在语句或事务完成之后立即执行(而不是在任何锁释放或任何提交完成之前写入)，这确保日志以提交顺序记录。
对于不支持事务的表，执行之后马上会被记录到二进制日志中去；
对于未提交的事务中，更改事务表(例如InnoDB表)的所有更新(UPDATE、DELETE或INSERT)将被缓存，直到服务器收到COMMIT语句，在这一点上，mysqld在执行COMMIT之前将整个事务写入二进制日志；
对非事务性表的修改无法回滚。如果回滚的事务包括对非事务性表的修改，则会在最后使用ROLLBACK语句记录整个事务，以确保对这些表的修改进行复制。注：binlog只记录完整事务。
启用二进制日志选项数据库性能会降低1%，但保障数据库完整性，对于重要数据库值得以性能换完整。
2 参数
[mysqld]中的参数：
1)log_bin
默认关闭，{0-关闭，1-开启}，不可以在线打开和关闭；
2)log_bin_basename
不指定路径则为hostname-bin.00000N；
3)log_bin_index
不指定路径则为hostname-bin.index；
注：文件路径需要在log_bin中设置，而不是log_bin_basename，所有的日志都建议自己起名字，不要用默认。
4)binlog_cache_size
内存中缓存二进制的缓冲区大小，所有未提交的事务会记录到一个缓存中，等待事务提交时，直接将缓存中的binlog写入二进制日志文件，该缓存大小由该参数决定，默认大小为32K，并且binlog_cache_size是基于会话的，也就是当一个线程要开始一个事务时，MySQL会自动分配一个该值大小的缓存，如果一个语句大于这个值，线程将打开一个临时文件来存储事务，当线程结束时，临时文件被删除。
注：对于大的事务，二进制日志会超过max_binlog_size设定的值，但事务仅仅写入一个二进制日志。设置此值需小心，如果设置太小就使用磁盘上的临时文件来记录，可通过show global status命令查看binlog_cache_use和binlog_cache_disk_use的使用情况，来判断当前的binlog_cache_size的设置是否合适。
binlog_cache_use状态变量显示用于存储语句的此缓冲区(可能还有一个临时文件)的事务数；
binlog_cache_disk_use状态变量显示有多少这些事务实际上必须使用临时文件；
这两个变量可用于将binlog_cache_size调整到足够大的值，以避免使用临时文件。
5)max_binlog_size
二进制日志的大小，该参数指定了单个binlog的最大值，如果超过该值就会自动生成新的binlog文件(重启MySQL实例也会生成新的binlog)；
6)max_binlog_cache_size
限制用于缓存多语句事务的总大小，如果一个事务大于这个值，它将失败并回滚；
7)sync_binlog
{0，1}二进制内容从缓冲区落入磁盘的时机。
5.7.7以后，sync_binlog参数被默认设置为1，减少了binlog中的不一致可能性。
8)expire_logs_days
日期过期时间，一定要和备份相结合；
9)binlog_format
binlog模式，有以下几种：
基于语句：当时语句的原样写入；
基于行：只记录受影响的行；
混合模式：默认用语句模式，遇到下面请看用基于行模式：
uuid()；
UDF；
temporary；
rows_found/row_count；
user()/current_user()；
LOAD_FILE()；
MySQL中的复制功能最初是基于从主库到从库的SQL语句的传输，称为基于语句的日志记录，可以使用--binlog-format=STATEMENT启动服务器使用此格式；
在基于行的日志记录中，主库将事件写入二进制日志，记录表中各个行如何被改变，因此，重要的是，表始终使用主键来确保可以有效的识别行，可以使用--binlog-format=ROW启动基于行的日志记录；
注：在MySQL 5.7.7之前，基于语句的日志记录格式是默认的，在MySQL 5.7.7及更高的版本中，基于行的记录格式是默认的。
使用混合日志记录(MIXED)，默认使用基于语句的日志记录，但是如下所述，在某些情况下，记录模式将自动切换到基于行的记录，可以使用--binlog-format=MIXED启动基于混合模式的日志记录。
使用基于语句的二进制日志记录，主机将执行的查询写入二进制日志，这是一种非常快速、紧凑、高效的记录方法，在大多数情况下没有问题，然而，如果查询的设计方式使得数据修改是非确定性的，主从库的数据可能会变得不同。
3 试验
1)查看二进制日志配置
mysql> show variables like '%log_bin%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin | OFF |
| log_bin_basename | |
| log_bin_index | |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+-------+
6 rows in set (0.01 sec)
2)开启二进制日志
在my.cnf配置文件增加下面两行记录，保存然后重启MySQL实例：
log_bin=/u01/data/mydb/binlog/mysql-bin
log_bin_index=/u01/data/mydb/binlog/mysql-bin.index
3)查看二进制日志的格式
mysql> show variables like '%binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 row in set (0.02 sec)
注：修改二进制日志格式需要super权限；
4)查看二进制日志内容
可使用mysqlbinlog工具：
审计SQL语句和数据库的更改；
定点恢复；
5)查看二进制日志
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 154 |
+------------------+-----------+
1 row in set (0.00 sec)
6)切换二进制日志
mysql> flush logs;
Query OK, 0 rows affected (0.14 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 201 |
| mysql-bin.000002 | 154 |
+------------------+-----------+
2 rows in set (0.00 sec)
7)清理所有二进制日志，并从新开始记录二进制日志
mysql> reset master;
Query OK, 0 rows affected (0.13 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 154 |
+------------------+-----------+
1 row in set (0.00 sec)
8)清理指定编号之前的二进制日志
mysql> purge binary logs to 'mysql-bin.000004';
Query OK, 0 rows affected (0.04 sec)
9)清理指定时间之前的二进制日志
mysql> purge binary logs before '2019-05-11 17:38:56';
Query OK, 0 rows affected (0.16 sec)
注：不建议手动rm删除二进制日志，会造成index文件不一致。
五 中继日志
主从复制中，从服务器上一个很重要的文件，从服务器IO线程将主服务器的二进制日志读取过来并记录到从服务器本地文件(relay log)中，然后从服务器上的SQL线程会读取中继日志的内容并应用到从服务器。
73
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
