Mysql之Binlog

2022/6/26 2:23:40

本文主要是介绍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日志 show variables like 'log_%'; 

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

# binlog存储的位置
log-bin=/var/lib/mysql/mysql-bin
# 日志过期时间
expire_logs_days=30
# 不加这个启动会报错
server-id=123454

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

4、binlog查看乱码问题

上面框起来的就是binlog日志(你可以先对表进行一些增删改操作),但是我们使用 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 '自增id',
  `name` varchar(255) NOT NULL COMMENT '名称',
  `age` int(11) NOT NULL COMMENT '年龄',
  `birthday` datetime NOT NULL COMMENT '生日' ,
   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;

这里我们删掉 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的开始和结束的位置,我们可恢复此阶段的数据

# 进入到 binlog 目录下
cd /var/lib/mysql
# 数据恢复命令
mysqlbinlog --start-position=开始的pos --stop-position=结束的pos --database=要操作的数据库 binlog的名称 | mysql -u登陆名 -p登陆密码 -v 要操作的数据库

# 最终执行命令(我的)注意空白间隔
mysqlbinlog --start-position=219 --stop-position=518 --database=my_test mysql-bin.000007 | mysql -uroot -p123456 -v test

执行结果信息:

再次进入数据库,查询,发现数据有了

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、使用时间恢复数据

由于时间这块日志,不太能具体看清具体恢复的内容时间点,所有不推荐使用该方式。

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

数据比较多,这里我只截图需要的部分 

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中的数据强制写入磁盘

 



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


扫一扫关注最新编程教程