开启辅助访问

Bo's Oracle Station

Bo's Oracle Station › 博客首页 ›Oracle技术› Oracle云、Linux、MySQL和Java › 查看内容

【博客文章2026】MySQL 9.x数据库管理技巧---给初学者的建议3：认识各种MySQL数据库的日志

2026-3-11 15:15| 发布者: admin| 查看: 218| 评论: 0|原作者: Bo Tang

摘要: 本博客通过实验学习MySQL 9.x中的general log、slow log、bin log和audit log。
【博客文章2026】MySQL 9.x数据库管理技巧---给初学者的建议3：认识各种MySQL数据库的日志



Author: Bo Tang

1. 检查genenral log和slow log是否打开：

    登录Mysql客户端，执行：

[root@station95 ~]# mysql -u root -p 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 9.6.0 MySQL Community Server - GPL

Copyright (c) 2000, 2026, 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.

23:26 (none)> show global variables like 'general%log'\G
*************************** 1. row ***************************
Variable_name: general_log
        Value: ON
1 row in set (0.006 sec)

23:26 (none)> show global variables like 'slow%log'\G
*************************** 1. row ***************************
Variable_name: slow_query_log
        Value: ON
1 row in set (0.004 sec)

 2. 设定日志输出模式为TABLE：

    检查日志模式：

23:27 (none)> show global variables like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+
1 row in set (0.006 sec)

    设定日志输出模式为TABLE：

23:38 (none)> set global log_output='TABLE';
Query OK, 0 rows affected (0.001 sec)

23:44 (none)> show global variables like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | TABLE |
+---------------+-------+
1 row in set (0.007 sec)

    清空general log和slow log表：

02:18 (none)> truncate mysql.general_log;
Query OK, 0 rows affected (0.003 sec)

02:29 (none)> truncate mysql.slow_log;
Query OK, 0 rows affected (0.005 sec)

 3. 为产生general log，而创建一个新数据库world_y：

    使用https://www.botangdb.com/portal.php?mod=attachment&id=777，进行导入：
    请编辑下载后的world_x.sql，将两处world_x改成world_y：

-- MySQL dump 10.13  Distrib 8.0.19, for osx10.14 (x86_64)
--
-- Host: 127.0.0.1    Database: world_x
-- ------------------------------------------------------
-- Server version       8.0.19-debug

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
SET @old_autocommit=@@autocommit;

--
-- Current Database: `world_x`
--

/*!40000 DROP DATABASE IF EXISTS `world_x`*/;

CREATE DATABASE `world_y` DEFAULT CHARACTER SET utf8mb4;

USE `world_y`;

--
-- Table structure for table `city`
--

DROP TABLE IF EXISTS `city`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `city` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `Name` char(35) NOT NULL DEFAULT '',
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `District` char(20) NOT NULL DEFAULT '',
  `Info` json DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

......

    这些操作会产生许多general log:

19:29 (none)> source /root/world_x.sql
......

Query OK, 0 rows affected (0.000 sec)
Query OK, 0 rows affected (0.000 sec)
22:31 (none)> use world_y;
Database changed
22:31 world_y> show tables;
+-------------------+
| Tables_in_world_y |
+-------------------+
| city              |
| country           |
| countryinfo       |
| countrylanguage   |
+-------------------+
4 rows in set (0.004 sec)

22:31 world_y> select  count(*) from mysql.general_log where argument like 'create table%';
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.042 sec)

22:32 world_y>  select  count(*) from mysql.general_log where argument like 'CREATE TABLE%';
+----------+
| count(*) |
+----------+
|        4 |
+----------+
1 row in set (0.065 sec)

 4. 为产生slow log，而做以下实验：    

    做以下实验：

22:32 world_y> select  count(*) from mysql.slow_log;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.001 sec)

22:37 world_y> select sleep(11);
+-----------+
| sleep(11) |
+-----------+
|         0 |
+-----------+
1 row in set (11.001 sec)

22:37 world_y>  select  count(*) from mysql.slow_log;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.001 sec)
22:46 world_y> select start_time, CONVERT(sql_text USING utf8mb4) AS sql_text   from mysql.slow_log;
+----------------------------+------------------+
| start_time                 | sql_text         |
+----------------------------+------------------+
| 2026-03-12 22:37:52.044952 | select sleep(11) |
+----------------------------+------------------+
1 row in set (0.001 sec)

5. 查看bin log，而做以下实验：    

    做以下实验：

22:54 world_y> RESET BINARY LOGS AND GTIDS;
Query OK, 0 rows affected (0.164 sec)

22:55 world_y> create database foo;
Query OK, 1 row affected (0.047 sec)

22:56 world_y> drop database foo;
Query OK, 0 rows affected (0.047 sec)

