MySQL主从复制

概念

主从数据库:主是主库的意思,从是从库的意思。数据库主库对外提供读写操作，从库只提供读操作

为什么需要主从架构

1. 有时sql语句需要锁表，导致不能使用读的服务，那么这时就可以使用从库进行读,解决了即使主库不能读写,通过从库可以保证业务正常运作
2. 读写分离,提供查询服务,减少主库压力,提升性能
3. 备份数据,避免影响业务

数据库主从复制原理

1. Master库有个binlog二进制文件,记录了所有的增删改SQL语句
2. 从数据库把主数据库的binlog文件的SQL语句复制到自己的中继日志relaylog (I/O线程)
3. 从数据库的relaylog重做日志文件,执行这些sql语句.(sql执行线程)

换句话说就是:

• 从库会生成两个线程,一个IO线程,一个SQL线程

• IO线程会请求主库的binlog,并将得到的binlog写到本地的relaylog文件中

• 主库会生成一个logdump线程,用来给从库IO线程传binlog

• SQL线程会读取relaylog的日志,执行SQL

上图主从复制分了五个步骤

• 主库的更新SQL被写到binlog
• 从库发起连接,连接到主库
• 主库会创建一个binlog dump thread,把bin log的内容发送到从库
• 从库启动后,创建一个IO线程,读取binlog并写入到relaylog
• 从库创建一个SQL线程,从relay log里面读取内容,从execmasterlog_pos位置开始执行读取到的更新事件,将更新的内容写入到slave的DB

主主,主从,主备的区别

数据库主主:两台都是主数据库,同时对外提供读写操作,客户访问任意一台,数据库双向同步

数据库主从:一台主数据库,一台从数据库

数据库主备:一台主数据库,同时对外提供读写操作.一台备库,只做备份作用

MySQL如何保证主从一致

长链接

主库和从库如果在数据同步时中断了,数据就会丢失,这时主库与从库之间维持了一个长链接,主库内部有一个线程,专门服务于从库的这个长链接

binlog格式

binlog有三种格式,分别是statement,row,mixed

如果是statement格式,binlog记录的是SQL原文,如果从库和主库选的索引都不一致,可能会导致主库不一致

比如:主库执这个SQL(id和createTime都有索引)

delete from user where id>1 and createTime>'2022-4-7' limit 1

数据库选择id索引和选择createTime索引,最后limit 1出来的数据不一样,所以就会存在这种情况,在binlog=statement格式时,主库在执行这个SQL,主库选择索引id,从库在执行这个SQL,使用索引createTime,最后导致主从数据不一致

如何解决这个问题

可以把binlog格式改为row.row格式记录的不是SQL原文,而是记录具体删除的行id和其他信息,因此就不会出现主从不一致问题,但是如果删除的数据比较多,row格式就会占用很大空间,这时可以使用mixed格式的binlog,所谓mixed就是row和statement格式混合使用,当mysql判断数据不一致时,就会使用row格式,否则反之.

数据库主从延迟的原因和解决方案

1. 从库的机器性能比主库机器性能差,会导致主从延迟,这种情况好解决,只需要选择主从库性能一样的机器
2. 从库压力过大,也导致主从延迟.比如主库直接影响业务,查询都放到了从库,这种情况下可以搞一主多从架构,还可以将binlog接入到hadoop系统中,让它们提供查询的能力
3. 大事务也会导致主从延迟.如果一个事务执行就要十分钟,那么主库执行完后,到从库执行,最后这个事务可能导致从库延迟十分钟,因此不要一次性delete太多SQL,需要分批进行,避免大事务
4. 网络延迟也会导致主从延迟,这种情况只能优化网络
5. 从数据库过多也会导致主从延迟,因此要避免复制的从节点数量过大