MySQL主从篇

一、主从复制

　　1. 复制原理

　　

 　　binlog：二进制日志，记录数据的更改。可用于本机数据恢复和主从同步。刷入磁盘的方式：每秒刷入磁盘|事务提交刷入磁盘|每秒+每次事务提交刷入磁盘。刷盘线程dump thread

 　　relay(中继)log：1. 从节点定时请求主节点的binlog获取修改内容 2. 将binlog拷贝到本地relaylog中 3. 从节点定时重放relaylog 

　　从库使用单线程执行binlog是为了保证SQL语句的顺序(LSN号)。

　　2. binlog的三种模式

　　　　1. statement level模式：记录执行的修改sql语句以及执行的上下文信息。

　　　　　　优点是减少binlog日志量，节约io，提高性能。缺点是需要额外记录上下文信息，并且某些情况下会有bug，从服务器不能正确复制

　　　　2. rowlevel模式：记录语句执行完毕，导致的数据行变化，从服务根据变化修改响应的数据行。缺点是修改语句往往导致很多行的修改，binlog日志量会非常大。

　　　　3. mixed模式：对每种修改挑选一个合适的模式。譬如表结构的修改选用statement level模式、update选用rowlevel模式。

　　3. 调整binlog日志模式

　　　　查询当前binlog的日志模式：

mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set, 1 warning (0.00 sec)

　　　　调整binlog的日志模式：STATEMENT、ROW、MIXED

mysql> set binlog_format=STATEMENT;
Query OK, 0 rows affected (0.00 sec)

　　4. 主从同步步骤

    1. 先同步两个数据库的内容

    2. 配置master

      1. 开启binlog，配置server-id，一般是ip地址的最后一段
      2. 使用show master status查看当前binlog位置
    3. 配置slave
      1. 配置server-id，一般是ip的最后一段
      2. 使用change master to配置主从
      3. 开启start slave开启主从复制
    4. 主服务器做同步授权，指定授权给某个ip下的用户　　

　　5. 主从同步延迟的原因和解决办法

　　　　1. 主从同步机制

    1. 主库将DDL和DML产生的日志写入binlog，顺序写速度快

    2. 主库生成一个log dump Thread，提供给从库I/O Thread读取binlog

    3. 从库的I/O Thread请求主库的binlog，将得到的日志写入relay log

    4. 从库的SQL Thread读取relay log文件，解析成具体操作，在从库执行

　　　　2. 延迟原因

　　　　　　从库执行命令的线程只有一个，当主库有大量并发写入时，从库只有单线程写入，来不及处理。或者是从库大型查询语句产生了锁等待，写入被延迟。

　　　　3. 解决方案

    1. 使用比主库更好的硬件设备当从库。

    2. 增加从服务器，分散读的压力，降低锁争用概率。

    3. 降低从库的写入成本，从库不需要太高的数据安全性。比如设置innodb_flush_log_at_trx_commit=0(每秒一次，将redo log buffer中的内容写入到系统缓存，并刷新到文件)，sync_binlog=0(依赖操作系统将binlog_cache刷新到磁盘，主库设置为1，即每次写binlog时都会同步到磁盘)

　　　　4. 判断主从延迟的方法

　　　　　　show slave status 命令输出的Seconds_Behind_Master参数的值来判断：

　　　　　　1. 值为0表示复制良好

　　　　　　2. 值为NULL表示io_thread或是sql_thread有任何一个发生故障

　　　　　　3. 正值表示主从出现延迟，数值越大则延迟越严重

　　　　5. MySQL自身优化

　　　　　　通过增加丛机的SQL Thread 线程数并发写入来增加丛机写入的速度。

　　　　　　（1）在MySQL 5.6版本中，增加了针对schema（库级别）的并发写入。分析Relay log，对其中不同数据库的语句分配不同的写入线程。

　　　　　　（2）进一步提升，在MySQL5.6版本中引入了组提交，先介绍一下组提交的概念。

　　　　　　组提交（group commit）

