Mysql database crash _ MySQL

Mysql database crash bitsCN.com

There is a crm system, where a report is made, where the source code and database are placed on the same server, and the database uses mysql. However, on the suddenly reported error page today, the system prompts that the connection cannot be connected using root @ locahost.

Log on with the command line and prompt can't connection to mysql; then check the port and find that the database has been closed;

Symptom:

You can log on to crm and click other pages. when you click report to View report data, an error is reported and the database crashes.

Preliminary analysis shows that a program error or database error causes a database crash and thus automatic disconnection

Solution:

1. view the mysql database error log content as follows:

InnoDB: Database page Upload uption on disk or a failed

InnoDB: file read of page 5761.

InnoDB: You may have to recover from a backup.

InnoDB: It is also possible that your operating

InnoDB: system has upted its own file cache

InnoDB: and rebooting your computer removes

InnoDB: error.

InnoDB: If the specified UPT page is an index page

InnoDB: you can also try to fix the specified uption

InnoDB: by dumping, dropping, and reimporting

InnoDB: the specified UPT table. You can use CHECK

InnoDB: TABLE to scan your table for processing uption.

InnoDB: See also http://dev.mysql.com/doc/refman/5.1/en/forcing-recovery.html

InnoDB: about forcing recovery.

InnoDB: Ending processing because of a temporary UPT database page.

2. search for the problem online for the following reasons:

Database table corruption leads to data access errors, leading to database crashes. mysql checks table corruption, but the table is innodb and cannot be repaired.

Solution: add innodb_force_recovery = 4 under mysqld in my. ini

Note:

1 (srv_force_ignore_0000upt): ignore the checked UPT page.

2 (SRV_FORCE_NO_BACKGROUND): prevents the main thread from running. if the main thread needs to execute the full purge operation, crash may occur.

3 (SRV_FORCE_NO_TRX_UNDO): no transaction rollback is performed.

4 (SRV_FORCE_NO_IBUF_MERGE): insert buffer merging is not performed.

5 (SRV_FORCE_NO_UNDO_LOG_SCAN): If you do not view redo logs, the InnoDB storage engine considers uncommitted transactions as committed.

6 (SRV_FORCE_NO_LOG_REDO): do not roll back.

3. after the database is restarted, the problem still exists and the error is identified as UPT. Therefore, innodb_force_recovery should be set to 1, so my. in ini, the innodb_force_recovery value is changed to 1 and the database is restarted.

4. the problem persists after restart

5. perform the following operations: (important)

Modify my. ini to annotate this line of innodb_force_recovery

# Innodb_force_recovery = 1

Restart the database, stop the database, modify my. ini, and remove the comment.

Innodb_force_recovery = 1

Restart the database and solve the problem

Ps: I have never figured out the operation in step 5. Why do I need to comment out the restart before it can take effect after it is enabled? I personally think there may be some cache causes. Record here to continue exploring the root cause of the problem...

From the column of sugar cane gourd

BitsCN.com