Mysql之Binlog

1、简述

binlog 二进制日志文件,这个文件记录了MySQL所有的DML操作。通过binlog日志我们可以做数据恢复,增量备份,主主复制和主从复制等等。

2、Docker中无法使用vim问题解决

https://blog.csdn.net/Tomwildboar/article/details/120710690

https://blog.csdn.net/KwaiSZ/article/details/106937983

3、开启binlog

查看是否开启binlog日志 <span>show variables like 'log_%';</span>&#xA0;

my.cnf里面加上配置如下配置,重启mysql。

binlog&#x5B58;&#x50A8;&#x7684;&#x4F4D;&#x7F6E;
log-bin=/var/lib/mysql/mysql-bin
&#x65E5;&#x5FD7;&#x8FC7;&#x671F;&#x65F6;&#x95F4;
expire_logs_days=30
&#x4E0D;&#x52A0;&#x8FD9;&#x4E2A;&#x542F;&#x52A8;&#x4F1A;&#x62A5;&#x9519;
server-id=123454

查看binlog其它的配置文件 show variables like 'binlog%';

4、binlog查看乱码问题

上面框起来的就是 binlog&#x65E5;&#x5FD7;(你可以先对表进行一些增删改操作),但是我们使用 vim打开发现全是乱码

其中标记的部分并非是乱码,而是经由 base64 编码之后的结果,可以在通过 mysqlbinlog 查看 binlog 日志时添加参数进行解码

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

5、binlog编码格式

我们可以使用 show variables like 'binlog_format';来查看默认的编码格式

注:不同版本的MySQL,binlog默认的编码格式可能不同

binlog有三种编码格式分别是 row statement mixed

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

每一条会修改数据的sql语句都会记录到binlog中。优点是并不需要记录每一条sql语句和每一行的数据变化,减少了binlog日志量,节约IO,提高性能。

在配置文件里面加入配置 binlog_format=statement,然后重启服务。

执行下面的语句:

CREATE TABLE my_test.xdx_test  (
  name varchar(255) NOT NULL,
  age int(0) NOT NULL,
  birthday datetime NOT NULL
)

insert into xdx_test (name,age,birthday) values ('xdx',18, now());

然后我们在打开最新的binlog日志,可以在里面找到上面的语句。

但就如同上面的 insert 语句,我使用了 now(),这个函数,如果用这个binlog语句去进行备份、同步那么时间字段就对不上了。

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

不记录每条sql语句的上下文信息,仅需记录哪条数据被修改了,修改成什么样了。而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发或 now() 无法被正确复制的问题。缺点是会产生大量的日志,尤其是alter table的时候会让日志暴涨。

修改配置文件为 binlog_format=row,然后重启服务器即可开启。

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

以上两种模式的混合使用,一般的复制使用STATEMENT模式保存binlog,对于STATEMENT模式无法复制的操作使用ROW模式保存binlog,MySQL会根据执行的SQL语句选择日志保存方式。

修改配置文件为 binlog_format=mixed,然后重启服务器即可开启。

6、通过binlog恢复数据

将binlog编码格式自行设置为mixed 混合格式。

6.1、准备数据

在操作之前,我们先执行 flush logs;开启一个新的 binlog日志,方便后面处理。

建表:

