Linux运维笔记|自动化运维攻城狮

MySQL 主从同步延迟的原因及解决办法

数据库  

一、主从数据库的区别

  • 主从分工

其中Master负责写操作负载，一切写的操作都在Master上执行，读的操作分摊到Slave上进行。在一般互联网应用中，经过数据分析调查得着：读写比例是10：1左右。在写的操作上会触发大量的行锁、表锁、块锁，都是降低系统执行效率的事情，所以采用读写分离，把写操作都集中到一个点上。另一方面提高了读的效率，也提供的系统的高可用性

  • 基本过程

    • 1.Mysql主从同步就是当Master发生变化的时候，会实时同步到Slave。
    • 2.主从复制可以水平扩展数据库的负载能力、容错、高可用、数据备份。
    • 3.不管是delete、update、insert，还是创建函数，都会在Master上操作，Slave会快速接收这些操作，从而做主从同步

  • 用途

    实时灾备 用于故障切换
    读写分离 提高查询能力
    备份 数据备份

  • 条件
    主库开启binlog日志 (设置 log-bin 参数)
    从库server-id 不同
    从库连接到主库

二、主从同步的粒度、原理和形式

  • 三种主要粒度实现：

    详细的主从主要有三种粒度实现：statement、row、mixed 1.statement：会将对数据库的操作写到 binlog 中。
    2.row：会将每一条数据的变化写到 binlog 中。
    3.mixed：statement 和 row混合。Mysql觉得什么时候写statement 的binlog ，什么时候写row 格式的binlog。

  • 主要实现原理、具体操作：

    • Master 机器操作

      当master库发生变化，会按照事件顺序写到bin-log中，当Slave连接到Master后，Master 会为 Slave 开启bin-log dump线程，当Master的 binlog 发生变化，binlog dump会通知Slave，并将相应食物binlog发送给Slave。

    • Slave 机器操作

      当主从同步开启的时候，Slave 上会开启两个线程: I/O线程：该线程连接到Master，Master 的 bin-log dump线程会将binlog内容发送给I/O线程，I/O线程接受到binlog 再将内容写到本地的 relay log。
      sql线程：该线程读取到I/O线程写入的relay log，根据relay log 的内容，对Slave做相应的操作。

三、主从复制的延迟等问题，延迟及解决方案

  • 首先在服务器上执行show slave satus;可以看到很多同步的参数：
Master_Log_File： SLAVE中的I/O线程当前正在读取的主服务器二进制日志文件的名称  
Read_Master_Log_Pos： 在当前的主服务器二进制日志中，SLAVE中的I/O线程已经读取的位置  
Relay_Log_File： SQL线程当前正在读取和执行的中继日志文件的名称  
Relay_Log_Pos： 在当前的中继日志中，SQL线程已读取和执行的位置  
Relay_Master_Log_File： 由SQL线程执行的包含多数近期事件的主服务器二进制日志文件的名称  
Slave_IO_Running： I/O线程是否被启动并成功地连接到主服务器上  
Slave_SQL_Running： SQL线程是否被启动  
Seconds_Behind_Master： 从属服务器SQL线程和从属服务器I/O线程之间的时间差距，单位以秒计。
  • 从库同步延迟的情况出现
