• 【原创】大叔问题定位分享(32)mysql故障恢复


    mysql启动失败,一直crash,报错如下:

    2019-03-14T11:15:12.937923Z 0 [Note] InnoDB: Uncompressed page, stored checksum in field1 1118110825, calculated checksums for field1: crc32 2889511119/2044155182, innodb 4005355497, none 3735928559, stored checksum in field2 1244127832, calculated checksums for field2: crc32 2889511119/2044155182, innodb 952099433, none 3735928559, page LSN 4 2819295388, low 4 bytes of LSN at page end 2486405135, page number (if stored to page already) 3971, space id (if created with >= MySQL-4.1.1 and stored already) 1840
    InnoDB: Page may be an index page where index id is 6640
    2019-03-14T11:15:12.937950Z 0 [Note] InnoDB: Index 6640 is `GEN_CLUST_INDEX` in table `zabbix`.`history_uint`
    2019-03-14T11:15:12.937955Z 0 [Note] InnoDB: It is also possible that your operating system has corrupted its own file cache and rebooting your computer removes the error. If the corrupt page is an index page. You can also try to fix the corruption by dumping, dropping, and reimporting the corrupt table. You can use CHECK TABLE to scan your table for corruption. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery.

    看起来是文件损坏了,我有备库,所以希望主库尽快恢复,然后再恢复数据,直接移走有问题的frm和ibd,再启动,报错:

    2019-03-14T11:23:37.246589Z 0 [ERROR] InnoDB: Tablespace 1840 was not found at ./zabbix/history_uint.ibd.
    2019-03-14T11:23:37.246594Z 0 [ERROR] InnoDB: Set innodb_force_recovery=1 to ignore this and to permanently lose all changes to the tablespace.
    2019-03-14T11:23:37.247018Z 0 [ERROR] InnoDB: Cannot continue operation.

    修改my.cnf

    innodb_force_recovery=1

    再重启正常,尝试重新新建表,但是各种报错:

    mysql> CREATE TABLE `history` (
        ->   `itemid` bigint(20) unsigned NOT NULL,
        ->   `clock` int(11) NOT NULL DEFAULT '0',
        ->   `value` double(16,4) NOT NULL DEFAULT '0.0000',
        ->   `ns` int(11) NOT NULL DEFAULT '0',
        ->   KEY `history_1` (`itemid`,`clock`)
        -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
    ERROR 1813 (HY000): Tablespace '`zabbix`.`history`' exists.
    mysql> alter table history discard tablespace;
    ERROR 1146 (42S02): Table 'zabbix.history' doesn't exist
    mysql> drop tablespace history;
    ERROR 1529 (HY000): Failed to drop TABLESPACE history
    mysql> drop table history;
    ERROR 1051 (42S02): Unknown table 'zabbix.history'

    完全没有办法操作,这时的解决方法是:

    • CREATE TABLE bad_table ENGINE=MyISAM ...
    • rm bad_table.ibd
    • DROP TABLE bad_table
    • CREATE TABLE bad_table ENGINE=INNODB ...

    然后再恢复数据

    也有可能报错时并没有指出具体出错的文件或者表,类似

    2019-11-25T12:54:59.258844Z 0 [ERROR] InnoDB: Database page corruption on disk or a failed file read of page [page id: space=0, page number=593]. You may have to recover from a backup.

    这时需要使用mysqlcheck命令来检查某个数据库

    # mysqlcheck -uroot -proot $dbname

    也可以检查所有的数据库

    # echo 'show databases'|mysql -uroot -proot|xargs -i mysqlcheck -uroot -proot {}

    输出结果为两列,第二列是status,正常为OK,其他值为异常,找到异常的表,删除或者将数据导出后再导入,则问题修复;

    参考:
    https://stackoverflow.com/questions/15694168/error-tablespace-for-table-xxx-exists-please-discard-the-tablespace-before-imp

  • 相关阅读:
    test
    有偏估计和无偏估计
    Spark Shuffle
    Adaboost算法推导
    Spark优化 – 基础篇
    决策树 – 回归
    HBase的文件合并(minor/major compact)
    HBase的列式存储
    centos7配置固定ip
    Generate a Certificate Signing Request (CSR) in macOS Keychain Access
  • 原文地址:https://www.cnblogs.com/barneywill/p/10532996.html
Copyright © 2020-2023  润新知