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:
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 ;-(