MySQL corrupt - how to reinstall and restore data? Topic is solved

General questions about VestaCP
Syeef
Posts: 50
Joined: Tue May 03, 2016 11:35 pm

MySQL corrupt - how to reinstall and restore data?

Postby Syeef » Wed Mar 08, 2017 4:29 am

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
Posts: 50
Joined: Tue May 03, 2016 11:35 pm

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

Postby Syeef » Wed Mar 08, 2017 6:41 am

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
VestaCP Team
Posts: 7677
Joined: Fri Dec 26, 2014 2:23 pm
Location: Moscow
Contact:

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

Postby skurudo » Thu Mar 09, 2017 12:10 pm

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
-> DigitalOcean competition - please, support us
-> fix for phpmyadmin - nice and sweet now

Syeef
Posts: 50
Joined: Tue May 03, 2016 11:35 pm

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

Postby Syeef » Thu Mar 09, 2017 1:18 pm

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

skurudo
VestaCP Team
Posts: 7677
Joined: Fri Dec 26, 2014 2:23 pm
Location: Moscow
Contact:

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

Postby skurudo » Thu Mar 09, 2017 2:16 pm

Syeef, sorry to hear this. ;(
Well that all ended well.
-> DigitalOcean competition - please, support us
-> fix for phpmyadmin - nice and sweet now

skamasle
Posts: 355
Joined: Mon Feb 29, 2016 6:36 pm

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

Postby skamasle » Thu Mar 09, 2017 2:20 pm

Maybe innodb crash try setup force recovery in my.cnf

skurudo
VestaCP Team
Posts: 7677
Joined: Fri Dec 26, 2014 2:23 pm
Location: Moscow
Contact:

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

Postby skurudo » Thu Mar 09, 2017 8:14 pm

skamasle wrote:Maybe innodb crash try setup force recovery in my.cnf


Too late, I think, but it's nice idea for future!
-> DigitalOcean competition - please, support us
-> fix for phpmyadmin - nice and sweet now

Syeef
Posts: 50
Joined: Tue May 03, 2016 11:35 pm

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

Postby Syeef » Fri Mar 10, 2017 2:08 pm

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
VestaCP Team
Posts: 7677
Joined: Fri Dec 26, 2014 2:23 pm
Location: Moscow
Contact:

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

Postby skurudo » Wed May 17, 2017 6:53 am

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 ;-(
-> DigitalOcean competition - please, support us
-> fix for phpmyadmin - nice and sweet now


Return to “General Discussion”



Who is online

Users browsing this forum: Baidu [Spider] and 6 guests