MySQL日志

日志类型

日志文件	计入文件中信息类型
错误日志	启动、运行、停止时出现的问题
查询日志	记录建立客户端连接和执行语句
二进制日志	记录所有更改数据语句。用于恢复和及时点恢复
慢日志	执行时间超过long_query_time所有查询/不使用索引的查询
事务日志	记录InnoDB等支持事务的存储引擎执行事务时产生的日志

错误日志

记录内容

1.服务器启动/关闭过程中信息
2.服务器运行过程中错误信息
3.事件调度运行一个时间时产生的信息
4.在服务器上启动服务器进程产生信息

定义

在配置文件中(/etc/my.cnf)用log-error[=file_name]指定mysql保存错误日志文件的位置
1.配置文件中没有log-error，错误日志将在终端输出
mysql配置文件

查看当前错误日志文件位置

mysql> show variables like "%log_error%";
+---------------------+--------------+
| Variable_name       | Value        |
+---------------------+--------------+
| binlog_error_action | ABORT_SERVER |
| log_error           | stderr       |
| log_error_verbosity | 3            |
+---------------------+--------------+
3 rows in set (0.00 sec)
在终端输出

2.配置文件中只有log-error,用host_name.err，并在数据目录中写入日志文件

mysql> show variables like "%log_error%";
+---------------------+----------------------------+
| Variable_name       | Value                      |
+---------------------+----------------------------+
| binlog_error_action | ABORT_SERVER               |
| log_error           | /var/run/mysqld/mysqld.err |
| log_error_verbosity | 3                          |
+---------------------+----------------------------+
3 rows in set (0.00 sec)

3.配置文件中只文件名

mysql> show variables like "%log_error%";
+---------------------+--------------+
| Variable_name       | Value        |
+---------------------+--------------+
| binlog_error_action | ABORT_SERVER |
| log_error           | ./mysqld.err |
| log_error_verbosity | 3            |
+---------------------+--------------+
3 rows in set (0.00 sec)

使用flush.logs会产生新的错误日志

查询日志

相关变量

mysql> show variables like "%general_log%";
+------------------+------------------------------+
| Variable_name    | Value                        |
+------------------+------------------------------+
| general_log      | OFF                          |
| general_log_file | /var/lib/mysql/localhost.log |
+------------------+------------------------------+
2 rows in set (0.00 sec)

临时开启

mysql> set global general_log=ON;
Query OK, 0 rows affected (0.00 sec)

查看查询日志变化
tail -f 不停读取和显示文件中最新内容，监视文件变化，实时监视效果

[root@localhost ~]# tail -f /var/lib/mysql/localhost.log
2022-04-17T01:11:58.715518Z	    2 Query	show tables
2022-04-17T01:12:09.012528Z	    2 Query	select *from score
2022-04-17T01:12:36.501340Z	    2 Query	show variables like "%general_log%"
2022-04-17T01:13:12.130441Z	    2 Query	select * from student
2022-04-17T01:13:27.211315Z	    2 Query	select name from student
2022-04-17T01:13:56.066529Z	    2 Query	set global general_log=OFF
/usr/sbin/mysqld, Version: 5.7.37 (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
2022-04-17T03:06:19.486324Z	    2 Quit	
2022-04-17T03:09:17.356900Z	    3 Connect	root@localhost on  using Socket
2022-04-17T03:09:17.357146Z	    3 Query	select @@version_comment limit 1
2022-04-17T03:09:30.645102Z	    3 Query	show databases
2022-04-17T03:09:39.951767Z	    3 Query	SELECT DATABASE()
2022-04-17T03:09:39.952130Z	    3 Init DB	homework
2022-04-17T03:09:39.953480Z	    3 Query	show databases
2022-04-17T03:09:39.953902Z	    3 Query	show tables
2022-04-17T03:09:39.954197Z	    3 Field List	score 
2022-04-17T03:09:39.979367Z	    3 Field List	student 
2022-04-17T03:09:47.005675Z	    3 Query	select * from score

慢查询日志

内容
记录执行时间超过long_query_time的查询；
配置文件中：
slow_query_log_file[=file_name],
1.若没有file_name值，默认主机名，后缀slow.log
2…只有文件名，不是绝对路径，文件写入数据目录中；

相关变量

mysql> show variables like "%slow_query_log%";
+---------------------+-----------------------------------+
| Variable_name       | Value                             |
+---------------------+-----------------------------------+
| slow_query_log      | OFF                               |
| slow_query_log_file | /var/lib/mysql/localhost-slow.log |
+---------------------+-----------------------------------+
2 rows in set (0.00 sec)
默认没有开启，为调优，建议开启

临时开启

mysql> set global slow_query_log=on;
Query OK, 0 rows affected (0.01 sec)

二进制日志

内容
记录：insert,update,delete,replace,do,load data,select,truncate table

开启二进制日志

server_id必须是唯一的，一般为主机IP地址的后一位。

查看有哪些二进制文件

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       177 |
| mysql-bin.000002 |       620 |
+------------------+-----------+
2 rows in set (0.00 sec)

查看当前二进制文件

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      620 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

产生新的二进制文件
1.手动刷新时会产生新的二进制文件

mysql> flush logs;
Query OK, 0 rows affected (0.00 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       177 |
| mysql-bin.000002 |       667 |
| mysql-bin.000003 |       154 |
+------------------+-----------+
3 rows in set (0.00 sec)

2.重启服务之后

[root@localhost ~]# systemctl restart mysqld
mysql> show binary logs;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    2
Current database: homework

+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       177 |
| mysql-bin.000002 |       667 |
| mysql-bin.000003 |       177 |
| mysql-bin.000004 |       154 |
+------------------+-----------+
4 rows in set (0.01 sec)

3.达到max_binlog_size值
在my.cnf中设定max_binlog_size=200M,表示二进制日志最大尺寸为200M，超过200M进行滚动。

查看二进制日志文件内容

[root@localhost ~]# mysqlbinlog /var/lib/mysql/mysql-bin.000002

删除二进制文件

mysql> purge master logs to 'mysql-bin.000002';
Query OK, 0 rows affected (0.00 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000002 |       667 |
| mysql-bin.000003 |       177 |
| mysql-bin.000004 |       154 |
+------------------+-----------+
3 rows in set (0.00 sec)