Wednesday, February 20, 2013

Recover Joomla PhpMyAdmin MySQL Database

This morning I found myself confronted with a new problem:

Our server crashed, we had to set it up again - from scratch. Fortunately, we did have some backups - the Joomla! files, and the mysql folder (on Linux: /var/lib/mysql).

(We are using a Linux server with mysql and phpmyadmin.)

I then tried to recover our database. What I thought: Just copy the contents from the database backup folder (mysql/joomla) to the mysql folder on the server and everything should be fine. That was what they said on the internet, too. Turns out there were only .frm files in that folder, no .myd etc. Turns out that .frm only contains the table structure. But where is my data?

Everything's not lost.

I found a huge file in the mysql backup folder, called ibdata1.

So what you need to do now is (instructions are for Linux):

- Stop your mysql service
  sudo stop mysql
- Copy that ibdata1 file to the /var/lib/mysql folder (consider making a backup of the existing ibdata1 file)
  cd /var/lib/mysql
  sudo mv ibdata1 ibdata1backup
  sudo cp <backupfolder>/ibdata1 /var/lib/mysql
- check that permissions are set to mysql as owner and group
- restart mysql service
  sudo start mysql
- if you set up any new passwords, check that Joomla! has the correct password in its configuration.php file for mysql.