Register Members List Search Today's Posts Mark Forums Read

Reply
 
Article Options
How to Check, Recover & Repair MySQL Databases
UKBusinessLive
Join Date: Sep 2008
Posts: 1,637

Work like a donkey at work, love chillin on VB.org. Work Hard - Play Hard! - Try it you'll love it

Essex, United Kingdom
by UKBusinessLive UKBusinessLive is offline 03 Jan 2009
Rating: (1 vote - 5.00 average)

Looks Familiar??? No matter how much you manage your sever, at one stage in your dealings with MySQL Databases you will get errors, So what do you do ??


Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.

How to repair all MySQL Databases on a cPanel Server

There can be different reasons why one wants to repair a MySQL database. Database corruption maybe due to a lack of disk space or an unexpected server shutdown that caused the database to go bad. Queries that worked before suddenly stopped working and overall the database is not behaving anymore as expected. This post is not a full database troubleshooting guide, but rather give you a hand on doing a MySQL repair on all databases on a cPanel/WHM server (or any other server running MySQL).

The situation: Your server crashed hard and when it is back up the MySQL side of the house is not working anymore. You do your normal troubleshooting. If all other troubleshooting and verifications are done, you come to the conclusion that you will need to run a repair as the next step. A full database restore from a (hopefully existing) good database backup would be your last choice.

Repairing one MySQL database is fairly easy, but how do you do this for an entire server holding20, 30, 50 or over 100 databases? You will need SSH access as the root user to the server running MySQL. So, log in as the root user and then do the following to repair your MySQL databases:

Option #1

Check if you any need DB repair:

Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.

Then try a 'safe-recover' as the first step:

Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.

If the ‘safe-recover’ does not work, run a full recover:

Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.

Then use the 'force' flag to get things back to normal:

Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.

or:

Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.



Option #2

Use the mysqlcheck tool to repair your databases.


Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.


Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.


Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.

Option #3

1) Through cPanel:

Log in to your cPanel and go to MySQL Databases. In the section Modify Databases, select a database from the drop-down box next to Repair DB: and then click the Repair DB button.

If the database engine used by your database supports repair (for example, MyISAM supports it), you will see a list of the repaired tables within the database.

If your database engine does not support repair (e.g. InnoDB database engine), you will get the following error message:

note : The storage engine for the table doesn't support repair

2) Through phpMyAdmin:

Open your phpMyAdmin tool and select the database whose tables you wish to repair.

A list with all the database's tables will appear. Tick the tables you wish to repair, or simply click [Check All] to select all tables.

Click the box [With selected:] and choose Repair table. This will execute the REPAIR TABLE SQL query on the selected tables and they will be repaired.

There you have it. By using one of these threee options you should be able to repair and recover your databases. Please be advised that you should backup critical databases before running any of the commands above. If something goes wrong you can revert back and try again.

In general the idea is to get people feeling confident enough to be able to work out a solution to a problem rather than worrying that they have a major serious issue.

Hope that helps
Views: 11619
Reply With Quote
Comments
  #2  
Old 16 Jan 2009, 23:31
NAZIA's Avatar
NAZIA NAZIA is offline
 
Join Date: Feb 2008
Real name: NJ
Thanks for sharing...
what does the
Repair / Optimize Tables

OR
http://xyz.xyz /admincp/repair.php?do=list
Do in the admincp of the vbulletin?
Reply With Quote
  #3  
Old 18 Jan 2009, 02:42
VK3MTR VK3MTR is offline
 
Join Date: Dec 2006
Thanks man, appreciate the advice.
Reply With Quote
  #4  
Old 19 Jan 2009, 07:26
harisafp's Avatar
harisafp harisafp is offline
 
Join Date: Apr 2008
Real name: Harisa FP
Good for solutions ...
Thanks !!
Reply With Quote
  #5  
Old 04 Apr 2009, 05:10
Jasem's Avatar
Jasem Jasem is offline
 
Join Date: Feb 2006
Location: www.menokia.com
great work, thank you!
__________________
games
Forum Nokia
Reply With Quote
  #6  
Old 06 Apr 2009, 23:30
ragtek's Avatar
ragtek ragtek is offline
 
Join Date: Mar 2006
Location: austria, croatia
I think it would be better placed here=> http://www.vbulletin.org/forum/forumdisplay.php?f=188 it's no vb article..

also pls see this: http://www.vbulletin.org/forum/showthread.php?t=210032 but no feedback
__________________
my addons: http://ragtek.github.com
Reply With Quote
  #7  
Old 26 Jul 2009, 17:32
Golzarion's Avatar
Golzarion Golzarion is offline
 
Join Date: Jan 2008
Real name: Mohsen
Thanks .Nice article .
Reply With Quote
  #8  
Old 30 Jun 2011, 14:00
m2006's Avatar
m2006 m2006 is offline
 
Join Date: Jan 2006
Thank you for advice
Reply With Quote
  #9  
Old 09 Sep 2011, 15:12
itilm's Avatar
itilm itilm is offline
 
Join Date: Aug 2011
Location: Karachi
Real name: smartyounus
if forum not running & data not Restore so....???
Reply With Quote
Reply



Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Article Options

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


New To Site? Need Help?

All times are GMT. The time now is 14:54.

Layout Options | Width: Wide Color: