Page 1 of 1

MySQL corrupt - how to reinstall and restore data?

Posted: Wed Mar 08, 2017 4:29 am
by 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.

Re: MySQL corrupt - how to reinstall and restore data?

Posted: Wed Mar 08, 2017 6:41 am
by 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.

Re: MySQL corrupt - how to reinstall and restore data?

Posted: Thu Mar 09, 2017 12:10 pm
by 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

Re: MySQL corrupt - how to reinstall and restore data?

Posted: Thu Mar 09, 2017 1:18 pm
by Syeef
Yeah, I wanted to try repairs first but MySQL went full corrupt... it wouldn't even accept my root password.

Re: MySQL corrupt - how to reinstall and restore data?

Posted: Thu Mar 09, 2017 2:16 pm
by skurudo
Syeef, sorry to hear this. ;(
Well that all ended well.

Re: MySQL corrupt - how to reinstall and restore data?

Posted: Thu Mar 09, 2017 2:20 pm
by skamasle
Maybe innodb crash try setup force recovery in my.cnf

Re: MySQL corrupt - how to reinstall and restore data?

Posted: Thu Mar 09, 2017 8:14 pm
by skurudo
skamasle wrote:Maybe innodb crash try setup force recovery in my.cnf
Too late, I think, but it's nice idea for future!

Re: MySQL corrupt - how to reinstall and restore data?

Posted: Fri Mar 10, 2017 2:08 pm
by 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.

Re: MySQL corrupt - how to reinstall and restore data?

Posted: Wed May 17, 2017 6:53 am
by skurudo
Syeef wrote: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 ;-(