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 start
sudo /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
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!