mysql数据库管理-MySQL日志的综合管理

最新推荐文章于 2025-10-12 01:02:45 发布

原创最新推荐文章于 2025-10-12 01:02:45 发布 · 909 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库 #java

mysql数据库备份与恢复同时被 2 个专栏收录

16 篇文章

订阅专栏

mysql数据库高可用管理

16 篇文章

订阅专栏

该文档详细介绍了如何在MySQL中启用、查看和删除不同类型的日志，包括二进制日志。通过示例展示了如何使用flushlogs命令以及如何通过二进制日志恢复数据。同时，文档还提到了在处理utf8mb4编码时遇到的问题及解决方案。

文档概述

Mysql日志包括二进欧制日志，错误日志，通用查询日志和慢查询日志，redo log和undo log还有relay log等类型，以下实验内容如何启动查看删除各类日志，以及如何使用二进制日志恢复数据库。

文档目的

掌握各种日志的设置，查看，删除的方法，掌握使用二进制日志恢复数据的方案。

文档操作步骤

步骤01 启动二进制日志，并指定二进制日志名为binglog.000001

打开my.ini或者my.cnf在【mysqld】组中修改如下内容

[mysqld]

log-bin=d:\log\binlog.log

步骤2 重启mysql服务

net stop mysql

net start mysql

执行完上述命令后，mysql服务重启，可以看到在目录D:\log目录下创建了2个文件

binlog.000001,binlog.INDEX修改成功。

步骤3 查看flush logs对二进制的影响

方法1 mysqladmin -uroot -p flush logs

方法2 登录mysql自行flush logs命令

flush logs将切换日志（包括各种日志 err binlog slow log等等）

mysql> show binary logs;
+------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+------------+-----------+-----------+
| log.000089 | 156 | No |
| log.000090 | 156 | No |
| log.000091 | 156 | No |
| log.000092 | 179 | No |
| log.000093 | 156 | No |
| log.000094 | 156 | No |
| log.000095 | 156 | No |
| log.000096 | 156 | No |
| log.000097 | 156 | No |
| log.000098 | 156 | No |
| log.000099 | 156 | No |
| log.000100 | 156 | No |
| log.000101 | 179 | No |
| log.000102 | 156 | No |
| log.000103 | 156 | No |
| log.000104 | 156 | No |
| log.000105 | 156 | No |
| log.000106 | 156 | No |
| log.000107 | 179 | No |
| log.000108 | 156 | No |
| log.000109 | 156 | No |
| log.000110 | 156 | No |
| log.000111 | 179 | No |
| log.000112 | 179 | No |
| log.000113 | 179 | No |
| log.000114 | 156 | No |
| log.000115 | 156 | No |
| log.000116 | 156 | No |
+------------+-----------+-----------+
28 rows in set (0.01 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.04 sec)

mysql> show binary logs;l
+------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+------------+-----------+-----------+
| log.000089 | 156 | No |
| log.000090 | 156 | No |
| log.000091 | 156 | No |
| log.000092 | 179 | No |
| log.000093 | 156 | No |
| log.000094 | 156 | No |
| log.000095 | 156 | No |
| log.000096 | 156 | No |
| log.000097 | 156 | No |
| log.000098 | 156 | No |
| log.000099 | 156 | No |
| log.000100 | 156 | No |
| log.000101 | 179 | No |
| log.000102 | 156 | No |
| log.000103 | 156 | No |
| log.000104 | 156 | No |
| log.000105 | 156 | No |
| log.000106 | 156 | No |
| log.000107 | 179 | No |
| log.000108 | 156 | No |
| log.000109 | 156 | No |
| log.000110 | 156 | No |
| log.000111 | 179 | No |
| log.000112 | 179 | No |
| log.000113 | 179 | No |
| log.000114 | 156 | No |
| log.000115 | 156 | No |
| log.000116 | 197 | No |
| log.000117 | 156 | No |
+------------+-----------+-----------+
29 rows in set (0.00 sec)

