MySQL日志管理

目录

• 内容概述
• 内容详细
  • 1.日志分类
  • 2.错误日志
    • 查看错误日志
    • 查看警告日志
    • 设置错误日志
      • 临时设置
      • 永久设置
  • 3.事务的日志
    • redo log
    • undo log
  • 4.一般查询日志
  • 5.慢查询日志
    • 慢日志
    • 配置慢日志
  • 6.二进制日志
    • 开启和设置二进制日志
    • 二进制日志状态查看
    • 二进制日志切换方法
    • 二进制文件的查看
    • 使用二进制日志恢复数据库
    • 暂停二进制日志功能
    • 二进制日志的三种模式
  • 7.中继日志
    • 变量解释
  • 8.通用日志

内容概述

1.日志分类
2.错误日志
3.事务的日志
4.一般查询日志
5.慢查询日志
6.二进制日志
7.中继日志
8.通用日志

内容详细

1.日志分类

日志种类	作用
错误日志	记录 MySQL 服务器启动、关闭及运行错误等信息
事务日志	1、redo log重做日志 2、undo log回滚日志
查询日志	记录所有的sql
慢查询日志	记录执行时间超过指定时间的操作，如果是全表查询，即便没有超时也会被记录下来
二进制日志	又称binlog日志，以二进制文件的方式记录数据库中除 SELECT 以外的操作。即只记录写操作不记录读操作
中继日志	备库将主库的二进制日志复制到自己的中继日志中，从而在本地进行重放
通用日志	审计哪个账号、在哪个时段、做了哪些事件

2.错误日志

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.00 sec)

-- 方式二
在普通命令行中输入：
mysqladmin -uroot -p123456 variables | grep -w log_error
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
| 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)

设置错误日志

设置错误日志的方式有临时设置和永久设置两种

临时设置

/usr/local/mysql/support-files/mysql.server start --log_error=/tmp/DB-server.local.err
Starting MySQL.Logging to '/tmp/DB-server.local.err'.
 SUCCESS! 

mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.35-log MySQL Community Server (GPL)

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.local.err |
| log_error_verbosity | 3                        |
+---------------------+--------------------------+
3 rows in set (0.00 sec)

永久设置

vim /etc/my.cnf 
log-error=/var/log/mysqld.log

touch /var/log/mysql-error.log
chown mysql.mysql /var/log/mysql-error.log

systemctl stop mysqld
systemctl start mysqld

mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.35 MySQL Community Server (GPL)

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)

3.事务的日志

Innodb事务日志包括redo log和undo log。redo log是重做日志，提供前滚操作，undo log是回滚日志，提供回滚操作。

undo log不是redo log的逆向过程，其实它们都算是用来恢复的日志：

1. redo log通常是物理日志，记录的是数据页的物理修改，而不是某一行或某几行修改成怎样怎样，它用来恢复提交后的物理数据页(恢复数据页，且只能恢复到最后一次提交的位置)。
2. undo用来回滚行记录到某个版本，且记录的是旧版本的数据。undo log一般是逻辑日志，根据每行记录进行记录。

redo log

redo log 包括两部分：
1. 内存中的日志缓冲(redo log buffer)，该部分日志是易失性的
2. 磁盘上的重做日志文件(redo log file)，该部分日志是持久的

在概念上，innodb通过force log at commit机制实现事务的持久性，即在事务提交的时候，必须先将该事务的所有事务日志写入到磁盘上的redo log file和undo log file中进行持久化。

undo log

在数据修改的时候，不仅记录了redo，还记录了相对应的undo，如果因为某些原因导致事务失败或回滚了，可以借助该undo进行回滚。
# undo log 记录的是旧数据

当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。有时候应用到行版本控制的时候，也是通过undo log来实现的：当读取的某一行被其他事务锁定时，它可以从undo log中分析出该行记录以前的数据是什么，从而提供该行版本信息，让用户实现非锁定一致性读取。

# 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)

4.一般查询日志

一般我们不开启，因为一般日志记录的东西很多，生产环境中会产生大量的一般查询日志，所以我们一般不开启

vim /etc/my.cnf
general_log=on
general_log_file=/var/log/select.log

touch /var/log/select.log
chown mysql.mysql /var/log/select.log
chmod 640 /var/log/select.log

systemctl restart mysqld
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)

5.慢查询日志

慢日志

1.将MySQL服务器中影响数据库性能的相关SQL语句记录到日志文件中

