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

服务器托管，北京服务器托管，服务器租用 http://www.fwqtg.net

相关推荐: ChatGPT从⼊⻔到精通第⼀章：简介第二章：ChatGPT的基础知识第三章：ChatGPT 的实现第四章: ChatGPT 的进阶应⽤第五章: ChatGPT ⾏业应⽤研究第六章: ChatGPT 的最新进展第七章: 总结与展望

编者寄语 ChatGPT 作为⼀种强⼤的⾃然语⾔处理模型，已经成为人工智能领域的重要研究⽅向之⼀。在不断的发展和创新中，ChatGPT 已经具备了很强的⾃然语⾔处理能⼒，其可以实现⾃然语⾔的⽣成、理解和交互，为⼈类的⽣产和⽣活带来了巨⼤的便利和创新。本书从…

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