您现在的位置是：首页 > 数据库

当前栏目

MySQL查看bin-log日志

mysql log 日志查看 bin

2023-09-14 09:13:13 时间

MySQL查看bin-log日志

1.开启`bin-log`

如果需要查看mysql的bin-log，前提是系统必须设置开启了bin-log，否则是无法查看的。

查看bin-log是否开启

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_builtin_as_identified_by_password  | OFF                                                              |
| log_error                              | D:\Program Files\MySQL\MySQL Server 5.7\data\ICOS-20180710CX.err |
| log_error_verbosity                    | 3                                                                |
| log_output                             | FILE                                                             |
| log_queries_not_using_indexes          | OFF                                                              |
| log_slave_updates                      | OFF                                                              |
| log_slow_admin_statements              | OFF                                                              |
| log_slow_slave_statements              | OFF                                                              |
| log_statements_unsafe_for_binlog       | ON                                                               |
| log_syslog                             | ON                                                               |
| log_syslog_tag                         |                                                                  |
| log_throttle_queries_not_using_indexes | 0                                                                |
| log_timestamps                         | UTC                                                              |
| log_warnings                           | 2                                                                |
+----------------------------------------+------------------------------------------------------------------+
19 rows in set, 1 warning (0.00 sec)

添加内容如下

# Binary Logging.
log-bin = mysql-bin

修改之后重启mysql服务，再次查看，得到结果如下：

mysql> show variables like 'log%';
+----------------------------------------+--------------------------------------------------------------+
| Variable_name                          | Value                                                        |
+----------------------------------------+--------------------------------------------------------------+
| log_bin                                | ON                                                           |
| log_bin_basename                       | D:\Program Files\MySQL\MySQL Server 5.7\data\mysql-bin       |
| log_bin_index                          | D:\Program Files\MySQL\MySQL Server 5.7\data\mysql-bin.index |
| log_bin_trust_function_creators        | OFF                                                          |
| log_bin_use_v1_row_events              | OFF                                                          |
| log_builtin_as_identified_by_password  | OFF                                                          |
| log_error                              | .\mysql-error.err                                            |
| log_error_verbosity                    | 3                                                            |
| log_output                             | FILE                                                         |
| log_queries_not_using_indexes          | OFF                                                          |
| log_slave_updates                      | OFF                                                          |
| log_slow_admin_statements              | OFF                                                          |
| log_slow_slave_statements              | OFF                                                          |
| log_statements_unsafe_for_binlog       | ON                                                           |
| log_syslog                             | ON                                                           |
| log_syslog_tag                         |                                                              |
| log_throttle_queries_not_using_indexes | 0                                                            |
| log_timestamps                         | UTC                                                          |
| log_warnings                           | 2                                                            |
+----------------------------------------+--------------------------------------------------------------+
19 rows in set, 1 warning (0.00 sec)

这样就可以D:\Program Files\MySQL\MySQL Server 5.7\data\mysql-bin中查看自己的查询日志了。

2.实战

往表lawson中插入数据

mysql> insert into lawson values(1,'lawson');
Query OK, 1 row affected (0.11 sec)

mysql> insert into lawson values(2,'ting');
Query OK, 1 row affected (0.08 sec)


mysql> select * from lawson;
+------+--------+
| id   | name   |
+------+--------+
|    1 | lawson |
|    2 | ting   |
+------+--------+
2 rows in set (0.00 sec)

mysql> drop table lawson;
Query OK, 0 rows affected (0.38 sec)

查看mysql的 bin-log 日志
注意这个命令很重要。

mysql> show binlog events in 'mysql-bin.000001';
+------------------+-----+----------------+-----------+-------------+------------------------------------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                                             |
+------------------+-----+----------------+-----------+-------------+------------------------------------------------------------------+
| mysql-bin.000001 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.22-log, Binlog ver: 4                            |
| mysql-bin.000001 | 123 | Previous_gtids |         1 |         154 |                                                                  |
| mysql-bin.000001 | 154 | Anonymous_Gtid |         1 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                             |
| mysql-bin.000001 | 219 | Query          |         1 |         298 | BEGIN                                                            |
| mysql-bin.000001 | 298 | Table_map      |         1 |         357 | table_id: 108 (insidemysql.lawson)                               |
| mysql-bin.000001 | 357 | Write_rows     |         1 |         404 | table_id: 108 flags: STMT_END_F                                  |
| mysql-bin.000001 | 404 | Xid            |         1 |         435 | COMMIT /* xid=11 */                                              |
| mysql-bin.000001 | 435 | Anonymous_Gtid |         1 |         500 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                             |
| mysql-bin.000001 | 500 | Query          |         1 |         579 | BEGIN                                                            |
| mysql-bin.000001 | 579 | Table_map      |         1 |         638 | table_id: 108 (insidemysql.lawson)                               |
| mysql-bin.000001 | 638 | Write_rows     |         1 |         683 | table_id: 108 flags: STMT_END_F                                  |
| mysql-bin.000001 | 683 | Xid            |         1 |         714 | COMMIT /* xid=12 */                                              |
| mysql-bin.000001 | 714 | Anonymous_Gtid |         1 |         779 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                             |
| mysql-bin.000001 | 779 | Query          |         1 |         912 | use `insidemysql`; DROP TABLE `lawson` /* generated by server */ |
+------------------+-----+----------------+-----------+-------------+------------------------------------------------------------------+
14 rows in set (0.00 sec)