CREATE TABLE xdx_test (
  id int(1) unsigned NOT NULL AUTO_INCREMENT COMMENT '&#x81EA;&#x589E;id',
  name varchar(255) NOT NULL COMMENT '&#x540D;&#x79F0;',
  age int(11) NOT NULL COMMENT '&#x5E74;&#x9F84;',
  birthday datetime NOT NULL COMMENT '&#x751F;&#x65E5;' ,
   PRIMARY KEY (id) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

添加数据:

insert into xdx_test (name, age, birthday) values ('111',111,now());
insert into xdx_test (name, age, birthday) values ('222',222,now());

查询数据:

select * from xdx_test;

Mysql之Binlog

这里我们删掉 id=1的数据 delete from xdx_test where id = 1;,用来模拟异常情况数据丢失,然后我们再来恢复它。

6.2、恢复数据

6.2.1、开启新日志

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

6.2.2、找到需要操作的日志

这里我要操作的日志是 mysql-bin.000007

执行命令查看日志结构 show binlog events in 'mysql-bin.000007';

6.2.3、使用pos恢复数据(推荐)

从上面的图,我们可以看到pos的开始和结束的位置,我们可恢复此阶段的数据

&#x8FDB;&#x5165;&#x5230; binlog &#x76EE;&#x5F55;&#x4E0B;
cd /var/lib/mysql
&#x6570;&#x636E;&#x6062;&#x590D;&#x547D;&#x4EE4;
mysqlbinlog --start-position=&#x5F00;&#x59CB;&#x7684;pos --stop-position=&#x7ED3;&#x675F;&#x7684;pos --database=&#x8981;&#x64CD;&#x4F5C;&#x7684;&#x6570;&#x636E;&#x5E93; binlog&#x7684;&#x540D;&#x79F0; | mysql -u&#x767B;&#x9646;&#x540D; -p&#x767B;&#x9646;&#x5BC6;&#x7801; -v &#x8981;&#x64CD;&#x4F5C;&#x7684;&#x6570;&#x636E;&#x5E93;

&#x6700;&#x7EC8;&#x6267;&#x884C;&#x547D;&#x4EE4;&#xFF08;&#x6211;&#x7684;&#xFF09;&#x6CE8;&#x610F;&#x7A7A;&#x767D;&#x95F4;&#x9694;
mysqlbinlog --start-position=219 --stop-position=518 --database=my_test mysql-bin.000007 | mysql -uroot -p123456 -v test

执行结果信息:

再次进入数据库,查询,发现数据可用。

[En]

Enter the database again, query, and find that the data is available.

6.2.3.1、执行报错解决

报错信息如下:

Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock

进到 mysql 的config路径下,一般是在:

vim /etc/mysql/my.cnf

然后看到其中 [mysqld] 的socket配置为:

socket=/var/lib/mysql/mysql.sock

因此把这个sock文件挂载到 /var/run/mysqld/mysqld.sock 就可以了:

ln -s /var/lib/mysql/mysql.sock /var/run/mysqld/mysqld.sock

重新执行恢复命令即可。

6.2.4、使用时间恢复数据

由于时间限制,无法看到具体的恢复内容和时间点,因此不建议使用此方法。

[En]

Due to the log of time, it is not possible to see the specific recovery content and time point, so this method is not recommended.

使用命令查看binlog日志 mysqlbinlog -vv --base64-output=decode-rows mysql-bin.000013

这里面有很多数据。在这里,我只拍摄所需部件的屏幕截图。

[En]

There is a lot of data. Here I only take screenshots of the parts needed.

mysqlbinlog --start-datetime='2021-10-14 12:47:12' --stop-datetime='2021-10-14 12:52:40' --database=my_test mysql-bin.000013 | mysql -uroot -proot -v test

7、其他

7.1、binlog文件大小

可以使用命令来查看binlog 单个文件大小,默认是1G,如果超过了1G就会新增一个文件 show variables like 'max_binlog_size';

注: 当你重启MySQL的时候,就会新增一个新的 binlog 文件

7.2、过期删除

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

7.3、其他命令

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

7.4、sync_binlog

sync_binlog=0,当事务提交之后,MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘,而让Filesystem自行决定什么时候来做同步,或者cache满了之后才同步到磁盘。

sync_binlog=1,强一致,每次事物提交都进行磁盘同步。

sync_binlog=n,当每进行n次事务提交之后,MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘

Original: https://www.cnblogs.com/aerfazhe/p/16408464.html
Author: 阿尔法哲
Title: Mysql之Binlog

原创文章受到原创版权保护。转载请注明出处:https://www.johngo689.com/505272/

转载文章受原作者版权保护。转载请注明原作者出处!

(0)

大家都在看

  • 小试牛刀:Go 反射帮我把 Excel 转成 Struct

    背景 起因于最近的一项工作:我们会定义一些关键指标来衡量当前系统的健康状态,然后配置对应的报警规则来进行监控报警。但是当前的报警规则会产生大量的误报,需要进行优化。我所负责的是将一…

    数据库 2023年6月6日
    0137
  • 什么是回表,怎么解决?

    表tbl有a,b,c三个字段,其中a是主键,b上建了索引,然后编写sql语句SELECT * FROM tbl WHERE a=1这样不会产生回表,因为所有的数据在a的索引树中均能…

    数据库 2023年6月16日
    0183
  • 连接数据库空异常

    解决过程(嫌麻烦可以直接看结论): 首先我以为是我的数据库驱动名字什么的没有写对,但是确实是对的,使用的是 com.mysql.cj.jdbc.Driver ,也没有发现多余的空格…

    数据库 2023年6月16日
    0128
  • 2022CNAS能力验证-存在性

    背景 警方接到报案称有人利用无人机投放非法宣传材料,根据线索找到一处住宅,搜查发现无人机、智能电视机、小型网络设备等电子设备,提取了电子设备数据并对网络设备进行了抓包分析。现委托你…

    数据库 2023年6月11日
    0110
  • linux下centos7.2下安装redis 4.0.6

    一、安装 redis 第一步:下载 redis 安装包 wget http://download.redis.io/releases/redis-4.0.6.tar.gz [roo…

    数据库 2023年6月14日
    0120
  • 解决ip和域名都能够ping通但是启动nginx无法访问网页的问题

    解决思路 最近双11逛西部数码的官网看看有没有什么服务器优惠的时候,发现了可以申请一个一块钱用一整年的SSL证书,立马心动下单了,想想俺也可以用 https装装X了哈哈 不过在部署…

    数据库 2023年6月11日
    0272
  • Linux巡检脚本

    #!/bin/bash sys:centos6.x/7.x [ $(id -u) -ne 0 ] && echo "&#x8BF7;&#x…

    数据库 2023年6月14日
    0170
  • ShardingSphere-JDBC进行分库分表

    一、前言:分库分表 在大型的互联网系统中,可能单台MySQL的存储容量无法满足业务的需求,这时候就需要进行扩容了。 和之前的问题一样,单台主机的硬件资源是存在瓶颈的,不可能无限制地…

    数据库 2023年6月14日
    0121
  • MySQL&MariaDB数据库备份脚本

    MySQL&MariaDB数据库备份脚本 MySQL&MariaDB数据库备份脚本 MySQL & MariaDB .bat backup.sh MySQL…

    数据库 2023年6月9日
    0119
  • mysql权限问题

    注意问题 grant all privileges on shop.* to ‘hosp’@’%’ flush privileges; 查询账号权限类型%和localhost是不同…

    数据库 2023年6月9日
    0202
  • 力扣数据库题目182查找重复的电子邮箱

    力扣数据库题目182查找重复的电子邮箱 题目 编写一个 SQL 查询,查找 Person 表中所有重复的电子邮箱。 示例: +—-+——&#82…

    数据库 2023年6月9日
    0125
  • 重新学习数据库(1)

    单元概述 通过本章的学习能够了解MySQL结构查询语言的概念,掌握SELECT查询语句的基本语法,掌握SELECT查询语句中过滤条件的使用,掌握过滤条件中比较运算符和逻辑运算符的使…

    数据库 2023年6月16日
    0259
  • VMware Workstation 与 Device/Credential Guard 不兼容

    问题: 在确定已经禁用 Hyper-V 功能的前提下,启动虚拟机还是报错 ” VMware Workstation 与 Device/Credential Guard …

    数据库 2023年6月14日
    0128
  • Nginx常见问题

    基于端口的虚拟机: 使用端口来区分,浏览器使用域名或ip地址:端口号 访问。 注意:~ 代表自己输入的英文字母。 16、location正则案例 1 #优先级1,精确匹配,根路径 …

    数据库 2023年6月14日
    0175
  • 阿里慢SQL治理5大经典案例

    菜鸟供应链金融慢sql治理已经有一段时间,自己负责的应用持续很长时间没有慢sql告警,现阶段在推进组内其他成员治理应用慢sql。这里把治理过程中的一些实践拿出来分享下。 一、全表扫…

    数据库 2023年5月24日
    0165
  • mybatis批量操作

    List类型 Mapper.java public int updateAccount(List<orderjob> orderJobs);</orderjob&…

    数据库 2023年6月16日
    0131
亲爱的 Coder【最近整理,可免费获取】👉 最新必读书单  | 👏 面试题下载  | 🌎 免费的AI知识星球