运维首页 > 数据运维 > 谈谈mysql（1）——Binlog的复制方式和解析技巧

谈谈mysql（1）——Binlog的复制方式和解析技巧

2023年 7月 19日 39.0k 0

mysql

  • 我们先来看一下MySQL的基本架构，从大的方面来讲，一个server层，一个引擎层。server层就像一个接口，可以对接任何符合规定的引擎。具体的细节可以参考我之前写过的文章mysql的这些坑你踩过吗？快来看看怎么优化mysql

    在这里插入图片描述

MySQL的存储引擎的简单解读

在这里插入图片描述

  • 我们看到，有的引擎是支持事务的，有的引擎是不支持事务的。但是我们知道，MySQL的大部分引擎都支持主从复制的模式，比如myisam（基于语句），memory（基于语句），innodb(支持基于行和基于语句),MySQL通过复制主节点（master）的二进制日志（bin-log）来实现数据的同步。可以说bin-log是MySQL层面日志。

使用docker快速启动一个主备服务器

主库

  • 新建一个目录mysqla,进入目录执行以下命令,我们把配置文件和数据目录都挂载到我们宿主机上
docker run -d --name mysql-a -p 23307:3306 
-v $(pwd)/conf:/etc/mysql/conf.d 
-v $(pwd)/data:/var/lib/mysql 
-e MYSQL_ROOT_PASSWORD=root 
mysql
  • 在当前目录下的conf目录下，创建文件my.cnf 里面定义自己的日志名称目录和server-id，注意server-id不能重复
$ cat my.cnf
[mysqld]
server-id=1
log-bin=mysql-bin
  • 启动容器，创建一个slave用户用于复制
$ CREATE USER 'slave'@'%' IDENTIFIED BY '123456';
$ GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';
$ FLUSH PRIVILEGES；

备库

  • 新建一个目录mysqlb,进入目录执行以下命令,我们把配置文件和数据目录都挂载到我们宿主机上
docker run -d --name mysql-b -p 23307:3306 
-v $(pwd)/conf:/etc/mysql/conf.d 
-v $(pwd)/data:/var/lib/mysql 
-e MYSQL_ROOT_PASSWORD=root 
mysql
  • 在当前目录下的conf目录下，创建文件my.cnf 里面定义自己的日志名称目录和server-id，注意server-id不能重复
