Back to top

Restore Drupal MySQL database from raw files

MySQL folder on linux storing data

Accidents happen. Accidents can raid your backup storage, production and other site versions. You can accidentaly forget to set up regular backups (ò_ô). Whatever adversity hits you sometimes you have to face the difficult situation of having only raw mysql folder from a hard drive to show for your data, and no healthy database export.

Luckily there is a way to restore the data if you know the steps and you're careful enough. This article documents this process.

InnoDB MySQL database types aren't meant to be restored through the mysql folder on the system where the data is physically stored. The proper way is to export it using a tool that connects to MySQL or directly through CLI using mysqldump command for example.

But if there are no other options it is possible to restore data if it's in good condition.

Use an expendable virtual environment

Since it's no longer possible to login into mysql on your old hard drive (if it were, then you'd be able to export the data the usual way) you have to salvage the files and move them to an environment with working mysql service.

Meddling with the mysql folder and files inside it can be very destructive. Don't do this process on any computer you consider valuable that holds important data or has working websites you don't want to lose.

Instead, create a virtual machine or a similar environment that can run mysqld service where we can restore the database so it can exported, but it won't cause a lot of damage if something goes wrong (which is likely).

Create database with the same name on new environment

The first step is to get the mysqld service up and running, and create an empty database of the same name as the one you want to restore from the old environment.

  1. mysql -u root -p
  2. [password]
  3. show databases;
  4. create database [databaseName];
  5. exit

This will create the folder with that name inside the mysql folder and update the structure with that information.

Stop the mysqld service

It is really important to have the mysqld service stopped while we're doing any manipulations on the mysql folder. If the service is running while we're making changes the result will likely be corrupt.

The exact command depends on your particular configuration. On Fedora and related systems it might be:

sudo service mysqld stop

or Ubuntu:

sudo /etc/init.d/mysql stop.

Backup your new mysql folder

As always, make a backup of places you plan on changing, just in case. You can do it from /var/lib folder by creating (c) a compressed (z) archive file (f) using tar:

sudo tar cfz mysqlBU[date].tar.gz mysql/

There is a mysql.sock socket file that will not be included the backup:

tar: mysql/mysql.sock: socket ignored

Sockets are zero level files that are used by daemon processes to communicate with each other. They are created and destroyed as necessary when the daemons start and stop. They can safely be ignored.

Copy old ibdata1 and frm files into new environment

  1. sudo scp username@backupLocation:/[old files path]/ibdata1 .
  2. sudo scp -r username@backupLocation:/[old files path]/[oldDatabase] . 

The often huge ibdata1 file (integrated backup data part 1) will override the one from the new environment.

The old Database folder contains many .frm files and one db.opt. They will be copied inside the existing folder on the new environment and db.opt will be overriden.

Some other tutorials suggest to also copy the log files, and make other adjustments. I haven't been lucky when I followed that advice.

Don't touch the mysql and performance_schema that are inside the /var/lib/mysql, or any other files or folders!

Change ownership to mysql:mysql

Everything inside the mysql folder must be owned by the mysql user and be inside the mysql group. Without this the mysqld service probably won't be able tothe mysqld service start. Since we moved the files as a root user permissions have to be fixed:

sudo chown -R mysql:mysql [oldDatabase]

Start the the mysqld service

sudo service mysqld start

or:

sudo /etc/init.d/mysql start

This is the moment of truth. If the service starts, you've done a good job and you'll probably be able to get to your data so you can export it into a usable backup.

If the service won't start you have to check all the steps, file permissions, deviled details. You have to start again and be more careful if you missed something.

You don't have to start again if your mysql.sock file is missing, there are ways to solve it. It usually happens when you delete the corrupt mysql folder and restore it from the backup.

Export the data into a usable backup

sudo mysqldump -u root -p [databaseName] | gzip > [databaseName]BU[date].mysql.gz

This should ring familiar, the usual way you get your database data into a future-proof usable backup.

Would you like me to help you with restoring sanity after a disaster? Or with any other Drupal problem? Please contact me and I'll summon all my skills and Drupal might to solve it for you.