mysql 数据库无法启动(Ignoring the redo log due to missing MLOG_CHECKPOINT between the checkpoint .... and)

数据库机器的CPU和主板都换了,重新开机,发现mysql数据库无法启动!
在这里插入图片描述

Ignoring the redo log due to missing MLOG_CHECKPOINT between the checkpoint  .... and ...

这个问题出现在mysql 5.7之后的版本,主要的原因是mysql会在最新的checkpoint完成后都会在redo log写一个一字节的MLOG_CHECKPOINT 标记,用来标记在此之前的redo都已checkpoint完成。如果处于任何原因没有找到这个标记,那么整个redo日志文件都会被忽略。出现这个错误的话,最好是有备份进行恢复,如果没有做好备份,那只能采取非常规的启动方式,但可能造成数据丢失。
移除当前使用的redo日志文件,然后可以试着启动数据库,结果启动失败!
在这里插入图片描述这时的提示:[ERROR] InnoDB: Page [page id: space=0, page number=0] log sequence number 178377412422 is in the future! Current system log sequence number 165909011496. 这样的错误,这是因为mysql writer线程按照配置的时间间隔以page为单位刷新buffer数据到磁盘,当数据刷新到磁盘的时候,新写入磁盘的page包含了较新的lsn,此时系统system表空间头的lsn并没有同步更新,通常这是检查点线程的工作。在正常的崩溃恢复中,mysql可以借助redo日志来进行前滚和回滚,但是此时redo日志已经被我们删掉了,mysql无法进行恢复操作。此时,我们设置innodb_force_recovery=3来强制启动mysql,仍然启动不成功,改成4,启动了!
再使用mysqldump导出备份,结果噩梦又降临了!
在这里插入图片描述
设置参数innodb_force_recovery=5,数据库仍然启动失败,再设置成6,启动成功!用sqldump顺利吧数据备份出来了!
再初始化数据库,把刚刚备份的数据库导入,行了!数据库恢复成功完成!
这里的关键是设置innodb_force_recovery参数,对应这个参数的说明如下:

  1. (SRV_FORCE_IGNORE_CORRUPT):忽略检查到的corrupt页。
  2. (SRV_FORCE_NO_BACKGROUND):阻止主线程的运行,如主线程需要执行full purge操作,会导致crash
  3. (SRV_FORCE_NO_TRX_UNDO):不执行事务回滚操作。
  4. (SRV_FORCE_NO_IBUF_MERGE):不执行插入缓冲的合并操作。
  5. (SRV_FORCE_NO_UNDO_LOG_SCAN):不查看重做日志,InnoDB存储引擎会将未提交的事务视为已提交
  6. (SRV_FORCE_NO_LOG_REDO):不执行前滚的操作。
姚远ACE CSDN认证博客专家 ACE 华为云 MVP
Oracle ACE,华为云 MVP,Oracle10g,12c OCM; MySQL 5.6,5.7,8.0 OCP;CCNA; EMC Certified; IBM P Certified; RHCE; SQLServer 764; DB2 Certified; TOEIC 890;获得过两次国家部级科技进步奖;发明过两项计算机专利。微信:yaoyuanace 邮箱:yaoyuanace(at)qq.com
已标记关键词 清除标记
<div><p>I am having a problem with the image when using it with docker-compose, something makes the container to shutdown after running docker-compose up.</p> <p>Here is my docker-compose.yml file:</p> <pre><code>yaml version: '2' services: web: image: messenger ports: - "8000:8000" environment: MYSQL_USER: marwan MYSQL_PASSWORD: <pass> MYSQL_DATABASE: messenger depends_on: - mysql - elastic mysql: image: mysql environment: MYSQL_USER: marwan MYSQL_PASSWORD: <pass> MYSQL_DATABASE: messenger MYSQL_ROOT_PASSWORD: <pass> elastic: image: docker.elastic.co/elasticsearch/elasticsearch:5.5.2 ports: - 9200:9200 </pass></pass></pass></code></pre> <p>and here is the output:</p> <pre><code> Attaching to messenger_mysql_1, messenger_elastic_1, messenger_web_1 mysql_1 | 2017-09-01T10:01:43.480240Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). mysql_1 | 2017-09-01T10:01:43.481789Z 0 [Note] mysqld (mysqld 5.7.19) starting as process 1 ... mysql_1 | 2017-09-01T10:01:43.485266Z 0 [Note] InnoDB: PUNCH HOLE support available mysql_1 | 2017-09-01T10:01:43.485302Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins mysql_1 | 2017-09-01T10:01:43.485306Z 0 [Note] InnoDB: Uses event mutexes mysql_1 | 2017-09-01T10:01:43.485310Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier mysql_1 | 2017-09-01T10:01:43.485314Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3 mysql_1 | 2017-09-01T10:01:43.485321Z 0 [Note] InnoDB: Using Linux native AIO mysql_1 | 2017-09-01T10:01:43.485652Z 0 [Note] InnoDB: Number of pools: 1 mysql_1 | 2017-09-01T10:01:43.485777Z 0 [Note] InnoDB: Using CPU crc32 instructions mysql_1 | 2017-09-01T10:01:43.487402Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M mysql_1 | 2017-09-01T10:01:43.495110Z 0 [Note] InnoDB: Completed initialization of buffer pool mysql_1 | 2017-09-01T10:01:43.497050Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority(). mysql_1 | 2017-09-01T10:01:43.509905Z 0 [Note] InnoDB: Highest supported file format is Barracuda. mysql_1 | 2017-09-01T10:01:43.510822Z 0 [Note] InnoDB: Log scan progressed past the checkpoint lsn 2539618 mysql_1 | 2017-09-01T10:01:43.510851Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 2539872 mysql_1 | 2017-09-01T10:01:43.510897Z 0 [ERROR] InnoDB: Ignoring the redo log due to missing MLOG_CHECKPOINT between the checkpoint 2539618 and the end 2539872. mysql_1 | 2017-09-01T10:01:43.510934Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error mysql_1 | 2017-09-01T10:01:44.111639Z 0 [ERROR] Plugin 'InnoDB' init function returned error. mysql_1 | 2017-09-01T10:01:44.111678Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. mysql_1 | 2017-09-01T10:01:44.111690Z 0 [ERROR] Failed to initialize plugins. mysql_1 | 2017-09-01T10:01:44.111695Z 0 [ERROR] Aborting mysql_1 | mysql_1 | 2017-09-01T10:01:44.111711Z 0 [Note] Binlog end mysql_1 | 2017-09-01T10:01:44.111801Z 0 [Note] Shutting down plugin 'CSV' mysql_1 | 2017-09-01T10:01:44.112663Z 0 [Note] mysqld: Shutdown complete mysql_1 | messenger_mysql_1 exited with code 1 </code></pre> <p>What am I doing wrong?</p><p>该提问来源于开源项目:docker-library/mysql</p></div>
©️2020 CSDN 皮肤主题: Age of Ai 设计师:meimeiellie 返回首页