MySQL日志管理
MySQL日志管理
一、日志分类
| 日志种类 | 作用 |
|---|---|
| 错误日志 | 记录 MySQL 服务器启动、关闭及运行错误等信息 |
| 事务日志 | 1、redo log重做日志 2、undo log回滚日志 |
| 查询日志 | 记录所有的sql |
| 慢查询日志 | 记录执行时间超过指定时间的操作，如果是全表查询，即便没有超时也会被记录下来 |
| 二进制日志 | 又称binlog日志，以二进制文件的方式记录数据库中除 SELECT 以外的操作。即只记录写操作不记录读操作 |
| 中继日志 | 备库将主库的二进制日志复制到自己的中继日志中，从而在本地进行重放 |
| 通用日志 | 审计哪个账号、在哪个时段、做了哪些事件 |
二、错误日志
MySQL错误日志是记录MySQL 运行过程中较为严重的警告和错误信息，以及MySQL每次启动和关闭的详细信息。错误日志使用log_error以及log_warnings等参数进行定义。
查看错误日志
-- 方式一
mysql> show variables like '%log_error%';
+---------------------+---------------------+
| Variable_name | Value |
+---------------------+---------------------+
| binlog_error_action | ABORT_SERVER |
| log_error | /var/log/mysqld.log |
| log_error_verbosity | 3 |
+---------------------+---------------------+
3 rows in set (0.13 sec)
-- 方式二
[root@localhost ~]# mysqladmin -uroot -p123456 variables | grep -w log_error
| log_error | /var/log/mysqld.log
查看警告日志
log_warnings：
0：表示不记录警告信息
1：表示记录警告信息到错误日志
大于1表示"失败的连接"的信息和创建新连接时"拒绝访问"类的错误信息也会被记录到错误日志中。
mysql> show variables like '%log_warnings%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_warnings | 2 |
+---------------+-------+
1 row in set (0.00 sec)
设置错误日志
设置错误日志的方式也有两种，分别是临时设置和永久设置。
临时设置
[root@localhost mysql-5.7.34]# /usr/local/mysql-5.7.34/support-files/mysql.server start --log_error=/tmp/DB-Server.localdomain.err
[root@localhost mysql-5.7.34]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.34 Source distribution
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show variables like '%log_error%';
+---------------------+--------------------------------+
| Variable_name | Value |
+---------------------+--------------------------------+
| binlog_error_action | ABORT_SERVER |
| log_error | /tmp/DB-Server.localdomain.err |
| log_error_verbosity | 3 |
+---------------------+--------------------------------+
3 rows in set (0.01 sec)
mysql>
永久设置
[root@localhost ~]# vim /etc/my.cnf
log-error=/var/log/mysql-error.log
[root@localhost ~]# touch /var/log/mysql-error.log
[root@localhost ~]# chown mysql.mysql /var/log/mysql-error.log
[root@localhost ~]# systemctl start mysqld
[root@localhost ~]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.34 Source distribution
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show variables like '%log_error%';
+---------------------+--------------------------+
| Variable_name | Value |
+---------------------+--------------------------+
| binlog_error_action | ABORT_SERVER |
| log_error | /var/log/mysql-error.log |
| log_error_verbosity | 3 |
+---------------------+--------------------------+
3 rows in set (0.00 sec)
三、事务的日志
innodb事务日志包括redo log和undo log。redo log是重做日志，提供前滚操作，undo log是回滚日志，提供回滚操作。
undo log不是redo log的逆向过程，其实它们都算是用来恢复的日志：
- redo log通常是物理日志，记录的是数据页的物理修改，而不是某一行或某几行修改成怎样怎样，它用来恢复提交后的物理数据页(恢复数据页，且只能恢复到最后一次提交的位置)。
- undo用来回滚行记录到某个版本。undo log一般是逻辑日志，根据每行记录进行记录。
redo log
redo log包括两部分：
- 内存中的日志缓冲(redo log buffer)，该部分日志是易失性的
- 磁盘上的重做日志文件(redo log file)，该部分日志是持久的
在概念上，innodb通过force log at commit机制实现事务的持久性，即在事务提交的时候，必须先将该事务的所有事务日志写入到磁盘上的redo log file和undo log file中进行持久化。
为了确保每次日志都能写入到事务日志文件中，在每次将log buffer中的日志写入日志文件的过程中都会调用一次操作系统的fsync操作(即fsync()系统调用)。因为MariaDB/MySQL是工作在用户空间的，MariaDB/MySQL的log buffer处于用户空间的内存中。要写入到磁盘上的log file中，中间还要经过操作系统内核空间的os buffer，调用fsync()的作用就是将OS buffer中的日志刷到磁盘上的log file中。
undo log
undo log有两个作用：提供回滚和多个行版本控制(MVCC)。
在数据修改的时候，不仅记录了redo，还记录了相对应的undo，如果因为某些原因导致事务失败或回滚了，可以借助该undo进行回滚。
undo log和redo log记录物理日志不一样，它是逻辑日志。可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录。
当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。有时候应用到行版本控制的时候，也是通过undo log来实现的：当读取的某一行被其他事务锁定时，它可以从undo log中分析出该行记录以前的数据是什么，从而提供该行版本信息，让用户实现非锁定一致性读取。
undo log是采用段(segment)的方式来记录的，每个undo操作在记录的时候占用一个undo log segment。另外，undo log也会产生redo log，因为undo log也要实现持久性保护。
undo log相关的变量
mysql> show variables like "%undo%";
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| innodb_max_undo_log_size | 1073741824 |
| innodb_undo_directory | ./ |
| innodb_undo_log_truncate | OFF |
| innodb_undo_logs | 128 |
| innodb_undo_tablespaces | 0 |
+--------------------------+------------+
5 rows in set (0.00 sec)
四、一般查询日志
一般不会开启，因为哪怕你开启事务一顿操作，最后不提交也会记录，生产上程序跑sql很多，会非常非常占地方，从来都不启动，要看操作去binlog。
[root@localhost ~]# vim /etc/my.cnf
general_log=on
general_log_file=/var/log/select.log
[root@localhost ~]# touch /var/log/select.log
[root@localhost ~]# chown mysql.mysql /var/log/select.log
[root@localhost ~]# chmod 640 /var/log/select.log
[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]# mysql -uroot -p123456
mysql> show variables like '%gen%';
+----------------------------------------+---------------------+
| Variable_name | Value |
+----------------------------------------+---------------------+
| auto_generate_certs | ON |
| general_log | ON |
| general_log_file | /var/log/select.log |
| sha256_password_auto_generate_rsa_keys | ON |
+----------------------------------------+---------------------+
4 rows in set (0.01 sec)
五、慢查询日志
MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10S以上的语句。默认情况下，Mysql数据库并不启动慢查询日志，需要我们手动来设置这个参数，当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。
慢查询日志相关参数
MySQL 慢查询的相关参数解释：
-
slow_query_log：是否开启慢查询日志，1表示开启，0表示关闭。
-
log-slow-queries ：旧版（5.6以下版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log
-
slow-query-log-file：新版（5.6及以上版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log
-
long_query_time ：慢查询阈值，当查询时间多于设定的阈值时，记录日志
-
log_queries_not_using_indexes：未使用索引的查询也被记录到慢查询日志中（可选项）。
-
log_output：日志存储方式。log_output='FILE'表示将日志存入文件，默认值是'FILE'。log_output='TABLE'表示将日志存入数据库，这样日志信息就会被写入到mysql.slow_log表中。MySQL数据库支持同时两种日志存储方式，配置的时候以逗号隔开即可，如：log_output='FILE,TABLE'。日志记录到系统的专用日志表中，要比记录到文件耗费更多的系统资源，因此对于需要启用慢查询日志，又需要能够获得更高的系统性能，那么建议优先记录到文件。
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
#指定是否开启慢查询日志
slow_query_log = on
#指定慢日志文件存放位置（默认在data）
slow_query_log_file=/var/log/slow.log
#设定慢查询的阀值(默认10s)
long_query_time=0.05
#不使用索引的慢查询日志是否记录到日志
log_queries_not_using_indexes=ON
#查询检查返回少于该参数指定行的SQL不被记录到慢查询日志，少于100行的sql语句查询慢的话不记录，一般不使用
log_output='FILE'
测试慢日志
[root@localhost ~]# cat /etc/my.cnf
[mysqld]
#指定是否开启慢查询日志
slow_query_log = on
#指定慢日志文件存放位置（默认在data）
slow_query_log_file=/var/log/slow.log
#设定慢查询的阀值(默认10s)
long_query_time=0.05
#不使用索引的慢查询日志是否记录到日志
log_queries_not_using_indexes=ON
#查询检查返回少于该参数指定行的SQL不被记录到慢查询日志，少于100行的sql语句查询慢的话不记录，一般不使用
log_output='FILE'
[root@localhost ~]# touch /var/log/slow.log
[root@localhost ~]# chown mysql.mysql /var/log/slow.log
[root@localhost ~]# systemctl restart mysqld
mysql> show variables like '%slow_query%';
+---------------------+-------------------+
| Variable_name | Value |
+---------------------+-------------------+
| slow_query_log | OFF |
| slow_query_log_file | /var/log/slow.log |
+---------------------+-------------------+
2 rows in set (0.01 sec)
[root@localhost ~]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.34-log Source distribution
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select sleep(3);
+----------+
| sleep(3) |
+----------+
| 0 |
+----------+
1 row in set (3.01 sec)
六、二进制日志
MySQL的二进制日志（binary log）是一个二进制文件，主要用于记录修改数据或有可能引起数据变更的MySQL语句。二进制日志（binary log）中记录了对MySQL数据库执行更改的所有操作，并且记录了语句发生时间、执行时长、操作数据等其它额外信息，但是它不记录SELECT、SHOW等那些不修改数据的SQL语句。二进制日志（binary log）主要用于数据库恢复和主从复制，以及审计（audit）操作。
开启和设置二进制日志
默认情况下二进制日志是关闭的，通过配置文件来启动和设置二进制日志。修改my.cng，插入如下内容，然后重启mysqld服务。
[mysqld]
server-id = 1 # mysql5.7必须加，否则mysql服务启动报错
binlog_format='row' # binlog工作模式
log-bin = /var/lib/mysql/mybinlog # 路径及命名，默认在data下
expire_logs_days = 10 # 过期时间,二进制文件自动删除的天数,0代表不删除
max_binlog_size = 100M # 单个日志文件大小
binlog_rows_query_log_events=on # 打开才能查看详细记录，默认为off
-- 开启binglog
[root@localhost ~]# vim /etc/my.cnf
[root@localhost ~]# touch /var/lib/mysql/mybinlog
[root@localhost ~]# chown mysql.mysql /var/lib/mysql/mybinlog
[root@localhost ~]# systemctl restart mysqld
二进制日志状态查看
系统变量log_bin的值为OFF表示没有开启二进制日志（binary log）。ON表示开启了二进制日志（binary log）。
-- 通过show variables like 'log_bin%'查看二进制日志设置
mysql> show variables like 'log_bin%';
+---------------------------------+-------------------------------+
| Variable_name | Value |
+---------------------------------+-------------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/mybinlog |
| log_bin_index | /var/lib/mysql/mybinlog.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
+---------------------------------+-------------------------------+
5 rows in set (0.01 sec)
-- 查看当前服务器所有的二进制日志文件 show binary logs / show master logs
mysql> show binary logs;
+-----------------+-----------+
| Log_name | File_size |
+-----------------+-----------+
| mybinlog.000001 | 154 |
+-----------------+-----------+
1 row in set (0.00 sec)
-- 查看当前二进制日志状态 show master status
mysql> show master status;
+-----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| mybinlog.000001 | 154 | | | |
+-----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
二进制日志切换方法
每次重启MySQL服务也会生成一个新的二进制日志文件，相当于二进制日志切换。切换二进制日志时，你会看到这些number会不断递增。另外，除了这些二进制日志文件外，你会看到还生成了一个DB-Server-bin.index的文件，这个文件中存储所有二进制日志文件的清单又称为二进制文件的索引。
执行 flush logs 可以刷新切换二进制文件。
mysql> show master status;
+-----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| mybinlog.000001 | 154 | | | |
+-----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> flush logs;
Query OK, 0 rows affected (0.35 sec)
mysql> show master status;
+-----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| mybinlog.000002 | 154 | | | |
+-----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
二进制文件的查看
使用show binlog events 可以获取二进制日志。
mysql> show binlog events\G
*************************** 1. row ***************************
Log_name: mybinlog.000001
Pos: 4
Event_type: Format_desc
Server_id: 1
End_log_pos: 123
Info: Server ver: 5.7.34-log, Binlog ver: 4
*************************** 2. row ***************************
Log_name: mybinlog.000001
Pos: 123
Event_type: Previous_gtids
Server_id: 1
End_log_pos: 154
Info:
*************************** 3. row ***************************
Log_name: mybinlog.000001
Pos: 154
Event_type: Rotate
Server_id: 1
End_log_pos: 200
Info: mybinlog.000002;pos=4
3 rows in set (0.00 sec)
打印二进制日志到一个明文文件，该文件记录的更新了数据的sql，但是在5.7以上已经被加密。
# 打印日志文件
[root@localhost ~]# mysqlbinlog /var/lib/mysql/mybinlog.000001 > mysql-bin.log
# 解密文件
[root@localhost ~]# mysqlbinlog --base64-output=decode-rows -v /var/lib/mysql/mybinlog.000001 > mysql-bin.log
使用二进制日志恢复数据库
如果开启了二进制日志，出现了数据丢失，可以通过二进制日志恢复数据库，语法如下：mysqlbinlog [option] filename | mysql -u user -p passwd
option的参数主要有两个 --start-datetime --stop-datetime 和 start-position --stop-position ,前者指定恢复的时间点，后者指定恢复的位置（位置指的是二进制文件中 # at 580 580就是位置）。原理就是把记录的语句重新执行了一次，如果恢复了两次。会产生重复数据。
[root@localhost mysql]# mysqlbinlog --start-position="154" /var/lib/mysql/mybinlog.000004 | mysql -uroot -p123456
注意，要找到插入更新的语句所在的时间点或位置。如果恢复的语句包含只有delete，会报错1032错误。
暂时停止二进制日志功能
可以通过修改配置文件停止二进制日志功能，但是需要重启数据库，mysql提供了语句可以在线停止二进制功能。
set sql_log_bin = 0 # 停止二进制日志功能
set sql_log_bin = 1 # 开启二进制日志功能
二进制日志的三种模式
二进制日志三种格式：STATEMENT，ROW，MIXED，由参数binlog_format控制。
-
STATEMENT模式（SBR）
每一条会修改数据的sql语句会记录到binlog中。优点是并不需要记录每一条sql语句和每一行的数据变化，减少了binlog日志量，节约IO，提高性能。缺点是在某些情况（如非确定函数）下会导致master-slave中的数据不一致(如sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题)。 -
ROW模式（RBR）
不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了。而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。缺点是会产生大量的日志，尤其是alter table的时候会让日志暴涨。 -
MIXED模式（MBR）
以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。
七、中继日志
从服务器I/O线程将主服务器的二进制日志读取过来记录到从服务器本地文件，然后从服务器SQL线程会读取relay-log日志的内容并应用到从服务器，从而使从服务器和主服务器的数据保持一致。
mysql> show variables like '%relay%';
+---------------------------+--------------------------------------------------------+
| Variable_name | Value |
+---------------------------+--------------------------------------------------------+
| max_relay_log_size | 0 |
| relay_log | |
| relay_log_basename | /usr/local/mysql-5.7.34/data/localhost-relay-bin |
| relay_log_index | /usr/local/mysql-5.7.34/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)
变量详解
relay_log fileName： 指定中继日志的文件名。【文件名为空，表示禁用了中继日志】
relay_log_index： 索引表
relay_log_info_file： 记录中继日志文件的相关信息
relay_log_purge： 指定是否自动删除无用的中继日志文件
relay_log_recovery： 是否可以对中继日志做自动恢复相关的配置
relay_log_space_limit： 指定中继日志可以占用的空间大小（0表示不限制）
八、通用日志
记录连接数据库信息和所有操作信息。
mysql> show variables where variable_name like "%general_log%" or variable_name="log_output";
+------------------+---------------------+
| Variable_name | Value |
+------------------+---------------------+
| general_log | ON |
| general_log_file | /var/log/select.log |
| log_output | FILE |
+------------------+---------------------+
3 rows in set (0.00 sec)
-
general_log：OFF表示关闭通用日志，ON表示开启通用日志
-
general_log_file：表示通用日志文件路径
-
log_output：FILE表示记录文件，TABLE表示记录表，FILE,TABLE表示同时记录文件和表
测试通用日志
mysql> SET GLOBAL general_log = 'ON';
Query OK, 0 rows affected (0.00 sec)
mysql> SET GLOBAL log_output = 'FILE,TABLE';
Query OK, 0 rows affected (0.00 sec)
mysql> select * from mysql.general_log\G
*************************** 1. row ***************************
event_time: 2021-10-13 21:22:23.599822
user_host: root[root] @ localhost []
thread_id: 2
server_id: 1
command_type: Query
argument: select * from mysql.general_log
*************************** 2. row ***************************
event_time: 2021-10-13 21:22:55.783993
user_host: [root] @ localhost []
thread_id: 4
server_id: 1
command_type: Connect
argument: root@localhost on using Socket
*************************** 3. row ***************************
event_time: 2021-10-13 21:22:55.784466
user_host: root[root] @ localhost []
thread_id: 4
server_id: 1
command_type: Query
argument: select @@version_comment limit 1
*************************** 4. row ***************************
event_time: 2021-10-13 21:22:58.409048
user_host: root[root] @ localhost []
thread_id: 2
server_id: 1
command_type: Query
argument: select * from mysql.general_log
*************************** 5. row ***************************
event_time: 2021-10-13 21:23:27.197193
user_host: root[root] @ localhost []
thread_id: 2
server_id: 1
command_type: Query
argument: select * from mysql.general_log
5 rows in set (0.00 sec)
