• 【原创】大叔问题定位分享(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

  • 相关阅读:
    判断是否是三角形,三角形面积,三角形内外切圆半径和面积
    输入从a加到b的两个数字
    九九乘法表
    某公司销售员工的年终奖根据该员工的年销售总额s提成,年销售总额超过1万元才提成,超过部分提成比例如下:
    判断是否是闰年?
    从键盘上输入三个点的坐标值(1,1)、(2,4)、(3,2),编程求该三角形的面积。
    输入一个正方形的边长,输出正方形的外接圆和内接圆的面积。
    .输入一个4位正整数,以相反的次序输出,例如,输入1234,输出为4321。
    SecoClient在win10系统中连接失败解决方案
    PHP 关于判断输入日期是否合法
  • 原文地址:https://www.cnblogs.com/barneywill/p/10532996.html
Copyright © 2020-2023  润新知