Work:Repair and Optimize All Databases on an Account
Contents |
Disclaimer
This script has been tested on an account with a really large database, one database was over 4GB in size and no errors were logged, but this still needs testing, so please send as much feedback as possible. Any serious queries or recommendations will be looked into, but right now the script is about as optimized as it can get, so any changes will likely be aesthetic or improvement to the Interface itself. If you run the PHP script on a large database and it fails, then use the SSH method as it's never failed ever and contact jjones immediately.
Repair and Optimize all databases on an account
Older versions of this required a bit of cPanel work before you can run this, but in the end, it's worth it. So let's get started:
Reference: custom essays
PHP Version
This is a PHP wrapper to the bash command, it does nothing special really, just acquires the username based on the $HOME variable, sets up a couple variables, loads some Javascript and CSS, then asks the user to enter in their password. This will check to verify the password is correct. Please leave the ini_set() settings alone for now, and use this often?? Let jjones know if you have any problems with this script.
Known Bugs: The output is ugly and unformatted.
SSH/Bash Method
Use this if already on the commandline on the account, this also has the advantage of no overhead, so, if dealing with a huge database (PHP version tests perfect and fast on a 4GB database) you can use this method to quickly do a repair. So on to it. 1. Enable SSH, if required, and then SSH in to the customers account. And once at the prompt enter the following:
mysqlcheck -auto-repair -u`whoami` -p -A -o
It will prompt you for a password, enter it, remember it won't echo anything as you type so it's a type of faith.
Here's what sample output looks like from some poor souls databases (s)he didn't know what being fixed :-P
# mysqlcheck -auto-repair -u`whoami` -p -A -o Enter password: METAR_locations.metarLocations OK Movable_type.mt_asset OK Movable_type.mt_association OK Movable_type.mt_author OK Movable_type.mt_blog OK Movable_type.mt_category OK Movable_type.mt_comment OK Movable_type.mt_config OK Movable_type.mt_entry OK Movable_type.mt_fileinfo OK Movable_type.mt_ipbanlist OK Movable_type.mt_log OK Movable_type.mt_notification OK Movable_type.mt_objectasset OK Movable_type.mt_objectscore OK Movable_type.mt_objecttag OK Movable_type.mt_permission OK Movable_type.mt_placement OK Movable_type.mt_plugindata OK Movable_type.mt_role OK Movable_type.mt_session OK Movable_type.mt_tag OK Movable_type.mt_tbping OK Movable_type.mt_template OK Movable_type.mt_templatemap OK Movable_type.mt_trackback OK Movable_type.mt_ts_error OK Movable_type.mt_ts_exitstatus OK Movable_type.mt_ts_funcmap Table is already up to date Movable_type.mt_ts_job OK ampache.access_list OK ampache.album Table is already up to date ampache.album_data Table is already up to date ampache.artist Table is already up to date ampache.artist_data OK ampache.catalog Table is already up to date ampache.democratic OK ampache.flagged Table is already up to date ampache.genre Table is already up to date ampache.ip_history Table is already up to date ampache.live_stream OK ampache.now_playing OK ampache.object_count OK ampache.playlist Table is already up to date ampache.playlist_data OK ampache.preference OK ampache.rating Table is already up to date ampache.session OK ampache.session_stream OK ampache.song OK ampache.song_data Table is already up to date ampache.tag_map Table is already up to date ampache.tags Table is already up to date ampache.tmp_playlist OK ampache.tmp_playlist_data OK ampache.update_info OK ampache.user OK ampache.user_preference OK ampache.user_shout OK ampache.user_vote OK anope.ProfileServ Table is already up to date anope.anope_bs_core OK anope.anope_cs_access OK anope.anope_cs_akicks Table is already up to date anope.anope_cs_badwords Table is already up to date anope.anope_cs_info OK anope.anope_cs_levels OK anope.anope_cs_ttb OK anope.anope_hs_core OK anope.anope_info Table is already up to date anope.anope_ms_info OK anope.anope_ns_access OK anope.anope_ns_alias OK anope.anope_ns_core OK anope.anope_ns_request Table is already up to date anope.anope_os_akills Table is already up to date anope.anope_os_core OK anope.anope_os_exceptions Table is already up to date anope.anope_os_news Table is already up to date anope.anope_os_sglines Table is already up to date anope.anope_os_sqlines Table is already up to date anope.anope_os_szlines Table is already up to date irc_quotes.chirpy_accounts OK irc_quotes.chirpy_event_metadata OK irc_quotes.chirpy_events OK irc_quotes.chirpy_news OK irc_quotes.chirpy_quote_tag OK irc_quotes.chirpy_quotes OK irc_quotes.chirpy_sessions OK irc_quotes.chirpy_tags OK irc_quotes.chirpy_vars OK kjv.bible OK kwotes.captchas OK kwotes.kwote OK kwotes.kwote_backup OK kwotes.vote OK mysql.columns_priv Table is already up to date mysql.db OK mysql.func Table is already up to date mysql.host Table is already up to date mysql.tables_priv OK mysql.user OK noteholders.fsbo_contacts OK noteholders.mainmenu OK noteholders.note_holders OK noteholders.note_information OK noteholders.users OK rash_quotes.rash_news Table is already up to date rash_quotes.rash_queue OK rash_quotes.rash_quotes Table is already up to date rash_quotes.rash_tracking Table is already up to date rash_quotes.rash_users OK recipes.ad_groups OK recipes.admin_control OK recipes.admin_login OK recipes.ads OK recipes.categories OK recipes.emailed_recipes Table is already up to date recipes.ingredients OK recipes.ratings Table is already up to date recipes.recipes OK recipes.search_terms Table is already up to date recipes.subscribers OK recipes.users OK wordpress1.wp_comments OK wordpress1.wp_jsspamblock OK wordpress1.wp_links OK wordpress1.wp_options OK wordpress1.wp_postmeta OK wordpress1.wp_posts OK wordpress1.wp_term_relationships OK wordpress1.wp_term_taxonomy OK wordpress1.wp_terms OK wordpress1.wp_usermeta OK wordpress1.wp_users OK zoelife.articles OK zoelife.blog OK zoelife.comments Table is already up to date zoelife.dp_tunes OK zoelife.home OK zoelife.irc OK zoelife.quotes OK zoelife.users OK
So why go through the hassle instead of just using the cPanel Repair? Well, lots!! This way allows all the databases to be worked in in one shot and also Optimizes the databases. This is especially helpful for users that tend to hit CPU Exceeded Error and or Slow MySQL Queries see Here for more info.