22:56 world_y> show binlog events;
+---------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name      | Pos | Event_type     | Server_id | End_log_pos | Info                                                              |
+---------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| binlog.000001 |   4 | Format_desc    |         1 |         127 | Server ver: 9.6.0, Binlog ver: 4                                  |
| binlog.000001 | 127 | Previous_gtids |         1 |         158 |                                                                   |
| binlog.000001 | 158 | Gtid           |         1 |         235 | SET @@SESSION.GTID_NEXT= 'bdbf7923-19ac-11f1-a3ae-525400c872ca:1' |
| binlog.000001 | 235 | Query          |         1 |         340 | create database foo /* xid=22637 */                               |
| binlog.000001 | 340 | Gtid           |         1 |         417 | SET @@SESSION.GTID_NEXT= 'bdbf7923-19ac-11f1-a3ae-525400c872ca:2' |
| binlog.000001 | 417 | Query          |         1 |         518 | drop database foo /* xid=22638 */                                 |
+---------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
6 rows in set (0.001 sec)

    进行日志切换：


23:00 world_y> SHOW BINARY LOGS;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |       518 | No        |
+---------------+-----------+-----------+
1 row in set (0.001 sec)

23:00 world_y> FLUSH BINARY LOGS; 
Query OK, 0 rows affected (0.086 sec)

23:00 world_y> create database foo;
Query OK, 1 row affected (0.042 sec)

23:01 world_y> drop database foo;
Query OK, 0 rows affected (0.036 sec)

23:01 world_y> SHOW BINARY LOGS;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |       562 | No        |
| binlog.000002 |       558 | No        |
+---------------+-----------+-----------+
2 rows in set (0.001 sec)
23:04 world_y>  show binlog events in 'binlog.000002';
+---------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name      | Pos | Event_type     | Server_id | End_log_pos | Info                                                              |
+---------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| binlog.000002 |   4 | Format_desc    |         1 |         127 | Server ver: 9.6.0, Binlog ver: 4                                  |
| binlog.000002 | 127 | Previous_gtids |         1 |         198 | bdbf7923-19ac-11f1-a3ae-525400c872ca:1-2                          |
| binlog.000002 | 198 | Gtid           |         1 |         275 | SET @@SESSION.GTID_NEXT= 'bdbf7923-19ac-11f1-a3ae-525400c872ca:3' |
| binlog.000002 | 275 | Query          |         1 |         380 | create database foo /* xid=22644 */                               |
| binlog.000002 | 380 | Gtid           |         1 |         457 | SET @@SESSION.GTID_NEXT= 'bdbf7923-19ac-11f1-a3ae-525400c872ca:4' |
| binlog.000002 | 457 | Query          |         1 |         558 | drop database foo /* xid=22645 */                                 |
+---------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
6 rows in set (0.001 sec)

    清除二进制日志，再做一些操作：

