How to Recover an InnoDB MySQL Database from raw Backup
Introduction
In this guide I will teach you how to recover an InnoDB MySQL Database from Raw Backup files. I hope this helps you to recover your database in case of a disaster.
There are some situations when you don’t have access to make a MySQL dump to restore your Database, or you lost your MySQL DB’s dump backup but you still have access to the raw files.
The process is not that complicated, in a few words, we are going to clone the ib files and the database directory into a test server to be able to make the mysqldump, then we will import the mysqldump on the destination server.
Getting the required files
You need to get a copy of the raw mysql database directory from:
/var/lib/mysql/your_database
You also need a copy of the following ib files located in /var/lib/mysql/
ib_logfile0 ib_logfile1 ibdata1 ib_buffer_pool
⚠ Warning! Be careful not to restore those files to any production server or you will break the Databases.
Working on the test server
We are going to work with the database directory and ib* files on a test server, so we do not break anything on production, it’s prefer to use the same MySQL version and storage engine.
First, shutdown mysql on your test server, for centos do:
systemctl stop mysqld
Make a backup copy of the mysql directory, it’s better to be safe than sorry:
cp -pr /var/lib/mysql /var/lib/mysql2
Then you can copy and overwrite the ib* files and your database directory to /var/lib/mysql on the test server, by any means you want.
Make sure to change the file owner of the ib* files and the database directory & files to match mysql.mysql, or whatever you mysql server is running from.
chown mysql.mysql -R /var/lib/mysql/your_database chown mysql.mysql /var/lib/mysql/ib*
It’s time now to turn back on the mysqld server:
systemctl start mysqld
Now you should be able to make your mysqldump of the database:
mysqldump your_database > your_database.sql
If you have successfully created the mysqldump, you can now revert all the changes we did on the test server.
systemctl stop mysqld mv /var/lib/mysql /var/lib/mysql.del mv /var/lib/mysql2 /var/lib/mysql systemctl start mysqld
Hopefully it’s all good, now you can delete the backup folder mysql.del:
rm -rf /var/lib/mysql.del
The import of the mysqldump file
We are almost done, It’s time to login into the destination server and create the database:
mysql -e "CREATE DATABASE your_database;"
Copy the dump file of your_database.sql to the destination server an import it to the newly created database:
mysql your_database < your_database.sql
You should be able to show the tables from the imported database, also you can now associate an user to the DB and grant all the access required.
mysql -e "SHOW TABLES FROM your_database;"
Conclusion
Congrats! We have managed to rescue an innodb mysql database from raw backup files without breaking anything else, hopefully.
If you to want learn more about this tutorial or have any questions, feel free to send your comments down below.
Don’t forget to check our other Tutorials, we are constantly submitting new ones every week.
Tags: innodb mysql recovery, recover innodb, recover mysql database