Sunday, June 12, 2016

Recover corrupted InnoDB MySQL database for OpenStack


Due to power outages in the campus and not having backup power, the P2C cloud controller MySQL database was corrupted and the mysql daemon will not start. Worst of all, I don't have any backup! Essentially, P2C operation was halted. My main concern is to recover the disk images (glance), and if possible the user accounts (keystone) and the instances (nova). The controller uses Ubuntu Server(14.04) and MariaDB(5.5.49). In this guide I will discuss how I manage to PARTIALLY recover the cloud controller's database.

The corrupted file was /var/lib/mysql/ibdata1, a file used internally by mysql. It is possible to recover this file if  innodb_file_per_table is set, which LUCKILY is the case in my setup.


1. A backup of the entire /var/lib/mysql of the controller (will be referred to as M1) should be created first.

$sudo tar czvf p2c.controller.mysql.tar.gz /var/lib/mysql

2. Setup a different Ubuntu Server 14.04 machine(will be referred to M2) with MySQL version 5.6.30  (using apt-get). According to [1], 5.6 has the features needed to recover data from .frm and .ibd. More difficult methods exist [2][3].

3. Extract the backup created in step one on M2.

$sudo -s
#tar xzvf p2c.controller.mysql.tar.gz

4. Install mysql utilities on M2

#apt-get install mysql-utilities

5. Start with recovering keystone. I wrote the script below to automate the process. Run this script inside keystone.(WARNING!!Make sure that there are no other MySQL databases on M2.) The variables at the start of the script must be set based on your settings. Running the script takes a while.

#cd var/lib/mysql/keystone
#chmod 755 

6. If all goes well, mysql now contains the recovered data. You can dump it now.

#mysqldump --force -u root -p keystone > keystone.recovered.sql

7. Go to inside glance and nova and perform steps 5 and 6.

8. Copy back keystone.recovered.sql, glance.recovered.sql, nova.recovered.sql to M1.

9. Restore the dumps.

#rm /var/lib/mysql/ibdata1
#rm /var/lib/mysql/ib_logfile0
#rm /var/lib/mysql/ib_logfile1
#service mysql restart

#mysql -u root -p
mysql>drop database keystone;
mysql>create database keystone;
mysql>drop database glance;
mysql>create database glance;
mysql>drop database nova;
mysql>create database nova;

#mysql -u keystone -p keystone < keystone.recovered.sql
#mysql -u glance -p glance < glance.recovered.sql
#mysql -u nova -p nova < nova.recovered.sql

#service mysql restart

10. Reboot the controller and hope for the best.


Final Words

The process described here did not recover the data 100%. The following were observed:

  • Glance/Nova image list is empty though the images are still in the filesystem. These images must be again created (glance image-create).
  • Fixed network must be created (nova network-create)
  • Floating IPs must be created (nova floating-ip-bulk create)
  • ALL information on instances were lost, VMs however remained in their respective hosts.
  • Security groups must be created again.
  • Key Pairs must be generated again. 
  • It took me a weekend for this.