大叔问题定位分享(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
相关文章
- MySQL中实现事务的方式(mysql事务实现)
- 深入MySQL:嵌套事务的处理(mysql嵌套事务)
- MySQL: 不止1的世界(mysql不等于1)
- 导入MySQL中的外部数据(mysql导入外部数据)
- MySQL查询数据排序:最佳方法(mysql查询数据排序)
- MySQL数据库批量更新技巧分享(mysql数据库批量更新)
- MySQL中的二进制数据存储与管理(mysql二进制数据)
- 利用MySQL高效查询海量数据(mysql海量数据查询)
- MySQL 备份加锁:安全保障必不可少(mysql备份锁表)
- MySQL数据库容灾策略: 优化容错能力!(mysql数据库容灾方案)
- 如何彻底删除MySQL服务?25个心得分享(彻底删除mysql服务)
- 使用MySQL定位昨天日期的方法(mysql昨天日期)
- MySQL源码调试技巧分享(mysql源码调试)
- 化MySQL数字格式化技巧分享(mysql 数字 格式)
- 轻松学习MySQL数据库搭建教程(mysql数据库搭建教程)
- Mysql存储过程创建表:探索实现方式(mysql存储过程创建表)
- 怎样在C语言中引用Mysql数据库(c 中引用mysql)
- 使用MySQL实现两表联合更新的方法分享(mysql 两表联合更新)
- MySQL表合并查询技巧分享(mysql两表查询合并)
- 基于ASP的MySQL数据库连接实现(asp连接数mysql)
- MySQL实现多条数据删除(mysql中删除多条数据)
- MySQL中的列序号如何使用(mysql中列序号)
- MySQL是否区分大小写(mysql中分大小写吗)
- MySQL三表关联优化技巧分享(mysql三表关联优化)
- 解决MySQL数据导入失败的技巧分享(mysql一直导入失败)
- MySQL表格排序技巧分享(mysql一张表排序)
- 技术分享MySQL一周查询实战指南(mysql一周查询)
- 技术分享MySQL数据表不使用主键,有哪些需要注意的问题(mysql 不使用主键)
- 轻松搞定MySQL下载安装步骤详解(mysql 下载安转)
- MySQL注册服务器失败解决方法分享(mysql不能注册服务器)
- 快速稳定的MySQL 55中文下载方式分享(mysql下载5.5中文)
- mysql锁表锁行语句分享(MySQL事务处理)