In an attempt to tidy up my backend Wordpress databases, I thought I'd change the Wordpress table prefix and the move all my Wordpress tables into a single database.

Before actually moving the tables, I took the plunge of just renaming them by changing the table prefix to uniquely identify which Wordpress installation they belong to.

I did this as follows...

  1. Dump the table names and prepend the new prefix:
    # for x in `/usr/local/bin/mysqlshow --user=${DB_USER} \
    --password=${DB_PASSWD} \
    --host=${DB_HOST} \
    ${DB_NAME} | grep ${OLD_WP_PREFIX} | grep -v ^Database | tr -d " | "`
    do
    echo "RENAME TABLE ${DB_NAME}.$x TO ${DB_NAME}.${NEW_WP_PREFIX}_$x;"
    done

    You'll see all I did was to prepend a new prefix to the old one as all I'm doing is identifying which Wordpress installation the tables relate to.

  2. Copy the output from the above into phpMySQL and ran it. This output can also be directed back into mysql on the CLI. All my tables took on their new names successfully.
  3. Modify my wp-config.php to reflect the correct $table_prefix

Voila!!! At face value this appears to work and your blog will appear as it did before. That is until you attempt to login. You'll be presented with this lovely message:

You do not have sufficient permissions to access this page.

Even when attempting to login as admin. The cause? Simple, some of the Wordpress options are actually saved using the Wordpress prefix. You will now need to open up your favourite MySQL admin tool and modify the following:

In table ${NEW_WP_PREFIX}_options:

  • Option ${OLD_WP_PREFIX}_user_roles

In table ${NEW_WP_PREFIX}_usermeta:

  • Option ${OLD_WP_PREFIX}_capabilities
  • Option ${OLD_WP_PREFIX}_user_level
  • Option ${OLD_WP_PREFIX}_autosave_draft_ids

The last will only exist if you have saved a draft whilst editing. For each of these, you need to change the ${OLD_WP_PREFIX} part to reflect your new prefix, eg: wp_user_roles becomes new_wp_user_roles.

You may need to change other options from plugins that store the prefix as part of the variable name.

If you're using the Bad Behaviour plugin, you'll also need to disable it, remove it's option entries from the _options table and re-enable the plugin.