Home/Linux Howtos/How To mount MySQL database files after data recovery

How To mount MySQL database files after data recovery

(When you don't have a dump file)

The Problem: You have your filesystem backup but not a MySQL dump (DDL) file. How do you mount the database?

Believe it or not, it actually is not all that hard. Just a few weeks ago I had to do this very thing because I had a motherboard fry (Note: I will never buy Abit motherboards again! They're utter CRAP! I'm glad we never built systems for customers using Abit components, because Abit components are CRAP.) and the MBR of my hard drive got corrupted as a result.

Crashed at the worst time

Well, I had a backup of my data, but there was a database I had just entered a whole heap of data into, right before the motherboard burned up (and by burned up, I mean the Abit motherboard was actually arcing and burning). In fact, I was still entering data into it as the system started to burn.

Time to panic?

Darn. Even no one can fault me for not having a backup at that point, it didn't look good. However, having dealt with similar data recovery issues on Windows, I didn't panic. I was a bit miffed at having to spend time dealing with a hardware failure, and the downtime, but I knew there was some way to mount the database. As it turns out, it was even simpler than I thought it would be. Having recovered SQL Server and Oracle databases after crashes, I was prepared to be spending hours in hacking and massaging the system to mount the databases, then run various repair and recovery utilities once mounted.

But I was wrong!

Mounting the MySQL database took me mere minutes - and what's more, I mounted all the databases but one on the first shot, and mounted the last on the second shot. I was done in under five minutes and had verified that NO data was lost.

OK, here it is. Don't say I didn't tell you it was easy!

The solution: Mounting a MySQL Live Filesystem Backup

  1.  

    2. Make sure that you have a new MySQL installation you can blow away. If you have already created a new database you need to save, create a dump file so you can import it. DO IT NOW, because we will be deleting your current MySQL installation.

  2.  

    3. Locate the directory where your MySQL data files are located. If you do not know where they are check my.cnf. In our example we are running Suse 9.3, and Suse locates the data files in /var/lib/mysql/

  3.  

    4. Switch to the MySQL directory and see which user and group the MySQL files belong to kimp4:/var/lib # cd /var/lib/mysql total 20507 drwxr-xr-x 4 mysql mysql 248 2005-07-03 17:18 . drwxr-xr-x 40 root root 1064 2005-07-02 14:38 .. -rw-rw---- 1 mysql mysql 10485760 2005-07-03 17:18 ibdata1 -rw-rw---- 1 mysql mysql 5242880 2005-07-03 17:18 ib_logfile0 -rw-rw---- 1 mysql mysql 5242880 2005-07-03 17:18 ib_logfile1 drwx------ 2 mysql mysql 1584 2005-07-03 17:18 mysql -rw-rw---- 1 mysql mysql 1279 2005-07-03 17:18 mysqld.log drwx------ 2 mysql mysql 48 2005-07-03 17:18 test -rw-r--r-- 1 mysql mysql 0 2005-07-03 17:18 update-stamp-4.1 As you can see from our example, the files belong to mysql:mysql.

  4.  

    5. Delete the data files cd /var/lib/mysql/ rm -r ./*

  5.  

    6. Instruct MySQL to install a new empty DBMS schema then start mysqld kimp4:/etc # mysql_install_db Installing all prepared tables Fill help tables PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands: /usr/bin/mysqladmin -u root password 'new-password' /usr/bin/mysqladmin -u root -h kimp4.biyn.local password 'new-password' See the manual for more instructions. NOTE: If you are upgrading from a MySQL <= 3.22.10 you should run the /usr/bin/mysql_fix_privilege_tables. Otherwise you will not be able to use the new GRANT command! You can test the MySQL daemon with the benchmarks in the 'sql-bench' directory: cd sql-bench ; perl run-all-tests Please report any problems with the /usr/bin/mysqlbug script! The latest information about MySQL is available on the web at http://www.mysql.com Support MySQL by buying support/licenses at https://order.mysql.com kimp4:/etc # /etc/init.d/mysql start

  6.  

    7. Stop the MySQL daemon again kimp4:/etc # /etc/init.d/mysql stop Shutting down service mysql done

  7.  

    8. Copy the files from your backup, overwriting your newly-initialized MySQL installation cd /var/lib/mysql kimp4:/var/lib/mysql # cp -R /mnt/bigDisk/june-19/mysql/* .

  8.  

    9. Next, assign recursively ownership to the user:group you took note of above. cd /var/lib/mysql kimp4:/var/lib/mysql# chown -R mysql:mysql *

  9.  

    10. Now start the MySQL daemon kimp4:/var/lib/mysql # /etc/init.d/mysql start Starting service MySQL done

  10.  

    11. Now you should be able to log into your MySQL environment using your old username/passwords. You should now import any SQL/DDL dump files you may have created in the first step.

See? I TOLD you it was easy - easier than you thought!

Note to Experienced SysAdmins

I do realize I have a few extra steps in here but this is to ensure that you're working with a known quantity. I've done this several times now and know with absolute certainty that this method works. If you're a hardened sysadmin just skip the extra steps. I am trying to make this article so that it will work even for novices on various distributions. Again: I do know that it will work with several of the steps eliminated, but I want to ensure that novices are working with a known-clean environment. Thanks for understanding that many readers looking for MySQL tips are not so experienced, and not sending me nasty emails! ;)