正文

mysql二进制日志详解(代码片段)

jkin  jkin  2023-02-04  319

关键词：

一、什么是二进制日志

二进制日志主要记录mysql数据库的变化，二进制日志包含所有更新了数据或者潜在更新了数据（如没有匹配到任何行的delete语句），语句以时间的形式保存，描述了数据的更改。二进制日志还包含执行每个更新数据库语句的时间信息，使用二进制日志的主要目的是最大可能的恢复数据库。因为二进制日志包含备份后进行的所有更新，不记录没有修改任何数据的语句。

二、开启和设置二进制日志

1、默认情况下二进制日志是关闭的，通过配置文件来启动和设置二进制日志。修改my.cng，插入如下内容，然后重启mysqld服务。

server-id = 1                                       # mysql5.7必须加，否则mysql服务启动报错
log-bin = /data/3306/tmp/binlog/mysql-bin           #路径及命名，默认在data下
expire_logs_days = 10                               # 过期时间,二进制文件自动删除的天数,0代表不删除
max_binlog_size = 100M                              # 单个日志文件大小

2、通过show variables like ‘log_bin%‘查看二进制日志设置

mysql> show variables like ‘log_bin%‘;
+---------------------------------+---------------------------------------+
| Variable_name                   | Value                                 |
+---------------------------------+---------------------------------------+
| log_bin                         | ON                                    |
| log_bin_basename                | /data/3306/tmp/binlog/mysql-bin       |
| log_bin_index                   | /data/3306/tmp/binlog/mysql-bin.index |
| log_bin_trust_function_creators | OFF                                   |
| log_bin_use_v1_row_events       | OFF                                   |
+---------------------------------+---------------------------------------+

3、查看当前服务器所有的二进制日志文件 show binary logs / show master logs

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       732 |
+------------------+-----------+

4、查看当前二进制日志状态 show master status

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      732 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

三、二进制日志切换方法

每次重启MySQL服务也会生成一个新的二进制日志文件，相当于二进制日志切换。切换二进制日志时，你会看到这些number会不断递增。另外，除了这些二进制日志文件外，你会看到还生成了一个DB-Server-bin.index的文件，这个文件中存储所有二进制日志文件的清单又称为二进制文件的索引。

执行 flush logs 可以刷新切换二进制文件

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      732 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

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

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

四、二进制文件的查看

1、使用show binlog events 可以获取当前及指定日志 show binlog events ‘mysql-bin.000002‘ from 639(只产看639)

mysql> show binlog events;
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000001 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.24-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 |         291 | BEGIN                                 |
| mysql-bin.000001 | 291 | Table_map      |         1 |         350 | table_id: 108 (test.t_count)          |
| mysql-bin.000001 | 350 | Write_rows     |         1 |         412 | table_id: 108 flags: STMT_END_F       |
| mysql-bin.000001 | 412 | Xid            |         1 |         443 | COMMIT /* xid=5 */                    |
| mysql-bin.000001 | 443 | Anonymous_Gtid |         1 |         508 | SET @@SESSION.GTID_NEXT= ‘ANONYMOUS‘  |
| mysql-bin.000001 | 508 | Query          |         1 |         580 | BEGIN                                 |
| mysql-bin.000001 | 580 | Table_map      |         1 |         639 | table_id: 108 (test.t_count)          |
| mysql-bin.000001 | 639 | Write_rows     |         1 |         701 | table_id: 108 flags: STMT_END_F       |
| mysql-bin.000001 | 701 | Xid            |         1 |         732 | COMMIT /* xid=7 */                    |
| mysql-bin.000001 | 732 | Rotate         |         1 |         779 | mysql-bin.000002;pos=4                |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+

2、打印二进制日志到一个明文文件，该文件记录的更新了数据的sql，但是在5.7以上已经被加密

mysqlbinlog /data/3306/tmp/binlog/mysql-bin.000001 > mysql-bin.log                                  # 打印日志文件
mysqlbinlog --base64-output=decode-rows -v /data/3306/tmp/binlog/mysql-bin.000001 > binlog.sql      # 解密文件

#  截取的部分日志内容，可以看到insert语句
BEGIN
/*!*/;
# at 580
#181215 11:12:03 server id 1  end_log_pos 639 CRC32 0xc66c75c3  Table_map: `test`.`t_count` mapped to number 108
# at 639
#181215 11:12:03 server id 1  end_log_pos 701 CRC32 0xf8a50341  Write_rows: table id 108 flags: STMT_END_F
### INSERT INTO `test`.`t_count`
### SET
###   @1=533500356
###   @2=‘W3V‘
###   @3=‘fg8rVMuT0‘
###   @4=‘2018:08:28‘
###   @5=‘2016-08-12 15:36:09‘
# at 701
#181215 11:12:03 server id 1  end_log_pos 732 CRC32 0xc42a0c0d  Xid = 7
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= ‘AUTOMATIC‘ /* added by mysqlbinlog */ /*!*/;
DELIMITER ;

五、二进制日志的删除

1、reset mstaer 可以删除所有日志文件（不存在主从复制关系），执行完该语句，所有二进制日志被删除。mysql重新创建二进制日志，编号从000001开始

2、purge master logs语句删除指定日志文件

purge binary logs to ‘DB-Server-bin.000002‘;                      # 删除该日志文件之前的所有日志文件

purge binary logs before ‘2017-03-10 10:10:00‘;                   # 清除该时间点以前的所有日志文件

purge master logs before date_sub( now( ), interval 7 day);       # 清除7天前的日志文件

六、使用二进制日志恢复数据库

如果开启了二进制日志，出现了数据丢失，可以通过二进制日志恢复数据库，语法如下

