In some cases databases gets corrupted(shit happens). After a hardware fail or not correct usage like a “kill -9” of mysqld process without gracefully flushing of opened tables.
After the start of mysqld.service, you can get a log-entry in your mysqld-log file, which informs you about the corruption of database, like this one “corruption in the InnoDB tablespace“.
In some cases mysqld.service will crash every n-minutes..
Independently of the symptoms the crashed database should be repaired immediately!
Stop MariaDB/MySQL to create a file-based backup before go on with recovery:
service mysql stop |
1 2 3 4 | # backup corrupted database - in my case it's db4 rsync -az /var/lib/mysql/db4 /tmp/backup/ # backup ibdata1, ib_logfile0 and ib_logfile1 rsync -az /var/lib/mysql/ib* /tmp/backup/ |
Add recovery flag to my.cnf in mysqld-section:
1 2 | [mysqld] innodb_force_recovery = 4 |
Restart MariaDB/MySQL to take innodb_force_recovery in action:
service mysql start |
Create mysql dump of corrupted database:
mysqldump -p db4 > db04.sql |
If you get some errors during the dump progress, try to increase innodb_force_recovery the value step by step until 6.
innodb_force_recovery setting of 4 or greater places InnoDB in read-only mode.
Please try to avoid access of your applications to the database:
-
– you can drop incoming connections via firewall
– set mysqld to another port (port = 3307) to avoid incoming connection on default port
If mysqldump was completed without any errors, dump should contain a repaired state of your data which can be imported back:
mysql -u root -p db4 < db04.sql |
Leave a Reply