AI 助理
文档备案控制台
开发者社区 数据库 文章 正文

MySQL二进制日志操作

2017-11-12 996
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
RDS AI 助手，专业版
RDS Agent（兼容OpenClaw），2核4GB
RDS MySQL DuckDB 分析主实例，基础系列 4核8GB
简介：

二进制日志

概念

  • 记录对数据发生或潜在发生更改的SQL语句，并且是以二进制格式保存的日志

使用用途

  • 查看数据库变更历史

  • 数据库增量备份

  • 数据库灾难恢复

  • MySQL复制（主从、主主复制）


二进制日志性能影响

  • 日志即影响MySQL性能又占用大量磁盘空间。因此，往往需要做采样分析时才会打开

  • 即使做采样分析，也最好仅在一台测试机上开启

  • 二进制日志由于用途广泛，大多数情况下会开启。需要制定合理的备份计划和管理策略


开启二进制日志

方法一：不重启修改二进制日志配置

  • SET @@global.log_bin=1;

  • SET @@global.binlog_szie=37268;（单位：bytes）

  • 其他参数可以通过以下命令查阅：

    SHOW VARIABLES LIKE '%bin%';

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
mysql> show variables like  '%bin%' ;
+-----------------------------------------+------------------------------------------+
| Variable_name                           | Value                                    |
+-----------------------------------------+------------------------------------------+
| bind_address                            | *                                        |
| binlog_cache_size                       | 32768                                    |
| binlog_checksum                         | CRC32                                    |
| binlog_direct_non_transactional_updates | OFF                                      |
| binlog_error_action                     | IGNORE_ERROR                             |
| binlog_format                           | STATEMENT                                |
| binlog_gtid_simple_recovery             | OFF                                      |
| 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                                    |
| binlogging_impossible_mode              | IGNORE_ERROR                             |
| innodb_api_enable_binlog                | OFF                                      |
| innodb_locks_unsafe_for_binlog          | OFF                                      |
| log_bin                                 | ON                                       |
| log_bin_basename                        |  /data/3306/mysql-bin/mysql-bin        |
| log_bin_index                           |  /data/3306/mysql-bin/mysql-bin .index |
| log_bin_trust_function_creators         | OFF                                      |
| log_bin_use_v1_row_events               | OFF                                      |
| max_binlog_cache_size                   | 18446744073709547520                     |
| max_binlog_size                         | 1073741824                               |
| max_binlog_stmt_cache_size              | 18446744073709547520                     |
| simplified_binlog_gtid_recovery         | OFF                                      |
| sql_log_bin                             | ON                                       |
| sync_binlog                             | 0                                        |
+-----------------------------------------+------------------------------------------+
26 rows  in  set  (0.00 sec)


常用二进制日志相关配置参数

  • log-bin=

#开启并指定二进制日志保存路劲及文件名，不设置则使用默认值。默认存放位置为数据库文件所目录下，名称为hostname-bin.xxxxx

  • max-binlog-size=500m

#设置单个二进制日志文件的最大值，默认1G，最大1G


  • binlog-do-db与binlog-ignore-db

#指定二进制日志文件记录哪些数据库操作


  • binlog-cache-size=100m

#设置二进制日志缓存大小


  • sync-binlog=N

#每隔N秒将缓存中的二进制日志记录写回硬盘。默认为0。不过，你经常会陷入group commit函数与I/O之间二选一的矛盾。如果在replication环境中，由于考虑到耐久性和一致性，则需要设置为1。同时，还需要设置innodb_flush_log_at_trx_commit=1以及innodb-support-ax=1（默认已开启）


暂停二进制日志

  • SET sql_log_bin={0|1};


查看二进制日志

  • mysqlbinlog host2-bin.000001


创建一个数据库并做相应修改，并查看二进制日志的变化

mysql> create database t1;

mysql> create table tt1(id int,name varchar(20),birthday date);

mysql> insert into tt1(id,name,birthday) values('1','demi','1991-10-12');

mysql> insert into tt1(id,name,birthday) values('2','hoai','1992-02-20');

1
2
3
4
5
6
7
8
9
10
mysql>  select  * from tt1;
+------+------+------------+
|  id    | name | birthday   |
+------+------+------------+
|    1 | demi | 1991-10-12 |
|    2 | hoai | 1992-02-20 |
|    3 | wiss | 1991-07-14 |
|    4 | kime | 1993-06-18 |
+------+------+------------+
4 rows  in  set  (0.00 sec)

mysql> update tt1 set birthday='1991-06-28' where id=4;

