I seem to be getting corrupt databases often these days

[gavpeds]

So i seem to regularly have an issue with my databases getting corrupt. Being innoDB i am unable to repair or optimize in phpmyadmin and infact if i do try to repair it just crashes the mysql server all together. Any suggestions on how to better manage this?

 

[Fred.]

Check your harddisks and make a backup. It might be a bad disk.

 

[gavpeds]

Servint have been looking into it and said they can't find anything so will try moving server to another host machine.

 

[dethfire]

Run

mysqlcheck -u root -p --auto-repair -c -o --all-databases

from command line

 

[gavpeds]

Will this still not result in the problem that apparently innodb can not be repaired or optimized or as I understand does not support this.

 

[Digital Doctor]

my databases getting corrupt

Does it effect your members ? Slow down the site .. etc ? Will the accumulation of errors result in bigger problems ?

 

[dethfire]

Will this still not result in the problem that apparently innodb can not be repaired or optimized or as I understand does not support this.

I've done it for broken tables and it gives a warning saying optimize is not valid, but it does an alternative function like recreate and analyze. My forum then works.

 

[gavpeds]

Does it effect your members ? Slow down the site .. etc ? Will the accumulation of errors result in bigger problems ?

Yes it effects the site loading and members.

I've done it for broken tables and it gives a warning saying optimize is not valid, but it does an alternative function like recreate and analyze. My forum then works.

Ah OK so now I need to figure out SSH assuming that's what you mean by using command prompt. I have in the past tried to connect to SSH but have just never been able to figure it out.

 

[Robust]

Yes it effects the site loading and members.


Ah OK so now I need to figure out SSH assuming that's what you mean by using command prompt. I have in the past tried to connect to SSH but have just never been able to figure it out.

He didn't mean command prompt, he said command line.

Use something like putty http://www.chiark.greenend.org.uk/~sgtatham/putty/
Connect using your IP, default port is 22, and you should know your username and password if you're using a VPS or dedicated server. If you are using shared hosting, contact your host.

 

[gavpeds]

Sorry yes ment command line. I will give it a go. I know in the past I have tried to connect putty but for some reason never figured it thanks for the help will try it again.

 

[gavpeds]

OK so finally got connected, used the command and got this error. mysqlcheck doesn't support multiple contradicting commands.

 

[Robust]

mysqlcheck -u root -p --auto-repair --all-databases

That should be fine

 

[gavpeds]

Will give it a try.

 

[gavpeds]

Just a quick question. I am also trying to run a backup via ssh. I have the command but when prompted for password it does not work. This could be the app I am using as I am using juicessh app on android. But when I go to type password nothing is actually typed. It only seems to be when I am promoted for password after trying to run backup of dB. Again this is all pretty new to me so could be the app but I am not sure.

 

[Robust]

You don't see the password but when you type it is actually typing, just type it in and hit enter.

 

[gavpeds]

Thanks OK so now when in run it I get this error. Got error: 1045: Access denied for user 'root'@'localhost' (using password: YES) when trying to connect

 

[Robust]

Thanks OK so now when in run it I get this error. Got error: 1045: Access denied for user 'root'@'localhost' (using password: YES) when trying to connect

What command are you using?

 

[gavpeds]

The one you posted. I ran It from the root directory and also tried my site directory. Both gave the same error.

mysqlcheck -u roomo
-p --auto-repair --all-databases

EDIT:
OK it seems that my root myslq password was different from my root SSH password so my host is changing it now. Will try again in a mo

does this repair all databases or just one database? Reason I ask is I have a few sites on my vps so wonder if I ran this from root if it would do them all?

 

[gavpeds]

OK so after we got the passwords sorted my host told me to run this which worked.

mysqlcheck -Ar -u root -p

Thanks for the help all.

 

[dethfire]

Are you using MySQL 5.6? After upgrading to 5.6 I haven't had any table crashes