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 recover data if it's in good condition.
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 the
mysqld service so we can restore the old files. Once that is running the database will be able to be exported, and using a new virtual system makes sure there aren't negative consequences if something goes wrong (which is likely).
You can read about how I created a virtual Fedora 19 on Ubuntu through Oracle VM VirtualBox Manager.
Once you have the virtual OS running, install the mysql package and start the service (Fedora example):
sudo yum update
sudo yum install mysql mysql-server
# we need mysqld off until we transfer the files
# sudo service mysqld start
mysql -u root -p
The first step is to get the
mysqld service up and running. Then create an empty database of the same name as the one you want to restore from the old environment.
mysql -u root -p
create database [databaseName];
This will create the folder with that name inside the
mysql folder and update the structure with the relevant information.
It is really important to have the
mysqld service stopped while we're doing any manipulations on the
mysqld service is running while we're making changes to the
mysql folder 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
sudo /etc/init.d/mysql stop.
As always, make a backup of places you plan on changing, in any case. You can do it from
/var/lib folder by creating (
c) a compressed (
z) archive file (
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.
sudo scp username@backupLocation:/[old files path]/ibdata1 .
sudo scp -r username@backupLocation:/[old files path]/[oldDatabase] .
The often huge
ibdata1 file (stands for “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 overridden.
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
performance_schema that are inside the
/var/lib/mysql, or any other files or folders!
Everything inside the
mysql folder must be owned by the
mysql user and be inside the
Without this, the
mysqld service probably won't be able to start. Since we moved the files as a root user that was applied to the copied files so permissions have to be fixed:
sudo chown -R mysql:mysql [oldDatabase]
sudo service mysqld start
sudo /etc/init.d/mysql start
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 recheck 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.
After all this effort you can finally export our data into a much more usable format, the way it's intended to be used:
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. Congratulations! You've salvaged your data from raw files!