zeldor.biz

Linux, programming and more

Copyright © 2025
Log in

InnoDB recovery

August 12, 2017 by Igor Drobot Leave a Comment

InnoDB recovery

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

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/

# 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

[mysqld] innodb_force_recovery = 4

Restart MariaDB/MySQL to take innodb_force_recovery in action:

service mysql start

service mysql start

Create mysql dump of corrupted database:

mysqldump -p db4 > db04.sql

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

mysql -u root -p db4 < db04.sql

Filed Under: Linux Tagged With: InnoDB, InnoDB recovery, MariaDB, MySQL

Categories

Archives

Tags

apache2 Apple arduino ARM Automation backup bash Cisco Cluster Corosync Database Debian Debian squeeze DIY DNS Fedora FTP Fun Icinga Ipv6 KVM Linux LVM MAC OS X Monitoring MySQL Nagios Nginx openSUSE OpenVPN PHP Proxy Python python3 qemu RAID rsync Samba security ssh Ubuntu virtualization Windows Windows 7 Wordpress

Leave a Reply

Your email address will not be published. Required fields are marked *

Yeaaah Cookie! We use cookies to ensure that we give you the best experience on our website. If you continue to use this site we will assume that you are happy with it.Ok