mysql数据库表数据丢失怎么办
发布网友
发布时间：2022-04-09 02:03
共2个回答
懂视网
时间：2022-04-09 06:24
由于在实际项目中，我们往往使用支持事务的InnoDB存储引擎，MySQL默认情况下是开启内部的XA事务。我们分析InnoDB存储引擎：
从上篇的文章《MySQL事务提交过程（一）》和《MySQL事务提交过程（二）》中知道，事务的实现方式是基于redo log和undo log。也可以理解为MySQL事务是采用日志现行的策略。前提未开启binlog的情况下，数据的变更首先在内存中完成，并且将事务顺序的写入到redo log中，即表示该事务已经完成，就可以返回发给客户端已提交的信息。但此时变更后的数据还在内存中，并没有刷新写入到磁盘中，当达到一定条件，将内存中的数据合并写入到磁盘，即落地到磁盘。这样做的目的是提高性能，但同时也埋下了隐患。在这个过程中，如果服务器宕机，内存中数据将会丢失，重启服务器后，通过redo log日志recovery重做日志，保障了数据不会丢失。因此只要事务能够实时写入到磁盘（redo log），InnoDB存储引擎就不会丢失数据。
如何控制事务写入到磁盘（redo log）的时机哪？ 通过配置参数innodb_flush_log_at_trx_commit控制时机。
0 ：每秒 write cache & flush disk
1 ：每次commit都 write cache & flush disk
2 ：每次commit都 write cache，然后根据innodb_flush_log_at_timeout（默认为1s）时间 flush disk
如果设置innodb_flush_log_at_trx_commit=1最为安全数据不会丢失，因为每次commit都保证redo写入了disk。但是这种方式性能对DML性能来说比较低。
如果设置为0最不安全数据会丢失，性能为最高的。
如果设置为2，DML性能要比设置为1高许多倍。
如果可以接受丢失innodb_flush_log_at_timeout（默认为1s）时间内的数据，建议设置innodb_flush_log_at_trx_commit=2。
二、主从复制层面丢失数据
我们先了解一下binlog的刷新机制以及MySQL的内部XA事务是如何保证binlog与redo log的一致性的。
1、内部XA事务原理
MySQL XA分为两类，内部XA与外部XA；
内部XA用于同一实例下跨多个引擎的事务，由Binlog作为协调者；
外部XA用于跨多个MySQL实例的分布式事务，需要应用层介入作为协调者(崩溃时的悬挂事务，全局提交还是回滚，需要由应用层决定，对应用层的实现要求较高)；
最常见的内部XA事务存在于binlog与InnoDB存储引擎之间，从而保证了主从环境的数据一致性。在事务提交时，先写binlog日志，然后再写由InnoDB存储引起的redo日志。对于这个操作过程，要求必须是原子性的，即两者都要写入成功。内部XA事务机制就是来保障binlog和redo log都写入成功。
内部XA事务简化的大致流程：
①、事务提交后，InnoDB存储引擎会做一个prepare操作，将事务的XID写入到redo log中。
②、写binlog日志。
③、再该事务的commit信息写入到redo log中。
如果是在步骤①和②时失败，整个事务回滚。
如果是在步骤③时失败，MySQL在重启后会首先检查UXID是否已经提交，若没有提交，则在存储引擎再执行一次提交操作。这样就保障了redo log和binlog数据的一致性，防止数据丢失。
2、binlog刷新机制
我们从内部的XA事务知道，Master写binlog。Binlog日志是如何写、什么时机写？分析控制参数sync_binlog是如何做的：
= 0 ：表示MySQL不控制binlog的刷新，由文件系统自己控制它的缓存的刷新
> 0 ：表示每sync_binlog次事务提交，MySQL调用文件系统的刷新操作将缓存刷下去
其中最安全的就是sync_binlog设置为1，表示每次事务提交，MySQL都会把binlog缓存刷下去，这样在掉电等情况下，系统才有可能丢失1个事务的数据。同时对系统的IO消耗也是非常大的。
3、Master非实时写redo和binlog丢失数据
我们从存储引擎层面丢失数据章节中知道，如果innodb_flush_log_at_trx_commit没有设置为1，仍会丢数据的。
如果严格要求保证数据不丢失，必须设置redo log和bin log实时刷盘。但是保证的数据的安全性，却性能下降了。
4、slave非实时写redo和binlog丢失数据
如果在Master日志记录，事务提交均正常。而在slave出现异常甚至宕机，此时数据会丢失么？
我们知道主从同步机制中SQL Thread的作用是事件重放。在slave机器上会存在三个文件来保证事件的正确重放：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个文件如果不及时落地，则MySQL crash后会导致数据的不一致。
5、Master宕机后无法及时恢复造成的丢失数据
当master出现故障后，binlog未及时传到slave，或者各个slave收到的binlog不一致。且master无法在第一时间恢复，这个时候我们该怎么处理？
如果master不切换，则整个数据库只能只读，影响应用的运行。
如果将某个的slave提升为新的master，那么原master未来得及传到slave的binlog的数据则会丢失，并且还涉及到下面2个问题。
①、各个slave之间接收到的binlog不一致，如果强制拉起一个slave，则slave之间数据会不一致。
②、原master恢复正常后，由于新的master日志丢弃了部分原master的binlog日志，这些多出来的binlog日志怎么处理？
对于上面出现的问题，
一种方法是确保binlog传到从库，或者说保证主库的binlog有多个拷贝。
第二种方法就是允许数据丢失，制定一定的策略，保证最小化丢失数据。
①、确保binlog全部传到从库
方案一：使用semi
sync（半同步）方式，事务提交后，必须要传到slave，事务才能算结束。对性能影响很大，依赖网络适合小tps系统。
方案二：双写binlog，通过DBDR
OS层的文件系统复制到备机，或者使用共享盘保存binlog日志。
方案三：在数据层做文章，比如保证数据库写成功后，再异步队列的方式写一份，部分业务可以借助设计和数据流解决。
②、保证数据最小化丢失
上面的方案设计及架构比较复杂，如果能容忍数据的丢失，可以考虑使用淘宝的TMHA复制管理工具。
当master宕机后，TMHA会选择一个binlog接收最大的slave作为master。当原master宕机恢复后，通过binlog的逆向应用，把原master上多执行的事务回退掉。
参考
《高性能MySQL》
MySQL数据丢失情况分析
标签：
热心网友
时间：2022-04-09 03:32
