A Step-by-Step MySQL Tutorial For Self-Hosted WordPress Bloggers

A Step-by-Step MySQL Tutorial For Self-Hosted WordPress Bloggers:

If you’re a blogger, the chances are that you use WordPress, a powerful solution that is the engine behind many popular websites.

There are two ways in which you can use WordPress, ideal for those with some technical know-how and those without. The first is to use the hosted blog service at WordPress.com, which is similar to Blogger in that you don’t pay any fees and all of the background maintenance is automatic. The downside to this is that you are restricted in which plugins you can add.

For those who prefer more control over their blogs and have some technical ability (although not much is required) then the self-hosted option available from WordPress.org is probably preferred. Choosing this option may require you to have some knowledge of setting up a MySQL database, importing and exporting data, creating backups and understanding how to perform checks and repair the database.

Setting Up The Database

WordPress runs at its best on a MySQL database; you will find that most web hosts offer this as an option on Linux and Microsoft hosting packages. We’ll leave it to you to find a suitable host, just make sure that you have downloaded the latest version of WordPress or are able to install it via your web host. Many offer tools such as Fantastico which install popular web applications for you.

Automated tools such as this can be used to setup a new MySQL database for WordPress, but if this option isn’t available then you will need to sign into your hosting control panel (via the details you will have received when signing up to the hosting package) and find the MySQL Databases tool. In the examples shown here, we’re using cPanel.

Here you will need to use the Create New Database option to name your database; click the Create Database button when done. You should have noticed that the database name has a prefix, resulting in a longer name than you might have expected, e.g.: user101_mydbname.

Adding A Database User

Once you have added the database you will need to Add New User. Add a username, again noting the prefix, and a password, completing the task by clicking Create User.

The final step is to use the Add User to Database option to assign the new user to the database you created earlier. A second screen will typically appear in which you must add All Privileges to your user..

When you’re setting up WordPress, you will need to add the username, database name and password into your wp-config.php file, so make sure you keep a note of them!

Exporting & Importing Data In phpMyAdmin

With a completely new blog you will be able to install WordPress and start typing out your first blog post. If you’re setting up WordPress in order to import posts and comments from another website, however, then you will need to use the various import and export tools that are on offer.

Exporting data in phpMyAdmin via your website’s control panel is the best option. This is done by first selecting the database on the left-hand menu and then clicking Export on the main menu. You can then select the preferred Export Method (Quick or Custom) and a Format (SQL, CSV, many others besides).

Click Go to confirm your choices, save the dump to your computer and wait while it downloads.

Importing the data into your new WordPress installation is a case of first creating a database and installing WordPress. You can then log in to phpMyAdmin and select the appropriate database before selecting Import and using the Browse button to find the dumped MySQL file on your computer.

Select the correct file format and click Go to begin the upload. Once this has completed, phpMyAdmin will begin importing the data into the existing database. Note that this will overwrite any identical data.

File Too Big? Try BigDump

One problem that you may run into when attempting to import a SQL dump is that there is too much data for the server to handle. In this situation you will need to use a tool such as BigDump, a PHP script that you can download from here.

You will need to upload the bigdump.php to your WordPress server along with the SQL dump. Run BigDump by browsing to the file in the WWW directory of your server; it should be something like www.mywebsite.com/bigdump.php. With the script running all you need to do is complete the required fields and browse to the SQL data, and BigDump will do the rest.

Do note however that if your host is using the most up-to-date version of phpMyAdmin then you might find the Partial Import tool can be used instead of BigDump.

Importing & Exporting With WordPress

An alternative to using the MySQL import and export tools is to take advantage of native WordPress features. These are mainly intended for you to import a blog from another platform in a wide variety of formats such as Blogger, Live Journal and even RSS, as well as WordPress.

Available via the Tools menu in the WordPress Dashboard, posts and pages can be exported with comments and other fields and imported using the WordPress option (which isn’t part of WordPress by default, so will need to be installed when selected).

Backing Up WordPress

Although your host will probably run its own backup routine, you should also have your own in place to account for any potential gaps. Overlaps can help you to get your site up and running following errors or failed backups, so it is well worth considering.

There are several ways that you can make a database backup:

• MySQL dump – as explained above
• WordPress backup tool – this popular option is a plugin which can be downloaded and installed
• cPanel/admin panel backup tool – this depends on what options your host offers

Also bear in mind that your WordPress site is bigger than the database. Most users hosted WordPress owners use a non-standard theme, so you might think about backing up the installation directory from time to time. This is equally true if WordPress is only one element of your web presence.

MySQL Database Checks & Repair

Performance issues can often be resolved by checking the tables in your database and repairing them. There are various ways of applying these tasks – if you are using cPanel, then you will find that check and repair tools are present. However if you would rather use phpMyAdmin, or run your checks and repairs on a single table basis, then this is easily done.

To check or repair all tables, simply open the phpMyAdmin screen, select the database in question and – after checking you’re in the Structure view – use the Check All option to select all tables before selecting Check Table or Repair Table from the drop-down menu and clicking Go.

Checking individual tables can either be done using the same method (dropping the Check All option and just selecting the table you want to check/repair) or by opening the SQL query window and entering the Check Table command.

For instance to check the wp_commentmeta table:

CHECK TABLE wp_commentmeta

Conclusion

With so many functions and flexible import and export options, WordPress databases can be easily set up, backed up and imported to using native tools, plugins and phpMyAdmin.

Remember to understand the importance of performing manual maintenance, especially if you use a host with limited options in this area. Performing your own checks and repairs and running backups is vital if you are to keep your WordPress blog running at its optimum level.