Migrating the WordPress MYSQL Database to a New Table Prefix the Hard Way.

Written by James McDonald

May 6, 2009

So I have moved my domain to dreamhost.com

Why?

  • Got sick of my home computer being a VMWare Server. Couldn’t reboot or upgrade when I wanted
  • Computers are evil time suckers. Moving my website means I don’t have to worry if it’s up, down or sideways. dreamhost.com can do the worrying
  • I had heard good things regarding dreamhost from my Sister whose websites flourish like the fleas on a mangy dog (I mean she has a lot of Websites)
  • They are heavily open source
  • They have a one click install for packages I use (wordpress, gallery, joomla)

To start I dived into the dreamhost.com control panel and did a one click WordPress install, then copied my wp-content folder over the new install. (As a side point the guys at dreamhost are obviously high end propeller heads to be able to provide so many features, I wonder if they wear Bow Ties and Cummberbunds to parties?)

Dumping your database from the command line

I dumped my old WordPress DB using the command

mysqldump --tables wordpress -p -u root > wordpress.sql

Notice the use of the --tables command line option that stops the sql file from containing any USE dbname or DROP dbname statement and just exports the tables alone.

Dreamhost.com has a per customer instance of phpmyadmin so changes and updates to the mysql database can be done with ease.

WordPress Table Prefix Change

Dreamhost uses a random seeming table prefix for wordpress db tables something like wp_xjsirsl_tablename (e.g. wp_xjsirsl_comments) and this mean’t I couldn’t just load the DB up through phpmyadmin because my table prefix was wrong wp_tablename (e.g. wp_comments)

I thought of opening my DB dump file (wordpress.sql) and changing all the table names but … when I opened it in gedit it froze (I think gedit was spending wayyy too much time trying to syntax highlight the file) so I canned that and loaded the file using phpmyadmin which loaded the old tables with the wrong wp_ prefix.

I ended up doing a labourious table rename procedure. Dropping each table with the wp_xjsirsl_ prefix and then rename my uploaded table to be correct wp_xjsirsl_tablename

This caused some issues when trying to log into the wordpress admin console something like “you don’t have permission to access this page” or similar (can’t remember now). However searching on the error message in Google turned up some sql updates that I did manually in phpmyadmin

Anyway it’s all installed and seems to be functioning properly.

Perhaps now I can leave it alone for a while and get to the important things, like my rear deck renovation.

2 Comments

  1. JeremyK@DH

    alternatively, you could have simply changed the prefix in the config file 🙂

    Reply
  2. james

    @JeremyK@DH
    My rationale for not simply changing the prefix value in wp-config.php back to the standard “wp_” was that an automated remote exploit would try for the standard table names. So I thought moving to the different prefix was worth the bother.

    I have no evidence to support my theory though 🙂

    Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

You May Also Like…