[Gllug] Extract the data from a MySQL database's *files*?

[Walter Stanish]

> I've got a friend who has a VM image.  This image used to be a MySQL
> database instance, but for various reasons we cannot boot it any more.
> 
> However, the raw MySQL database files are still on there.
>
> Can we extract the data somehow?  Can we tell another MySQL instance
> to look at these files?

Yes, option one is make boot work.  Easiest way is to boot from a 
boot disk of the same distro/version, then chroot in to the 
system, eg:
 - set VM to boot from CD
 - boot to a root prompt
 - at prompt, do:
    # mkdir /mnt/saveme
    # mount /dev/<yourdisk> /mnt/saveme
    # chroot /mnt/saveme /bin/bash
    # source /etc/profile
    # export PS1="(chroot) $PS1"
    ... now try to start MySQL server
     ... if OK, do a: 'mysqldump -u root yourdbname >saved.sql'

Option two is boot the VM from a boot disk and you can get access to the
files, then copy them off to another machine for resuscitation.

They typically reside in /var/lib/mysql or another directory as
specified in /etc/mysql/my.cnf

Note that in order to re-use the files:
 - Take note of the MySQL version installed on the VM, even if
   it won't run you can see library versions with something like
   # ldd /mnt/vm-hard-drive/usr/bin/mysql
   or ...
   # ls /mnt/vm-hard-drive/usr/lib/*mysql*
   If possible, on the system you wish to import the data on,
   you should make sure the version matches exactly.
 - Attempt to start them on a new machine (from a copy - DO keep the
   original untouched!) with the original MySQL version

Random considerations from memory:
 - matching version is VERY important
 - database names appears as subdirs in mysql data dir
 - make sure MySQL is stopped on the new host machine before
   copying the database files in to its data dirs
 - if the data included binary logs, and they are now partial,
   you may have to turn off binlogs and remove the files

Good luck.

If all else fails, and the data is predominantly text-based,
'strings' utility and perl may help ;)

- Walter
-------------- next part --------------
A non-text attachment was scrubbed...
Name: winmail.dat
Type: application/ms-tnef
Size: 3762 bytes
Desc: not available
URL: <http://mailman.lug.org.uk/pipermail/gllug/attachments/20100401/c363a4d8/attachment.bin>
-------------- next part --------------
-- 
Gllug mailing list  -  Gllug at gllug.org.uk
http://lists.gllug.org.uk/mailman/listinfo/gllug