通过bin-log可以看到我们执行了什么语句【但是我有一处不明白，为什么插入数据的操作，没有记录到bin-log中呢？】

3.SQL详解

这里详解一下

SHOW BINLOG EVENTS
[IN 'log_name']
[FROM pos]
[LIMIT [offset,] row_count]

Shows the events in the binary log. If you do not specify ‘log_name’, the first binary log is displayed.

各个参数都很好理解，我这里给个例子，估计大家就很明白了。

mysql> show binlog events in 'mysql-bin.000001' from 779 limit 1;
+------------------+-----+------------+-----------+-------------+------------------------------------------------------------------+
| Log_name         | Pos | Event_type | Server_id | End_log_pos | Info                                                             |
+------------------+-----+------------+-----------+-------------+------------------------------------------------------------------+
| mysql-bin.000001 | 779 | Query      |         1 |         912 | use `insidemysql`; DROP TABLE `lawson` /* generated by server */ |
+------------------+-----+------------+-----------+-------------+------------------------------------------------------------------+
1 row in set (0.00 sec)

各个参数意思如下：

Log_name:The name of the file that is being listed.
Pos：The position at which the event occurs.
Event_type：An identifier that describes the event type.
Server_id：The server ID of the server on which the event originated.
End_log_pos：The position at which the next event begins, which is equal to Pos plus the size of the event.
Info：More detailed information about the event type. The format of this information depends on the event type.

4. 查看完整binlog

4.1 问题

查看 binlog 的日志，但是因为 binlog 日志是二进制形式的，而不能直接查看，所以需要使用mysql 自带的命令解析 binlog。

4.2 原因

binlog 是二进制的，无法直接查看。

4.3 解决办法

使用 mysqlbinlog 命令。
执行命令前：

D:\Program Files\MySQL\MySQL Server 5.7\data>dir
 驱动器 D 中的卷是 Software
 卷的序列号是 C2E2-A20C

 D:\Program Files\MySQL\MySQL Server 5.7\data 的目录

2018/12/05  17:01    <DIR>          .
2018/12/05  17:01    <DIR>          ..
2018/11/19  18:23             9,035 002bin.sql
2018/11/19  11:35    <DIR>          appmetadatadb
2018/09/24  20:37                56 auto.cnf
2018/10/29  17:33    <DIR>          bas
2018/11/05  14:20    <DIR>          cam
2018/12/05  22:02    <DIR>          datamart
2018/12/05  17:17       660,065,474 dim_shop.sql
2018/12/05  17:07             3,508 dim_sub_channel_type.sql
2018/10/04  15:01    <DIR>          employees
2018/11/05  13:42    <DIR>          ems
2018/11/05  11:34    <DIR>          extractdb
2018/12/04  10:09           114,688 first.ibd
2018/12/05  22:02       146,800,640 ibdata1
2018/11/28  11:22        12,582,912 ibtmp1
2018/11/28  11:22               393 ib_buffer_pool
2018/12/05  22:02        50,331,648 ib_logfile0
2018/12/05  22:02        50,331,648 ib_logfile1
2018/11/05  18:57           125,997 ICOS-20180710CX.err
2018/11/28  11:22                 5 ICOS-20180710CX.pid
2018/12/04  10:14    <DIR>          insidemysql
2018/09/24  20:37    <DIR>          mysql
2018/11/11  17:35            11,207 mysql-bin.000001
2018/11/11  18:11               177 mysql-bin.000002
2018/11/18  22:56             7,525 mysql-bin.000003
2018/11/19  19:25             7,287 mysql-bin.000004
2018/11/19  19:57             1,210 mysql-bin.000005
2018/11/28  11:09             2,165 mysql-bin.000006
2018/11/28  11:13               177 mysql-bin.000007
2018/11/28  11:18               177 mysql-bin.000008
2018/11/28  11:22               177 mysql-bin.000009
2018/12/05  22:02        26,461,143 mysql-bin.000010
2018/11/28  11:22               190 mysql-bin.index
2018/12/05  17:25            86,028 mysql-error.err
2018/09/24  20:37    <DIR>          performance_schema
2018/11/23  11:24    <DIR>          rollupdb
2018/09/26  11:12    <DIR>          stagedb
2018/09/24  20:37    <DIR>          sys
              24 个文件    946,943,467 字节
              15 个目录 67,366,494,208 可用字节