2.通过对这些特殊的SQL语句进行分析和改进，提高数据库的性能 默认情况下，MySQL数据库并不启动慢查询日志，需要我们手动来设置这个参数。 如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志或多或少会带来一定的性能影响。 慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表

配置慢日志

vim /etc/my.cnf
 slow_query_log = on	# 开启慢查询
 
 log_queries_not_using_indexes=on	# 慢查询中记录没有使用索引的query
 
 slow_query_log_file=/tmp/slow.log	# 慢查询日志存储的路径
 
 long_query_time=0.5	# 慢查询时间，这里时间为0.5秒，超过0.5秒就会被记录
 
 配置完配置文件后，退出已登录的MySQL用户，重启MySQL服务
 
 # 查看是否开启
 mysql> show variables like '%slow_query_log%';
+---------------------+---------------+
| Variable_name       | Value         |
+---------------------+---------------+
| slow_query_log      | ON            |
| slow_query_log_file | /tmp/slow.log |
+---------------------+---------------+
2 rows in set (0.00 sec)

6.二进制日志

MySQL二进制日志（binary log）是一个二进制文件，主要用于记录修改数据或有可能引起数据变更的SQL语句。

二进制日志中记录了对MySQL数据库执行更改的所有操作，并且记录了语句发生的时间、执行时长、操作数据等其他额外信息，但它不记录select、show等不修改数据的SQL语句。

二进制日志主要用于数据库恢复与主从复制。

开启和设置二进制日志

二进制日志默认是关闭的，通过配置文件来启动和设置二进制日志，修改/etc/my.cnf，加入一些内容，然后重启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

mkdir -p /var/lib/mysql
touch /var/lib/mysql/mybinlog
chown mysql.mysql /var/lib/mysql/mybinlog
systemctl restart mysqld

二进制日志状态查看

系统变量log_bin的值为OFF表示没有开启二进制日志（binary log）。ON表示开启了二进制日志（binary log）

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)

# 查看当前服务所有的二进制日志文件
mysql>  show binary logs;
+-----------------+-----------+
| Log_name        | File_size |
+-----------------+-----------+
| mybinlog.000001 |       154 |
+-----------------+-----------+
1 row in set (0.00 sec)

# 查看当前二进制日志状态
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.00 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.35-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以上版本已被加密

# 打印日志文件
mysqlbinlog /var/lib/mysql/mybinlog.000001 > mysql_bin.log

# 解密文件
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 
其中：
前者指定恢复的时间点，后者指定恢复的位置（位置指二级制文件中# 230 230就是位置）
原理：
就是将记录的SQL语句重新执行了一次，如果恢复了两次，会产生重复的数据

案例：
mysqlbinlog --start-position="154" /var/lib/mysql/mybinlog.000004 | mysql -uroot -p123

注意：
需要找到插入更新的语句所在的时间点与位置，如果恢复的语句中包含delete，会报错

暂停二进制日志功能

可以通过修改配置文件停止二进制日志功能，但需要重启数据库，MySQL提供了语句可以在线停止二进制功能

set sql_log_bin = 0		# 停止二进制日志功能
set sql_log_bin = 1		# 开启二进制日志功能

二进制日志的三种模式

二进制日志三种格式：STATEMENT，ROW，MIXED，由参数binlog_format控制。

1. STATEMENT模式（SBR）
每一条会修改数据的sql语句会记录到binlog中。优点是并不需要记录每一条sql语句和每一行的数据变化，减少了binlog日志量，节约IO，提高性能。缺点是在某些情况（如非确定函数）下会导致master-slave中的数据不一致(如sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题)。

2. ROW模式（RBR）
不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了。而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。缺点是会产生大量的日志，尤其是alter table的时候会让日志暴涨。

3. MIXED模式（MBR）
以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。

7.中继日志

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

mysql> show variables like '%relay%';
+---------------------------+-------------------------------------+
| Variable_name             | Value                               |
+---------------------------+-------------------------------------+
| max_relay_log_size        | 0                                   |
| relay_log                 |                                     |
| relay_log_basename        | /mysql_data/laodage-relay-bin       |
| relay_log_index           | /mysql_data/laodage-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表示不限制）

8.通用日志

记录连接数据库信息和所有操作信息

show variables where variable_name like "%general_log%"；
+------------------+---------------------+
| 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表示同时记录文件和表