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

General questions about VestaCP
Forum rules
Before creating a new topic or reply on the forum you should fill out additional fields "Os" and "Web" in your profile section.
In case of violation, the topic can be closed or response from the support will not be received.
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: 7808
Joined: Fri Dec 26, 2014 2:23 pm
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: 7808
Joined: Fri Dec 26, 2014 2:23 pm
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
Collaborator
Posts: 385
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: 7808
Joined: Fri Dec 26, 2014 2:23 pm
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: 7808
Joined: Fri Dec 26, 2014 2:23 pm
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: No registered users and 8 guests

cron