$ cat ../../mysqlb/conf/my.cnf
[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
secure-file-priv= NULL
server_id=100
log-bin=mysql-slave-bin
relay_log=edu-mysql-relay-bin
  • 查看主库和备库的容器ip
$ docker inspect -f '{{.Name}} - {{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}' a4--这个是容器id
/mysql-b - 172.17.0.3

docker inspect -f '{{.Name}} - {{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}' 52--这个是容器id
/mysql-a - 172.17.0.2
  • 我使用的mysql8 版本，要使用复制用户请求服务器公钥，在备库服务器上先执行mysql -uslave -p123456 -h172.17.0.2 --get-server-public-key，否则会报错error: Authentication requires secure connection. ,因为mysql8中caching_sha2_password 是默认的身份验证插件
  • 执行SQL设置主库的信息，注意：master_log_file 和master_log_pos是在主库执行 show master status;命令后可以获取到
change master to master_host='172.17.0.2', master_user='slave', master_password='123456', master_port=3306, master_log_file='mysql-bin.000001', master_log_pos= 157, master_connect_retry=30;
  • 查看slave的状态，如果有两个yes ，就代表成功了
    在这里插入图片描述
  • 如果中间配置错误使用stop slave;reset slave;重置主库信息
  • 我们在主库可以通过show processlist来查看备库的同步binlog的线程，比如我这个，线程端口45976 state说已经全部同步到备库了，等待更多的更新。
    在这里插入图片描述

binlog

  • 为什么会有binlog
    我们知道，MySQL服务器单机是有性能瓶颈的，特别是写比较多的时候，写锁一直被占用，导致读操作一直阻塞，最终导致请求超时，为了解决这种问题，MySQL就要提供一种更大规模、高性能的服务，那么MySQL提供了一种水平扩展的架构，让一台服务器与其他服务器保持同步的功能，一台主库的数据可以同步到其他备用库上，备用库也可以配置成其他服务的主库。binlog解决了数据库之间数据同步的问题。

记录binlog的方式

有两种复制方式，一种是基于语句的复制（逻辑复制），这种方式在MySQL版本3的时候就存在了。另一种就是基于行的复制方式，这是在MySQL版本5提出来的。而具体支持哪种方式和

基于语句的复制

在这种模式下，binlog中记录的是那些造成数据更改的SQL，并在备库上重放这些SQL。这种模式的优点就是二进制日志里的事件更加紧凑，而且binlog的日志量会更小，比如我们更新了几十万条记录，而日志里面只记录了一条update语句。

但是缺点也是明显的，比如主库和备库的执行时间有可能会不一致，导致数据的时间戳也不一样。第二点就是这种记录日志必须是串行的执行，那么我们可能就需要更多的锁来保证它是串行的。

基于行的复制

MySQL5.1以后开始支持行的复制，这种方式就是将实际数据存储到binlog里面，这种方式可以保证主备库的数据完全一致。而且不需要逻辑的binlog，复制数据的效率也更高。而且对于较为复杂的sql来说，这种方式也更高效，因为你中间不论执行了多少sql，我日志只记录物理日志，可能就是一行数据。但是对于一些update操作，那么每行被更改的数据都得记录到binlog里面。而且binlog的可读性比较差，我们不知道执行了哪些sql语句。
在这里插入图片描述

如果声明是mixed，MySQL则动态切换的，基于语句的复制执行不了的时候，就会采取行复制。我们也可以根据SHOW VARIABLES LIKE 'binlog_format'来查看当前的复制方式。

同步的过程

在这里插入图片描述

binlog的语句分析以及开始和结束的标记

  • 相关参考添加链接描述
    我们可以通过mysqlbinlog分析binlog,其中最常用到两个参数--base64-output和--verbose

  • base64-output
    AUTO: 默认为AUTO方式，原始的记录binlog events的方式。如果要通过binlog恢复数据(mysqlbinlog log_file | mysql -h server_name)，必须使用AUTO方式
    NEVER: 不显示binlog statements，遇到ROW格式的binlog直接报错
    DECODE-ROWS: 压缩显示row格式events

  • verbose
    将行模式下的binlog以注释的SQL语句的形式显示，在适用的情况下，还包括表的分区信息。即通过伪代码的方式重构出行数据改变的等价的SQL语句

接下来我们通过执行SQL来生成一些binlog日志

row模式下的日志

  • 查看binlog是否开启show variables like 'log_bin';
    在这里插入图片描述

  • 查看binlog的记录方式show variables like 'binlog_format%';
    在这里插入图片描述

  • 通过flush binary logs命令关闭当前使用的binary log，然后打开一个新的binary log文件，文件的序号加1

  • 执行一个insert操作

  • 在不加任何参数的情况下，我们看到的日志是压缩过的
    在这里插入图片描述

  • 我们通过mysqlbinlog mysql-bin.000005 --base64-output=DECODE-ROWS -vv 查看日志（v就是verbose参数简写）
    在这里插入图片描述

  • 我们执行一个update语句看一下日志是什么样子

mysql> update user set name='www.acurd.com' where id>2;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

我们发现被解析成了两个update语句
在这里插入图片描述

基于statement的binlog
  • 我们改一下主库的my.cnf配置，声明binlog的格式是statement
$ cat my.cnf
[mysqld]
server-id=1
log-bin=mysql-bin
default_authentication_plugin=mysql_native_password
binlog_format=statement
  • 查看binlog的记录方式show variables like 'binlog_format%';
    在这里插入图片描述
  • 刷新日志文件flush binary logs
  • 更新数据 update user set name='acurd' where id>1;
  • 通过mysqlbinlog查看日志mysqlbinlog mysql-bin.000008 ，日志中记录的就是我们执行的SQL
    在这里插入图片描述
竹子爱熊猫
数据运维
0 0

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF，云和恩墨技术通讯（2024年7月刊）
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论

竹子爱熊猫

这个人很懒，什么都没有留下～

文章8367 评论0