vb.org Archive - How to Check, Recover & Repair MySQL Databases

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 ??

Code:

jtablesession::Store Failed

DB function failed with error number 145

Table './webkinzc_jo151/jos_session' is marked as crashed and should be repaired SQL=INSERT INTO `jos_session` ( `session_id`,`time`,`username`,`gid`,`guest`,`client_id` ) VALUES ( 'fd773853ae5aa0aca6aa7c22c79dcbbd','1230945305','','0','1','0' )

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:

Code:

myisamchk --check /var/lib/mysql/*/*.MYI

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

Code:

myisamchk --safe-recover /var/lib/mysql/*/*.MYI

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

Code:

myisamchk --recover /var/lib/mysql/*/*.MYI

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

Code:

myisamchk --safe-recover --extend-check --force /var/lib/mysql/*/*.MYI

or:

Code:

myisamchk --recover --extend-check --force /var/lib/mysql/*/*.MYI

Option #2

Use the mysqlcheck tool to repair your databases.

Code:

mysqlcheck --all-databases -r #repair

Code:

mysqlcheck --all-databases -a #analyze

Code:

mysqlcheck --all-databases -o #optimize

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 :D

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.01149 seconds Memory Usage 1,738KB Queries Executed 10 (?)
More Information
Template Usage: (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (9)bbcode_code_printable (1)bbcode_quote_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)post_thanks_navbar_search (1)printthread (9)printthreadbit (1)spacer_close (1)spacer_open Phrase Groups Available: global postbit showthread	Included Files: ./printthread.php ./global.php ./includes/init.php ./includes/class_core.php ./includes/config.php ./includes/functions.php ./includes/class_hook.php ./includes/modsystem_functions.php ./includes/class_bbcode_alt.php ./includes/class_bbcode.php ./includes/functions_bigthree.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete printthread_start bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages: