ERROR 2006 (HY000): MySQL server has gone away
MySQL: Forcing InnoDB Recovery
If there is database page corruption, you may want to dump your tables from the database with SELECT INTO OUTFILE. Usually, most of the data obtained in this way is intact. Even so, the corruption may cause SELECT * FROM tbl_name statements or InnoDB background operations to crash or assert, or even to cause InnoDB roll-forward recovery to crash.
However, you can force the InnoDB storage engine to start up while preventing background operations from running, so that you are able to dump your tables. For example, you can add the following line to the [mysqld] section of your option file before restarting the server:
[mysqld]
innodb_force_recovery = 4
Read more...
Also, when dealing with Innodb recovery for bad corruptions you may want to:
- Start with lower innodb_force_recovery settings and see which one allows you to start.
- After recovery succeeds you can try restarting with lower recovery setting as it may allow you to recover more data or avoid full dump and restore.
- Another trick is to do full system restart before attempting recovery. In few instances problems were caused by Kernel bug, inconsistence in OS cache or RAID cache or something else which made problem to disappear after system restart.
MySQL: Forcing InnoDB Recovery
If there is database page corruption, you may want to dump your tables from the database with SELECT INTO OUTFILE. Usually, most of the data obtained in this way is intact. Even so, the corruption may cause SELECT * FROM tbl_name statements or InnoDB background operations to crash or assert, or even to cause InnoDB roll-forward recovery to crash.
However, you can force the InnoDB storage engine to start up while preventing background operations from running, so that you are able to dump your tables. For example, you can add the following line to the [mysqld] section of your option file before restarting the server:
[mysqld]
innodb_force_recovery = 4
Read more...
Also, when dealing with Innodb recovery for bad corruptions you may want to:
- Start with lower innodb_force_recovery settings and see which one allows you to start.
- After recovery succeeds you can try restarting with lower recovery setting as it may allow you to recover more data or avoid full dump and restore.
- Another trick is to do full system restart before attempting recovery. In few instances problems were caused by Kernel bug, inconsistence in OS cache or RAID cache or something else which made problem to disappear after system restart.
Learn more about MySQL using the following books/tutorials:
Sams Teach Yourself Mysql in 24 Hours
MySQL Tutorial
Managing and Using Mysql
High Performance Mysql: Optimization, Backups, Replication and Balancing
Mysql Cookbook
0 comments
Post a Comment