Mysql – how to: rescue Data from MySQL (only files on HDD left)

debiandebian-lennyMySQLrestore

After eventually managing to upgrade my debian virtual server from etch to lenny.
(Read here for the full story: debian: upgrade from etch to lenny fails)

I now lost the data (including all tables) from my MySQL DB. The files are still present on the hard drive and phpMyAdmin (further referenced to as PMA) still shows me all the database names but without any tables. 'mysql' and ''information_schema' databases still have their tables. But accessing e.g. 'mysql.user' gives me the following error:

#1017 - Can't find file: './mysql/user.frm' (errno: 13)

Of course (as any 1337 PEBKAC does) I don't have a mySQL dump file. (using the F-word multiple times)

Does anyone know of a tool that could restore the lost data from the file structure?

Thanks in advance for any help granted to that layer-8 problem a.k.a. me

Best Answer

IF you have the files you should be able to view the data (except innodb tables). This error is commonly given when you have a permissions issue - try changing the /var/lib/mysql files to mysql:mysql and see if it fixes it.
chown -R mysql:mysql /var/lib/mysql

Related Solutions

Debian MySQL User – What is the debian-sys-maint MySQL User?

What is debian-sys-maint used for?

One major thing it is used for is telling the server to roll the logs. It needs at least the reload and shutdown privilege.

See the file /etc/logrotate.d/mysql-server

It is used by the /etc/init.d/mysql script to get the status of the server. It is used to gracefully shutdown/reload the server.

Here is the quote from the README.Debian

* MYSQL WON'T START OR STOP?:
=============================
You may never ever delete the special mysql user "debian-sys-maint". This user
together with the credentials in /etc/mysql/debian.cnf are used by the init
scripts to stop the server as they would require knowledge of the mysql root
users password else.

What is the easiest way to restore it after I've lost it?

The best plan is to simply not lose it. If you really lose the password, reset it, using another account. If you have lost all admin privileges on the mysql server follow the guides to reset the root password, then repair the debian-sys-maint.

You could use a command like this to build a SQL file that you can use later to recreate the account.

mysqldump --complete-insert --extended-insert=0 -u root -p mysql | grep 'debian-sys-maint' > debian_user.sql

Is the password in /etc/mysql/debian.cnf already hashed

The password is not hashed/encrypted when installed, but new versions of mysql now have a way to encrypt the credentials (see: https://serverfault.com/a/750363).

Linux – Copying from external device very slow

As Bernd asked:

Which usb module is loaded? uhci or ehci

lsmod | grep hci

The output should look something like this:

uhci_hcd               22404  0 
ehci_hcd               34688  0 
usbcore               134576  5 oss_usb,usbhid,uhci_hcd,ehci_hcd

If it only shows uhci and not ehci, then the usb 2.0 drivers just aren't loading

Best Answer

Related Solutions

Debian MySQL User – What is the debian-sys-maint MySQL User?

Linux – Copying from external device very slow

Related Topic