MySql教程

mysql-binlog日志

本文主要是介绍mysql-binlog日志,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

一、简介

binlog日志也称二进制日志,记录了所有的DDL和DML( 除了数据查询语句 )语句,以事件形式记录,还包含语句所执行的消耗的时间,MySQL的二进制日志是事务安全型的。

一般来说开启二进制日志大概会有1%的性能损耗。二进制有两个最重要的使用场景:

  • 主从复制
  • 恢复数据

 

二、master节点开启binlog

[root@mysql mysql-5.7.36]# cat /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
server-id=1

#日志前缀为mysql-bin
log-bin=mysql-bin
#日志过期时间
expire_logs_days=30
#定义存储格式
binlog_format=row

[client]
socket = /tmp/mysql.sock

[root@mysql mysql-5.7.36]# 

 

三、查看bin-log相关信息

3.1 show variables like 'log_%';

log_bin:ON 表示已开启binlog日志

log_bin_basename:binlog日志的存储位置

log_bin_index:binlog日志索引文件的位置

mysql> show variables like 'log_%';
+----------------------------------------+---------------------------------------+
| Variable_name                          | Value                                 |
+----------------------------------------+---------------------------------------+
| log_bin                                | ON                                    |
| log_bin_basename                       | /usr/local/mysql/data/mysql-bin       |
| log_bin_index                          | /usr/local/mysql/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.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                             | OFF                                   |
| log_syslog_facility                    | daemon                                |
| log_syslog_include_pid                 | ON                                    |
| log_syslog_tag                         |                                       |
| log_throttle_queries_not_using_indexes | 0                                     |
| log_timestamps                         | UTC                                   |
| log_warnings                           | 2                                     |
+----------------------------------------+---------------------------------------+
21 rows in set (0.00 sec)

mysql> 

 

3.2 show variables like 'binlog%';

binlog_format:binlog日志存储格式

mysql> show variables like 'binlog%';
+--------------------------------------------+--------------+
| Variable_name                              | Value        |
+--------------------------------------------+--------------+
| binlog_cache_size                          | 32768        |
| binlog_checksum                            | CRC32        |
| binlog_direct_non_transactional_updates    | OFF          |
| binlog_error_action                        | ABORT_SERVER |
| binlog_format                              | ROW          |
| binlog_group_commit_sync_delay             | 0            |
| binlog_group_commit_sync_no_delay_count    | 0            |
| binlog_gtid_simple_recovery                | ON           |
| 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        |
| binlog_transaction_dependency_history_size | 25000        |
| binlog_transaction_dependency_tracking     | COMMIT_ORDER |
+--------------------------------------------+--------------+
15 rows in set (0.00 sec)

mysql> 

 

3.3 binlog日志文件查看乱码

直接用vim查看binlog日志文件是乱码的,这是经由base64编码之后的结果,可以在通过 mysqlbinlog 查看 binlog 日志时添加参数进行解码

 

mysqlbinlog -vv --base64-output=decode-rows mysql-bin.000001

 

 

四、binlog编码格式

binlog有三种编码格式:row   statement   mixed

可用 show variables like 'binlog_format'; 查看默认的编码格式,也可在 /etc/my.cnt 设置编码格式

 

 

4.1 row 基于行的复制(row-based replication, RBR)

 不记录每条sql语句的上下文信息,仅需记录哪条数据被修改了,修改成什么样了。而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发或 now() 无法被正确复制的问题

 优点:保持数据的绝对一致性。因为不管sql是什么,引用了什么函数,它只记录执行后的效果

 缺点:会产生大量的日志,尤其是alter table的时候会让日志暴涨

 

4.2 statement 基于SQL语句的复制(statement-based replication, SBR)

 binlog会记录每次一执行写操作的语句。

​ 相对row模式节省空间,但是可能产生不一致性,例如:update table_name set create_date=now();

​ 如果用binlog日志进行恢复,由于执行时间不同可能产生的数据就不同 ( master落库数据时create_date为2021-08-08 11:10:30 ,但binlog从库落库执行语句时create_date的时间可能就变为2021-08-08 11:11:23 ,主要是语句执行时间为异步)

​ 优点: 节省空间

​ 缺点: 有可能造成数据不一致

 

4.3 mixed 混合模式复制(mixed-based replication, MBR)

 statement的升级版,一定程度上解决了因一些情况而造成的statement模式不一致问题,例如以下情况,会按照 ROW的方式进行处理

  • 当函数中包含 UUID() 时
  • 包含 AUTO_INCREMENT 字段的表被更新时
  • 执行 INSERT DELAYED 语句时
  • 用 UDF 时

 优点:节省空间,同时兼顾了一定的一致性

 缺点:还有些极个别情况依旧会造成不一致,另外statement和mixed对于需要对binlog的监控的情况都不方便

 

