mysql 主主同步丢数据_MySQL数据库丢失数据场景分析

在线QQ客服：1922638

专业的SQL Server、MySQL数据库同步软件

同Oracl近似，事务提交时需要写redoundologInnoDB采用预写日志(WriteA headLog战略，数据页的变更会首先在内存中完成，同时将事务把持顺序记实到redolog中。完成上述的把持后表示该事务已经实现，可以或许返回给操纵已提交的消息。但此时实际被更改的数据页还保存在内存中(称为脏页)并没有flush磁盘上即没有落地。刷盘把持个体会在达到必定条件后，触发checkpoint机制，此时会将内存中的脏页合并写入到磁盘里，完成数据同步。

刷盘策略

由innodb_flush_log_at_trx_commit参数节制的战略。以下图:

参数innodb_flush_log_at_trx_commit=0每秒writeoscache&flushdisk=1每次commit都writeoscache&flushdisk=2每次commit都writeoscach尔后按照innodb_flush_log_at_timeout参数(默认为1sflushdiskinnodb_flush_log_at_trx_commit=1最为保险，因为每次commit都保证redolog写入了disk但是这类方式性能对DML性能来说斗劲低，测试中发现，如果设置为2DML性能要比设置为1高10倍左右(以是在短信平台出现写入成就时，已将该参数设置为2innodb_flush_log_at_trx_commit为0或2不同重要表现在mysqlservicecrash或systemcrash时丢失事务的范例:1当mysqlservicecrash时，设置为0就会丢失1秒内的所有已提交及未提交的事件，且无法回滚(由于redolog还记录在logbuff中，没有落盘到redolog而设置为2时，每次提交都会写入到oscach中，即便servicecrash掉，也只会丢失1秒内所有未提交的事件，而已提交的事务已经写入redolog中，可以或许回滚。2当systemcrash时，与上述类似。是以，业内的共鸣是一些DML把持频仍的场景下，参数innodb_flush_log_at_trx_commit设置为2当然这样就存在丢数据的危险:当出现mysqlservicecrash时，重启后InnoDB会进行crashrecoveri则会丢失innodb_flush_log_at_timeout秒内的已提交的数据。未提交的数据则可由应用中的事务赔偿机制处置惩罚。但是IO性能可以或许前进至多10倍。PS:当开启了外部XA 事件(默认封闭)且开启binlog情况稍有不一样。见下文。

1.2.MyISA M损失数据场景阐发MyISA M存储引擎在生产环境中基础没有利用。并且我线上的5.6版本已将系统的数据字典表元数据表等系统表的默认存储引擎改削为InnoDB因为MyISA M不支持事务，且没有datacach一切DML把持只写到OScach中，flushdisk把持均由OS来完成，因此如果服务器宕机，这部分数据一定会丢失。

2.主从复制导致数据不一致的场景MySQL主从复制原理:MySQL主库在事务提交时写binlog并通过sync_binlog参数来控制binlog革新到磁盘”落地”从库中有两个线程:IO线程担负从主库读取binlog并记录到当地的relaylog中；SQL线程再将relaylog中的记录利用到从库。以下图所示:

引擎层数据损失场景

1.1.InnoDB损失数据场景阐发

InnoDB作为MySQL支撑事件的存储引擎。iVBORw0KGgoA A A A NSUhEUgA A A A EA A A A BCA YA A A A fFcSJA A A A DUlEQVQImWNgYGBgA A A A BQA Bh6FO1A A A A A BJRU5ErkJggg==” width=379>

2.1.binlog改革机制master写binlog与innodb引擎写redo近似，由参数sync_binlog节制:=0默示MySQL不控制binlog革新，由文件系统节制binlogcach刷盘操作 =N表示每sync_binlogN次事务提交后，MySQL调用文件体系的flush把持将binlogcach中的内容信息刷盘 sync_binlog=1时最安全，即表示每次事务提交，MySQL都会把binlogcach中的内容信息flushdisk如许在掉电等情况下，系统只有可以或许丧失1个事务的数据。但是sync_binlog为1时，体系的IO耗费非常大。但是N值也不易过大，不然在系统掉电时会丢失较多的事件。当面我生产系统设置为100.

2.2.外部XA 事务道理MySQL存储引擎与MySQL服务层之间，或者存储引擎与存储引擎之间的漫衍式事务，称之为MySQL外部XA 事件。最为罕见的外部XA 事务存在与binlog与InnoDB存储引擎之间。事务提交时，先写二进制日志，再写InnoDB存储引擎的redolog对于这个把持要求必需是原子的即必要保证两者同时写入。外部XA 事务机制就是保证二者的同时写入。XA 事件的大致流程:1事务提交后，InnoDB存储引擎会先做一个PREPA RE操纵，将事务的XID写入到redolog中 2写binlog3将该事务的commit动静写到redolog中

若是在步调1和步骤2失利的情况下，全数事务会回滚，若是在步调3失利的情况下，MySQL数据库在重启后会先检查PREPA REXID事件是否已经提交，若没有，则在存储引擎层再履行一次提交操作。这样就保证了redo与binlog不合性，防备损失事件。

2.3.主库写redologbinlog不实时造成的数据不一致下面我先容了MySQL外部XA 事务流程，但是这个流程并不是浑然一体的redoib_logfil与binlog日志如果被设置非实时flush就有可以或许出现以下数据不一致的环境:1Redologtrx_prepar未写入，但binlog已写入，则crashrecoveri后从库数据比主库多。2Redologtrx_prepar与commit都写入了但binlog未写入，则crashrecoveri后从库数据量比主库少。从目前来看，只能殉国性能去换取数据的保险性，必须要设置redolog和binlog为实时刷盘，如果对性能要求很高，则考虑使用SSD来替代机械盘。

2.4.从库写redologbinlog不实时造成的数据不一致主库正常，但是从库出现异常情况宕机，如果数据丧失，从库的SQL线程还会重新操纵吗？这个我必要先了解SQL线程的机制。从库读取主库的binlog日志后，必要落地3个文件:relaylog即IOThread读取过去的主库binlog内容信息格式与主库的binlog分歧 relayloginfo记实SQLThread利用的relaylog地位、文件号等信息 masterinfo记实IOThread读取主库的binlog地位、文件号、提前等信息 因此如果当这3个文件如果不及时落地，则systemcrash后会导致数据的不一致。MySQL5.6.2之前，从库记录的主库动静战从库应用binlog动静寄放在文件中，即master.info与relay-log.info5.6.2版本以后，允许记实到tabl中，参数设置以下:master-info-repository=TA BLErelay-log-info-repository=TA BLE对应的表分别为mysql.slave_master_info与mysql.slave_relay_log_info且这两个表均为innodb引擎表。masterinfo与relayinfo另有3个参数控制革新:1sync_relay_log默认为10000即每10000次sync_relay_log事件会刷新到磁盘。为0则表示不刷新，交由OScach节制。2sync_master_info:若master-info-repositori为FILE当设置为0时，则每次sync_master_info事件都会革新到磁盘，默认为10000次刷新到磁盘；若master-info-repositori为TA BLE当设置为0时，则表不做任何更新，设置为1则每次事件会更新表。默认为100003sync_relay_log_info若relay_log_info_repositori为FILE当设置为0时，交由OS改革磁盘，默认为10000次刷新到磁盘；若relay_log_info_repositori为TA BLE则无论为任何值，每次evnet都会更新表。如果参数设置以下:sync_relay_log=1sync_master_info=1sync_relay_log_info=1master-info-repository=TA BLErelay-log-info-repository=TA BLE将导致调用fsync/fdatasync跟着master事件的增加而增加，且若slavebinlog和redo也实时刷新的话，会带来很严重的IO性能瓶颈。

2.5.主库宕机后无法及时回复复兴形成的数据不一致当主库出现弊端后，binlog未及时拉到从库中，或者各个从库收到binlog不一致(大都是由于搜集起因)且主库无法在第姑且间恢复:1如果主库不切换，则操纵只能读写主库。如果有读写分离的场景则会影响应用(读写分别场景中从库会从)2如果将某一从库提升为新的主库(如MHA 那么原主库未来得及传到从库的binlog数据则会丢失，并且还涉及到上面2个问题:a各个从库之间接收到binlog不一致，如果逼迫拉起一个从库做新主库，则从库之间数据会不一致。b原主库回复复兴正常后，由于新的主库日志丢弃了部分原主库的binlog日记，那么会多出来弊端时代的这部分binlog对于上面泛起的成绩，业内已经有较成熟的体式格局来解决:2.5.1确保binlog全部传到从库打算一:利用semisyncrepl半同步复制)插件。半同步复制的特色是从库中有一台提交后，主库才能提交事件。长处是保障了主、从库的数据不合性；错误谬误是对性能影响很大，凭借收集，合适tp压力小的场景。打算二:双写binlog经由过程DBDROS层的文件系统复制到备机，或者操纵共享盘保存binlog日记。利益和打算一类似，但此打算缺点较明显:1DBDR必要安排本身的办事 2DBDR脑裂严重。发生灾难场景时，经常不能正确切换。3必要成立heartbeat机制。保证被监控机的存活。打算三:架构层面调剂，引入情况队列做异步情况处置惩罚。比如保证数据库写成功后，再异步队列的方式写一份，部分停业可以或许借助假想和数据流解决。2.5.2保证数据最小化丢失下面的打算假想及架构斗劲庞杂，如果能容忍数据的丧失，可以或许考虑利用MHA 当master宕机后，MHA 可以或许指定一台或者选提前最低或binlogpo最新的一台从库，并将其汲引为主库。MHA 切换master后，原master可以或许修复后以新masterslave角色重新加入集群。从而达到高可用。

3.总结通过下面的总结阐发，MySQL丢数据的场景浩瀚，重要还是波及到引擎层数据损失场景、主从的数据不一致场景等。遵照漫衍式领域的CA P实践(Consist不合性、Availabl高可用性、Partitiontoler分区耐受性)任何的漫衍式系统只能同时知足2点，没方法三者统筹。MySQL主从环境满足Availabl且在半同步场景中数据可以或许做到数据强一致性，可以或许知足Consist但是不能完全知足Partitiontoler因此现在业内对于事件(数据)丧失的处理有很多处置惩罚计划，如事务赔偿机制、半同步复制、双写机制、异步情况队列等等。以至，还可以或许针对停业对CA P中哪两者更有须要来选择相应的数据产物，如必要分区耐受性和高可用兼顾时，可以或许利用Cassandra等列式存储。都可以达到停业响应的数据不合性需求。