Repairing WordPress Database is easy by using Cloud Database Server like from The Rackspace Cloud which provides MySQL database server with PHPmyAdmin software. Repairing WordPress Database is needed for various reasons – after getting hacked, after few years of continuous posting 5-6 articles per day and as a optimization process. Consider Repairing WordPress Database when your WordPress database crosses 120 MB with 2400 posts. I did a calculation and found that it is not possible to have a 120 MB database with 2400 posts. The ratio can be lower. If you are Repairing WordPress Database as prophylaxis (i.e. not under being hack), then you can do it in cool head. First, read this article to reduce the wp_commentmeta and wp_comments table size.
Mantras for Repairing WordPress Database
The first mantra for Repairing WordPress Database is, do not hire anyone unless you have a plant that grows bank notes. WordPress is easy and the way we are describing here for Repairing WordPress Database is far better than the hired people will do for you.
For this example of Repairing WordPress Database, we are using PHPMyAdmin (please search on this website if you are not sure what it is) for accessing MySQL database of WordPress, which is by technology is a Cloud Database and the Service provider is The Rackspace Cloud. You can use MySQL cloud database with PHPMyAdmin of any provider. The major reason to use this Cloud Database is, we can connect it to any server. So, if you are using a poor hardware powered server (most falls in this category), you only know that the database is always is localhost, i.e. database is on the same server. But Cloud Database can connect with FTP content of any server with relative url or IP. Now you have understood, basically for few cents (as most are billed for usage only) to few dollars, you can connect your FTP to test the database in this Repairing WordPress Database process.
Second Mantra is, understand the basic structure and mechanism of how WordPress works :
- Only few tables of WordPress stores the core data including your writings, categories etc. Others are for active or even deleted plugins.
- FTP of WordPress : For backup purpose, all is not needed except the wp-content folder, .htaccess (at root), robots.txt (root), wp-config.php (for Rackspace users only who need the original complex database details to login). This wp-content folder contains the post images, theme images, theme css etc.
Third Mantra for Repairing WordPress Database is :
Only these MySQL database Tables are default of WordPress :
Among the default database tables, we need not the circled wp_options for Repairing WordPress Database.
The last Mantra for Repairing WordPress Database in this way is, always import table by table in sql format.
Steps for Repairing WordPress Database using Rackspace Cloud Database Server
If you are a Rackspace Cloud Sites user or just use their database as a service, the method will be the same for Repairing WordPress Database.
First step for Repairing WordPress Database
In cool head install WordPress freshly on a separate domain or subdomain or subfolder. Its better if it has just an FTP. Simply upload the WordPress files in uncompressed format there. Do not do anything with wp-config-sample.php file. In browser, point to the root of this new WordPress future installation. It will ask whether it will create a config file. Agree and proceed. Enter the database details of the Cloud Database, for our Repairing WordPress Database guide its of Rackspace. Done. Its a new fresh installation.
Second step for Repairing WordPress Database
Now open your working site’s database via PHPMyAdmin (Which we will repair in this guide for Repairing WordPress Database). For existing Rackspace users , its SSL connection and you have to use two browsers. Even if you are not an existing Rackspace user, open these two databases in two different browsers for Repairing WordPress Database. The reason is – map in your mind – suppose Chrome is my working website’s database, I will be very careful not to delete any table.
Third step for Repairing WordPress Database
Import these tables in sql format from old database :
How to import a table as SQL from PHPMyAdmin for Repairing WordPress Database :
- Click Browse Tab, click the name of the Row / Table like wp_users
- Click Export.
- You can check it to Custom, but do not do anything which you have no idea about.
- By default it will be SQL, still check it carefully.
- Click GO button and save on your desktop.
Forth step for Repairing WordPress Database
Now open the new WordPress Installation’s database, where we will be Repairing WordPress Database. Delete one by Table / Row and import it from your desktop (basically that is you original old database tables).
Final checking and packing up for Repairing WordPress Database
As we have not set the permalink structure in this fresh WordPress installation for Repairing WordPress Database (we will not do it), we will get a fresh WordPress with all posts, categories etc. Normally, this WordPress will run fine. You can install selective plugins (except cache plugins) which you actually be using, you can ask the developer about the table name to import old tables (like tables for Socialize plugin which has data of how many +1 ed this post).
Its better if you can save the default tables for Repairing WordPress Database in a separate folder. If still it creates problem to run here, delete it, replace with fresh one’s.
So, simply you can either connect this database (in case the database provider and ftp server space provider is same) with your FTP by editing the original site’s wp-config.php file or can do the reverse process how we fetched the data here for Repairing WordPress Database.
Some important points for Repairing WordPress Database in this method :
- Do not change the permalink
- Do not create or install any plugin that will create .htaccess file
- WordPress saves the data as – ?p=5678934 for pages, ?tag=word - in this format. It does not matter where you are installing it. Its the wp_options that changes the things and makes it difficult. This is the reason we avoided to import that table. Otherwise we had to use Search and Replace Plugin, which is a last measure to correct url redirections and path.
- We used SQL to export and import rows for Repairing WordPress Database as it has least chance to add wrong data or corrupt it.
- If you fail for Repairing WordPress Database as you can not import, you have to import as text and run SQL queries.
- It is obvious that, this method for Repairing WordPress Database can be used for changing domain name, changing server etc.
- You can simply make this method of Repairing WordPress Database automated by using cron job.
Searched Terms:database cleaning