mysqlbinlog [option] filename | mysql -u user -p passwd

option的参数主要有两个 --start-datetime --stop-datetime 和 start-position --stop-position ,前者指定恢复的时间点，后者指定恢复的位置（位置指的是二进制文件中 # at 580 580就是位置）

mysqlbinlog --start-position="291" --stop-position="439" /data/3306/tmp/binlog/mysql-bin.000001 | mysql -uroot -p111111

mysql的备份与恢复详解(代码片段)

...当前的生产环境。同时为了保证恢复的完整性，建议开启二进制日志功能，二进制日志文件给恢复工作带来了很大的灵活性，可以基于时间点或位置进行恢复，考虑到数据库性能，可以将二进制日志文件保存在其他安全的硬盘中... 查看详情

mysql主从同步详解(代码片段)

...个或多个其它服务器充当从服务器。主服务器将更新写入二进制日志，并维护文件的一个索引以跟踪日志循环。这些日志可以记录发送到从查看详情

mysql主从同步详解(代码片段)

...个或多个其它服务器充当从服务器。主服务器将更新写入二进制日志，并维护文件的一个索引以跟踪日志循环。这些日志可以记录发送到从查看详情

mysql状态变量详解(代码片段)

...MySQL服务器失败的次数 Binlog_cache_disk_use0 使用临时二进制日志缓存但超过binlog_cache_size值并使用临时文件存储来自事务的语句的事务数 Binlog_cache_use0 使用二进制日志缓存的事务数Binlog_stmt_cache_disk_use0 使用二进... 查看详情

（4.6）mysql备份还原——深入解析二进制日志binlog二进制格式详解(代码片段)

...如果是用行的话，记录全部的记录、最小的记录、不记录二进制　　（3）binlog_rows_query_log_events=on--(on,off)　　　　如果打开以后会记录整个语句详细的操作，如果不打开，只会记录这个操作事件（比如是更新还是插入，但无具体... 查看详情

mysql复制详解(代码片段)

...基于行的复制(Row-BasedReplication)五、复制所用到的文件1、二进制文件2、中继日志文件3、备库连接到主库的信息4、前备库复制的二进制日志和中继日志坐标六、发送复制事件到其它slave七、复制过滤(ReplicationFilters)八、复制的常用... 查看详情

linux学习-mysql二进制日志(代码片段)

二进制日志formatstatementrowmixed|sql_log_bin|ON|#关闭二进制日志mysql>setsql_log_bin=0;QueryOK,0rowsaffected(0.00sec)#查看二进制日志内容mysql>showbinlogeventsin'mysql-bin.000004';+------------------& 查看详情

mysql日志管理(代码片段)

...查询的信息慢查询日志记录执行时间超过指定时间的操作二进制日志又称binlog日志，以二进制文件的方式记录数据库中除select以外的操作中继日志备库将主库的二进制日志复制到自己的中继日志中，从而在本地中进行重... 查看详情

mysql日志(代码片段)

...xff0c;建议首先查看此日志。showvariableslike"log_error";2.二进制日志二进制日志（BINLOG）记查看详情

mysql-日志(代码片段)

...线图】获取学习路线图。文章目录前言一、日志简介二、二进制日志1、启动和设置二进制日志2、查看二进制日志3、删除二进制日志4、使用二进制日志还原数据库5、查看详情

mysql-日志(代码片段)

mysql程序只mysqlbinlog详解(代码片段)

mysqlbinlog命令详解mysqlbinlog用于处理二进制的日志文件，如果想要查看这些日志文件的文本内容，就需要使用mysqlbinlog工具用法：mysqlbinlog[options]log-files参数详解：-?,--help#显示帮助信息并退出--base64-output=name#binlog输出语句的base64解... 查看详情

linux12-mysql数据库--＞12日志管理(代码片段)

...g_error_verbosity参数二、一般查询日志1、一般查询日志三、二进制日志1、二进制日志简介2、不要混淆以下三种日志：3、开启binlog日志的好处4、二进制日志工作模式1、查查看详情

mysql日志分类：错误日志二进制日志查询日志慢查询日志(代码片段)

MySQL性能强劲，是目前使用最广泛的数据库之一，以 MySQL为学习原型也方便之后掌握其他数据库，下面就给大家全面讲解下MySQL8.0的新特性，从零基础到高阶一站式学习，结合实际案例让大家有所收获！▼M... 查看详情

mysql运维日志--错误日志二进制日志查询日志慢查询日志(代码片段)

文章目录1.错误日志2.二进制日志2.1介绍2.2格式2.3查看2.4删除3.查询日志4.慢查询日志（可以记录用时较长的SQL）4.1开启慢查询日志4.2慢查询日志测试4.3补充说明1.错误日志错误日志是MySQL中最重要的日志之一，它记录了... 查看详情

mysql-bin查看_mysqlbinlog数据查看的方法详解(代码片段)

...登录到mysql查看binlog用mysqlbinlog工具查看binlog介绍binlog,即二进制日志,它记录了数据库上的所有改变.改变数据库的SQL语句执行结束时,将在binlog的末尾写入一条记录,同时通知语句解析器,语句执行完毕.binlog格式基于语句,无法保证所... 查看详情

mysql5.6二进制软件包编译安装详解(代码片段)

一、软件环境[[email protected]~]#uname-r3.10.0-862.el7.x86_64[[email protected]~]#cat/etc/redhat-releaseCentOSLinuxrelease7.5.1804(Core)二、安装部署过程详解MySQL安装3种方式：1>rpm包安装应用文件默认安装在/usr/local目录下2> 查看详情