MYSQL主从复制

MySQL主从复制的基本流程

从库同步数据的流程

Binary Log 二进制日志 与 Relay Log 中继日志

1.Binary Log 包含描述数据库更改（例如表创建操作或表数据更改）的事件；它还包含针对可能进行了更改的语句的事件（例如， DELETE不匹配任何行的）；包含有关每个语句花费该更新数据多长时间的信息；

主要作用：

• 对于复制，复制源服务器上的 Binary Log 提供了要发送到副本的数据更改的记录。
• 某些数据恢复操作需要使用 Binary Log。还原备份后，将重新执行在执行备份后记录的二进制日志中的事件。

2. Relay Log 与 Binary Log 一样，由一组编号文件和一个索引文件组成，其中编号文件包含描述数据库更改的事件，而索引文件则包含所有使用的中继日志文件的名称。

I/O Thread 与 SQL Thread

副本的 Relay Log（由复制 I/O Thread 写入）包含从复制源服务器的 Binary Log 读取的事务。

数据库配置文件配置（一主一从）

主库 my.cnf 配置

[mysqld]
server_id=1
log_bin=mysql.bin   // binlog日志文件名称
binlog_format=ROW   // 日志文件格式
binlog-ignore-db=information-schema //不同步的数据库
binlog-do-db=db01 //需要同步的数据库 
binlog-do-db=db02

从库 my.cnf 配置

[mysqld]
server-id=2
relay-log=mysql-relay // 配置中继日志

binlog_format 的模式有

• row 仅记录某条记录的数据修改细节，不关系上下文
• statement SQL语句原文
  风险: 如果主/从用到的索引不同，操作语句带 limit 时，处理的可能是不同行的记录数据。
• mixed 混合模式
  一般语句使用 statement 格式保存，如果使用了一些函数或者包含内联的DML，statement 格式无法完成主从复制的操作，采用 row 格式。

从库配置与主库的关联，slave 端的 mysql 命令行执行：
配置同步的斌log日志文件，同步数据使用的用户，以及同步数据的位置

master 端的 mysql执行：
设置binlog的读取位置当下节点

reset master;
show master status\G;

slave端的mysql的执行：
设置中继日志relay-log，启动 slave 的IO Thread 和 SQL Thread

stop slave;
reset slave;

# 配置读取从库的 bin log 文件
CHANGE MASTER TO MASTER_HOST='192.168.214.91',MASTER_USER='slaveuser',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=156;

start slave;
show slave status\G;

注意 server-id 是代表数据库的实例id

• 有server-id相同的连接，就会先注销该连接，然后重新注册

• 丢弃数据源是自己的这些event，导致从库无法同步数据

主从复制的模式

1.一主一从

使用场景

1.1 读写分离

2.一主多从

使用场景

2.1 读写分离，从库负载均衡

3.多主一从

使用场景

1.数据备份
如果按照数据切分方向来讲，那就是垂直切分。业务 A、B、C、D 是之前拆分好的业务，现在需要把这些拆分好的业务汇总起来备份。

2.聚合多台的分片数据。

3.汇总多个server的数据（几张表数据合并到一张里）。

4.多主多从

使用场景

1.负载均衡，保护主库

5.MySQL 联级复制

使用场景

1.库的拆分,某库压力很大,可以使用级联复制将其独立出去

弊端

这种架构可以降低主库的负载，但是存在的问题是由于存在了多级复制，而主从复制由于是异步复制存在延迟问题，故最底层的从库会延迟更大，并且延迟随着级联层次的增大而增大，故如果读服务不能容忍太大的数据延迟，则不能采用这种架构模式。

5.MySQL主从同步延迟的原因

假设主从之间网络稳定可靠

• mysql的主从复制都是单线程的操作，主库对所有DDL和DML产生的日志写进BinLog，由于binlog是顺序写，所以效率很高。
• slave的sql thread线程将主库的DDL和DML操作事件在slave中重新生成。DML和DDL的IO操作是随机的，不是顺序，所以成本要高很多。
• 另一方面，由于sql thread也是单线程的，当主库的并发较高时，产生的DML数量超过slave的SQL thread所能处理的速度，或者当slave中有大型query语句产生了锁等待，那么延时就产生了。

https://blog.csdn.net/hao_yunfeng/article/details/82392261

6.MySQL主从同步延迟的解决方案

6.1 架构层

• 业务的持久化层的实现采用分库架构，mysql服务平行扩展分散压力。
• 单个库读写分离，一主多从，主写从读，分散压力。这样从库压力比主库高，保护主库。
• 不同业务的mysql物理上放在不同机器，分散压力。

6.2 硬件层

• 从库服务器CPU数量越多，从库性能越好。
• 存储用ssd，盘阵，san（存储局域网络），提升随机写的性能。
• 主从间保证处在同一个交换机下面。

6.3 mysql主从同步加速

• sync_binlog 在slave库设置为0。sync_binlog = 0 事务提交以后，FileSystem决定刷新binlog_cache中的信息到磁盘的时候；sync_binlog = n表示 n次事务提交后刷新binlog_cache的数据到磁盘种，表示 n=1的时候，是最安全的也是消耗性能最高的，会影响slave同步数据的速度。
• –logs-slave-updates 从服务器从主服务器接收到的更新不记入它的二进制日志。
• slave端直接禁用binlog。 binlog是存储数据使用的。
• slave端，若使用的存储引擎是innodb，设置innodb_flush_log_at_trx_commit =2。

PS: mysql由两块内存 log buffer 和 os cache ；调用 flush 主动将 log buffer 刷新到 os cache(磁盘内存映射)，也可以调用 fsync 强制操作系同步磁盘映射文件(os cache)到磁盘。还可以同时调用 flush + fsync, 将缓存直接落盘。
innodb_flush_log_at_trx_commit = 0 就是每秒调用 flush + fsync ，定时器自己维护;innodb_flush_log_at_trx_commit = 1 就是实时调用 flush + fsync 没法批处理，性能很低;innodb_flush_log_at_trx_commit = 2 就是实时flush ,定时 fsync 交给OS维护定时器。

• slave端，实行从库并行复制策略。需要对事务并行的粒度做好分组，否则SQL执行顺序会导致主备不一致的问题

7.数据库中间件 shardingsphere

shardingsphere功能包含 ShardingSphere-JDBC（增强型数据库驱动）,ShardingSphere-Proxy（数据库代理服务）和 ShardingSphere-Sidecar（开发中）三个项目

-	ShardingSphere-JDBC	ShardingSphere-Proxy	ShardingSphere-Sidecar
数据库	任意	MySQL/PostgreSQL	MySQL/PostgreSQL
连接消耗数	高	低	高
异构语言	仅Java	任意	任意
性能	损耗低	损耗略高	损耗低
无中心化	是	否	是
静态入口	无	有	无