Mysql丢数据以及解决分析

一、Mysql丢数据以及解决分析

1.1.master库写redo、binlog不实时丢数据的场景

     上面我们介绍了MySQL的内部XA事务流程，但是这个流程并不是天衣无缝的，redo的ib_logfile与binlog日志如果被设置非实时flush，就有可能存在丢数据的情况。

         1.redo的trx_prepare未写入，但binlog写入，造成从库数据量比主库多。

         2.redo的trx_prepare与commit都写入了，但是binlog未写入，造成从库数据量比主库少。

从目前来看，只能牺牲性能去换取数据的安全性，必须要设置redo和binlog为实时刷盘，如果对性能要求很高，则考虑使用SSD

1.2.slave库写redo、binlog不实时丢数据的场景

    master正常，但是slave出现异常的情况下宕机，这个时候会出现什么样的情况呢？如果数据丢失，slave的SQL线程还会重新应用吗？这个我们需要先了解SQL线程的机制。

    slave读取master的binlog日志后，需要落地3个文件：relay log、relay log info、master info：

        relay log：即读取过来的master的binlog，内容与格式与master的binlog一致

        relay log info：记录SQL Thread应用的relay log的位置、文件号等信息

        master info：记录IO Thread读取master的binlog的位置、文件号、延迟等信息

    因此如果当这3个文件如果不及时落地，则主机crash后会导致数据的不一致。

在MySQL 5.6.2之前，slave记录的master信息以及slave应用binlog的信息存放在文件中，即master.info与relay-log.info。在5.6.2版本之后，允许记录到table中，参数设置如下：

1. master-info-repository  = TABLE
2. relay-log-info-repository = TABLE

对应的表分别为mysql.slave_master_info与mysql.slave_relay_log_info，且这两个表均为innodb引擎表。

master info与relay info还有3个参数控制刷新：

• sync_relay_log：默认为10000，即每10000次sync_relay_log事件会刷新到磁盘。为0则表示不刷新，交由OS的cache控制。
• sync_master_info:若master-info-repository为FILE，当设置为0，则每次sync_master_info事件都会刷新到磁盘，默认为10000次刷新到磁盘；若master-info-repository为TABLE，当设置为0，则表不做任何更新，设置为1，则每次事件会更新表 #默认为10000
• sync_relay_log_info：若relay_log_info_repository为FILE，当设置为0，交由OS刷新磁盘，默认为10000次刷新到磁盘；若relay_log_info_repository为TABLE，且为INNODB存储，则无论为任何值，则都每次evnet都会更新表。

建议参数设置如下：

1. sync_relay_log = 1
2. sync_master_info = 1
3. sync_relay_log_info = 1
4. master-info-repository  = TABLE
5. relay-log-info-repository = TABLE

当这样设置，导致调用fsync()/fdatasync()随着master的事务的增加而增加，且若slave的binlog和redo也实时刷新的话，会带来很严重的IO性能瓶颈。

参考：（转） MySQL丢数据及主从数据不一致的场景 - 一杆子撸 - 博客园

1.3、开启 relay_log_recovery

1. 在从库中将relay_log_recovery不设置或者设置为off，如果碰到上面的情形，从库会丢失那些没有应用的日志，主从会不一致。
2. 在从库中将relay_log_recovery设置为on，假如果碰到上面的情形，从库会自动放弃所有未执行的relay log，重新生成一个relay log，并将从库的io线程的position重新指向新的relay log。并将sql线程的position退回到跟io线程的position保持一致，重新开始同步，这样在从库中事务不会丢失。这个参数建议开启。是不是很绕，没关系，看实验。

二、mysql分库分表（垂直拆分与水平拆分）

 

 

 

 

 三、原理与实践 分库分表中间件

proxy代理层：mycat、atlas、mysql-proxy、shardingproxy

jdbc应用层：shardingsphere(生态圈)、TDDL

 四、数据修改流程

1. 修改buffer pool里面的页数据 ---脏页
2. update语句 --> 生成redo log --> log buffer
3. redo log持久化（事务提交的时候）
4. 修改成功​​​​​​​

 

 

五、mysql中数据类型转换，统统把字符串转换成 0

SELECT 'a'=0;  -- 结果为1表示为真
SELECT 'b'=1;  -- 结果为0表示为假

参考：Mysql - 关于relay_log_recovery参数的测试 - 小豹子加油 - 博客园