执行如下命令：
mysqlbinlog mysql-bin.000001 > mysql-bin.000001.txt
执行命令后，再次查看文件路径：

D:\Program Files\MySQL\MySQL Server 5.7\data>dir
 驱动器 D 中的卷是 Software
 卷的序列号是 C2E2-A20C

 D:\Program Files\MySQL\MySQL Server 5.7\data 的目录

2018/12/05  23:07    <DIR>          .
2018/12/05  23:07    <DIR>          ..
2018/11/19  18:23             9,035 002bin.sql
2018/11/19  11:35    <DIR>          appmetadatadb
2018/09/24  20:37                56 auto.cnf
2018/10/29  17:33    <DIR>          bas
2018/11/05  14:20    <DIR>          cam
2018/12/05  22:02    <DIR>          datamart
2018/12/05  17:17       660,065,474 dim_shop.sql
2018/12/05  17:07             3,508 dim_sub_channel_type.sql
2018/10/04  15:01    <DIR>          employees
2018/11/05  13:42    <DIR>          ems
2018/11/05  11:34    <DIR>          extractdb
2018/12/04  10:09           114,688 first.ibd
2018/12/05  22:02       146,800,640 ibdata1
2018/11/28  11:22        12,582,912 ibtmp1
2018/11/28  11:22               393 ib_buffer_pool
2018/12/05  22:02        50,331,648 ib_logfile0
2018/12/05  22:02        50,331,648 ib_logfile1
2018/11/05  18:57           125,997 ICOS-20180710CX.err
2018/11/28  11:22                 5 ICOS-20180710CX.pid
2018/12/04  10:14    <DIR>          insidemysql
2018/09/24  20:37    <DIR>          mysql
2018/11/11  17:35            11,207 mysql-bin.000001
2018/12/05  23:07            18,989 mysql-bin.000001.txt
2018/11/11  18:11               177 mysql-bin.000002
2018/11/18  22:56             7,525 mysql-bin.000003
2018/11/19  19:25             7,287 mysql-bin.000004
2018/11/19  19:57             1,210 mysql-bin.000005
2018/11/28  11:09             2,165 mysql-bin.000006
2018/11/28  11:13               177 mysql-bin.000007
2018/11/28  11:18               177 mysql-bin.000008
2018/11/28  11:22               177 mysql-bin.000009
2018/12/05  22:02        26,461,143 mysql-bin.000010
2018/11/28  11:22               190 mysql-bin.index
2018/12/05  17:25            86,028 mysql-error.err
2018/09/24  20:37    <DIR>          performance_schema
2018/11/23  11:24    <DIR>          rollupdb
2018/09/26  11:12    <DIR>          stagedb
2018/09/24  20:37    <DIR>          sys
              25 个文件    946,962,456 字节
              15 个目录 67,366,473,728 可用字节

5. 参考文章

mysql-refman-5.7

猜你喜欢

H5微信支付、支付宝支付
Oracle 视图 DBA_PART_KEY_COLUMNS 官方解释，作用，如何使用详细说明
Spring MVC各组件近距离接触--下--04
突破极限，极致体验，展现Wu Linux的独特之处（wulinux）
Java中的快捷键大全「建议收藏」
助力AIoT，雅观科技发布空间智能化操作系统
Mac下安装配置mongodb并创建用户的方法
利用Redis构建设计性时间失效（redis设计时间失效）
Linux网卡快速启动指南（linux网卡启动）
php缩略图实现函数代码
hgame-week1-writeup
SQL Server：实现数据处理的功能强大器（sqlserver的功能）
为何开源的成功取决于同理心？
使用Redis时，账号有必要吗（使用redis需要账号吗）

相关主题

MySQL事务详解
MySQL查询优化
MySQL插入数据
MySQL 事务机制
MySQL 事件EVENT
Mysql：Redo Log
【mysql】mysql批量修改
MySQL 事务和锁
MySql中的undo.log

zl程序教程

当前栏目

MySQL查看bin-log日志

MySQL查看bin-log日志

1.开启`bin-log`

2.实战

3.SQL详解

4. 查看完整binlog

4.1 问题

4.2 原因

4.3 解决办法

5. 参考文章

相关文章

当前栏目

MySQL查看bin-log日志

MySQL查看bin-log日志

1.开启bin-log

2.实战

3.SQL详解

4. 查看完整binlog

4.1 问题

4.2 原因

4.3 解决办法

5. 参考文章

相关文章

1.开启`bin-log`