Tweet
Important Notes:
- If you are unsure of or do not entirely understand the below outlined steps, please contact your web host and ask they perform these steps for you. You can provide your web host with a link to this article.
- Repairing corruption in some instances means deleting corrupted data from MySQL database. So ideally you should always regularly backup your MySQL databases on your server or hosting account. And also make backups of your database BEFORE attempting repair in case repair deletes data which you may have other avenues or getting back if you still have access to a backup of the corrupted database state.
- Only real defence against data corruption is to always do regular backups your MySQL databases on your server or hosting account !
- MySQL database repair methods and process differ greatly between MySQL MyISAM and InnoDB storage engine tables. Most of the below outlined processes are for repairing MyISAM database tables. The most common method of repairing InnoDB storage engine is restoring the entire database and all tables from a recent backup (NOT just single or select few InnoDB tables - that won't work and will mess up your database even more if you attempt to restore single or partial set of InnoDB database tables). Again, very important you have regular MySQL database full backups of your entire databases' tables preferably via mysqldump within SSH telnet http://www.vbulletin.com/docs/html/m...nce_ssh_backup which is only available for VPS/Dedicated server owners. For shared hosting, ask your web host about MySQL database backups and what options are available to you.
- Ensure you have adequate disk free space on /tmp (for linux or windows equivalent) and mysql data directory partitions i.e. /var/lib/mysql (linux). You'd need at least 2x to 5x times the amount of disk space as the largest sized MySQL database table. So if you have 2GB sized post table, you'd need at least 4-10GB of free disk space on /tmp and/or /var/lib/mysql to perform optimize or repair operations. Otherwise you'd end up failing to repair or optimize your database and end up corrupting it even more.
- Any MySQL Database tables which use Memory storage engine such as cpsession or session DO NOT support repair/optimize so ignore any errors related to those tables not supporting repair/optimize
- You should run repair or optimize commands only against MyISAM storage engine tables and not InnoDB and InnoDB technically doesn't support repair or optimize command and is mapped to ALTER table command to rebuild the InnoDB table (http://dev.mysql.com/doc/refman/5.1/en/optimize-table.html). Frequently running optimize command against InnoDB storage engine tables will end up also bloating and increasing your disk usage if you are using the default InnoDB shared tablespace - where all your InnoDB table data is stored usually ibdata1 file in /var/lib/mysql. Very important DO NOT delete ibdata1 file !
For InnoDB tables, OPTIMIZE TABLE is mapped to ALTER TABLE, which rebuilds the table to update index statistics and free unused space in the clustered index. - While database repair or optimize is being run, your server cpu and resource usage loads will increase
- While database repair or optimize is being run, your forums performance will slow down, lag or hang when navigating the forums.
Repair Methods:
There are several ways to repair vBulletin forum MySQL database corruption:
- Method 1: Use mysqlcheck command line via SSH Telnet. Only VPS or dedicated server owners would have SSH Telnet access but it's the best way to repair corruption if you have access.
- Method 2: Via vB AdminCP Control panel - may not repair all forms of corruption.
==========================================================================
Method 1: Use mysqlcheck via SSH Telnet for VPS/Dedicated server owners
==========================================================================
step 1. Immediately, upon noticing MySQL database corruption, CLOSE or your forums via AdminCP to prevent further writes to MySQL databases.
step 2. BACKUP your forum MySQL database BEFORE running repair/optimize using mysqldump to sql file using ssh2 telnet and mysqldump as outlined here http://www.vbulletin.com/docs/html/m...nce_ssh_backup. Or get your web host to backup your database for you if you don't have SSH Telnet access. DO NOT solely rely on backups via phpmyadmin which may timeout or result in incomplete backups!
step 3. To repair such corruption log into server using ssh telnet as root user preferably try using mysqlcheck http://dev.mysql.com/doc/refman/5.1/en/mysqlcheck.html
1. check database tables
mysqlcheck -s -u mysqlusername -p vbforumdbname
ignore session and cp session table messages and on mysql 5.1.x, ignore errors related to mysql.general_log and mysql.slow_log they don't support mysqlcheck.
2. repair table
mysqlcheck -r -q -u mysqlusername -p vbforumdbname
3. recheck database tables
mysqlcheck -s -u mysqlusername -p vbforumdbname
if still get corruption errors that aren't related to vB session tables, then run
mysqlcheck -r -u mysqlusername -p vbforumdbname
if you want to do one table at a time then add tablename(s) to end of commandline
i.e.
mysqlcheck -s -u mysqlusername -p vbforumdbname tablename1 tablename2 tablename3
Personally, I suggest the mysqlcheck method as REPAIR doesn't fix all forms of corruption.
4. To optimise database
mysqlcheck -o -u mysqlusername -p vbforumdbname
I would take copy and paste the resulting output from mysqlcheck command to your own text files for your own records so as to know what tables you repaired.
==========================================================================
Method 2: Via vB AdminCP Control panel - may not repair all forms of corruption
==========================================================================
Basically, web gui interface to access MySQL CHECK, MySQL REPAIR and MySQL OPTIMIZE commands.
AdminCP -> Maintenance -> Repair / Optimize Tables
step 1. Immediately, upon noticing MySQL database corruption, CLOSE or your forums via AdminCP to prevent further writes to MySQL databases.
step 2. BACKUP your forum MySQL database BEFORE running repair/optimize using mysqldump to sql file using ssh2 telnet and mysqldump as outlined here http://www.vbulletin.com/docs/html/m...nce_ssh_backup. Or get your web host to backup your database for you if you don't have SSH Telnet access. DO NOT solely rely on backups via phpmyadmin which may timeout or result in incomplete backups!
step 3. Log into your forum's AdminCP control panel and navigate the left frame column to Maintenance menu and expand that option to reveal, Repair / Optimize Tables option link.
step 4. Check the checkbox next to all or specific vB database tables you want to run repair and optimize command against.
You can see which MySQL Tables are MyISAM, InnoDB or Memory storage engine based via AdminCP -> Maintenance -> Diganostics -> System Information -> Select Table Status drop down and run it
Second column reports what each vB MySQL database table's storage engine is MyISAM, InnoDB or Memory
Once you figure out which are MyISAM storage engine tables, you can check them under AdminCP -> Maintenance -> Repair / Optimize Tables
step 5. Then, check the boxes to choose if you want to run repair and/or optimize against. Then wait for the process to complete. It can take seconds to hours depending on size of MySQL database tables and your server hardware and configuration settings.
step 6. When repair or optimize process is complete you should see something like below image. I ran optimize and repair against attachment and stylevar table. As you can see attachment table was InnoDB storage engine based and didn't support optimize command while stylevar was MyISAM storage engine based and ran optimize fine. I would take a screenshot or save the right html frame for your own records so as to know what tables you repaired.
