MySQL corrupt - how to reinstall and restore data?

[Syeef]

Hello.

My VPS crashed all of a sudden few hours ago. So after getting it back online, I noticed everything is running fine except for MySQL service. It got corrupt and all the database directories are missing in "/var/lib/mysql". I cannot even log into VestaCP Admin, it says: "Invalid username or password."

So my question is, how can I re-install MySQL service and how can I restore only MySQL data from the Backups?

Thanks.

[Syeef]

Okay, nevermind... I have done it myself.

Here is what I did:

1) Un-installed current MySQL installation with command:

Code: Select all

yum remove mysql mysql-server

2) Removed directory "/var/lib/mysql" and file "/etc/my.cnf".

3) Re-installed MySQL again with command:

Code: Select all

yum install mysql mysql-server --enablerepo=remi

4) Re-configured MySQL setup and set new password by copying from file "/usr/local/vesta/conf/mysql.conf".

5) Restored database from Backup for each user with command: (example user: admin)

Code: Select all

v-restore-user admin admin.2017-03-07.tar

6) Needed another database for Roundcubemail, so created a database and user and grant all permission to the user. User password is copied from file "<roundcubemail-install-dir>/config/config.inc.php"

7) Copied the SQL file "<roundcubemail-install-dir>/SQL/mysql.initial.sql" and imported into the database.

Done.

[skurudo]

Nice report! Thanks.

But for starters I did use this:

Code: Select all

#!/bin/bash
mysqlcheck --no-defaults -u root -p --all-databases
mysqlcheck --no-defaults -u root -p --all-databases -o
mysqlcheck --no-defaults -u root -p --all-databases --auto-repair
mysqlcheck --no-defaults -u root -p --all-databases --analyze

[Syeef]

Yeah, I wanted to try repairs first but MySQL went full corrupt... it wouldn't even accept my root password.

[skurudo]

Syeef, sorry to hear this. ;(
Well that all ended well.

[skamasle]

Maybe innodb crash try setup force recovery in my.cnf

[skurudo]

Maybe innodb crash try setup force recovery in my.cnf

Too late, I think, but it's nice idea for future!

[Syeef]

Basically my Linux file system crashed, not sure if MySQL did that. Had to do a manual file system check to get the VPS online.

After getting it online, I found all the database files and folders were gone in "/var/lib/mysql", the folder was empty. I tried file recovery, did not work. i tried MySQL repair, but it would not accept my root password.

But now everything is okay. I will keep the suggestions in mind for next time [although i hope not :P]

Thanks.

[skurudo]

Basically my Linux file system crashed, not sure if MySQL did that. Had to do a manual file system check to get the VPS online.

It's possible problem on your hoster node and not on your vps, there is nothing to do with this ;-(