The MU forums have moved to WordPress.org

post-install change of $table_prefix (6 posts)

  1. jte
    Member
    Posted 14 years ago #

    I'd really like to change the table prefix of our installation.

    I've found some good directions on doing this in "vanilla" wordpress:

    - change $table_prefix in wp_config.php
    - rename all db tables to new prefix
    - change various values in wp_options and wp_usermeta to use new prefix

    The last part here is obviously a bit more tricky for a wpmu install. There are already a handful of wp_#_options tables, and there may be other places I'm not aware of that would need to be changed.

    Has anyone done this or (better) documented the steps you took?

  2. andrea_r
    Moderator
    Posted 14 years ago #

    As far as I'm aware, it's the same process. (yes, we tried)

  3. jte
    Member
    Posted 14 years ago #

    looks good.

    to change wp_ to xxxx_:

    stop webserver, backup db:)

    UPDATE wp_usermeta SET meta_key=(REPLACE(meta_key,"wp_","xxxx_"));

    for each wp_n_options // where n is a number
    UPDATE wp_n_options SET option_name="xxxx_n_user_roles" WHERE option_name="wp_n_user_roles";

    for each table
    RENAME TABLE wp_foo TO xxxx_foo;

    edit $table_prefix in wp-config.php.

    cross fingers.

  4. jte
    Member
    Posted 14 years ago #

    UPDATE wp_usermeta SET meta_key=(REPLACE(meta_key,"wp_","xxxx_"));

    that second meta_key should be enclosed in backticks.

  5. MarjWyatt
    Member
    Posted 14 years ago #

    It may not be worth the trouble but this thread interests me. I'm new to WP MU and would like to consolidate my wp mu installs in a single instance. So, since I have a very fresh install, the post from JTE about a month ago interests me.

    to change wp_ to xxxx_:

    stop webserver, backup db:)

    UPDATE wp_usermeta SET meta_key=(REPLACE(meta_key,"wp_","xxxx_"));

    for each wp_n_options // where n is a number
    UPDATE wp_n_options SET option_name="xxxx_n_user_roles" WHERE option_name="wp_n_user_roles";

    for each table
    RENAME TABLE wp_foo TO xxxx_foo;

    edit $table_prefix in wp-config.php

    I've been "dusting off" my SQL skills lately, it seems. I get most of this but want to make sure I understand something.

    The installation created several tables prefixed "wp_1_xxx" and since this is a completely fresh installation, am I supposed to perform this operation:

    for each wp_n_options // where n is a number
    UPDATE wp_n_options SET option_name="xxxx_n_user_roles" WHERE option_name="wp_n_user_roles";

    on each table prefixed "wp_1_xxx" or would this operation:

    RENAME TABLE wp_foo TO xxxx_foo;

    do the trick?

    Another way to ask the same silly question is ... does this operation:

    for each wp_n_options // where n is a number
    UPDATE wp_n_options SET option_name="xxxx_n_user_roles" WHERE option_name="wp_n_user_roles";

    only apply to wp_1_options table?

    I have one user in the database at this time, one category, no posts, no comments, no pages, and two blogroll links.

    I sure wish the install worked like a single wordpress install. Assigning a table prefix is written right into the wp-config.file.

    ~sigh~

    Marj Wyatt aka Virtually Marj

  6. jte
    Member
    Posted 14 years ago #

    hi Marj,

    you can assign a table prefix in the config file in wpmu as well... the only reason you'd want to do the above is if you've already installed and can't afford a reinstall for some reason (probably because you've got a bunch of existing content you need to keep). if you can clean install, just modify the config file before you start and you can have whichever table_prefix you want.

    the "for each" stuff is just pseudocode (not proper mysql) meaning for every number that exists. if you've only got wp_1_options, then it only applies to that table. if you've got wp_2_options, then do the same thing with 2 in the place of 1. likewise 3,4,etc... you'll have more than one if you've got multiple blogs set up.

About this Topic