　　　　　　将多个刷盘操作整合成一个。在未开启binlog时，在多个事务产生的redo log一起刷新到磁盘，减少磁盘IO。在开启binlog后，因为MySQL为了保证一致性，采用了二阶段提交，组提交的过程又有不同。在每个阶段都有一个队列，第一个进入队列的事务成为leader，负责整队的操作，在完成后通知队内其他事务操作结束。

　　　　　　刷盘redo log阶段：将队列中的redo log组提交。

　　　　　　刷盘binlog阶段：当队列中的事务等待X时间(binlog_group_commit_sync_delay)后刷盘binlog，或者当队列中的事务达到Y个(binlog_group_commit_sync_no_delay_count)则立即开始刷盘，后者优先级更高。这两个参数可配置

　　　　　　打commit标识：将队列中的已经刷盘的redo log打上commit标识

　　　　　　同一个组中的事务在binlog中有相同的commit_id，并且在同一个组中提交的事务不会修改同一行。即不同组的事务是有序的，同组的事务是无序的。所以在从库上可以并发执行同一个组中不同的事务。但是必须等一个组事务执行完成之后才会执行下一个组事务。如果一个组中只有一个大事务，那么同时只会有一个线程执行。

　　　　　　（3）MySQL5.7版本对并发度进一步提升

　　　　　　　　1. 同时出入prepare状态的事务可以并行执行

　　　　　　　　2. 处于prepare状态的事务和处于commit状态的事务可以并行执行

　　　　　　通过binlog_group_commit_sync_delay配置延迟多少秒后进行binlog刷盘，binlog_group_commit_sync_no_delay_count配置累计多少次后进行binlog刷盘。这两项配置来主动延迟binlog从写入缓存到刷盘时间间隔，来产生更多的同时处于prepare状态的事务。

　　　　　　（4）MySQL5.7.22版本再次调整，通过binlog-transaction-dependency-tracking配置不同的并行策略

　　　　　　　　1. COMMIT_ORDER：沿用MySQL5.7版本策略

　　　　　　　　2. WRITESET：对事务更新到的每一行计算一个hash，存入writeset，最后写到binlog中。如果两个事务的计算结果没有交集，说明可以并行

　　　　　　　　3. WRITESET_SESSION：在2的基础上多了一个约束，在主库上同一个线程先后执行的两个事务，在从库上也要保证执行的顺序

　　　　

二、MySQL-Proxy

　　MySQL Proxy将客户端的写请求发送给主库，将读请求发送给从库。

　　1. 配置文件

[mysql-proxy]
user=root
admin-username=root
admin-password=root
#对客户端暴露的地址
proxy-address=192.168.68.132:4040
#主库地址
proxy-backend-addresses=192.168.68.132:3306
#从库地址
proxy-read-only-backend-addresses=192.168.68.133:3306
proxy-lua-script=./share/doc/mysql-proxy/rw-splitting.lua
log-file=./logs/mysql-proxy.log
log-level=debug
keepalive=true
daemon=true

　　2. 客户端连接

mysql -uroot -proot -h192.168.10.134 -P4040

 三、MySQL Router

　　一个轻量级中间件。MySQL Router在应用程序和后端MySQL提供了透明路由和负载均衡，提高了可用性和伸缩性。

　　1. 配置　　　　　　　　　

[logger]
level = INFO

#从库代理
[routing:secondary]
bind_address = localhost
#代理端口
bind_port = 7001　　
#被代理服务器
destinations = 192.168.68.132:3306,192.168.68.133:3306
#路由策略
routing_strategy = round-robin

#主库代理
[routing:primary]
bind_address = localhost
#代理端口
bind_port = 7002
#被代理服务器
destinations = 192.168.68.132:3306,192.168.68.133:3306
#路由策略
routing_strategy = first-available

　　路由策略：

　　1. round-robin：每个新连接都以循环的方式连接到下一个可用服务器，实现负载均衡

　　2. first-available：新连接从路由列表选择第一个可用服务器

 

 

 

参考：

MySQL组提交：https://www.cnblogs.com/xibuhaohao/p/10907903.html

binglog主从同步：https://zhuanlan.zhihu.com/p/72087221

并行复制策略：https://blog.csdn.net/q975583865/article/details/90046638

posted @ 2021-03-25 13:41  walker993  阅读(47)  评论(0)  编辑  收藏  举报