Mysql binlog 查看方法（1）

franket

发布于 2021-10-19 11:43:56

2.3K0

发布于 2021-10-19 11:43:56

文章被收录于专栏：技术杂记

前言

Mysql 的运维工作中，因为排错的需要，有时我们会对过往的修改操作进行查看，mysql binlog 的机制正好可以应对这类需求

这里我分享一下查看 mysql binlog 的相关基础，详细可以参考 SHOW BINLOG EVENTS 和 Utility for Processing Binary Log Files

Tip: 当前的最新版本为 Mysql 5.7 ，这里实验使用 Percona Server version: 5.6.27-76.0

概要

环境

[root@h105 ~]# cat /etc/issue
CentOS release 6.6 (Final)
Kernel \r on an \m

[root@h105 ~]# uname -a
Linux h105 2.6.32-504.el6.x86_64 #1 SMP Wed Oct 15 04:27:16 UTC 2014 x86_64 x86_64 x86_64 GNU/Linux
[root@h105 ~]#
[root@h105 ~]# mysql --version
mysql  Ver 14.14 Distrib 5.6.27-76.0, for Linux (x86_64) using  6.0
[root@h105 ~]#

前提

要查看 binlog 的详细信息，必须先打开 binlog 日志

[root@h105 ~]# grep bin /etc/my.cnf
log-bin=mysql-bin
relay-log=relay-bin
[root@h105 ~]#
[root@h105 ~]# mysql -u root -p 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.6.27-76.0-log Percona Server (GPL), Release 76.0, Revision 5498987

Copyright (c) 2009-2015 Percona LLC and/or its affiliates
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

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> show variables like '%log_bin%';
+---------------------------------+--------------------------------+
| Variable_name                   | Value                          |
+---------------------------------+--------------------------------+
| log_bin                         | ON                             |
| log_bin_basename                | /var/lib/mysql/mysql-bin       |
| log_bin_index                   | /var/lib/mysql/mysql-bin.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)

mysql>

查看本地的 binlog 日志

mysql> SHOW BINARY LOGS;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       120 |
+------------------+-----------+
1 row in set (0.00 sec)

mysql>

也可以直接列出

mysql> \! ls /var/lib/mysql/*bin*
/var/lib/mysql/mysql-bin.000001  /var/lib/mysql/mysql-bin.index
mysql> \! cat /var/lib/mysql/mysql-bin.index
./mysql-bin.000001
mysql>

本文系转载，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

数据库