23:11 world_y>  purge binary logs to 'binlog.000002';
Query OK, 0 rows affected (0.038 sec
23:12 world_y> create database foo2;
Query OK, 1 row affected (0.034 sec)

23:15 world_y> drop database foo2;
Query OK, 0 rows affected (0.038 sec)

    使用mysqlbinlog命令，查看事件ID（注意，为了不报错，一定要在/etc/my.cnf中注释掉：“#prompt = \R:\m \d>\_”）：


[root@station95 ~]# mysqlbinlog /var/lib/mysql/binlog.000002
# The proper term is pseudo_replica_mode, but we use this compatibility alias
# to make the statement usable on server versions 8.0.24 and older.
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#260312 23:00:54 server id 1  end_log_pos 127 CRC32 0xcb7ec398  Start: binlog v 4, server v 9.6.0 created 260312 23:00:54
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
JtWyaQ8BAAAAewAAAH8AAAABAAQAOS42LjAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEwANAAgAAAAABAAEAAAAYwAEGggAAAAAAAACAAAACgoKKioAEjQA
CigAAAGYw37L
'/*!*/;
# at 127
#260312 23:00:54 server id 1  end_log_pos 198 CRC32 0x3d4af5a6  Previous-GTIDs
# bdbf7923-19ac-11f1-a3ae-525400c872ca:1-2
# at 198
#260312 23:01:07 server id 1  end_log_pos 275 CRC32 0x50cd0a14  GTID    last_committed=0        sequence_number=1       rbr_only=no     original_committed_timestamp=1773327667338125        immediate_commit_timestamp=1773327667338125     transaction_length=182
# original_commit_timestamp=1773327667338125 (2026-03-12 23:01:07.338125 CST)
# immediate_commit_timestamp=1773327667338125 (2026-03-12 23:01:07.338125 CST)
/*!80001 SET @@session.original_commit_timestamp=1773327667338125*//*!*/;
/*!80014 SET @@session.original_server_version=90600*//*!*/;
/*!80014 SET @@session.immediate_server_version=90600*//*!*/;
SET @@SESSION.GTID_NEXT= 'bdbf7923-19ac-11f1-a3ae-525400c872ca:3'/*!*/;
# at 275
#260312 23:01:07 server id 1  end_log_pos 380 CRC32 0x26a073bd  Query   thread_id=15    exec_time=0     error_code=0    Xid = 22644
SET TIMESTAMP=1773327667/*!*/;
SET @@session.pseudo_thread_id=15/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1168113696/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
/*!80016 SET @@session.default_table_encryption=0*//*!*/;
create database foo
/*!*/;
# at 380
#260312 23:01:15 server id 1  end_log_pos 457 CRC32 0x5572c600  GTID    last_committed=1        sequence_number=2       rbr_only=no     original_committed_timestamp=1773327675277626        immediate_commit_timestamp=1773327675277626     transaction_length=178
# original_commit_timestamp=1773327675277626 (2026-03-12 23:01:15.277626 CST)
# immediate_commit_timestamp=1773327675277626 (2026-03-12 23:01:15.277626 CST)
/*!80001 SET @@session.original_commit_timestamp=1773327675277626*//*!*/;
/*!80014 SET @@session.original_server_version=90600*//*!*/;
/*!80014 SET @@session.immediate_server_version=90600*//*!*/;
SET @@SESSION.GTID_NEXT= 'bdbf7923-19ac-11f1-a3ae-525400c872ca:4'/*!*/;
# at 457
#260312 23:01:15 server id 1  end_log_pos 558 CRC32 0x732f31dd  Query   thread_id=15    exec_time=0     error_code=0    Xid = 22645
SET TIMESTAMP=1773327675/*!*/;
drop database foo
/*!*/;
# at 558
#260312 23:15:22 server id 1  end_log_pos 635 CRC32 0xe1b9530f  GTID    last_committed=2        sequence_number=3       rbr_only=no     original_committed_timestamp=1773328522939219        immediate_commit_timestamp=1773328522939219     transaction_length=185
# original_commit_timestamp=1773328522939219 (2026-03-12 23:15:22.939219 CST)
# immediate_commit_timestamp=1773328522939219 (2026-03-12 23:15:22.939219 CST)
/*!80001 SET @@session.original_commit_timestamp=1773328522939219*//*!*/;
/*!80014 SET @@session.original_server_version=90600*//*!*/;
/*!80014 SET @@session.immediate_server_version=90600*//*!*/;
SET @@SESSION.GTID_NEXT= 'bdbf7923-19ac-11f1-a3ae-525400c872ca:5'/*!*/;
# at 635
#260312 23:15:22 server id 1  end_log_pos 743 CRC32 0x07aabf0c  Query   thread_id=15    exec_time=0     error_code=0    Xid = 22652
SET TIMESTAMP=1773328522/*!*/;
/*!80016 SET @@session.default_table_encryption=0*//*!*/;
create database foo2
/*!*/;
# at 743
#260312 23:15:29 server id 1  end_log_pos 820 CRC32 0x368b0e65  GTID    last_committed=3        sequence_number=4       rbr_only=no     original_committed_timestamp=1773328529110486        immediate_commit_timestamp=1773328529110486     transaction_length=181
# original_commit_timestamp=1773328529110486 (2026-03-12 23:15:29.110486 CST)
# immediate_commit_timestamp=1773328529110486 (2026-03-12 23:15:29.110486 CST)
/*!80001 SET @@session.original_commit_timestamp=1773328529110486*//*!*/;
/*!80014 SET @@session.original_server_version=90600*//*!*/;
/*!80014 SET @@session.immediate_server_version=90600*//*!*/;
SET @@SESSION.GTID_NEXT= 'bdbf7923-19ac-11f1-a3ae-525400c872ca:6'/*!*/;
# at 820
#260312 23:15:29 server id 1  end_log_pos 924 CRC32 0x88a91caf  Query   thread_id=15    exec_time=0     error_code=0    Xid = 22653
SET TIMESTAMP=1773328529/*!*/;
drop database foo2
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

6. 查看有没有audit log的plugin，而做以下实验：    

    做以下实验：

mysql> show variables like 'audit_log_file';
Empty set (0.006 sec)

    要打开audit log需要插件：

mysql> show global variables like 'plugin_dir';
+---------------+--------------------------+
| Variable_name | Value                    |
+---------------+--------------------------+
| plugin_dir    | /usr/lib64/mysql/plugin/ |
+---------------+--------------------------+
1 row in set (0.006 sec)

    没有相应插件：

[root@station95 ~]# ls -l /usr/lib64/mysql/plugin/audit_log.so
ls: cannot access '/usr/lib64/mysql/plugin/audit_log.so': No such file or directory

    如果要体验audit log，请参考本人的这篇博客：通过GPL开源plugin，向MySQL8.0社区版增加审计日志功能。






路过
雷人
握手
鲜花
鸡蛋
收藏 邀请
上一篇：【博客文章2026】MySQL 9.x数据库管理技巧---给初学者的建议2下一篇：【博客文章2026】MySQL 9.x数据库管理技巧---给初学者的建议4：在线帮助系统、mysqladmin和mysql-workbench

相关分类

QQ|手机版|Bo's Oracle Station   

GMT+8, 2026-3-15 20:41 , Processed in 0.073630 second(s), 21 queries .

返回顶部