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.
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).
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, and 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 that information.
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
sudo /etc/init.d/mysql stop.
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 (
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 (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
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
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]
sudo service mysqld start
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.
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.