Trying to recover MySQL database from a crashed server and I keep getting "mysqldump couldn't execute 'show create table doesn't exist (1146)"

Question

My OpenSuse 13.1 server crashed and couldn't boot but I managed to connect the hard drive to another computer and copied the files in /var/lib/mysql

On a new installation, I followed the steps outlined in this answer: https://serverfault.com/a/449238/371208

However, when I attempt to start MySQL, I get this: "Job for mysql.service failed. See 'systemctl status mysql.service' and 'journalctl -xn' for details."

On running 'journalctl -xn', this is the output:

-- Logs begin at Thu 2016-08-18 15:19:12 EAT, end at Thu 2016-08-18 17:49:02 EAT. -- Aug 18 17:45:13 CPMA dbus[600]: [system] Successfully activated service 'org.kde.powerdevil.backlighthelper' Aug 18 17:46:28 CPMA dbus[600]: [system] Activating service name='org.kde.powerdevil.backlighthelper' (using servicehelper) Aug 18 17:46:28 CPMA dbus[600]: [system] Successfully activated service 'org.kde.powerdevil.backlighthelper' Aug 18 17:48:32 CPMA systemd[1]: Starting LSB: Start the MySQL database server... -- Subject: Unit mysql.service has begun with start-up -- Defined-By: systemd

-- Support:

-- Unit mysql.service has begun starting up. Aug 18 17:49:02 CPMA mysql[6300]: Starting service MySQL warning: /var/run/mysql/mysql.sock didn't appear within 30 seconds Aug 18 17:49:02 CPMA mysql[6300]: chmod: cannot access '/var/run/mysql/mysqld.pid': No such file or directory Aug 18 17:49:02 CPMA mysql[6300]: ..failed Aug 18 17:49:02 CPMA systemd[1]: mysql.service: control process exited, code=exited status=1 Aug 18 17:49:02 CPMA systemd[1]: Failed to start LSB: Start the MySQL database server. -- Subject: Unit mysql.service has failed -- Defined-By: systemd -- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel

-- Documentation: http://www.freedesktop.org/wiki/Software/systemd/catalog/be02cf6855d2428ba40df7e9d022f03d

-- Unit mysql.service has failed.

-- The result is failed. Aug 18 17:49:02 CPMA systemd[1]: Unit mysql.service entered failed state.

How do I resolve this error?

Answer 1

I've seen this error before! You're probably over thinking the problem. It's actually either a permissions problem, a missing files/folders, or a missing pid file. It's easier to diagnose the problem by becoming the mysql user with su (su -s /bin/bash mysqld) or sudo (sudo -u mysqld -i), since being the correct user will also expose permissions problems. Once you are the mysql user you should check that the directory /var/run/mysql/ exists. It should also have a folder for each database (not table). Additionally check that the mysql user has rw access to /var/run/mysql* and /var/lib/mysql*.

Anyway permissions problems might be fixable with chown. Occasionally filesystem ownership/permissions information gets scrambled when the disk gets bad blocks, but could also just be that your mysql user has a different uid on the new machine.

Anyway sometimes mysql will complain and cause massive problems if the pid file is missing, so I would recreate it while logged into the machine as the mysql user. You want the pid of mysqld_safe to be in the file. If mysql is not running than just leave it blank I guess.

TL;DR: This really is exactly what it looks like (usually). If not, write back?