MySQL 日志简介
MySQL 日志简介
MySQL 日志记录了 MySQL 数据库日常操作和错误信息.
Windows 下使用 netstat 查看 MySQL 的运行状态
Microsoft Windows [版本 10.0.17134.345]
(c) 2018 Microsoft Corporation。保留所有权利。
C:\Users\haifeng>netstat -an|find "3306"
TCP 0.0.0.0:3306 0.0.0.0:0 LISTENING
TCP [::]:3306 [::]:0 LISTENING
C:\Users\haifeng>
查看与日志相关的变量
查看与日志相关的变量
查看所有含有 log 的变量. 比如可以查到 log_error 存放于文件 c:\wamp\bin\mysql\mysql5.6.17\data\hp.err
mysql> SHOW GLOBAL VARIABLES LIKE "%log%";
+-----------------------------------------+------------------------------------------------+
| Variable_name | Value |
+-----------------------------------------+------------------------------------------------+
| back_log | 80 |
| binlog_cache_size | 32768 |
| binlog_checksum | CRC32 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_format | STATEMENT |
| binlog_max_flush_queue_time | 0 |
| binlog_order_commits | ON |
| binlog_row_image | FULL |
| binlog_rows_query_log_events | OFF |
| binlog_stmt_cache_size | 32768 |
| expire_logs_days | 0 |
| general_log | OFF |
| general_log_file | c:\wamp\bin\mysql\mysql5.6.17\data\hp.log |
| innodb_api_enable_binlog | OFF |
| innodb_flush_log_at_timeout | 1 |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_buffer_size | 8388608 |
| innodb_log_compressed_pages | ON |
| innodb_log_file_size | 50331648 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | .\ |
| innodb_mirrored_log_groups | 1 |
| innodb_online_alter_log_max_size | 134217728 |
| innodb_undo_logs | 128 |
| log_bin | OFF |
| log_bin_basename | |
| log_bin_index | |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| log_error | c:\wamp\bin\mysql\mysql5.6.17\data\hp.err |
| log_output | FILE |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| log_throttle_queries_not_using_indexes | 0 |
| log_warnings | 1 |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 1073741824 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| max_relay_log_size | 0 |
| relay_log | |
| relay_log_basename | |
| relay_log_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 |
| slow_query_log | OFF |
| slow_query_log_file | c:\wamp\bin\mysql\mysql5.6.17\data\hp-slow.log |
| sql_log_bin | ON |
| sql_log_off | OFF |
| sync_binlog | 0 |
| sync_relay_log | 10000 |
| sync_relay_log_info | 10000 |
+-----------------------------------------+------------------------------------------------+
57 rows in set (0.02 sec)
我们看到 general_log 并未开启, 其存放的位置为 c:\wamp\bin\mysql\mysql5.6.17\data\hp.log
由于未开启, 因此 data 文件夹中没有 hp.log 这个文件.
查看变量 SQL_LOG_BIN,
mysql> SHOW VARIABLES LIKE '%SQL_LOG_BIN%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin | ON |
+---------------+-------+
1 row in set (0.04 sec)
查看变量 SQL_LOG_BIN,
MySQL 的错误日志
MySQL 的错误日志
错误日志文件包含了当 mysqld 启动和停止时, 以及服务器在运行过程中发生任何严重错误时的相关信息.
错误日志一般放置于 data 文件夹, 文件名为 hostname.err, 比如这里是 hp.err.
mysql> SHOW VARIABLES LIKE 'LOG%';
+----------------------------------------+-------------------------------------------+
| Variable_name | Value |
+----------------------------------------+-------------------------------------------+
| log_bin | OFF |
| log_bin_basename | |
| log_bin_index | |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| log_error | c:\wamp\bin\mysql\mysql5.6.17\data\hp.err |
| log_output | FILE |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| log_throttle_queries_not_using_indexes | 0 |
| log_warnings | 1 |
+----------------------------------------+-------------------------------------------+
13 rows in set (0.00 sec)
mysql> SHOW VARIABLES LIKE 'log_error';
+---------------+-------------------------------------------+
| Variable_name | Value |
+---------------+-------------------------------------------+
| log_error | c:\wamp\bin\mysql\mysql5.6.17\data\hp.err |
+---------------+-------------------------------------------+
1 row in set (0.00 sec)
打开 hp.err
删除错误日志, 当然作为实验, 我们可以先将其备份一下. 删除 hp.err 后, 执行 flush-logs, 会重新创建 hp.err.
然后重新登录 MySQL, 执行 flush-logs. 该命令
实验
C:\wamp\bin\mysql\mysql5.6.17\data>del hp.err
C:\wamp\bin\mysql\mysql5.6.17\data\hp.err
拒绝访问。
以管理员身份执行 cmd.exe
再尝试删除 hp.err, 仍然是拒绝访问.
启动 services.msc, 关闭名为 wampmysqld64 的服务.
关闭后, 就没有 hp.err 文件了.
C:\wamp\bin\mysql\mysql5.6.17\data>del hp.err
找不到 C:\wamp\bin\mysql\mysql5.6.17\data\hp.err
重新启动 wampmysqld64 服务.
大小与原来备份的不同
C:\wamp\bin\mysql\mysql5.6.17\data>dir hp*.err
驱动器 C 中的卷是 Windows
卷的序列号是 92F5-811A
C:\wamp\bin\mysql\mysql5.6.17\data 的目录
2018/10/30 08:39 564,354 hp - 副本.err
2018/11/11 23:29 1,455 hp.err
2 个文件 565,809 字节
0 个目录 61,381,074,944 可用字节
使用 Vim 打开查看
MySQL 的通用查询日志
MySQL 的通用查询日志
MySQL 服务器默认情况下并没有开启通用查询日志.
我们在之前看到变量 general_log 是处于关闭状态.
mysql> SHOW VARIABLES LIKE "general_%";
+------------------+-------------------------------------------+
| Variable_name | Value |
+------------------+-------------------------------------------+
| general_log | OFF |
| general_log_file | c:\wamp\bin\mysql\mysql5.6.17\data\hp.log |
+------------------+-------------------------------------------+
2 rows in set (0.00 sec)
data 目录下没有 hp.log 文件.
C:\wamp\bin\mysql\mysql5.6.17\data>dir *.log
驱动器 C 中的卷是 Windows
卷的序列号是 92F5-811A
C:\wamp\bin\mysql\mysql5.6.17\data 的目录
找不到文件
mysql> set @@global.general_log=1;
Query OK, 0 rows affected (0.02 sec)
mysql> SHOW VARIABLES LIKE "general_%";
+------------------+-------------------------------------------+
| Variable_name | Value |
+------------------+-------------------------------------------+
| general_log | ON |
| general_log_file | c:\wamp\bin\mysql\mysql5.6.17\data\hp.log |
+------------------+-------------------------------------------+
2 rows in set (0.00 sec)
用 Vim 打开通用日志文件 hp.log
U 盘实验
对安装在 U 盘上的 MySQL 8.0.12 进行实验
前提: U 盘上已经有 MySQL 8.0.12 系统, 且已经设置好. 请参考 http://atzjg.net/admin/do/view_answers.php?qid=2162
插入 U 盘, 假设 MySQL 安装的目录位于 F:\programs\mysql-8.0.12-winx64. 检查其中的 my.ini 文件.
主要检查其中的盘符是否正确.
启动 MySQL 服务
查看 data 目录中的日志文件
其中 hp.err 是错误日志, binlog.index, binlog.000008, binlog.000009 是二进制日志.
登录后, 在 mysql> 提示符下执行 FLUSH LOGS
mysql> FLUSH LOGS;
Query OK, 0 rows affected (0.09 sec)
然后查看二进制日志文件, 是否多了一个: binlog.000010
查看二进制日志
使用 mysqlbinlog 命令查看文件 binlog.000001
网上的例子
关于 mysqlbinlog 的例子
https://www.thegeekstuff.com/2017/08/mysqlbinlog-examples/
mysql> SHOW BINARY LOGS;
+---------------+-----------+
| Log_name | File_size |
+---------------+-----------+
| binlog.000008 | 178 |
| binlog.000009 | 199 |
| binlog.000010 | 155 |
+---------------+-----------+
3 rows in set (0.00 sec)
F:\programs\mysql-8.0.12-winx64\data>mysqlbinlog binlog.000010
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#181112 10:49:45 server id 1 end_log_pos 124 CRC32 0x5f810c8d Start: binlog v 4, server v 8.0.12 created 181112 10:49:45
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
SeroWw8BAAAAeAAAAHwAAAABAAQAOC4wLjEyAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEwANAAgAAAAABAAEAAAAYAAEGggAAAAICAgCAAAACgoKKioAEjQA
CgGNDIFf
'/*!*/;
# at 124
#181112 10:49:45 server id 1 end_log_pos 155 CRC32 0xe3f06263 Previous-GTIDs
# [empty]
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*/;
mysql> desc t_1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> select * from t_1;
Empty set (0.02 sec)
mysql> INSERT INTO t_1 VALUES (1, "Mike");
Query OK, 1 row affected (0.07 sec)
使用 mysqlbinlog.exe 查看二进制日志. 比如先查看一下旧的日志 binlog.000008
再看一下 binlog.000010
PS F:\programs\mysql-8.0.12-winx64\data> mysqlbinlog binlog.000010
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#181112 10:49:45 server id 1 end_log_pos 124 CRC32 0x5f810c8d Start: binlog v 4, server v 8.0.12 created 181112 10:49:45
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
SeroWw8BAAAAeAAAAHwAAAABAAQAOC4wLjEyAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEwANAAgAAAAABAAEAAAAYAAEGggAAAAICAgCAAAACgoKKioAEjQA
CgGNDIFf
'/*!*/;
# at 124
#181112 10:49:45 server id 1 end_log_pos 155 CRC32 0xe3f06263 Previous-GTIDs
# [empty]
# at 155
#181112 13:08:59 server id 1 end_log_pos 230 CRC32 0x6f177c11 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=yes original_committed_timestamp=1541999339716629 immediate_commit_timestamp=1541999339716629 transaction_length=283
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1541999339716629 (2018-11-12 13:08:59.716629 ?D1ú±ê×?ê±??)
# immediate_commit_timestamp=1541999339716629 (2018-11-12 13:08:59.716629 ?D1ú±ê×?ê±??)
/*!80001 SET @@session.original_commit_timestamp=1541999339716629*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 230
#181112 13:08:59 server id 1 end_log_pos 305 CRC32 0xac7c2e83 Query thread_id=9 exec_time=0 error_code=0
SET TIMESTAMP=1541999339/*!*/;
SET @@session.pseudo_thread_id=9/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C gbk *//*!*/;
SET @@session.character_set_client=28,@@session.collation_connection=28,@@session.collation_server=255/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
/*!80005 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
BEGIN
/*!*/;
# at 305
#181112 13:08:59 server id 1 end_log_pos 362 CRC32 0x23ff88df Table_map: `test`.`t_1` mapped to number 67
# at 362
#181112 13:08:59 server id 1 end_log_pos 407 CRC32 0xfca07341 Write_rows: table id 67 flags: STMT_END_F
BINLOG '
6wrpWxMBAAAAOQAAAGoBAAAAAEMAAAAAAAEABHRlc3QAA3RfMQACAw8CKAADAQEAAgP8/wDfiP8j
6wrpWx4BAAAALQAAAJcBAAAAAEMAAAAAAAEAAgAC/wABAAAABE1pa2VBc6D8
'/*!*/;
# at 407
#181112 13:08:59 server id 1 end_log_pos 438 CRC32 0xe8b2877e Xid = 16
COMMIT/*!*/;
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*/;
PS F:\programs\mysql-8.0.12-winx64\data> mysqlbinlog --version
F:\programs\mysql-8.0.12-winx64\bin\mysqlbinlog.exe Ver 8.0.12 for Win64 on x86_64 (MySQL Community Server - GPL)
PS F:\programs\mysql-8.0.12-winx64\data> mysqlbinlog -d test binlog.000010 > test-events.txt
WARNING: The option --database has been used. It may filter parts of transactions, but will include the GTIDs in any case. If you want to exclude or include transactions, you should use the options --exclude-gtids or --include-gtids, respectively, instead.
使用 mysqlbinlog -d test binlog.000010 > test-events.txt 将二进制日志存储为文本文件. (选项 -d 也可以写为 --database)
在恢复数据前禁止二进制日志
当我们使用命令 mysqlbinlog 从数据崩溃中恢复时, 我们不希望将恢复的过程写进二进制日志. 因为如果这样做会陷入循环, 即在持续恢复的时候, 却一直在生成新的二进制文件. 因此此时应该禁止二进制日志的运行.
mysqlbinlog -D .\binlog.000010, 注意此时会有以下输出
也可以使用下面的选项
mysqlbinlog --disable-log-bin .\binlog.000010
PS F:\programs\mysql-8.0.12-winx64\data> mysqlbinlog -D .\binlog.000010
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!32316 SET @OLD_SQL_LOG_BIN=@@SQL_LOG_BIN, SQL_LOG_BIN=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#181112 10:49:45 server id 1 end_log_pos 124 CRC32 0x5f810c8d Start: binlog v 4, server v 8.0.12 created 181112 10:49:45
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
SeroWw8BAAAAeAAAAHwAAAABAAQAOC4wLjEyAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEwANAAgAAAAABAAEAAAAYAAEGggAAAAICAgCAAAACgoKKioAEjQA
CgGNDIFf
'/*!*/;
# at 124
#181112 10:49:45 server id 1 end_log_pos 155 CRC32 0xe3f06263 Previous-GTIDs
# [empty]
# at 155
#181112 13:08:59 server id 1 end_log_pos 230 CRC32 0x6f177c11 Anonymous_GTID last_committed=0
sequence_number=1 rbr_only=yes original_committed_timestamp=1541999339716629 immediate_commit_timestamp=1541999339716629 transaction_length=283
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1541999339716629 (2018-11-12 13:08:59.716629 ?D1ú±ê×?ê±??)
# immediate_commit_timestamp=1541999339716629 (2018-11-12 13:08:59.716629 ?D1ú±ê×?ê±??)
/*!80001 SET @@session.original_commit_timestamp=1541999339716629*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 230
#181112 13:08:59 server id 1 end_log_pos 305 CRC32 0xac7c2e83 Query thread_id=9 exec_time=0 error_code=0
SET TIMESTAMP=1541999339/*!*/;
SET @@session.pseudo_thread_id=9/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C gbk *//*!*/;
SET @@session.character_set_client=28,@@session.collation_connection=28,@@session.collation_server=255/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
/*!80005 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
BEGIN
/*!*/;
# at 305
#181112 13:08:59 server id 1 end_log_pos 362 CRC32 0x23ff88df Table_map: `test`.`t_1` mapped to number 67
# at 362
#181112 13:08:59 server id 1 end_log_pos 407 CRC32 0xfca07341 Write_rows: table id 67 flags: STMT_END_F
BINLOG '
6wrpWxMBAAAAOQAAAGoBAAAAAEMAAAAAAAEABHRlc3QAA3RfMQACAw8CKAADAQEAAgP8/wDfiP8j
6wrpWx4BAAAALQAAAJcBAAAAAEMAAAAAAAEAAgAC/wABAAAABE1pa2VBc6D8
'/*!*/;
# at 407
#181112 13:08:59 server id 1 end_log_pos 438 CRC32 0xe8b2877e Xid = 16
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!32316 SET SQL_LOG_BIN=@OLD_SQL_LOG_BIN*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
End
Thanks very much!