1
2
3
4
5
6
7
8
9
10
mysql>  select  * from tt1;
+------+------+------------+
|  id    | name | birthday   |
+------+------+------------+
|    1 | demi | 1991-10-12 |
|    2 | hoai | 1992-02-20 |
|    3 | wiss | 1991-07-14 |
|    4 | kime | 1991-06-28 |
+------+------+------------+
4 rows  in  set  (0.00 sec)


使用mysqlbinlog命令查看二进制日志的变化

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
[root@slave1 ~] # mysqlbinlog /data/3306/mysql-bin/mysql-bin.000011
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#160314 18:34:51 server id 3  end_log_pos 120 CRC32 0xfd6a32fe    Start: binlog v 4, server v 5.6.27-log created 160314 18:34:51 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
y5PmVg8DAAAAdAAAAHgAAAABAAQANS42LjI3LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAADLk+ZWEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAf4y
av0=
'/*!*/;
# at 120
#160314 21:03:56 server id 3  end_log_pos 208 CRC32 0x12f2847a    Query   thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1457960636/*!*/;
SET @@session.pseudo_thread_id=4/*!*/;
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 utf8 * // *!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create database t1
/*!*/;
# at 208
#160314 21:08:36 server id 3  end_log_pos 333 CRC32 0x806b0266    Query   thread_id=4 exec_time=0 error_code=0
use `t1`/*!*/;
SET TIMESTAMP=1457960916/*!*/;
create table tt1( id  int,name varchar(20),birthday  date )
/*!*/;
# at 333
#160314 21:10:05 server id 3  end_log_pos 408 CRC32 0xc4950130    Query   thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1457961005/*!*/;
BEGIN
/*!*/;
# at 408
#160314 21:10:05 server id 3  end_log_pos 543 CRC32 0xab0bc88f    Query   thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1457961005/*!*/;
insert into tt1( id ,name,birthday) values( '1' , 'demi' , '1991-10-12' )
/*!*/;
# at 543
#160314 21:10:05 server id 3  end_log_pos 574 CRC32 0xacd9a973    Xid = 27
COMMIT/*!*/;
# at 574
#160314 21:10:40 server id 3  end_log_pos 649 CRC32 0xb6e8ef83    Query   thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1457961040/*!*/;
BEGIN
/*!*/;
# at 649
#160314 21:10:40 server id 3  end_log_pos 784 CRC32 0xef33bae2    Query   thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1457961040/*!*/;
insert into tt1( id ,name,birthday) values( '2' , 'hoai' , '1992-02-20' )
/*!*/;
# at 784
#160314 21:10:40 server id 3  end_log_pos 815 CRC32 0xc1ec540f    Xid = 29
COMMIT/*!*/;
# at 815
#160314 21:11:00 server id 3  end_log_pos 890 CRC32 0xb4e3c45e    Query   thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1457961060/*!*/;
BEGIN
/*!*/;
# at 890
#160314 21:11:00 server id 3  end_log_pos 1025 CRC32 0xf8584b1d   Query   thread_id=4 exec_time=0    error_code=0
SET TIMESTAMP=1457961060/*!*/;
insert into tt1( id ,name,birthday) values( '3' , 'wiss' , '1991-07-14' )
/*!*/;
# at 1025
#160314 21:11:00 server id 3  end_log_pos 1056 CRC32 0xe75877f8   Xid = 30
COMMIT/*!*/;
# at 1056
#160314 21:11:33 server id 3  end_log_pos 1131 CRC32 0x12b7b345   Query   thread_id=4 exec_time=0    error_code=0
SET TIMESTAMP=1457961093/*!*/;
BEGIN
/*!*/;
# at 1131
#160314 21:11:33 server id 3  end_log_pos 1266 CRC32 0xab6f89cc   Query   thread_id=4 exec_time=0    error_code=0
SET TIMESTAMP=1457961093/*!*/;
insert into tt1( id ,name,birthday) values( '4' , 'kime' , '1993-06-18' )
/*!*/;
# at 1266
#160314 21:11:33 server id 3  end_log_pos 1297 CRC32 0x2e4dbdb4   Xid = 31
COMMIT/*!*/;
# at 1297
#160314 21:14:04 server id 3  end_log_pos 1372 CRC32 0x571a3dda   Query   thread_id=4 exec_time=0    error_code=0
SET TIMESTAMP=1457961244/*!*/;
BEGIN
/*!*/;
# at 1372
#160314 21:14:04 server id 3  end_log_pos 1489 CRC32 0xaee0efb9   Query   thread_id=4 exec_time=0    error_code=0
SET TIMESTAMP=1457961244/*!*/;
update tt1  set  birthday= '1991-06-28'  where  id =4
/*!*/;
# at 1489
#160314 21:14:04 server id 3  end_log_pos 1520 CRC32 0xa7faed24   Xid = 33
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;