步骤4 打开和查看二进制日志

使用mysqlbinlog的方式打开二进制文件

mysql> drop table t;
Query OK, 0 rows affected (0.06 sec)

mysql> create table t (id int);
Query OK, 0 rows affected (0.07 sec)

mysql> insert into t values (1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t values (2);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t values (3);
Query OK, 1 row affected (0.01 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.05 sec)

mysql>

mysql> show binary logs;
+------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+------------+-----------+-----------+
| log.000089 | 156 | No |
| log.000090 | 156 | No |
| log.000091 | 156 | No |
| log.000092 | 179 | No |
| log.000093 | 156 | No |
| log.000094 | 156 | No |
| log.000095 | 156 | No |
| log.000096 | 156 | No |
| log.000097 | 156 | No |
| log.000098 | 156 | No |
| log.000099 | 156 | No |
| log.000100 | 156 | No |
| log.000101 | 179 | No |
| log.000102 | 156 | No |
| log.000103 | 156 | No |
| log.000104 | 156 | No |
| log.000105 | 156 | No |
| log.000106 | 156 | No |
| log.000107 | 179 | No |
| log.000108 | 156 | No |
| log.000109 | 156 | No |
| log.000110 | 156 | No |
| log.000111 | 179 | No |
| log.000112 | 179 | No |
| log.000113 | 179 | No |
| log.000114 | 156 | No |
| log.000115 | 156 | No |
| log.000116 | 197 | No |
| log.000117 | 1404 | No |
| log.000118 | 156 | No |
+------------+-----------+-----------+
30 rows in set (0.00 sec)

mysql>

log_bin | ON
log_bin_basename | D:\mysql-8.0.24-winx64\log
log_bin_index | D:\mysql-8.0.24-winx64\log.index

为了支持emoji, mysql数据库的编码都改成了 utf8mb4, 由此也引发了mysqlbinlog命令行的错误. 比如执行 mysqlbinlog --help时报错如下:

mysqlbinlog: [ERROR] unknown variable 'default-character-set=utf8mb4'
执行其他操作, 比如查看binlog文件, 导出binlog文件到sql时:

mysqlbinlog mysql-bin.000001
mysqlbinlog mysql-bin.000001 > h:/1.sql
都一样会遇到错误.

解决方法有2种:

在MySQL的配置 my.cnf 中将default-character-set=utf8mb4 修改为 character-set-server = utf8mb4，但是这需要重启MySQL服务, 比较麻烦

加参数 --no-defaults

第二种比较好, 但是注意: binlog的文件路径要写完整, 否则识别不了

mysqlbinlog --no-defaults D:/MySQL5.7.26/data/mysql-bin.000001 > h:/1_bin.sql

D:\mysql-8.0.24-winx64>mysqlbinlog --no-defaults d:\mysql-8.0.24-winx64\log.000117
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#230112 15:08:14 server id 1 end_log_pos 125 CRC32 0x84f088f8 Start: binlog v 4, server v 8.0.24 cr
ated 230112 15:08:14
BINLOG '
3rG/Yw8BAAAAeQAAAH0AAAAAAAQAOC4wLjI0AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEwANAAgAAAAABAAEAAAAYQAEGggAAAAICAgCAAAACgoKKioAEjQA
CigB+IjwhA==
'/*!*/;
# at 125
#230112 15:08:14 server id 1 end_log_pos 156 CRC32 0x238674c2 Previous-GTIDs
# [empty]
# at 156
#230112 15:10:44 server id 1 end_log_pos 233 CRC32 0x5960a8e3 Anonymous_GTID last_committed=0
sequence_number=1 rbr_only=no original_committed_timestamp=1673507445000224 immediate_com
it_timestamp=1673507445000224 transaction_length=203
# original_commit_timestamp=1673507445000224 (2023-01-12 15:10:45.000224 ?D1ú±ê×?ê±??)
# immediate_commit_timestamp=1673507445000224 (2023-01-12 15:10:45.000224 ?D1ú±ê×?ê±??)
/*!80001 SET @@session.original_commit_timestamp=1673507445000224*//*!*/;
/*!80014 SET @@session.original_server_version=80024*//*!*/;
/*!80014 SET @@session.immediate_server_version=80024*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 233
#230112 15:10:44 server id 1 end_log_pos 359 CRC32 0x455219f8 Query thread_id=8 exec_time=0
error_code=0 Xid = 16
use `test`/*!*/;
SET TIMESTAMP=1673507444/*!*/;
SET @@session.pseudo_thread_id=8/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@sessio
.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=
55/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
DROP TABLE `t` /* generated by server */
/*!*/;
# at 359
#230112 15:10:55 server id 1 end_log_pos 436 CRC32 0x09b857dc Anonymous_GTID last_committed=1
sequence_number=2 rbr_only=no original_committed_timestamp=1673507455203807 immediate_com
it_timestamp=1673507455203807 transaction_length=188
# original_commit_timestamp=1673507455203807 (2023-01-12 15:10:55.203807 ?D1ú±ê×?ê±??)
# immediate_commit_timestamp=1673507455203807 (2023-01-12 15:10:55.203807 ?D1ú±ê×?ê±??)
/*!80001 SET @@session.original_commit_timestamp=1673507455203807*//*!*/;
/*!80014 SET @@session.original_server_version=80024*//*!*/;
/*!80014 SET @@session.immediate_server_version=80024*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 436
#230112 15:10:55 server id 1 end_log_pos 547 CRC32 0xdf9ec033 Query thread_id=8 exec_time=0
error_code=0 Xid = 17
SET TIMESTAMP=1673507455/*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
create table t (id int)
/*!*/;
# at 547
#230112 15:11:06 server id 1 end_log_pos 626 CRC32 0x2f21abc8 Anonymous_GTID last_committed=2
sequence_number=3 rbr_only=yes original_committed_timestamp=1673507466750468 immediate_com
it_timestamp=1673507466750468 transaction_length=272
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1673507466750468 (2023-01-12 15:11:06.750468 ?D1ú±ê×?ê±??)
# immediate_commit_timestamp=1673507466750468 (2023-01-12 15:11:06.750468 ?D1ú±ê×?ê±??)
/*!80001 SET @@session.original_commit_timestamp=1673507466750468*//*!*/;
/*!80014 SET @@session.original_server_version=80024*//*!*/;
/*!80014 SET @@session.immediate_server_version=80024*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 626
#230112 15:11:06 server id 1 end_log_pos 701 CRC32 0xd5ca16d7 Query thread_id=8 exec_time=0
error_code=0
SET TIMESTAMP=1673507466/*!*/;
BEGIN
/*!*/;
# at 701
#230112 15:11:06 server id 1 end_log_pos 748 CRC32 0xd9893668 Table_map: `test`.`t` mapped to numbe
91
# at 748
#230112 15:11:06 server id 1 end_log_pos 788 CRC32 0x65b07672 Write_rows: table id 91 flags: STMT_E
D_F

BINLOG '
irK/YxMBAAAALwAAAOwCAAAAAFsAAAAAAAEABHRlc3QAAXQAAQMAAQEBAGg2idk=
irK/Yx4BAAAAKAAAABQDAAAAAFsAAAAAAAEAAgAB/wABAAAAcnawZQ==
'/*!*/;
# at 788
#230112 15:11:06 server id 1 end_log_pos 819 CRC32 0x44e73183 Xid = 18
COMMIT/*!*/;
# at 819
#230112 15:11:09 server id 1 end_log_pos 898 CRC32 0x6eabbe58 Anonymous_GTID last_committed=3
sequence_number=4 rbr_only=yes original_committed_timestamp=1673507469934650 immediate_com
it_timestamp=1673507469934650 transaction_length=272
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1673507469934650 (2023-01-12 15:11:09.934650 ?D1ú±ê×?ê±??)
# immediate_commit_timestamp=1673507469934650 (2023-01-12 15:11:09.934650 ?D1ú±ê×?ê±??)
/*!80001 SET @@session.original_commit_timestamp=1673507469934650*//*!*/;
/*!80014 SET @@session.original_server_version=80024*//*!*/;
/*!80014 SET @@session.immediate_server_version=80024*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 898
#230112 15:11:09 server id 1 end_log_pos 973 CRC32 0x398f57ab Query thread_id=8 exec_time=0
error_code=0
SET TIMESTAMP=1673507469/*!*/;
BEGIN
/*!*/;
# at 973
#230112 15:11:09 server id 1 end_log_pos 1020 CRC32 0xc46815ee Table_map: `test`.`t` mapped
o number 91
# at 1020
#230112 15:11:09 server id 1 end_log_pos 1060 CRC32 0x4dfb4e61 Write_rows: table id 91 flags
STMT_END_F

BINLOG '
jbK/YxMBAAAALwAAAPwDAAAAAFsAAAAAAAEABHRlc3QAAXQAAQMAAQEBAO4VaMQ=
jbK/Yx4BAAAAKAAAACQEAAAAAFsAAAAAAAEAAgAB/wACAAAAYU77TQ==
'/*!*/;
# at 1060
#230112 15:11:09 server id 1 end_log_pos 1091 CRC32 0xcd84c286 Xid = 19
COMMIT/*!*/;
# at 1091
#230112 15:11:14 server id 1 end_log_pos 1170 CRC32 0x30f33927 Anonymous_GTID last_committe
=4 sequence_number=5 rbr_only=yes original_committed_timestamp=1673507474508911 immed
ate_commit_timestamp=1673507474508911 transaction_length=272
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1673507474508911 (2023-01-12 15:11:14.508911 ?D1ú±ê×?ê±??)
# immediate_commit_timestamp=1673507474508911 (2023-01-12 15:11:14.508911 ?D1ú±ê×?ê±??)
/*!80001 SET @@session.original_commit_timestamp=1673507474508911*//*!*/;
/*!80014 SET @@session.original_server_version=80024*//*!*/;
/*!80014 SET @@session.immediate_server_version=80024*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1170
#230112 15:11:14 server id 1 end_log_pos 1245 CRC32 0xc73fae75 Query thread_id=8 exec_
ime=0 error_code=0
SET TIMESTAMP=1673507474/*!*/;
BEGIN
/*!*/;
# at 1245
#230112 15:11:14 server id 1 end_log_pos 1292 CRC32 0x1c609a27 Table_map: `test`.`t` mapped
o number 91
# at 1292
#230112 15:11:14 server id 1 end_log_pos 1332 CRC32 0x2e4c3b93 Write_rows: table id 91 flags
STMT_END_F

BINLOG '
krK/YxMBAAAALwAAAAwFAAAAAFsAAAAAAAEABHRlc3QAAXQAAQMAAQEBACeaYBw=
krK/Yx4BAAAAKAAAADQFAAAAAFsAAAAAAAEAAgAB/wADAAAAkztMLg==
'/*!*/;
# at 1332
#230112 15:11:14 server id 1 end_log_pos 1363 CRC32 0x3246152f Xid = 20
COMMIT/*!*/;
# at 1363
#230112 15:11:17 server id 1 end_log_pos 1404 CRC32 0xd5a182d9 Rotate to log.000118 pos: 4
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*/;

D:\mysql-8.0.24-winx64>

测试2 ；

mysql> insert into t1 values(1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t1 values(2);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t1 values(3);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)

mysql>

mysql> FLUSH LOGS;
Query OK, 0 rows affected (0.04 sec)

mysql> FLUSH LOGS;
Query OK, 0 rows affected (0.04 sec)

mysql> DROP TABLE T1;
ERROR 1046 (3D000): No database selected
mysql> USE TEST;
Database changed
mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE T1;
Query OK, 0 rows affected (0.03 sec)

mysql> SELECT * FROM T1;
ERROR 1146 (42S02): Table 'test.t1' doesn't exist
mysql> EXIT
Bye

D:\mysql-8.0.24-winx64>mysqlbinlog --no-defaults d:\mysql-8.0.24-winx64\log.000118|mysql -uroot -p
Enter password: ***

D:\mysql-8.0.24-winx64>mysql -uroot -p123
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 19
Server version: 8.0.24 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> use test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1 |
+----------------+
1 row in set (0.00 sec)

mysql> select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)

