In this post, I will explain how to recover an innodb crash in a Linux server.
STEP 1:
To confirm innodb crash, first of all add the following parameter to mysql configuration file "/etc/my.cnf" and try to restart mysql.
--------
skip-innodb
-------
If it starts fine now, then we can confirm that it is a innodb crash.
Now, remove the parameter "skip-innodb" from my.cnf and follow the steps below to recover innodb:
STEP 1:
Add this line to your /etc/my.cnf configuration file: [mysqld] innodb_force_recovery = 4
STEP 2:
Restart Mysql now. Database should start, but with innodb_force_recovery in my.cnf. All insert and update operation will be ignored.
STEP 3:
Mysqldump all databases.
mysqldump -A > dump.sql
STEP 4:
Dump the user and db table from mysql databae
mysqldump mysql user > user.sql
mysqldump mysql db > db.sql
if the dump fails create a copy of current /var/lib/mysql directory
cp -pr /var/lib/mysql /var/lib/mysql_bak
STEP 5:
Shutdown database and delete the data directory (better if you move it) and execute mysql_install_db from shell to create MySQL default tables.
mysql_install_db
STEP 6:
Remove the "innodb_force_recovery" line from your /etc/my.cnf file and restart the database.
STEP 7:
Restore everything from your backup except default 'mysql' database. For restoring the previous user db privileges do the following.
cp -rp /var/lib/mysql_bak/mysql /var/lib/mysql/oldmsqldb
mysqldump oldmysqldb user > oldmysqldb_user.sql
mysql mysql user < /var/lib/mysql/oldmysqldb_user.sql
mysql mysql db < /var/lib/mysql/oldmysqldb_db.sql
Keep reading :)
STEP 1:
To confirm innodb crash, first of all add the following parameter to mysql configuration file "/etc/my.cnf" and try to restart mysql.
--------
skip-innodb
-------
If it starts fine now, then we can confirm that it is a innodb crash.
Now, remove the parameter "skip-innodb" from my.cnf and follow the steps below to recover innodb:
STEP 1:
Add this line to your /etc/my.cnf configuration file: [mysqld] innodb_force_recovery = 4
STEP 2:
Restart Mysql now. Database should start, but with innodb_force_recovery in my.cnf. All insert and update operation will be ignored.
STEP 3:
Mysqldump all databases.
mysqldump -A > dump.sql
STEP 4:
Dump the user and db table from mysql databae
mysqldump mysql user > user.sql
mysqldump mysql db > db.sql
if the dump fails create a copy of current /var/lib/mysql directory
cp -pr /var/lib/mysql /var/lib/mysql_bak
STEP 5:
Shutdown database and delete the data directory (better if you move it) and execute mysql_install_db from shell to create MySQL default tables.
mysql_install_db
STEP 6:
Remove the "innodb_force_recovery" line from your /etc/my.cnf file and restart the database.
STEP 7:
Restore everything from your backup except default 'mysql' database. For restoring the previous user db privileges do the following.
cp -rp /var/lib/mysql_bak/mysql /var/lib/mysql/oldmsqldb
mysqldump oldmysqldb user > oldmysqldb_user.sql
mysql mysql user < /var/lib/mysql/oldmysqldb_user.sql
mysql mysql db < /var/lib/mysql/oldmysqldb_db.sql
Keep reading :)
No comments:
Post a Comment
Note: only a member of this blog may post a comment.