mysql 中mysql表丢失数据库_MySQL数据库丢失数据场景分析（I）

原创：Perry.Zhang

发表：Jane.Hoo

1.引擎层数据丢失场景

InnoDB作为MySQL支持事务的存储引擎，同Oracle类似，事务提交时需要写redo、undo log。InnoDB采用预写日志(Write Ahead Log)策略，数据页的变更会首先在内存中完成，同时将事务操作顺序记录到redo log中。完成上述的操作后表示该事务已经完成，可以返回给应用已提交的信息。但此时实际被更改的数据页还保存在内存中(称为脏页)，并没有flush到磁盘上即没有落地。刷盘操作一般会在达到一定条件后，触发checkpoint机制，此时会将内存中的脏页合并写入到磁盘里，完成数据同步。刷盘策略由innodb_flush_log_at_trx_commit参数控制的策略。如下图：

参数innodb_flush_log_at_trx_commit：

=0 ：每秒 write os cache & flush disk

=1 ：每次commit都 write os cache & flush disk

=2 ：每次commit都 write os cache，然后根据innodb_flush_log_at_timeout参数(默认为1s) flush disk

innodb_flush_log_at_trx_commit=1最为安全，因为每次commit都保证redo log写入了disk。但是这种方式性能对DML性能来说比较低，在我们的测试中发现，如果设置为2，DML性能要比设置为1高10倍左右(所以在短信平台出现写入问题时，已将该参数设置为2)。

innodb_flush_log_at_trx_commit为0或2的区别主要体现在在mysql service crash或system crash时丢失事务的类型：

1)当mysql service crash时，设置为0就会丢失1秒内的所有已提交及未提交的事务，且无法回滚(因为redo log还记录在log buffer中，没有落盘到redo log)。而设置为2时，每次提交都会写入到os cache中，即使service crash掉，也只会丢失1秒内所有未提交的事务，而已提交的事务已经写入redo log中，可以回滚。

2)当system crash时，与上述类似。

因此，业内的共识是在一些DML操作频繁的场景下，参数innodb_flush_log_at_trx_commit设置为2。

虽然这样就存在丢数据的风险：当出现mysql service crash时，重启后InnoDB会进行crash recovery，则会丢失innodb_flush_log_at_timeout秒内的未提交的数据。已提交的数据则可由应用中的事务补偿机制处理。但是IO性能可以提高至少10倍。

PS:当开启了内部XA事务(默认开启)，且开启binlog，情况稍有不一样。见下文。

1.2.MyISAM丢失数据场景分析

MyISAM存储引擎在我们的生产环境中基本没有使用。而且我们线上的5.6版本已将系统的数据字典表元数据表等系统表的默认存储引擎修改为InnoDB。

由于MyISAM不支持事务，且没有data cache，所有DML操作只写到OS cache中，flush disk操作均由OS来完成，因此如果服务器宕机，这部分数据肯定会丢失。

InnoDB作为MySQL支持事务的存储引擎，同Oracle类似，事务提交时需要写redo、undo log。InnoDB采用预写日志(Write Ahead Log)策略，数据页的变更会首先在内存中完成，同时将事务操作顺序记录到redo log中。完成上述的操作后表示该事务已经完成，可以返回给应用已提交的信息。但此时实际被更改的数据页还保存在内存中(称为脏页)，并没有flush到磁盘上即没有落地。刷盘操作一般会在达到一定条件后，触发checkpoint机制，此时会将内存中的脏页合并写入到磁盘里，完成数据同步。刷盘策略由innodb_flush_log_at_trx_commit参数控制的策略。如下图：

参数innodb_flush_log_at_trx_commit：

=0 ：每秒 write os cache & flush disk

=1 ：每次commit都 write os cache & flush disk

=2 ：每次commit都 write os cache，然后根据innodb_flush_log_at_timeout参数(默认为1s) flush disk

innodb_flush_log_at_trx_commit=1最为安全，因为每次commit都保证redo log写入了disk。但是这种方式性能对DML性能来说比较低，在我们的测试中发现，如果设置为2，DML性能要比设置为1高10倍左右(所以在短信平台出现写入问题时，已将该参数设置为2)。

innodb_flush_log_at_trx_commit为0或2的区别主要体现在在mysql service crash或system crash时丢失事务的类型：

1)当mysql service crash时，设置为0就会丢失1秒内的所有已提交及未提交的事务，且无法回滚(因为redo log还记录在log buffer中，没有落盘到redo log)。而设置为2时，每次提交都会写入到os cache中，即使service crash掉，也只会丢失1秒内所有未提交的事务，而已提交的事务已经写入redo log中，可以回滚。

2)当system crash时，与上述类似。

因此，业内的共识是在一些DML操作频繁的场景下，参数innodb_flush_log_at_trx_commit设置为2。

虽然这样就存在丢数据的风险：当出现mysql service crash时，重启后InnoDB会进行crash recovery，则会丢失innodb_flush_log_at_timeout秒内的未提交的数据。已提交的数据则可由应用中的事务补偿机制处理。但是IO性能可以提高至少10倍。

PS:当开启了内部XA事务(默认开启)，且开启binlog，情况稍有不一样。见下文。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30109892/viewspace-1814513/，如需转载，请注明出处，否则将追究法律责任。