Recover InnoDB MySQL or MariaDB database from raw files

So you have raw files of your database and don’t have a dump or backup at hand. Worry not, it’s easy to recover an InnoDB MySQL or MariaDB database from raw files.

Things to note:

• You will need the same version of the database on the machine you want to recover the database
• Don’t use a production database/server for recovery

Files

The process is very simple and straight forward. You will need the following files from the raw files backup you have.

• database
• ib_logfile0
• ib_logfile1
• ibdata1
• ib_buffer_pool

Recovery process

On your test server, 1st shutdown the database server.

systemctl stop mariadb
OR
systemctl stop mysqld

Next we will make a copy of our current database directory, so later we can restore it once we are done.

cp -r /var/lib/mysql /var/lib/mysql_org

Once completed, copy over the raw files mentioned above.

cp /path/to/database /var/lib/mysql
cp /path/to/ib_logfile0 /var/lib/mysql
cp /path/to/ib_logfile1 /var/lib/mysql
cp /path/to/ibdata1 /var/lib/mysql
cp /path/to/ib_buffer_pool /var/lib/mysql

Changing ownership of the copied files is important and not to be missed.

chown -R mysql:mysql /var/lib/mysql/database
chown -R mysql:mysql /var/lib/mysql/ib*

You can now start the database server.

systemctl start mariadb
OR
systemctl start mysqld

Backup your database using mysqldump command.

mysqldump -u root -p DATABASE > /path/to/backups/db.sql

Restore

Once you have successfully created a backup of the database. You can restore your old databases directory.

systemctl stop mariadb
OR
systemctl stop mysqld

mv /var/lib/mysql /var/lib/mysql_rm
mv /var/lib/mysql_org /var/lib/mysql

systemctl start mariadb
OR
systemctl start mysqld