五、通过binlog恢复数据

 5.1 建库建库建数据

create database test;
use test
create table t1(id int(11),name char(50));
insert into t1 set id=1,name='tom';
insert into t1 set id=2,name='jer';

 

 

 

5.2 删除一条数据,模拟数据丢失,再恢复

 

 

 5.3 恢复数据

 执行命令 flush logs; 开启新的日志记录,这样就不会收到后面操作的干扰

5.3.1 使用pos恢复数据

  show binlog events in 'mysql-bin.000013'; 查看日志结构,选出插入那条数据前后的pos,进行恢复

 

#进入到binlog目录下
cd /usr/local/mysql/data/
#数据恢复
mysqlbinlog --start-position=558 --stop-position=818 --database=test mysql-bin.000003 | mysql -uroot -pyy123456 -v test

 

5.3.2 使用时间恢复数据(也可用对应at的值进行恢复)

 

 

#进入到binlog目录下
cd /usr/local/mysql/data/
#数据恢复
mysqlbinlog --start-datetime='2022-05-09 10:51:12' --stop-datetime='2022-05-09 10:51:42' --database=test mysql-bin.000002 | mysql -uroot -pyy123456 -v test

 

5.3.3 日志结构
  • 【Log_name】:日志名称
  • 【pos】上次操作点的序号
  • 【event_type】事件类型
事件类型 说明
FORMAT_DESCRIPTION_EVENT 代表binlog日志的第一条,且只会在第一次出现
Previous_gtids 开启GTID模式(主从复制)后,每个binlog开头都会有一个(在后简称PE)事件,它的值是上一个binlog的PE+GTID信息
Query 记录更新操作的语句,包括:create,insert,update,delete
Write_rows 在开启ROW模式记录的binlog文件中,记录了插入的行记录
Delete_rows 在开启ROW模式记录的binlog文件,记录了删除的行记录
Update_rows 在开启ROW模式记录的binlog文件,记录了更新的行记录
Xid 当事务提交时,无论哪种模式都会记录
Rotate 当binlog文件的大小达到max_binlog_size的值或者执行flush logs命令时,binlog会发生切换,这个时候会在当前的binlog日志添加一个ROTATE_EVENT事件,用于指定下一个日志的名称和位置
Gtid_log 在启用GTID模式(主从复制模式)后,MySQL将为每个事务都分配了个GTID
Stop 当MySQL数据库停止时,会在当前的binlog末尾添加一个事件表示数据库停止

 

5.3.4 日志内容
内容 说明
postion(描述) at后面的数字代表在binlog日志文件的第几个字节开始(at 4 )
timestamp(事件发生的时间戳) 即第二行的(#220509 10:34:34)
server id(服务器标识) (1),代表执行的主机编号,/etc/my.cnt里所配置的
end_log_pos(结束字节数) 结束的字节位置 123
Query(类型) 事件类型
thread_id 处理的线程编号
exec_time 执行花费的时间
error_code 错误码
SET TIMESTAMP=1652064328/*!*/; 代表执行的时间戳
### INSERT INTO `test`.`t1` 代表执行的语句,遇到下一个#at 则为下一个binlog日志事件

 

 

六、其他

6.1 binlog文件大小

 默认是1G,如果超过了1G,或者使用 flush logs; ,或者重启mysql的时候,就会新增一个binlog文件

 可用 show variables like 'max_binlog_size'; 查看文件大小

 

6.2 过期删除

 可以使用 expire_logs_days=30 来配置日志保存时间,最好不要自己去删除binlog日志,这样会导致过期删除出错,如果非要删除,要记得更新一下 xxxxx.mysql-bin.index

 

6.3 其他命令

  • 查看全部的日志 show master logs;
  • 查看日志的最后一次操作 show master status;
  • 刷新binlog日志,也就是新开启一个日志文件 flush logs (这个在恢复数据的时候很有用)

 

6.4 sync_binlog

  • sync_binlog=0,当事务提交之后,MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘,而让Filesystem自行决定什么时候来做同步,或者cache满了之后才同步到磁盘。
  • sync_binlog=1,强一致,每次事物提交都进行磁盘同步。
  • sync_binlog=n,当每进行n次事务提交之后,MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘

 

这篇关于mysql-binlog日志的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!