Difference between revisions of "Recovering from mysql failures"
From Biowikifarm Metawiki
m (+date versioned automatically) |
|||
| (3 intermediate revisions by 2 users not shown) | |||
| Line 1: | Line 1: | ||
==Recovering from InnoDB corruption== | ==Recovering from InnoDB corruption== | ||
| − | Various procedures exist to recover from various kind of mysql database corruptions or crashes. This presently documents a procedure that was needed after the system disk was completely filled and innodb ended in a non-recoverable state. The mysql ISAM structures were not affected. | + | Various procedures exist to recover from various kind of mysql database corruptions or crashes. A good resource for other situations may be [http://www.softwareprojects.com/resources/programming/t-how-to-fix-mysql-database-myisam-innodb-1634.html]. |
| + | |||
| + | This presently documents a procedure that was needed after the system disk was completely filled and innodb ended in a non-recoverable state. The mysql ISAM structures were not affected. | ||
Symptoms: innodb does not start, without error message. phpmyadmin shows all databases, but for the tables inside the database that use innodb, instead of size etc., at the end "in use" is displayed (MyISAM table show fully correctly). | Symptoms: innodb does not start, without error message. phpmyadmin shows all databases, but for the tables inside the database that use innodb, instead of size etc., at the end "in use" is displayed (MyISAM table show fully correctly). | ||
| Line 7: | Line 9: | ||
When trying to dump the database, the dump does not start at all, or after setting set innodb_force_recovery = 4, the dump starts, but terminates with a message like: "mysqldump: Got error: 1033: Incorrect information in file: './enwiki/account_credentials.frm' when using LOCK TABLES" | When trying to dump the database, the dump does not start at all, or after setting set innodb_force_recovery = 4, the dump starts, but terminates with a message like: "mysqldump: Got error: 1033: Incorrect information in file: './enwiki/account_credentials.frm' when using LOCK TABLES" | ||
| − | '''Solution: increase innodb_force_recovery to 8 | + | '''Solution: increase innodb_force_recovery to 8''' (the actual max may only be 6 according to [http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html], but 8 worked). |
Recipe (may be partly incomplete, with respect to creating folders etc.): | Recipe (may be partly incomplete, with respect to creating folders etc.): | ||
| + | <source lang="bash"> | ||
# copy all sql data including innodb ibdata1 ib_logfile0 ib_logfile1 to a backup position: | # copy all sql data including innodb ibdata1 ib_logfile0 ib_logfile1 to a backup position: | ||
cp -pr ib* /mnt/storage/var-lib-mysql /mnt/dump/mysqlxxx | cp -pr ib* /mnt/storage/var-lib-mysql /mnt/dump/mysqlxxx | ||
| Line 18: | Line 21: | ||
#then dump all databases | #then dump all databases | ||
mysqldump -u root -pTHEPASSWORD --port=8089 --all-databases --opt > /mnt/dump/alldbs.sql | mysqldump -u root -pTHEPASSWORD --port=8089 --all-databases --opt > /mnt/dump/alldbs.sql | ||
| + | # or file name versioned automatically with date and time: | ||
| + | mysqldump -u root -pTHEPASSWORD --port=8089 --all-databases --opt > /mnt/dump/alldbs_`date +"%Y-%m-%d_%H-%M"`.sql | ||
# or directly compressed: | # or directly compressed: | ||
# mysqldump -u root -pTHEPASSWORD --port=8089 --all-databases --opt | gzip > /mnt/dump/alldbs.sql.gz | # mysqldump -u root -pTHEPASSWORD --port=8089 --all-databases --opt | gzip > /mnt/dump/alldbs.sql.gz | ||
| Line 29: | Line 34: | ||
nano /etc/mysql/my.cnf # return port to 8088 | nano /etc/mysql/my.cnf # return port to 8088 | ||
/etc/init.d/mysql start | /etc/init.d/mysql start | ||
| + | </source> | ||
Latest revision as of 19:41, 25 March 2011
Recovering from InnoDB corruption
Various procedures exist to recover from various kind of mysql database corruptions or crashes. A good resource for other situations may be [1].
This presently documents a procedure that was needed after the system disk was completely filled and innodb ended in a non-recoverable state. The mysql ISAM structures were not affected.
Symptoms: innodb does not start, without error message. phpmyadmin shows all databases, but for the tables inside the database that use innodb, instead of size etc., at the end "in use" is displayed (MyISAM table show fully correctly).
When trying to dump the database, the dump does not start at all, or after setting set innodb_force_recovery = 4, the dump starts, but terminates with a message like: "mysqldump: Got error: 1033: Incorrect information in file: './enwiki/account_credentials.frm' when using LOCK TABLES"
Solution: increase innodb_force_recovery to 8 (the actual max may only be 6 according to [2], but 8 worked).
Recipe (may be partly incomplete, with respect to creating folders etc.):
# copy all sql data including innodb ibdata1 ib_logfile0 ib_logfile1 to a backup position:
cp -pr ib* /mnt/storage/var-lib-mysql /mnt/dump/mysqlxxx
# change port to 8089 and set innodb_force_recovery = 2 (or 4, 6, 8) in
nano /etc/mysql/my.cnf
/etc/init.d/mysql restart
#then dump all databases
mysqldump -u root -pTHEPASSWORD --port=8089 --all-databases --opt > /mnt/dump/alldbs.sql
# or file name versioned automatically with date and time:
mysqldump -u root -pTHEPASSWORD --port=8089 --all-databases --opt > /mnt/dump/alldbs_`date +"%Y-%m-%d_%H-%M"`.sql
# or directly compressed:
# mysqldump -u root -pTHEPASSWORD --port=8089 --all-databases --opt | gzip > /mnt/dump/alldbs.sql.gz
/etc/init.d/mysql stop
nano /etc/mysql/my.cnf # remove innodb_force_recovery
# remove innodb, copy exists, next restart will recreate:
rm /mnt/storage/var-lib-mysql/ib*
/etc/init.d/mysql start
mysql -uroot -pTHEPASSWORD --port=8089 --compress < /mnt/dump/alldbs.sql
/etc/init.d/mysql stop
nano /etc/mysql/my.cnf # return port to 8088
/etc/init.d/mysql start
