MYSQL 主从复制与读写分离

1.mysql主从复制和读写分离的相关知识

1.1 什么是读写分离?

读写分离，基本的原理是让主数据库处理事务性增、改、删操作( INSERT、UPDATE、DELETE) ，而从数据库处理SELECT查询操作。数据库复制被用来把事务性操作导致的变更同步到集群中的从数据库。

1.2 为什么要读写分离？

因为数据库的“写”（写10000条数据可能要3分钟）操作是比较耗时的。

但是数据库的“读”（读10000条数据可能只要5秒钟）；

所以读写分离，解决的是，数据库的写入，影响了查询的效率。

1.3 什么时候要读写分离?

数据库不一定要读写分离，如果程序使用数据库较多时，而更新少，查询多的情况下会考虑使用。利用数据库主从同步，再通过读写分离可以分担数据库压力，提高性能。

 1.4 主从复制的优点

数据分布：通过复制将数据分布到不同地理位置

负载均衡：读写分离以及将读负载到多台从库

备份：可作为实时备份

高可用性：利用主主复制实现高可

1.5 主从复制与读写分离
在实际的生产环境中，对数据库的读和写都在同一个数据库服务器中，是不能满足实际需求的。无论是在安全性、高可用性还是高并发等各个方面都是完全不能满足实际需求的。

因此，通过主从复制的方式来同步数据，再通过读写分离来提升数据库的并发负载能力。有点类似于rsync（文件同步工具），但是不同的是rsync是对磁盘文件做备份，而mysql主从复制是对数据库中的数据、语句做备份。
 

1.6 mysql支持的复制类型
（1） STATEMENT：基于语句的复制。在服务器上执行sql语句，在从服务器上执行同样的语句，mysql默认采用基于语句的复制（5.7版本之前），执行效率高。高并发的情况可能会出现执行顺序的误差，事务的死锁。

（2）ROW：基于行的复制。把改变的内容复制过去，而不是把命令在从服务器上执行一 遍。精确，但效率低，保存的文件会更大。（5.7版本之后默认采用ROW模式）

（3）MIXED：混合类型的复制。默认采用基于语句的复制，一旦发现基于语句无法精确复制时，就会采用基于行的复制。更智能，所以大部分情况下使用MIXED。
 

STATEMENT和row的对比
STATEMENT：

传输效率高，减少延迟。

在从库更新不存在的记录时，语句赋值不会失败。而行复制会导致失败，从而更早发现主从之间的不一致。

设表里有一百万条数据，一条sql更新了所有表，基于语句的复制仅需要发送一条sql，而基于行的复制需要发送一百万条更新记录

row

不需要执行查询计划。

不知道执行的到底是什么语句。
1.7 mysql主从复制的过程

1.主服务开启二进制日志（Binary log），从服务器开始中继日志（Relay log）
2.主服务器数据更新写入二进制日志
3.从服务器开启IO线程请求二进制日志事件/日志
4.主服务为每个IO线程开启dump线程，向从发送二进制日志事件
  （在5.7开启了版同步复制的情况下，主会用ACK collector线程接受从反馈的确认
    信息）
5.从保存二进制日志事件到中继日志
6.从开启SQL线程毒气中继日志中的二进制事件并解析成SQL语句，逐一执行。

1.8 MySQL主从复制的几个同步模式 

2.1 主从复制延迟的原因及解决方案 

主从复制延迟原因

• master服务器高并发，形成大量事务。
• 网络延迟。
• 主从硬件设备导致（cpu主频、内存IO、硬盘IO）。
• 是同步复制，而不是异步复制。

 解决方案

（1）硬件方面

从库配置更好的硬件，提升随机写的性能。比如原本是机械盘，可以考虑更换为ssd固态。升级核心数更强的cpu、加大内存。避免使用虚拟云主机，使用物理主机

（2）网络方面 

  将从库分布在相同局域网内或网络延迟较小的环境中。尽量避免跨机房，跨网域进行主从数据库服务器的设置

（3）架构方面

在事务当中尽量对主库读写，其他非事务中的读在从库。消除一部分延迟带来的数据库不一致。增加缓存降低一些从库的负载。 

（4）mysqld服务配置

该配置设置针对mysql主从复制性能优化最大化，安全性并不高。如果从安全的角度上考虑的话，就要设置双一设置

追求安全性的双一设置：

1. innodb_flush_log_at_trx_commit=1

2. sync_binlog=1

2.2  主从复制不一致的原因及解决方案
 产生主从复制不一致的可能原因
人为原因导致从库与主库数据不一致（从库写入）
主从复制过程中，主库异常宕机
设置了ignore/do/rewrite等replication等规则
binlog非row格式
异步复制本身不保证，半同步存在提交读的问题，增强半同步起来比较完美。 但对于异常重启（Replication Crash Safe），从库写数据（GTID）的防范，还需要策略来保证。
从库中断很久，binlog应用不连续，监控并及时修复主从
从库启用了诸如存储过程，从库禁用存储过程等
数据库大小版本/分支版本导致数据不一致？，主从版本统一
备份的时候没有指定参数 例如mysqldump --master-data=2 等
主从sql_mode 不一致
一主二从环境，二从的server id一致
MySQL自增列 主从不一致
主从信息保存在文件里面，文件本身的刷新是非事务的，导致从库重启后开始执行点大于实际执行点
采用5.6的after_commit方式半同步，主库当机可能会引起主从不一致，要看binlog是否传到了从库
启用增强半同步了（5.7的after_sync方式），但是从库延迟超时自动切换成异步复制

（2） 将从库设置为只读模式

（3）可以使用5.7增强半同步避免数据丢失等。

（4）binlog row格式

（5）必须引定期的数据校验机制。 

问题发生的解决方案  

方法一：忽略错误后，继续同步（问题出在从库上）

该方法适用于主从库数据相差不大，或者要求数据可以不完全统一的情况，数据要求不严格的情解决

 从库进行mysql服务中：

1. mysql -u root -p

2. stop slave;

3. #表示跳过一步错误，后面的数字可变

4. set global sql_slave_skip_counter =1;

5. start slave;

6. 之后再用mysql> show slave status\G 查

1. 二：重新做主从，完全

2. 先进入主库，进行锁表，防止数据写

3. mysql> flush tables with read lock;

4. 注意：该处是锁定为只读状态，语句不区分大小写

2.进行数据备份

1. #把数据备份到mysql.bak.sql文件

2. mysqldump -uroot -p -hlocalhost > mysql.bak.sql

3.查看master 状态

4.把mysql备份文件传到从库机器，进行数据恢复

1. #使用scp命令

2. [root@server01 mysql]# scp mysql.bak.sql 从库主机:/指定路径

5.停止从库的状态

mysql > stop slave ;

6.然后到从库执行mysql命令，导入数据备份7.设置从库同步，注意该处的同步点，就是主库show master status信息里的| File| Position两项

mysql> source /tmp/mysql.bak.sql