临沂网站建设价格,潍坊网络公司,怎么做外国网站流量,渠道网络前言
在生产环境中#xff0c;为了满足安全性#xff0c;高可用性以及高并发等方面的需求#xff0c;基本上采用的MySQL数据库架构都是MHA、MGR等#xff0c;最低也得是一主一从的架构#xff0c;搭配自动切换脚本#xff0c;实现故障自动切换。 上述架构都是通过集群主…前言
在生产环境中为了满足安全性高可用性以及高并发等方面的需求基本上采用的MySQL数据库架构都是MHA、MGR等最低也得是一主一从的架构搭配自动切换脚本实现故障自动切换。 上述架构都是通过集群主从复制Master-Slave的方式来同步数据。MySQL集群简单架构图 说到主从同步离不开binlog这个东西先从binlog说起。
binlog
binlog是什么有什么作用 binlog是一个二进制文件主要记录所有数据库表结构变更例如CREATE、ALTER TABLE…以及表数据修改INSERT、UPDATE、DELETE…的所有操作。 binlog 是 mysql 的逻辑日志并且由 Server层进行记录使用任何存储引擎的 mysql 数据库都会记录 binlog 日志。在实际应用中 binlog 的主要使用场景有四个
恢复recovery某些数据的恢复需要二进制日志。例如在一个数据库全备文件恢复后用户可以通过二进制日志进行point-in-time的恢复。复制replication其原理与恢复类似通过复制和执行二进制日志使一台远程的MySQL数据库一般称为slave或者standby与一台MySQL数据库一般称为master或者primary进行实时同步。审计audit用户可以通过二进制日志中的信息来进行审计判断是否有对数据库进行注入攻击。用于数据备份在数据库备份文件生成后binlog保存了数据库备份后的详细信息以便下一次备份能从备份点开始。除了上面介绍的几个作用外binlog对于事务存储引擎的崩溃恢复也有非常重要的作用。在开启binlog的情况下为了保证binlog与redo的一致性MySQL将采用事务的两阶段提交协议。当MySQL系统发生崩溃时事务在存储引擎内部的状态可能为prepared和commit两种。对于prepared状态的事务是进行提交操作还是进行回滚操作这时需要参考binlog如果事务在binlog中存在那么将其提交如果不在binlog中存在那么将其回滚这样就保证了数据在主库和从库之间的一致性。 日志格式 binlog 日志有三种格式分别为 STATMENT 、 ROW 和 MIXED日志格式通过 binlog-format 指定。详情如下:
STATEMENTStatement 模式只记录执行的 SQL不需要记录每一行数据的变化因此极大的减少了 binlog 的日志量避免了大量的 IO 操作提升了系统的性能。但是正是由于 Statement 模式只记录 SQL而如果一些 SQL 中包含了函数那么可能会出现执行结果不一致的情况。比如说 uuid() 函数每次执行的时候都会生成一个随机字符串在 master 中记录了 uuid当同步到 slave 之后再次执行就获取到另外一个结果了。ROW从 MySQL5.1.5 版本开始binlog 引入了 Row 格式Row 格式不记录 SQL 语句上下文相关信息仅仅只需要记录某一条记录被修改成什么样子了。Row 格式的日志内容会非常清楚的记录下每一行数据修改的细节这样就不会出现 Statement 中存在的那种数据无法被正常复制的情况。不过 Row 格式也有一个很大的问题那就是日志量太大了特别是批量 update、整表 delete、alter 表等操作由于要记录每一行数据的变化此时会产生大量的日志大量的日志也会带来 IO 性能问题。现在对于ROW格式的二进制日志基本是标配了主要是因为它的优势远远大于缺点。并且由于ROW格式记录行数据所以可以基于这种模式做一些DBA工具比如数据恢复不同数据库之间数据同步等。MIXED从 MySQL5.1.8 版开始MySQL 又推出了 Mixed 格式这种格式实际上就是 Statement 与 Row 的结合。在 Mixed 模式下系统会自动判断该用 Statement 还是 Row一般的语句修改使用 Statement 格式保存 binlog对于一些 Statement 无法准确完成主从复制的操作则采用 Row 格式保存 binlog。Mixed 模式中MySQL 会根据执行的每一条具体的 SQL 语句来区别对待记录的日志格式也就是在 Statement 和 Row 之间选择一种。
重要参数sync_binlog 在MySQL 5.7之前版本默认情况下二进制日志并不是在每次写的时候同步的磁盘用户可以理解为缓冲写。因此当数据库所在的操作系统发生宕机时可能会有最后一部分数据没有写入二进制文件中这会给恢复和复制带来问题。参数sync_binlog[N]中的N表示每提交多少个事务就进行binlog刷新到磁盘。如果将N设为1即sync_binlog1表示采用同步写磁盘的方式来写二进制日志每次事务提交时就会刷新binlog到磁盘sync_binlog为0表示刷新binlog时间点由操作系统自身来决定操作系统自身会每隔一段时间就会刷新缓存数据到磁盘 如果使用Innodb存储引擎进行复制并且想得到最大的高可用性需要将此值设置为1。不过该值为1时确时会对数据库IO系统带来一定的开销。但是即使将sync_binlog设为1还是会有一种情况导致问题的发生。当使用InnoDB存储引擎时在一个事务发出COMMIT动作之前由于sync_binlog为1因此会将二进制日志立即写入磁盘。如果这时已经写入了二进制日志但是提交还没有发生并且此时发生了宕机那么在MySQL数据库下次启动时由于COMMIT操作并没有发生这个事务会被回滚掉。但是二进制日志已经记录了该事务信息不能被回滚。 对于这个问题MySQL使用了两阶段提交来解决的简单说就是对于已经写入到binlog文件的事务一定会提交成功 而没有写入到binlog文件的事务就会进行回滚从而保证二进制日志和InnoDB存储引擎数据文件的一致性保证主从复制的安全。 主从复制原理 mysql主从复制需要三个线程masterbinlog dump thread、slaveI/O thread 、SQL thread复制基本过程面试常考
主库写入数据并且生成binlog文件。该过程中MySQL将事务串行的写入二进制日志依赖binlog dump线程。在事件写入二进制日志完成后master通知存储引擎提交事务。从库服务器上的IO线程连接Master服务器请求从执行binlog日志文件中的指定位置开始读取binlog至从库。主库接收到从库的IO线程请求后其上复制的IO线程会根据Slave的请求信息分批读取binlog文件然后返回给从库的IO线程。Slave服务器的IO线程获取到Master服务器上IO线程发送的日志内容、日志文件及位置点后会将binlog日志内容依次写到Slave端自身的Relay Log即中继日志文件的最末端并将新的binlog文件名和位置记录到master-info文件中以便下一次读取master端新binlog日志时能告诉Master服务器从新binlog日志的指定文件及位置开始读取新的binlog日志内容。从库服务器的SQL线程会实时监测到本地Relay Log中新增了日志内容然后把RelayLog中的日志翻译成SQL并且按照顺序执行SQL来更新从库的数据。从库在relay-log.info中记录当前应用中继日志的文件名和位置点以便下一次数据复制。
并行复制
在MySQL 5.6版本之前Slave服务器上有两个线程I/O线程和SQL线程。 I/O线程负责接收二进制日志SQL线程进行回放二进制日志。如果在MySQL5.6版本开启并行复制功能那么SQL线程就变为了coordinator线程coordinator线程主要负责以上两部分的内容。 这意味着coordinator线程并不是仅将日志发送给worker线程自己也可以回放日志但是所有可以并行的操作交付由worker线程完成。 coordinator线程与worker是典型的生产者与消费者模型。 不过到MySQL 5.7才可称为真正的并行复制这其中最为主要的原因就是slave服务器的回放与主机是一致的即master服务器上是怎么并行执行的slave上就怎样进行并行回放。不再有库的并行复制限制对于二进制日志格式也无特殊的要求。 为了兼容MySQL 5.6基于库的并行复制5.7引入了新的变量slave-parallel-type其可以配置的值有
DATABASE默认值基于库的并行复制方式LOGICAL_CLOCK基于组提交的并行复制方式
下面分别介绍下两种并行复制方式按库并行 每个 worker 线程对应一个 hash 表用于保存当前正在这个worker的执行队列里的事务所涉及到的库。其中hash表里的key是数据库名用于决定分发策略。该策略的优点是构建hash值快只需要库名同时对于binlog的格式没有要求。 但这个策略的效果只有在主库上存在多个DB且各个DB的压力均衡的情况下这个策略效果好。因此对于主库上的表都放在同一个DB或者不同DB的热点不同则起不到多大效果 组提交优化 该特性如下
能够同一组里提交的事务定不会修改同一行主库上可以并行执行的事务从库上也一定可以并行执行。详细内容可以去官网看看https://dev.mysql.com/doc/refman/5.7/en/replication-options-slave.html 主从延迟
主从延迟是怎么回事 根据前面主从复制的原理可以看出两者之间是存在一定时间的数据不一致也就是所谓的主从延迟。我们来看下导致主从延迟的时间点
主库 A 执行完成一个事务写入 binlog该时刻记为T1.传给从库B从库接受完这个binlog的时刻记为T2.从库B执行完这个事务该时刻记为T3.
那么所谓主从延迟就是同一个事务从库执行完成的时间和主库执行完成的时间之间的差值即T3-T1。我们也可以通过在从库执行show slave status返回结果会显示seconds_behind_master表示当前从库延迟了多少秒。seconds_behind_master如何计算的
每一个事务的binlog都有一个时间字段用于记录主库上写入的时间从库取出当前正在执行的事务的时间字段跟当前系统的时间进行相减得到的就是seconds_behind_master也就是前面所描述的T3-T1。
主从延迟原因
为什么会主从延迟 正常情况下如果网络不延迟那么日志从主库传给从库的时间是相当短所以T2-T1可以基本忽略。 最直接的影响就是从库消费中转日志relaylog的时间段而造成原因一般是以下几种 1、从库的机器性能比主库要差 比如将20台主库放在4台机器把从库放在一台机器。这个时候进行更新操作由于更新时会触发大量读操作导致从库机器上的多个从库争夺资源导致主从延迟。 不过目前大部分部署都是采取主从使用相同规格的机器部署。 2、从库的压力大 按照正常的策略读写分离主库提供写能力从库提供读能力。将进行大量查询放在从库上结果导致从库上耗费了大量的CPU资源进而影响了同步速度造成主从延迟。 对于这种情况可以通过一主多从分担读压力也可以采取binlog输出到外部系统比如Hadoop让外部系统提供查询能力。 3、大事务的执行 一旦执行大事务那么主库必须要等到事务完成之后才会写入binlog。比如主库执行了一条insert … select非常大的插入操作该操作产生了近几百G的binlog文件传输到只读节点进而导致了只读节点出现应用binlog延迟。 因此DBA经常会提醒开发不要一次性地试用delete语句删除大量数据尽可能控制数量分批进行。 4、主库的DDL(alter、drop、create) 只读节点与主库的DDL同步是串行进行如果DDL操作在主库执行时间很长那么从库也会消耗同样的时间比如在主库对一张500W的表添加一个字段耗费了10分钟那么从节点上也会耗费10分钟。 从节点上有一个执行时间非常长的的查询正在执行那么这个查询会堵塞来自主库的DDL表被锁直到查询结束为止进而导致了从节点的数据延迟。 5、锁冲突 锁冲突问题也可能导致从节点的SQL线程执行慢比如从机上有一些select …. for update的SQL或者使用了MyISAM引擎等。 6、从库的复制能力 一般场景中因偶然情况导致从库延迟了几分钟都会在从库恢复之后追上主库。但若是从库执行速度低于主库且主库持续具有压力就会导致长时间主从延迟很有可能就是从库复制能力的问题。 从库上的执行即sql_thread更新逻辑在5.6版本之前是只支持单线程那么在主库并发高、TPS高时就会出现较大的主从延迟。 因此MySQL自5.7版本后就已经支持并行复制了。可以在从服务上设置 slave_parallel_workers为一个大于0的数然后把slave_parallel_type参数设置为LOGICAL_CLOCK这就可以了。
怎么减少主从延迟
主从同步问题永远都是一致性和性能的权衡得看实际的应用场景若想要减少主从延迟的时间可以采取下面的办法
降低多线程大事务并发的概率优化业务逻辑优化SQL避免慢SQL减少批量操作建议写脚本以update-sleep这样的形式完成。提高从库机器的配置减少主库写binlog和从库读binlog的效率差。尽量采用短的链路也就是主库和从库服务器的距离尽量要短提升端口带宽减少binlog传输的网络延时。实时性要求的业务读强制走主库从库只做灾备备份。
主从延迟案例分析
问题背景 有一套主从每到凌晨就出现延迟现象需要排查原因。首先查看master库每天凌晨有什么操作分析binlog日志
mysqlbinlog --no-defaults --base64-outputdecode-rows -v mysql-bin.000204 mysql-bin.000204.sql 日志在凌晨有大量的delete操作 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 #191126 0:08:35 server id 1 end_log_pos 1073744234 CRC32 0x11bf4f5d Table_map: user.table_name mapped to number 4012691 # at 1073744234 #191126 0:08:35 server id 1 end_log_pos 1073744393 CRC32 0xa3229214 Delete_rows: table id 4012691 flags: STMT_END_F ### DELETE FROM user.table_name ### WHERE ### 1259121 ### 22019-11-25 ### 31 ### 41 ### 50 ### 62019-11-25 ### 708:30 ### 82019-11-25 ### 917:30 ### 10540 ### 11 ### 12 ### 13NULL ### 14 ### 15 ### 16NULL ### 170 ### 180 ### 190 ### 200 ### 210 ### 22540 ### 230 ### 240 ### 250 ### 260 ### 270 ### 28{} # at 1073744393 #191126 0:08:35 server id 1 end_log_pos 1073744424 CRC32 0x5a03e7aa Xid 25909247548 判断为大量的delete操作产出大量的binlog日志slave应用不过来。 一般而言slave相对master延迟较大其根本原因就是slave上的复制线程没办法真正做到并发。简单说就是在master上是并发模式(以InnoDB引擎为主)完成事务提交的而在slave上复制线程只有一个sql thread用于binlog的apply所以slave在高并发时会远落后master。 查看slave复制方式: 1 2 3 4 5 6 7 8 mysql show variables like%slave_parallel%; ----------------------------------------------------------------- | Variable_name | Value | ----------------------------------------------------------------- | slave_parallel_type | DATABASE | | slave_parallel_workers | 0 | ----------------------------------------------------------------- 2 rows in set (0.00 sec)
当前的复制类型是 DATABASE也就是统一数据库下只有一个线程进行复制不能并行复制。当前并行工作的进程数是 0.配置从服务器上的多线程并行复制的参数此处为实现多线程复制的重要参数在数据库配置文件 my.cnf中设置 1 2 3 4 5 6 slave-parallel-typeLOGICAL_CLOCK slave-parallel-workers16 #16为设置的并发线程个数之后根据项目对数据传输的具体要求再更改 #一个schema下slave_parallel_workers中的worker线程并发执行relay log中主库提交的事务 master_info_repositoryTABLE relay_log_info_repositoryTABLE relay_log_recoveryON 注变量slave-parallel-type可以有两个值DATABASE 为默认值意为基于库的并行复制方式LOGICAL_CLOCK基于组提交的并行复制方式