修改二进制日志记录配置为ROW方式（ROW记录的二进制信息最为完整）

mysql> set @@global.binlog_format=ROW;

mysql> set binlog_format=ROW;


1
2
3
4
5
6
7
mysql> show variables like  'binlog_format' ;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row  in  set  (0.00 sec)


修改mysql数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> update tt1  set  birthday= '1992-12-20'  where  id =1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql>  select  * from tt1;
+------+------+------------+
|  id    | name | birthday   |
+------+------+------------+
|    1 | demi | 1992-12-20 |
|    2 | hoai | 1992-02-20 |
|    3 | wiss | 1991-07-14 |
|    4 | kime | 1991-06-28 |
+------+------+------------+
4 rows  in  set  (0.00 sec)


查看二进制日志的变化

[root@slave1 ~]# mysqlbinlog /data/3306/mysql-bin/mysql-bin.000011

1
2
3
4
5
6
7
8
9
10
11
12
13
BINLOG '
07zmVhMDAAAAMAAAAGYGAAAAAEcAAAAAAAEAAnQxAAN0dDEAAwMPCgI8AAe2N8 /Z
07zmVh8DAAAAPgAAAKQGAAAAAEcAAAAAAAEAAgAD ///4AQAAAARkZW1pTI8P +AEAAAAEZGVtaZSR
D3vrjpU=
'/*!*/;
# at 1700
#160314 21:29:55 server id 3  end_log_pos 1731 CRC32 0xff29a00b   Xid = 41
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;



[root@slave1 ~]# mysqlbinlog /data/3306/mysql-bin/mysql-bin.000011 -v

#加-v解析二进制日志


本文转自 HMLinux 51CTO博客，原文链接:http://blog.51cto.com/7424593/1751071

文章标签：
云数据库 RDS MySQL 版
日志服务
关系型数据库
数据库
监控
MySQL
关键词：
云数据库 RDS MySQL 版二进制
mysql二进制日志服务
二进制日志服务
mysql日志服务
云数据库 RDS MySQL 版日志
相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发，帮助IT从业者们通过“阿里云Web应用上云解决方案”，了解一个企业级Web应用上云的常见架构，了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。   相关的阿里云产品：云数据库RDS MySQL 版 阿里云关系型数据库RDS（Relational Database Service）是一种稳定可靠、可弹性伸缩的在线数据库服务，提供容灾、备份、恢复、迁移等方面的全套解决方案，彻底解决数据库运维的烦恼。 了解产品详情: https://www.aliyun.com/product/rds/mysql 
科技小能手
目录
相关文章
Raymond运维
|
7月前
|
Ubuntu 关系型数据库 MySQL
MySQL二进制包安装
本文详细介绍了在多种Linux系统上通过二进制包安装MySQL 8.0和8.4版本的完整过程，涵盖用户创建、glibc版本匹配、程序解压、环境变量配置、初始化数据库及服务启动等步骤，并提供支持多发行版的一键安装脚本，助力高效部署MySQL环境。
Raymond运维
1188 4
MySQL二进制包安装
蓝易云
|
8月前
|
SQL 运维 关系型数据库
深入探讨MySQL的二进制日志（binlog）选项
总结而言，对MySQL binlogs深度理解并妥善配置对数据库运维管理至关重要；它不仅关系到系统性能优化也是实现高可靠性架构设计必须考虑因素之一。通过精心规划与周密部署可以使得该机能充分发挥作用而避免潜在风险带来影响。
蓝易云
268 6
蓝易云
|
SQL 监控 关系型数据库
MySQL日志分析：binlog、redolog、undolog三大日志的深度探讨。
数据库管理其实和写小说一样，需要规划，需要修订，也需要有能力回滚。理解这些日志的作用与优化，就像把握写作工具的使用与运用，为我们的数据库保驾护航。
蓝易云
851 23
金桔数科
|
Oracle 关系型数据库 MySQL
Oracle linux 8 二进制安装 MySQL 8.4企业版
Oracle linux 8 二进制安装 MySQL 8.4企业版
金桔数科
587 1
游客bmwhmm6u34qx6
|
SQL 运维 关系型数据库
MySQL Binlog 日志查看方法及查看内容解析
本文介绍了 MySQL 的 Binlog（二进制日志）功能及其使用方法。Binlog 记录了数据库的所有数据变更操作，如 INSERT、UPDATE 和 DELETE，对数据恢复、主从复制和审计至关重要。文章详细说明了如何开启 Binlog 功能、查看当前日志文件及内容，并解析了常见的事件类型，包括 Format_desc、Query、Table_map、Write_rows、Update_rows 和 Delete_rows 等，帮助用户掌握数据库变化历史，提升维护和排障能力。
游客bmwhmm6u34qx6
6066 6
蓝易云
|
存储 SQL 关系型数据库
mysql的undo log、redo log、bin log、buffer pool
MySQL的undo log、redo log、bin log和buffer pool是确保数据库高效、安全和可靠运行的关键组件。理解这些组件的工作原理和作用，对于优化数据库性能和保障数据安全具有重要意义。通过适当的配置和优化，可以显著提升MySQL的运行效率和数据可靠性。
蓝易云
354 16
蓝易云
|
存储 SQL 关系型数据库
mysql的undo log、redo log、bin log、buffer pool
MySQL的undo log、redo log、bin log和buffer pool是确保数据库高效、安全和可靠运行的关键组件。理解这些组件的工作原理和作用，对于优化数据库性能和保障数据安全具有重要意义。通过适当的配置和优化，可以显著提升MySQL的运行效率和数据可靠性。
蓝易云
268 4
运营研究坊
|
SQL 存储 关系型数据库
简单聊聊MySQL的三大日志（Redo Log、Binlog和Undo Log）各有什么区别
在MySQL数据库管理中，理解Redo Log（重做日志）、Binlog（二进制日志）和Undo Log（回滚日志）至关重要。Redo Log确保数据持久性和崩溃恢复；Binlog用于主从复制和数据恢复，记录逻辑操作；Undo Log支持事务的原子性和隔离性，实现回滚与MVCC。三者协同工作，保障事务ACID特性。文章还详细解析了日志写入流程及可能的异常情况，帮助深入理解数据库日志机制。
运营研究坊
1878 0
顾翔
|
8月前
|
缓存 关系型数据库 BI
使用MYSQL Report分析数据库性能（下）
使用MYSQL Report分析数据库性能
顾翔
541 158
翼龙云TG_yilongcloud
|
8月前
|
关系型数据库 MySQL 数据库
自建数据库如何迁移至RDS MySQL实例
数据库迁移是一项复杂且耗时的工程，需考虑数据安全、完整性及业务中断影响。使用阿里云数据传输服务DTS，可快速、平滑完成迁移任务，将应用停机时间降至分钟级。您还可通过全量备份自建数据库并恢复至RDS MySQL实例，实现间接迁移上云。
翼龙云TG_yilongcloud
296 1

