How to change the database prefix in multisite (WPMU) install

by

I am starting to implement security features on a client’s website and so I would like to change the WP database prefix. However, the website is a multisite installation (WPMU) and so changing the prefix like you would normally does not work and I do not want to manually change the wp-config files for each sub-site (i.e. I will not be monitoring the sub-site creation).

I tried using the database prefix change option on the All in One WP Security plugin too but no luck.

Does anyone know how to go about this? Any thoughts would be appreciated.

The website is running WP 3.6

1 Answer
1

Just call the following function but make sure to change new_prefix with whatever table prefix you want to add and also check whether config file path is correct at your side, i have used find_wp_config_path() function to find it but sometime it may return null.

function find_wp_config_path() {
    $dir = dirname(__FILE__);
    do {
        if( file_exists($dir."/wp-config.php") ) {
            return $dir;
        }
    } while( $dir = realpath("$dir/..") );
    return null;
}

function change_table_prefix() {

    global $wpdb, $table_prefix;
    $old_table_prefix = $table_prefix;
    $old_prefix_length = strlen( $old_table_prefix );
    $table_new_prefix = 'new_prefix';

    /* Config file path */
    $path = find_wp_config_path();

    $error = $wpdb->set_prefix( $table_new_prefix );
    if ( is_wp_error( $error ) )
        wp_die( '<strong style="color: #ff0000;">ERROR</strong>: table prefix can only contain numbers, letters, and underscores.' );


    //Get the table resource
    $result = mysql_list_tables(DB_NAME);

    //Count the number of tables
    $num_rows = mysql_num_rows( $result );
    $table_count = 0;

    //Rename all the tables name
    for ($i = 0; $i < $num_rows; $i++) {

        //Get table name with old prefix
        $table_old_name = mysql_tablename($result, $i);

        if ( strpos( $table_old_name, $old_table_prefix ) === 0 ) {

            //Get table name with new prefix
            $table_new_name = $table_new_prefix . substr( $table_old_name, $old_prefix_length );

            //Write query to rename tables name
            // $sql = "RENAME TABLE $table_old_name TO $table_new_name";
            $sql = "RENAME TABLE %s TO %s";

            //Execute the query
            if ( false === $wpdb->query($wpdb->prepare($sql, $table_old_name, $table_new_name)) ) {
                $error = 1;
                echo "<p class="error">", $table_old_name, " table name not updated.</p>";
            } else {
                //echo "<p class="success">$table_old_name table name updated to $table_new_name.</p>";
                $table_count++;
            }
        } else {
            continue;
        }
    }
    if ( @$error == 1 ) {
        echo "<p class="error">Please change the above tables prefix to ", $table_new_prefix, " manualy.</p>";
    } else {
        echo "<p class="success">", $table_count, " tables prefix updated successfully.</p>";
    }

    //Update the wp-config.php file
    $configFile = file($path);
    foreach ($configFile as $line_num => $line) {
        switch (substr($line,0,16)) {
            case '$table_prefix  =':
                $configFile[$line_num] = str_replace($old_table_prefix, $table_new_prefix, $line);
                break;
        }
    }

    //making the the config readable to change the prefix
    @chmod($path, 0777);
    if ( is_writeable($path) ) {
        $handle = fopen($path, 'w');
        foreach( $configFile as $line ) {
            fwrite($handle, $line);
        }
        fclose($handle);

        echo '<p class="success">wp-config.php file updated successfully.</p>';
    } else {
        echo "<p class="error">File Not Writeable: Please open wp-config.php file in your favurite editor and search 
              for variable", $table_prefix, " and assign ", $table_new_prefix, " to the same variable.";
    }

    //Create query to update option table
    $update_option_table_query = "UPDATE " . $table_new_prefix . "options 
                                  SET option_name="" . $table_new_prefix . "user_roles" 
                                  WHERE option_name="" . $old_table_prefix . "user_roles" 
                                  LIMIT 1";

    //Execute the update query to update option table user_roles row
    if ( false === $wpdb->query($update_option_table_query) ) {
        echo "<p class="error">Changing value: ",
             $old_table_prefix,
             "user_roles in table ",
             $table_new_prefix,
             "options to  ",
             $table_new_prefix,
             "user_roles</p>";

        echo "<p class="error">End of updating options table data with above error.</p>";
    } else {
        echo "<p class="success">Updated options table data successfully.</p>";
    }

    //Create query to update user_meta table
    $custom_sql = "SELECT user_id, meta_key 
                    FROM " . $table_new_prefix . "usermeta 
                    WHERE meta_key 
                    LIKE '" . $old_table_prefix . "%'";

    $meta_keys = $wpdb->get_results( $custom_sql );

    //Update all the meta_key field value which having the old table prefix in user_meta table
    foreach ($meta_keys as $meta_key ) {

        //Create new meta key
        $new_meta_key = $table_new_prefix . substr( $meta_key->meta_key, $old_prefix_length );

        $update_user_meta_sql = "UPDATE " . $table_new_prefix . "usermeta 
                                SET meta_key='" . $new_meta_key . "' 
                                WHERE meta_key='" . $meta_key->meta_key . "' 
                                AND user_id=" . $meta_key->user_id;

        $wpdb->query($update_user_meta_sql);

    }
    echo "<p class="success">Updated usermeta table data successfully.</p>";

}

or you can directly use this plugin http://wordpress.org/plugins/change-table-prefix

Leave a Comment