本文详细介绍了MySQL数据库的日志系统，包括错误日志、通用查询日志、二进制日志和慢日志的作用、配置与管理。二进制日志对于数据恢复至关重要，通用查询日志记录所有执行的SQL语句，慢日志则监控慢查询。正确管理和利用这些日志能确保数据安全并优化性能。
目录
一.日志
1.日志和备份的必要性
在数据库保存数据时，有时候会因为误删除数据库，意外断电或程序意外终止，由于病毒造成的数据库损坏或丢失，文件系统损坏后，系统进行自检操作，升级数据库时，命令语句不严格，设备故障，自然灾害等不可避免会出现数据丢失或者被破坏，这样情况下，我们必须保证数据的安全性和完整性，就需要使用日志来查看或者恢复数据了。
默认情况下，所有日志创建于mysqld数据目录中。通过刷新日志（mysql下执行flush logs或者命令行执行mysqladmin flush-logs，mysqladmin refresh），你可以强制 mysqld来关闭和重新打开日志文件（或者在某些情况下切换到一个新的日志）,如果你正使用MySQL复制功能，从复制服务器将维护更多日志文件，被称为接替日志。
日志刷新
[root@localhost mysql]# ll
-rw-r----- 1 mysql mysql 1782 Jul 23 20:33 binlog.000001
-rw-r----- 1 mysql mysql 593 Jul 24 10:01 binlog.000002
mysql8.0 [SLB]>flush logs;
Query OK, 0 rows affected (0.03 sec)
[root@localhost mysql]# ll
-rw-r----- 1 mysql mysql 1782 Jul 23 20:33 binlog.000001
-rw-r----- 1 mysql mysql 637 Jul 24 10:08 binlog.000002
-rw-r----- 1 mysql mysql 157 Jul 24 10:08 binlog.000003
[root@localhost mysql]# mysqladmin flush-logs -uroot -p
Enter password:
[root@localhost mysql]# ll
-rw-r----- 1 mysql mysql 1782 Jul 23 20:33 binlog.000001
-rw-r----- 1 mysql mysql 637 Jul 24 10:08 binlog.000002
-rw-r----- 1 mysql mysql 201 Jul 24 10:11 binlog.000003
-rw-r----- 1 mysql mysql 157 Jul 24 10:11 binlog.000004
2.mysql的日志类型
（1）错误日志
错误日志主要记录：服务器启动和关闭过程中的信息、服务器运行过程中的错误信息、事件调度器运行一个时间时产生的信息、在从服务器上启动从服务器进程时产生的信息。
上表参考位置中，如果没有给定名称和路径，mysqld使用错误日志名host_name.err并在数据目录中写入日志文件。如果日志文件不存在，mysql启动或者执行flush logs时会创建新的日志文件。
查看当前错误日志和是否记录警告设置
mysql8.0 [SLB]>show global variables like '%log_error%';
+----------------------------+----------------------------------------+
| Variable_name | Value |
+----------------------------+----------------------------------------+
| binlog_error_action | ABORT_SERVER |
| log_error | /var/log/mysqld.log |
| log_error_services | log_filter_internal; log_sink_internal |
| log_error_suppression_list | |
| log_error_verbosity | 2 |
+----------------------------+----------------------------------------+
5 rows in set (0.00 sec)
mysql8.0 [SLB]>show global variables like '%log_warnings%';
Empty set (0.01 sec)
（2）通用查询日志
记录建立的客户端连接和执行的语句，默认关闭
mysql服务器默认情况下并没有开启通用查询日志。如果需要通用查询日志，可通过修改配置文件来开启：
启动开关：general_log={ON|OFF}
日志文件变量：general_log_file[=/PATH/TO/file]
全局日志开关：log={ON|OFF} 该开关打开后，所有日志都会被启用
记录类型：log_output={TABLE|FILE|NONE}，FILE表示将日志存入文件，默认值是FILE；TABLE表示将日志存入数据库，这样日志信息就会被写入到mysql.slow_log表中。mysql数据库支持同时两种日志存储方式，配置的时候以逗号隔开。
因此，要启用通用查询日志，需要至少配置general_log=ON，log_output={TABLE|FILE}。而general_log_file如果没有指定，默认名是host_name.log。
查看通用查询日志的设置
mysql8.0 [SLB]>show global variables like '%general%';
+------------------+------------------------------+
| Variable_name | Value |
+------------------+------------------------------+
| general_log | OFF |
| general_log_file | /var/lib/mysql/localhost.log |
+------------------+------------------------------+
2 rows in set (0.01 sec)
可以通过mysql临时设置开启
mysql8.0 [SLB]>set global general_log=on;
Query OK, 0 rows affected (0.01 sec)
mysql8.0 [SLB]>show global variables like '%general%';
+------------------+------------------------------+
| Variable_name | Value |
+------------------+------------------------------+
| general_log | ON |
| general_log_file | /var/lib/mysql/localhost.log |
+------------------+------------------------------+
2 rows in set (0.04 sec)
永久开启需要更改my.cnf文件，添加内容
general_log=ON
general_log_file=路径和名称（3）二进制日志
二进制日志包含关于每个更新数据库的语句的执行时间信息。 它不包含不会修改任何数据的语句，例如select，show等。
二进制日志的主要目的是在数据库存在故障时，恢复时能够最大可能地更新数据库（即时点恢复），因为二进制日志包含备份后进行的所有更新。二进制日志还用于在主复制服务器上记录所有将发送给从服务器的语句。
- 执行后数据非常大此时基于记录来保存
- 数据是一些即时的比如时间等，需要基于行就保存记录准确的值
- 两种方式可能一起搭配使用
查看二进制文件的设置，查看最大大小，也可以在my.cnf中通过"max_binlog_size=xx"指定
在my.cnf中使用log-bin [= file_name]来开启二进制日志，使用skip-log-bin=1来关闭二进制日志
mysql8.0 [SLB]>show variables like '%log_bin%';
+---------------------------------+-----------------------------+
| Variable_name | Value |
+---------------------------------+-----------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/binlog |
| log_bin_index | /var/lib/mysql/binlog.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)
mysql8.0 [SLB]>show variables like 'max_binlog_size';
+-----------------+------------+
| Variable_name | Value |
+-----------------+------------+
| max_binlog_size | 1073741824 |
+-----------------+------------+
1 row in set (0.00 sec)
查看二进制日志文件以及当前正使用哪个文件
mysql8.0 [SLB]>show binary logs;
+---------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 | 1782 | No |
| binlog.000002 | 637 | No |
| binlog.000003 | 201 | No |
| binlog.000004 | 180 | No |
| binlog.000005 | 778 | No |
| binlog.000006 | 157 | No |
+---------------+-----------+-----------+
6 rows in set (0.00 sec)
mysql8.0 [SLB]>show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000006 | 157 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
读取二进制日志，mysqlbinlog 查看文件具体内容
mysql8.0 [SLB]>show binlog events in 'binlog.000006';
+---------------+-----+----------------+-----------+-------------+-----------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+-----+----------------+-----------+-------------+-----------------------------------+
| binlog.000006 | 4 | Format_desc | 1 | 126 | Server ver: 8.0.34, Binlog ver: 4 |
| binlog.000006 | 126 | Previous_gtids | 1 | 157 | |
+---------------+-----+----------------+-----------+-------------+-----------------------------------+
2 rows in set (0.00 sec)
mysql8.0 [SLB]>show binlog events in 'binlog.000002' from 4; ---指定从片段4开始查看
+---------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------+
| binlog.000002 | 4 | Format_desc | 1 | 126 | Server ver: 8.0.34, Binlog ver: 4 |
| binlog.000002 | 126 | Previous_gtids | 1 | 157 | |
| binlog.000002 | 157 | Anonymous_Gtid | 1 | 234 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000002 | 234 | Query | 1 | 339 | create database SLB /* xid=18 */ |
| binlog.000002 | 339 | Anonymous_Gtid | 1 | 418 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000002 | 418 | Query | 1 | 593 | use `SLB`; create table t1 ( id int primary key, name varchar(10) not null, dpart char(20) not null) /* xid=261 */ |
| binlog.000002 | 593 | Rotate | 1 | 637 | binlog.000003;pos=4 |
+---------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------+
7 rows in set (0.00 sec)
/*
Log_name表示当前事件所在的binlog文件名称
Pos当前事件的开始位置，每个事件都占用固定的字节大小
Event_type，在event_type列中，在当前binlog v4版本中，每个binlog文件总是以Format Description Event作为开始，以Rotate Event结束作为结束。
Format_desc，也就是我们所说的Format Description Event，是binlog文件的第一个事件
Previous_gtids，该事件完整名称为PREVIOUS_GTIDS_LOG_EVENT，是在之前的binlog文件中，已经执行过的GTID。需要我们开启GTID选项，这个事件才会有值。
Rotate，Rotate Event是每个binlog文件的结束事件
Server_id表示产生这个事件的mysql server_id，通过设置my.cnf中的server-id选项进行配置。
End_log_position表示下一个事件的开始位置。
Info表示1当前事件的描述信息。
*/
[root@localhost mysql]# mysqlbinlog binlog.000006清空二进制日志文件，删除二进制日志文件
mysql8.0 [SLB]> reset master; ---master表示当前使用的
mysql8.0 [SLB]> purge binary/master logs to '日志'; ---删除此日志之前的所有日志文件
mysql8.0 [SLB]> purge binary/master logs before 'date'; ---删除某个时间之前的所有日志文件恢复二进制日志
在数据库出现意外丢失数据时，使用mysqlbinlog工具从指定的时间点开始（比如，最后一次备份）直到如今或者是到另外一个指定的时间点的日志中恢复数据。
- 创建数据库，创建表，查看当前使用的日志文件和信息
mysql8.0 [(none)]>create database SLB1;
Query OK, 1 row affected (0.02 sec)
mysql8.0 [(none)]>use SLB1;
Database changed
mysql8.0 [SLB1]>create table t1 (
-> id int primary key,
-> name char(20));
Query OK, 0 rows affected (0.04 sec)
mysql8.0 [SLB1]>show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000002 | 741 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql8.0 [SLB1]>show binlog events in 'binlog.000002'\G;
*************************** 1. row ***************************
Log_name: binlog.000002
Pos: 4
Event_type: Format_desc
Server_id: 1
End_log_pos: 126
Info: Server ver: 8.0.34, Binlog ver: 4
*************************** 2. row ***************************
Log_name: binlog.000002
Pos: 126
Event_type: Previous_gtids
Server_id: 1
End_log_pos: 157
Info:
*************************** 3. row ***************************
Log_name: binlog.000002
Pos: 157
Event_type: Anonymous_Gtid
Server_id: 1
End_log_pos: 234
Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
*************************** 4. row ***************************
Log_name: binlog.000002
Pos: 234
Event_type: Query
Server_id: 1
End_log_pos: 339
Info: create database SLB /* xid=18 */
*************************** 5. row ***************************
Log_name: binlog.000002
Pos: 339
Event_type: Anonymous_Gtid
Server_id: 1
End_log_pos: 416
Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
*************************** 6. row ***************************
Log_name: binlog.000002
Pos: 416
Event_type: Query
Server_id: 1
End_log_pos: 524
Info: create database SLB1 /* xid=257 */
*************************** 7. row ***************************
Log_name: binlog.000002
Pos: 524
Event_type: Anonymous_Gtid
Server_id: 1
End_log_pos: 601
Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
*************************** 8. row ***************************
Log_name: binlog.000002
Pos: 601
Event_type: Query
Server_id: 1
End_log_pos: 741
Info: use `SLB1`; create table t1 (
id int primary key,
name char(20)) /* xid=262 */
8 rows in set (0.00 sec)
- 删除表和数据，并刷新日志
mysql8.0 [SLB1]>drop table t1;
Query OK, 0 rows affected (0.03 sec)
mysql8.0 [SLB1]>drop database SLB1;
Query OK, 0 rows affected (0.02 sec)
mysql8.0 [(none)]>flush logs;
Query OK, 0 rows affected (0.03 sec)
mysql8.0 [(none)]>show databases;
+--------------------+
| Database |
+--------------------+
| SLB |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
- 我当前使用的是binlog.000002，就去二进制文件中查看binlog.000002的内容
mysql8.0 [SLB1]>show variables like '%log_bin%'; ---就去/var/lib/mysql下查找
+---------------------------------+-----------------------------+
| Variable_name | Value |
+---------------------------------+-----------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/binlog |
| log_bin_index | /var/lib/mysql/binlog.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)
[root@localhost mysql]# pwd
/var/lib/mysql
[root@localhost mysql]# ll | grep binlog.000002
-rw-r----- 1 mysql mysql 1170 Jul 24 12:57 binlog.000002
[root@localhost mysql]# mysqlbinlog binlog.000002
- 找到我们删除表或库的那个时间，根据以下命令来恢复
[root@localhost mysql]# mysqlbinlog --stop-datetime="23-07-24 12:56:45" binlog.000002 | mysql -uroot -p
- 查看
mysql8.0 [(none)]>show databases;
+--------------------+
| Database |
+--------------------+
| SLB |
| SLB1 |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)
Database changed
mysql8.0 [SLB1]>show tables;
+----------------+
| Tables_in_SLB1 |
+----------------+
| t1 |
+----------------+
1 row in set (0.01 sec)
（4）慢日志
记录所有执行时间超过long_query_time秒的所有查询或不使用索引的查询，默认关闭
mysql8.0 [SLB]>show variables like "%query_time%"; ---查询超过默认的10s，可成为慢查询
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.05 sec)
MySQL如果启用了 slow_query_log=ON 选项，就会记录执行时间超过long_query_time的查询（初使表锁定的时间不算作执行时间）。my.cnf记录文件为slow_query_log_file[=file_name]，如果没有给出file_name值， 默认为主机名，后缀为-slow.log。如果给出了文件名，但不是绝对路径名，文件则写入数据目录。
查看慢日志位置和状态
mysql8.0 [SLB]>show variables like '%query_log%';
+------------------------------+-----------------------------------+
| Variable_name | Value |
+------------------------------+-----------------------------------+
| binlog_rows_query_log_events | OFF |
| slow_query_log | ON |
| slow_query_log_file | /var/lib/mysql/localhost-slow.log |
+------------------------------+-----------------------------------+
3 rows in set (0.00 sec)
mysql8.0 [SLB]>show variables like 'slow_query_log';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| slow_query_log | OFF |
+----------------+-------+
1 row in set (0.00 sec)
临时开启慢日志，永久开启同理修改my.cnf文件
mysql8.0 [SLB]>set global slow_query_log=ON;
Query OK, 0 rows affected (0.01 sec)
mysql8.0 [SLB]>show variables like 'slow_query_log';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| slow_query_log | ON |
+----------------+-------+
1 row in set (0.00 sec)
扫一扫
2294
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