热门文章

最新文章

  • 1
    阿里云DTS踩坑经验分享系列｜如何使用DTS进行MySQL->ClickHouse同步
  • 2
    MySQL高级篇——MVCC多版本并发控制
  • 3
    你有这么高效的MySQL版本号排序，记住我给出的原理
  • 4
    MySQL整数类型的范围
  • 5
    MySQL中英文添加用户到启动
  • 6
    MySQL 存储过程探秘
  • 7
    mysql表故障
  • 8
    MySQL数据库碎片化：隐患与解决策略
  • 9
    分布式 PostgreSQL，Citus(11.x) 效用函数
  • 10
    MySQL数据库操作中常用的一些SQL语句
  • 1
    【赵渝强老师】大数据日志采集引擎Flume
    446
  • 2
    16 倍性能提升，成本降低 98%！ 解读 SLS 向量索引架构升级改造
    626
  • 3
    防止员工泄密软件中文件访问日志管理的 Go 语言 B + 树算法
    231
  • 4
    局域网网络监控软件的设备连接日志哈希表 C++ 语言算法
    626
  • 5
    Python日志模块配置：从print到logging的优雅升级指南
    672
  • 6
    SQL日志优化策略：提升数据库日志记录效率
    404
  • 7
    程序出错瞎找？教你写“会说话”的错误日志，秒定位原因
    598
  • 8
    SLS Copilot 实践：基于 SLS 灵活构建 LLM 应用的数据基础设施
    2736
  • 9
    EventLog Analyzer：高效的Web服务器日志监控与审计解决方案
    432
  • 10
    5 个 IIS 日志记录最佳实践
    447

    • 相关课程

    更多
  • MySQL企业常见架构与调优经验分享
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 阿里云数据库产品家族及特性
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • MySQL 技术大全：开发、优化与运维实战
  • 阿里云数据库运维实战手册
  • 搭建电商项目架构连接MySQL

    • 推荐镜像

    更多
  • mysql
    • 下一篇
    阿里云正式发布 Agentic 代码安全：AI驱动的双Agent协同引擎