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.
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 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 startsudo /usr/bin/mysql_secure_installation
mysql -u root -p
cd /var/lib/
Create database with the same name on new environment
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
[password]
show databases;
create database [databaseName];
exit
This will create the folder with that name inside the mysql folder and update the structure with the relevant 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 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
or Ubuntu:
sudo /etc/init.d/mysql stop.
Backup your new mysql folder
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 (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
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 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 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]
Start 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 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.
Export the data into a usable 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
How to recover #Drupal #MySQL database from raw filesTweet this
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!