mysql>

步骤6 删除二进制文件

reset master 慎用将重置binlog到1.

mysql> reset master;
Query OK, 0 rows affected (0.09 sec)

mysql> show binary logs;
+------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+------------+-----------+-----------+
| log.000001 | 156 | No |
+------------+-----------+-----------+
1 row in set (0.00 sec)

mysql>

步骤 7 暂停和重启二进制日志

set sql_log_bin=0

set sql_log_bin=1

步骤8 查看错误日志信息

mysql> show variables like '%error%';
+-------------------------------+-------------------------------------------------+
| Variable_name | Value |
+-------------------------------+-------------------------------------------------+
| binlog_error_action | ABORT_SERVER |
| error_count | 0 |
| log_error | D:\mysql-8.0.24-winx64\data\UP57VFMG5AH8GDS.err |
| log_error_services | log_filter_internal; log_sink_internal |
| log_error_suppression_list | |
| log_error_verbosity | 2 |
| max_connect_errors | 10 |
| max_error_count | 1024 |
| performance_schema_error_size | 4890 |
| slave_skip_errors | OFF |
+-------------------------------+-------------------------------------------------+
10 rows in set, 1 warning (0.00 sec)

步骤8 查看慢查询日志信息

mysql> show variables like '%slow%';
+---------------------------+------------------------------------------------------+
| Variable_name | Value |
+---------------------------+------------------------------------------------------+
| log_slow_admin_statements | OFF |
| log_slow_extra | OFF |
| log_slow_slave_statements | OFF |
| slow_launch_time | 2 |
| slow_query_log | OFF |
| slow_query_log_file | D:\mysql-8.0.24-winx64\data\UP57VFMG5AH8GDS-slow.log |
+---------------------------+------------------------------------------------------+
6 rows in set, 1 warning (0.00 sec)

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

