Solve the problem that the MySQL database crashes unexpectedly, causing the table data file to be damaged and unable to start

Problem:

The MySQL database crashed unexpectedly and the database could not be started.

Error log:

Startup error: service mysqld restart

ERROR! MySQL server PID file could not be found!
Starting MySQL. ERROR! The server quit without updating PID file (/www/wdlinux/mysql/var/iZ2358oz5deZ.pid).

Database error log:

200719 22:07:43 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Error: trying to add tablespace 840 of name './ob_wp/ob_termmeta.ibd'
InnoDB: to the tablespace memory cache, but tablespace
InnoDB: 840 of name './dev_nss/dg_queue.ibd' already exists in the tablespace
InnoDB: memory cache!
200719 22:07:43 mysqld_safe mysqld from pid file /www/wdlinux/mysql/var/iZ2358oz5deZ.pid ended

Tip: When the table space information is read at database startup, the data file of the ob_users.ibd table in the ob-wp library already exists in the table space.

expand:

The storage engine is myisam. You will see three types of files in the database directory: .frm, .myi, and .myd
(a) *.frm--table definition, which is a file that describes the table structure.
(b) *.MYD--"D" data information file, which is the data file of the table.
(c) *.MYI--"I" index information file, which is the data tree of any index in the table data file. The storage engine is InnoDB. There are two types of files in the data directory: .frm and .ibd
(a) *.frm--file with table structure.
(b) *.ibd--table data file

Source: https://www.cnblogs.com/liucx/

Method 1:

According to the prompt information, the InnoDB table is determined to be damaged, so try to back up the table structure and table data files in the dev_nss library directory
mv ob_termmeta.ibd ob_termmeta.ibd,bak
mv ob_termmeta.frm ob_termmeta.frm.bak
Then I restarted mysql, but still couldn't start it. It prompted that other table data files already existed. I backed up the damaged files three times in a row, but still couldn't start it. Therefore, this method is abandoned.

Method 2:

1. Consult the official website documentation, add configuration to the MySQL configuration file /etc/my.cnf, and start successfully
[mysqld]
innodb_force_recovery = 1

2. Back up the database
mysqldump -h172.168.2.100 -uroot -p -A > mysql_all_bak.sql
If the report does not exist, mysqldump can add the parameter: --force to skip the error

3. Delete the database
drop database hxdb; or mv hxdb hxdb_bak (for safety)

4. Remove the parameter innodb_force_recovery
After removing the previously set parameters, restart the database

5. Import data
mysql -uroot -p < mysql_all_bak.sql
Warning: Using a password on the command line interface can be insecure.
ERROR 1050 (42S01) at line 25: Table '`hxdb`.`tb_info`' already exists

If it prompts that the table already exists, it is because after removing the innodb_force_recovery parameter, the database will roll back and generate the corresponding ibd file, so you need to delete the file and re-import it.
mysql -uroot -p < mysql_all_bak.sql

Note:

innodb_force_recovery parameter explanation: Crash recovery mode, usually only changed in serious troubleshooting situations. Possible values ​​are from 0 to 6.

Set this variable to a value greater than 0 only in an emergency, so that you can start InnoDB and dump your tables. As a safety measure, InnoDB prevents insert, update, or delete operations when innodb_force_recovery is greater than 0.
In 5.6.15, innodb_force_recovery is set to 4 or greater to put InnoDB into read-only mode. Because relay_log_info_repository=TABLE and master_info_repository=TABLE store information in InnoDB tables, these restrictions can cause replication management commands to fail with errors.

innodb_force_recovery is 0 by default (normal startup without forced recovery). Permitted non-zero values ​​for innodb_force_recovery are 1 through 6. Larger values ​​include the functionality of smaller values. For example, value 3 contains all the features of values ​​1 and 2.
If you are able to dump the table with an innodb_force_recovery value of 3 or less, you are relatively safe from losing only some of the data on a single page that was corrupted. Values ​​of 4 or greater are considered dangerous because data files may become permanently corrupted. A value of 6 is considered excessive because database pages are left in an out-of-date state, which in turn may expose B-trees and other database structures to more corruption.

For safety reasons, InnoDB prevents INSERT, UPDATE, or DELETE operations when innodb_force_recovery is greater than 0. As of MySQL 5.6.15, setting innodb_force_recovery to 4 or more places InnoDB in read-only mode.
1 (SRV_FORCE_IGNORE_CORRUPT)
Let the server run even if it detects a corrupted page. Try having SELECT * FROM tbl_name skip corrupted index records and pages, which can help dump the table.
2 (SRV_FORCE_NO_BACKGROUND)
Blocks the main thread and any cleanup threads from running. If a crash occurs during a cleanup operation, this recovery value will prevent the crash.
3 (SRV_FORCE_NO_TRX_UNDO)
Do not run transaction rollback after crash recovery.
4 (SRV_FORCE_NO_IBUF_MERGE)
Prevents insert buffer merge operations. If they will cause a crash, don't do them. Table statistics are not calculated. This value may permanently corrupt the data file. After using this value, be prepared to drop and recreate all secondary indexes. In MySQL 5.6.15, set InnoDB to read-only.
5 (SRV_FORCE_NO_UNDO_LOG_SCAN)
Don't look at the undo log when you start the database: InnoDB will consider even incomplete transactions as committed. This value may permanently corrupt the data file. In MySQL 5.6.15, set InnoDB to read-only.
6 (SRV_FORCE_NO_LOG_REDO)
No redo log rollforward is performed on recovery. This value may permanently corrupt the data file. Leaving database pages in an out-of-date state, which in turn may introduce more corruption to B-trees and other database structures. In MySQL 5.6.15, set InnoDB to read-only.

Source: https://www.cnblogs.com/liucx/

Refer to the official website:
https://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_force_load_corrupted

Hope this helps

This is the end of this article about how to solve the problem of MySQL database crashing unexpectedly causing table data files to be damaged and unable to start. For more related solutions to the problem of MySQL database crashing unexpectedly causing table data files to be damaged and unable to start, please search for previous articles on 123WORDPRESS.COM or continue to browse the related articles below. I hope you will support 123WORDPRESS.COM in the future!