Binary Log file - position-based Replication
MySQL refers to its traditional replication method as binary log file position-based replication, where the MySQL instance operating as the source (where the database changes take place) writes updates and changes as “events” to the binary log. The information in the binary log is stored in different logging formats according to the database changes being recorded. Replicas are configured to read the binary log from the source and to execute the events in the binary log on the replica’s local database.
Each replica receives a copy of the entire contents of the binary log. It is the responsibility of the replica to decide which statements in the binary log should be executed. Unless you specify otherwise, all events in the source’s binary log are executed on the replica. If required, you can configure the replica to process only events that apply to particular databases or tables.
Each replica keeps a record of the binary log coordinates: the file name and position within the file that it has read and processed from the source. This means that multiple replicas can be connected to the source and executing different parts of the same binary log. Because the replicas control this process, individual replicas can be connected and disconnected from the server without affecting the source’s operation. Also, because each replica records the current position within the binary log, it is possible for replicas to be disconnected, reconnect and then resume processing.
基于binlog postion 的主从复制的原理
SQL thread负责读取 relay log 中的内容，解析成具体的操作并执行，最终保证主从数据的一致性。
对于每一个主从连接，都需要这三个线程来完成。当主节点有多个从节点时，主节点会为每一个当前连接的从节点建一个 binlog dump thread，而每个从节点都有自己的 I/O receiver thread，SQL thread。
从节点用两个线程将从主库拉取更新和执行分成独立的任务，这样在执行同步数据任务的时候，不会降低读操作的性能。比如，如果从节点没有运行，此时 I/O receiver thread 可以很快从主节点获取更新，尽管 SQL thread还没有执行。如果在 SQL thread 执行之前从节点服务停止，至少 I/O receiver thread 已经从主节点拉取到了最新的变更并且保存在本地 relay log 中，当服务再次起来之后就可以完成数据的同步。
要实施复制，首先必须打开 source 端的 Binlog 功能，否则无法实现。
因为整个复制过程实际上就是 replica 从 source 端获取该日志然后再在自己身上完全顺序的执行日志中所记录的各种操作。如下图所示：
复制的基本过程
- 在从节点上执行
sart replica命令开启主从复制开关，开始进行主从复制。从节点上的 I/O receiver thread 连接主节点，并请求从指定日志文件的指定位置（或者从最开始的日志）之后的日志内容。 - 主节点接收到来自从节点的 I/O 请求后，主节点的 binlog dump thread 会持续地读取主节点的 binlog，并发送到从节点，返回给从节点。返回信息中除了日志所包含的信息之外，还包括本次返回的信息的 Binlog file 以及 Binlog position（Binlog 下一个数据读取位置）。
- 从节点的 I/O receiver thread会接收到主节点发送过来的日志内容、日志文件及位置点后，将接收到的日志内容更新到本机的 relay log 文件（Mysql-relay-bin.xxx）的最末端，并将读取到的 Binlog文件名和位置保存到
source-info文件中，以便在下一次读取的时候能够清楚的告诉 source ：“ 我需要从哪个 Binlog 的哪个位置开始往后的日志内容，请发给我”。 - replica 的 SQL thread 检测到 relay log 中新增加了内容后，会将 relay log 的内容解析成在能够执行 SQL 语句，然后在本数据库中按照解析出来的顺序执行，并在
relay log.info中记录当前应用中继日志的文件名和位置点。
How to Setup
Refer to
- https://swsmile.info/post/demo-mysql-binlog-based-source-replica-replication/
- https://dev.mysql.com/doc/refman/8.0/en/replication-howto.html
Practical Usage
Reference
- https://dev.mysql.com/doc/refman/8.0/en/binlog-replication-configuration-overview.html
- https://dev.mysql.com/doc/refman/8.0/en/replication-semisync.html
- https://dev.mysql.com/doc/refman/8.0/en/replication-howto.html
- https://dev.mysql.com/doc/refman/8.0/en/replication-asynchronous-connection-failover.html