mysql> show variables like '%slow%';
+---------------------------+------------------------------------------------------+
| Variable_name | Value |
+---------------------------+------------------------------------------------------+
| log_slow_admin_statements | OFF |
| log_slow_extra | OFF |
| log_slow_slave_statements | OFF |
| slow_launch_time | 2 |
| slow_query_log | ON |
| slow_query_log_file | D:\mysql-8.0.24-winx64\data\UP57VFMG5AH8GDS-slow.log |
+---------------------------+------------------------------------------------------+
6 rows in set, 1 warning (0.00 sec)

D:\mysql-8.0.24-winx64>net stop mysql80
mysql80 服务正在停止.
mysql80 服务已成功停止。

D:\mysql-8.0.24-winx64>net start mysql80
mysql80 服务正在启动 .
mysql80 服务已经启动成功。

D:\mysql-8.0.24-winx64>

mysql> show variables like '%slow%';
+---------------------------+------------------------------------------------------+
| Variable_name | Value |
+---------------------------+------------------------------------------------------+
| log_slow_admin_statements | OFF |
| log_slow_extra | OFF |
| log_slow_slave_statements | OFF |
| slow_launch_time | 2 |
| slow_query_log | OFF |
| slow_query_log_file | D:\mysql-8.0.24-winx64\data\UP57VFMG5AH8GDS-slow.log |
+---------------------------+------------------------------------------------------+
6 rows in set, 1 warning (0.01 sec)

mysql>

重启就不生效了。。。。。。。。