show slave status 显示参数Seconds_Behind_Master不为0，这个数值可能会很大  
show slave status 显示参数Relay_Master_Log_File和Master_Log_File显示bin-log的编号相差很大，说明bin-log在从库上没有及时同步，所以近期执行的bin-log和当前IO线程所读的bin-log相差很大  
mysql的从库数据目录下存在大量mysql-relay-log日志，该日志同步完成之后就会被系统自动删除，存在大量日志，说明主从同步延迟很厉害

  • Mysql主从同步的延迟原因

    • Mysql主从同步原理：主库针对写操作，顺序写binlog，从库单线程去主库读binlog，从库单线程去主库 取到binlog 在本地随机写，来保证主从数据逻辑上的一致。主从复制都是单线程操作，主库对所有DDL和DML产生binlog，binlog是顺序写，效率很高。从库的SlaveIORunning 线程到主库取binlog，效率也很高。下一步从库的SlaveSqlRunning 将主库的 DDL和DML在从库上执行，DDL 和 DML 是随机写，成本很高，还有可能从库上其他查询产生的lock争用。由于SlaveSqlRunning是单线程的，所以一个DDL卡住了，需要10分钟，那么之后的DDL都需要等这个DDL执行完了才会执行，所以导致了主从延时。 还有一个原因：主库支持并发操作，从库的SlavrSqlRunning 不可以

    • 当主库DDL并发较高时，超过了从库Sql线程所能承受的范围，延时就产生了。还有原因就是从库大型query产生了锁等待。 主要原因：数据库在业务上读写压力太大，CPU计算负荷大，网卡负荷大，硬盘随机IO太高 次要原因：读写binlog带来的性能影响，网络传输延迟

  • MySql数据库从库同步的延迟解决方案

    • 架构方面
      1）.业务的持久层采用分库架构，mysql服务能力水平扩展，分散压力
      2）.单个库读写分离，一主多从，主写读从，分散压力。这样从库比主库压力高，保护主库
      3）.服务在业务和DB之间加入memcache 和 redis 的cache层，降低读的压力
      4）.不同业务的mysql放在不同的物理机，降低压力
      5）.使用比主库更好的硬件设备，Mqsql压力小，延迟就减少了

    • 硬件方面
      1）.采用好服务器，比如4u比2u性能明显好，2u比1u性能明显好。
      2）.存储用ssd或者盘阵或者san，提升随机写的性能。
      3）.主从间保证处在同一个交换机下面，并且是万兆环境。

    • Mysql主从同步加速
      1）.syncbinlog在Slave端设置为0.
      2）.log-slave-updates 从服务器从主服务器接受的更新日志不计入二进制日志
      3）.直接禁用 Slave 的binlog
      4）.Slave端，如果存储引擎是innodb，innodb      flushlogattrxcommit =2
      5）.同步参数调整主库是写，对数据安全性较高，比如syncbinlog=1，innodbflushlogattrxcommit = 1 之类的设置是需要的而slave则不需要这么高的数据安全，完全可以讲syncbinlog设置为0或者关闭binlog，innodbflushlog也可以设置为0来提高sql的执行效率

      1、syncbinlog=1 MySQL提供一个syncbinlog参数来控制数据库的binlog刷到磁盘上去。默认，syncbinlog=0，表示MySQL不控制binlog的刷新，由文件系统自己控制它的缓存的刷新。这时候的性能是最好的，但是风险也是最大的。一旦系统Crash，在binlogcache中的所有binlog信息都会被丢失。 如果syncbinlog>0，表示每syncbinlog次事务提交，MySQL调用文件系统的刷新操作将缓存刷下去。最安全的就是syncbinlog=1了，表示每次事务提交，MySQL都会把binlog刷下去，是最安全但是性能损耗最大的设置。这样的话，在数据库所在的主机操作系统损坏或者突然掉电的情况下，系统才有可能丢失1个事务的数据。但是binlog虽然是顺序IO，但是设置syncbinlog=1，多个事务同时提交，同样很大的影响MySQL和IO性能。虽然可以通过group commit的补丁缓解，但是刷新的频率过高对IO的影响也非常大。 对于高并发事务的系统来说，"syncbinlog"设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多。所以很多MySQL DBA设置的syncbinlog并不是最安全的1，而是2或者是0。这样牺牲一定的一致性，可以获得更高的并发和性能。默认情况下，并不是每次写入时都将binlog与硬盘同步。因此如果操作系统或机器(不仅仅是MySQL服务器)崩溃，有可能binlog中最后的语句丢失了。要想防止这种情况，你可以使用syncbinlog全局变量(1是最安全的值，但也是最慢的)，使binlog在每N次binlog写入后与硬盘同步。即使syncbinlog设置为1,出现崩溃时，也有可能表内容和binlog内容之间存在不一致性。
      2、innodbflushlogattrx_commit （这个很管用）抱怨Innodb比MyISAM慢 100倍？那么你大概是忘了调整这个值。默认值1的意思是每一次事务提交或事务外的指令都需要把日志写入（flush）硬盘，这是很费时的。特别是使用电池供电缓存（Battery backed up cache）时。设成2对于很多运用，特别是从MyISAM表转过来的是可以的，它的意思是不写入硬盘而是写入系统缓存。日志仍然会每秒flush到硬 盘，所以你一般不会丢失超过1-2秒的更新。设成0会更快一点，但安全方面比较差，即使MySQL挂了也可能会丢失事务的数据。而值2只会在整个操作系统 挂了时才可能丢数据。

  • 业务角度

    • .强制走主库方案

      对于必须要拿到最新结果的请求，强制将其发到主库上。比如，在一个交易平台上，卖家发布商品以后，马上要返回主页面，看商品是否发布成功。那么，这个请求需要拿到最新的结果，就必须走主库。>对于可以读到旧数据的请求，才将其发到从库上。在这个交易平台上，买家来逛商铺页面，就算晚几秒看到最新发布的商品，也是可以接受的。那么，这类请求就可以走从库。

    • .sleep 方案

      主库更新后，读从库之前先 sleep 一下。具体的方案就是，类似于执行一条 select sleep(1) 命令。 这个方案的假设是，大多数情况下主备延迟在 1 秒之内，做一个 sleep 可以有很大概率拿到最新的数据。

    • 判断主库无延迟的方案

      第一种方法，show slave status 结果里的 secondsbehindmaster 参数的值，可以用来衡量主备延迟时间的长短。所以第一种确保主备无延迟的方法是，每次从库执行查询请求前，先判断 secondsbehindmaster 是否已经等于 0。如果还不等于 0 ，那就必须等到这个参数变为 0 才能执行查询请求。
      第二种方法，对比位点确保主备无延迟：MasterLogFile 和 ReadMasterLogPos，表示的是读到的主库的最新位点；RelayMasterLogFile 和 ExecMasterLogPos，表示的是备库执行的最新位点。如果 MasterLogFile 和 RelayMasterLogFile、ReadMasterLogPos 和 ExecMasterLogPos 这两组值完全相同，就表示接收到的日志已经同步完成。
      第三种方法，对比 GTID 集合确保主备无延迟：AutoPosition=1 ，表示这对主备关系使用了 GTID 协议。RetrievedGtidSet，是备库收到的所有日志的 GTID 集合；ExecutedGtid_Set，是备库所有已经执行完成的 GTID 集合。如果这两个集合相同，也表示备库接收到的日志都已经同步完成。 回顾一下，一个事务的 binlog 在主备库之间的状态： 主库执行完成，写入 binlog，并反馈给客户端； binlog 被从主库发送给备库，备库收到；
      在备库执行 binlog 完成。