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的日志模式：
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.
-
-
配置master
- 开启binlog，配置server-id，一般是ip地址的最后一段
- 使用show master status查看当前binlog位置
- 开启binlog，配置server-id，一般是ip地址的最后一段
- 配置slave
- 配置server-id，一般是ip的最后一段
- 使用change master to配置主从
- 开启start slave开启主从复制
- 配置server-id，一般是ip的最后一段
- 主服务器做同步授权，指定授权给某个ip下的用户
-
5. 主从同步延迟的原因和解决办法
1. 主从同步机制
-
主库将DDL和DML产生的日志写入binlog，顺序写速度快
-
-
从库的I/O Thread请求主库的binlog，将得到的日志写入relay log
-
从库的SQL Thread读取relay log文件，解析成具体操作，在从库执行
-
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
