牛刀小试MySQL学习—The Binary Log

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
日志服务 SLS,月写入数据量 50GB 1个月
简介: mysql server有四种类型的日志 (参考的文档是5.5-en.html)The Error Log                           错误日志The General Query Log          一般查询日志The Binary Log                         二进制日志The Slow Query Log                慢查询日志 其中:           错误日志,顾名思义,包含了mysqld启动,关闭和服务器运行时发生了任何错误的信息都会写在error log上面。
mysql server有四种类型的日志 (参考的文档是5.5-en.html)
The Error Log                           错误日志
The General Query Log          一般查询日志
The Binary Log                         二进制日志
The Slow Query Log                慢查询日志
其中:
          错误日志,顾名思义,包含了mysqld启动,关闭和服务器运行时发生了任何错误的信息都会写在error log上面。如果mysqld通知
表需要自动的检查和修复,也会把记录写在error log上面
          慢查询日志,可以查看mysql服务器中运行比较慢的sql,这个在日常工作中,会经常使用到。官方的,就是日志中包含了一些超过了参数 long_query_time 指定时间的sql statement。和 min_examined_row_limit最小检查行数限制。
        (两个参数,可以使用show variables like 'long_query_time'  查看, long_query_time的最小值和默认值是0和10)
          一般查询日志,是记录了 mysqld正在做的事情,mysql服务器会记录客户端连接和不连接,以及来自客户端的每条sql statement记录信息,当然,如果你想准确得知道客户端到底传了什么东西给 mysqld,这个日志非常的有用,但是它非常影响服务器的性能,所以,一般情况下,它是不开启的
           二进制日志,包含了”events“,这些events描述了数据库的改动,例如表创建的操作和数据的改动。它也包括了一些潜在的改动(例如:用了 一条delete语句,但是什么都没有删除掉),除非使用row-based logging,否则会包含所有改动数据的sql statement。二进制日志有两个重要的用途:

           For replication(这个是mysql replication,主从服务器,以后我会写一篇日志,关于他的介绍和安装配置)。二进制日志会记录master里面所有的statement,然后 slave会接受master的二进制日志,从而根据二进制日志,修改slave服务器上的数据。

           数据库的恢复操作需要the binary log,在对一个备份进行restore之后,记录在二进制日志中的events可以re-executed,这些events可以使数据库进行基于时间点的恢复( Point-in-Time (Incremental) Recovery Using the Binary Log


基本概念和理论讲述完毕,现在自己开始实战演练:

1,开启binary log
在/etc/my.cnf(这个是我对应的测试数据库的位置)添加如下脚本:

  1. log-bin =mysql-bin
  2. log-bin-index=mysql-bin.index
复制代码
    其中,linux系统服务中存储的binary log的文件的前缀和mysql-bin一致,为mysql-bin.000001,

2,进入mysql服务器

  1. mysql> reset master;(重做master日志,日志从mysql-bin.000001日志开始)
  2. Query OK, 0 rows affected (0.00 sec)
  3. mysql> show master status;(查看master服务器,目前日志记录的位置,开始为107)
  4. +------------------+----------+--------------+------------------+
  5. | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
  6. +------------------+----------+--------------+------------------+
  7. | mysql-bin.000001 |      107 |              |                  |
  8. +------------------+----------+--------------+------------------+
  9. 1 row in set (0.00 sec)
  10. mysql> insert into user values(200);
  11. Query OK, 1 row affected (0.00 sec)
  12. mysql> insert into user values(300);
  13. Query OK, 1 row affected (0.01 sec)
  14. mysql> insert into user values(400);
  15. Query OK, 1 row affected (0.00 sec)
  16. mysql> show master status;(这里的position发生了改变,position为377)
  17. +------------------+----------+--------------+------------------+
  18. | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
  19. +------------------+----------+--------------+------------------+
  20. | mysql-bin.000001 |      377 |              |                  |
  21. +------------------+----------+--------------+------------------+
  22. 1 row in set (0.00 sec)
  23. mysql> show binlog events\G;(这里这记录了上述发生的所有事情)
  24. *************************** 1. row ***************************
  25.    Log_name: mysql-bin.000001
  26.         Pos: 4
  27. Event_type: Format_desc
  28.   Server_id: 1
  29. End_log_pos: 107
  30.        Info: Server ver: 5.5.2-m2-log, Binlog ver: 4
  31. *************************** 2. row ***************************
  32.    Log_name: mysql-bin.000001
  33.         Pos: 107
  34. Event_type: Query
  35.   Server_id: 1
  36. End_log_pos: 197
  37.        Info: use `zsd`; insert into user values(200)
  38. *************************** 3. row ***************************
  39.    Log_name: mysql-bin.000001
  40.         Pos: 197
  41. Event_type: Query
  42.   Server_id: 1
  43. End_log_pos: 287
  44.        Info: use `zsd`; insert into user values(300)
  45. *************************** 4. row ***************************
  46.    Log_name: mysql-bin.000001
  47.         Pos: 287
  48. Event_type: Query
  49.   Server_id: 1
  50. End_log_pos: 377
  51.        Info: use `zsd`; insert into user values(400)
  52. 4 rows in set (0.00 sec)
复制代码
Format_desc:这是每一个binlog文件的头,是每一个binlog文件必有的第一个event,在这个event中,记录了一些诸如binary log格式版本,产生这个event的mysql server      版 本等等.
其中:
Log_name: event所在的binlog名称.这里是在mysql-bin.000001
Pos: event在当前binlog中的位置
Event_type: event的类型
Server_id: event是在哪个server上发生的..
End_log_pos:下一个event的位置.因此当前这个event的长度是End_log_pos-Pos.
Info:直观的可读的关于本条event的信息.

3,执行flush logs;

  1. mysql> flush logs ;
  2. Query OK, 0 rows affected (0.01 sec)
  3. mysql> show master status;(flush logs 会切换日志)
  4. +------------------+----------+--------------+------------------+
  5. | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
  6. +------------------+----------+--------------+------------------+
  7. | mysql-bin.000002 |      107 |              |                  |
  8. +------------------+----------+--------------+------------------+
  9. 1 row in set (0.00 sec)
复制代码

        再次查看show binlog events 里面多了一个行,就是记录刚刚切换flush_log的event
  1. mysql> show binlog events\G;
  2. *************************** 1. row ***************************
  3.    Log_name: mysql-bin.000001
  4.         Pos: 4
  5. Event_type: Format_desc
  6.   Server_id: 1
  7. End_log_pos: 107
  8.        Info: Server ver: 5.5.2-m2-log, Binlog ver: 4
  9. *************************** 2. row ***************************
  10.    Log_name: mysql-bin.000001
  11.         Pos: 107
  12. Event_type: Query
  13.   Server_id: 1
  14. End_log_pos: 197
  15.        Info: use `zsd`; insert into user values(200)
  16. *************************** 3. row ***************************
  17.    Log_name: mysql-bin.000001
  18.         Pos: 197
  19. Event_type: Query
  20.   Server_id: 1
  21. End_log_pos: 287
  22.        Info: use `zsd`; insert into user values(300)
  23. *************************** 4. row ***************************
  24.    Log_name: mysql-bin.000001
  25.         Pos: 287
  26. Event_type: Query
  27.   Server_id: 1
  28. End_log_pos: 377
  29.        Info: use `zsd`; insert into user values(400)
  30. *************************** 5. row ***************************
  31.    Log_name: mysql-bin.000001
  32.         Pos: 377
  33. Event_type: Rotate (这里是使用了flush logs,指定了此事件的类型就为rotate)
  34.   Server_id: 1
  35. End_log_pos: 420
  36.        Info: mysql-bin.000002;pos=4(切换到的日志的位置)
  37. 5 rows in set (0.00 sec)
复制代码
  1. show binlog events \G只能查看第一个日志的events
  2. show binlog events in 'mysql-bin.000003';可以查看具体位置日志的events
复制代码

4,mysqlbinlog可以在shell下查看日志的具体记录
语法:
shell> mysqlbinlog log_file | mysql -h server_name
例子:
  1. [root@www mysql]# bin/mysqlbinlog ./data/mysql-bin.000001
  2. # at 107(这里的107和上面的107如出一辙,只是表现的方式不一样,内容相同)
  3. #120426  4:43:54 server id 1  end_log_pos 197   Query   thread_id=9  exec_time=0error_code=0
  4. use zsd/*!*/;
  5. SET TIMESTAMP=1335429834/*!*/;(这个代表unix的时间戳)
  6. insert into user values(200)
  7. /*!*/;
  8. # at 197
  9. #120426  4:43:58 server id 1  end_log_pos 287   Query   thread_id=9  exec_time=0error_code=0
  10. SET TIMESTAMP=1335429838/*!*/;
  11. insert into user values(300)
  12. /*!*/;
  13. # at 287
  14. #120426  4:44:01 server id 1  end_log_pos 377   Query   thread_id=9  exec_time=0error_code=0
  15. SET TIMESTAMP=1335429841/*!*/;
  16. insert into user values(400)
  17. /*!*/;
  18. # at 377
  19. #120426  4:51:53 server id 1  end_log_pos 420   Rotate to mysql-bin.000002  pos: 4
  20. DELIMITER ;
复制代码
上述的日志,我进行了一些删改,不过这样可以很清楚的看出,binary log记录了数据库改动的信息。

二:Binary  Logging  Formats
Binary log有三种format:
--binlog-format=STATEMENT:基于statement,顾名思义,不管对数据库有没有影响数据的sql statement都会存储在binary log里面
--binlog-format=ROW:基于row,是对表的行数据有影响的sql statement 存储在binary log里面
--binlog-format=MIXED:基于mixed,默认情况下使用statement-based logging,但是某些场合下logging mode会自动转化为 row -based( 这里官方文档的英文没有看懂,什么叫某些场合下自动转化,希望高手能指点一二

     Set The Binary Log Format
     这里有两种设置: GLOBAL和SESSION 一个针对所有的client,一个针对个人的clinet,脚本如下

  1. mysql> SET GLOBAL binlog_format = 'STATEMENT';
  2. mysql> SET GLOBAL binlog_format = 'ROW';
  3. mysql> SET GLOBAL binlog_format = 'MIXED';
  4. mysql> SET SESSION binlog_format = 'STATEMENT';
  5. mysql> SET SESSION binlog_format = 'ROW';
  6. mysql> SET SESSION binlog_format = 'MIXED'
复制代码

官方文档有解释说,在 statement-based replication下,会有些statement具有不可靠性,不能保证可以replicated。 Statement may not be safe to log in statement format.在statement模式下sql statement的不可靠)所以,要避免上述问题,就要使用MySQL's row-based replication
相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
4天前
|
存储 缓存 关系型数据库
图解MySQL【日志】——Redo Log
Redo Log(重做日志)是数据库中用于记录数据页修改的物理日志,确保事务的持久性和一致性。其主要作用包括崩溃恢复、提高性能和保证事务一致性。Redo Log 通过先写日志的方式,在内存中缓存修改操作,并在适当时候刷入磁盘,减少随机写入带来的性能损耗。WAL(Write-Ahead Logging)技术的核心思想是先将修改操作记录到日志文件中,再择机写入磁盘,从而实现高效且安全的数据持久化。Redo Log 的持久化过程涉及 Redo Log Buffer 和不同刷盘时机的控制参数(如 `innodb_flush_log_at_trx_commit`),以平衡性能与数据安全性。
17 5
图解MySQL【日志】——Redo Log
|
3天前
|
关系型数据库 MySQL 数据库
图解MySQL【日志】——两阶段提交
两阶段提交是为了解决Redo Log和Binlog日志在事务提交时可能出现的半成功状态,确保两者的一致性。它分为准备阶段和提交阶段,通过协调者和参与者协作完成。准备阶段中,协调者向所有参与者发送准备请求,参与者执行事务并回复是否同意提交;提交阶段中,若所有参与者同意,则协调者发送提交请求,否则发送回滚请求。MySQL通过这种方式保证了分布式事务的一致性,并引入组提交机制减少磁盘I/O次数,提升性能。
17 4
图解MySQL【日志】——两阶段提交
|
17天前
|
SQL 缓存 关系型数据库
MySQL原理简介—7.redo日志的底层原理
本文介绍了MySQL中redo日志和undo日志的主要内容: 1. redo日志的意义:确保事务提交后数据不丢失,通过记录修改操作并在系统宕机后重做日志恢复数据。 2. redo日志文件构成:记录表空间号、数据页号、偏移量及修改内容。 3. redo日志写入机制:redo日志先写入Redo Log Buffer,再批量刷入磁盘文件,减少随机写以提高性能。 4. Redo Log Buffer解析:描述Redo Log Buffer的内存结构及刷盘时机,如事务提交、Buffer过半或后台线程定时刷新。 5. undo日志原理:用于事务回滚,记录插入、删除和更新前的数据状态,确保事务可完整回滚。
|
1天前
|
关系型数据库 MySQL 数据库
MySQL日志
本文介绍了MySQL中三个重要的日志:binlog、redolog和undolog。binlog记录数据库更改操作,支持数据恢复、复制和审计;redolog保证事务的原子性和持久性,实现crash-safe;undolog用于事务回滚及MVCC的实现。每个日志都有其独特的作用和应用场景,确保数据库的稳定性和数据一致性。
|
3天前
|
关系型数据库 MySQL
图解MySQL【日志】——磁盘 I/O 次数过高时优化的办法
当 MySQL 磁盘 I/O 次数过高时,可通过调整参数优化。控制刷盘时机以降低频率:组提交参数 `binlog_group_commit_sync_delay` 和 `binlog_group_commit_sync_no_delay_count` 调整等待时间和事务数量;`sync_binlog=N` 设置 write 和 fsync 频率,`innodb_flush_log_at_trx_commit=2` 使提交时只写入 Redo Log 文件,由 OS 择机持久化,但两者在 OS 崩溃时有丢失数据风险。
14 3
|
6天前
|
缓存 关系型数据库 MySQL
图解MySQL【日志】——Buffer Pool
Buffer Pool 是数据库管理系统(DBMS)中用于缓存磁盘数据页的内存区域,主要包含数据页、索引页、undo 页等。它通过减少磁盘 I/O 提升性能,特别是在处理大型数据库时效果显著。查询时,整个数据页而非单条记录会被加载到 Buffer Pool 中,以提高访问效率。
16 0
图解MySQL【日志】——Buffer Pool
|
6天前
|
存储 关系型数据库 MySQL
图解MySQL【日志】——Undo Log
Undo Log(回滚日志)是 MySQL 中用于实现事务原子性和一致性的关键机制。在默认的自动提交模式下,MySQL 隐式开启事务,每条增删改语句都会记录到 Undo Log 中。其主要作用包括:
18 0
|
1月前
|
关系型数据库 MySQL 数据库连接
数据库连接工具连接mysql提示:“Host ‘172.23.0.1‘ is not allowed to connect to this MySQL server“
docker-compose部署mysql8服务后,连接时提示不允许连接问题解决
|
19天前
|
关系型数据库 MySQL 数据库
Docker Compose V2 安装常用数据库MySQL+Mongo
以上内容涵盖了使用 Docker Compose 安装和管理 MySQL 和 MongoDB 的详细步骤,希望对您有所帮助。
113 42
|
10天前
|
关系型数据库 MySQL 网络安全
如何排查和解决PHP连接数据库MYSQL失败写锁的问题
通过本文的介绍,您可以系统地了解如何排查和解决PHP连接MySQL数据库失败及写锁问题。通过检查配置、确保服务启动、调整防火墙设置和用户权限,以及识别和解决长时间运行的事务和死锁问题,可以有效地保障应用的稳定运行。
63 25