WordPress database Modify table prefix causes errors and solutions

The default data table prefix for WordPress is "WP_": (Note: This is the default datasheet, and if other plug-ins are installed it is possible to generate other WP_ data tables)

Wp_commentmeta
Wp_comments
Wp_links
Wp_options
Wp_postmeta
Wp_posts
Wp_terms
Wp_term_relationships
Wp_term_taxonomy
Wp_usermeta
Wp_users

The first step: So the first thing is to change the table prefix to other names, such as Ooxx_ (recommended with phpmyadmin or Adminer modification)

Ooxx_commentmeta
Ooxx_comments
Ooxx_links
Ooxx_options
Ooxx_postmeta
Ooxx_posts
Ooxx_terms
Ooxx_term_relationships
Ooxx_term_taxonomy
Ooxx_usermeta
Ooxx_users

Step Two: Modify wp-config.php

Find $table _prefix = ' wp_ '; instead $table _prefix = ' ooxx_ ';

Is that all? Originally thought is, because visit the blog all normal, but when you log in backstage, even if you are the administrator waiting for you is the following line of words:

You don't have sufficient permissions to access this page.

Dan, the Anthony and the kin? Br/>
Step Three: Modify the field at the beginning of wp_ in the table

1. Ooxx_options: There's a wp_user_roles to Ooxx_user_roles.

2. Ooxx_usermeta: Put the inside Wp_capabilities, Wp_user_level, Wp_user-settings, Wp_user-settings-time, and Wp_dashboard_quick Wp_ all _press_last_post_id field names to Ooxx_

3. Also ensure that the values of the ooxx_capabilities and ooxx_user_level in the Ooxx_usermeta table are as follows
Ooxx_capabilities should be a:1:{s:13: "Administrator"; s:1: "1";}
Ooxx_user_level should be 10

Summarize:

Toss after the simple method, suitable for the database is not the case: Export the original database (not compressed SQL format), open the export file with a text editor, search replace "Wp_" as "new prefix _", import the new server database. But my habit is still single. It is better to set a special table prefix for a site.

WordPress Security correctly change database table prefix

Before using the default database prefix to do the Web site, and so on, for security reasons, do not want to use the default database prefix, so you want to change the database table prefix.

There are many ways to replace the WordPress default database prefix. But here I think the better, can let you replace the WordPress database prefix perfectly.

Change wordpress database table name prefix step: (wordpress version 3.0.1)

The following changes to the database prefix tutorial only requires running more than 10 SQL commands in phpMyAdmin.

The first thing to do is to change the prefix of the ten tables, which is simple, and commands as follows:

ALTER TABLE Wp_commentmeta RENAME to Xzy_commentmeta;

ALTER TABLE wp_comments RENAME to xzy_comments;

ALTER TABLE wp_options RENAME to Xzy_options;

ALTER TABLE wp_posts RENAME to xzy_posts;

ALTER TABLE Wp_postmeta RENAME to Xzy_postmeta;

ALTER TABLE wp_users RENAME to Xzy_users;

ALTER TABLE Wp_usermeta RENAME to Xzy_usermeta;

ALTER TABLE wp_links RENAME to Xzy_links;

ALTER TABLE wp_terms RENAME to Xzy_terms;

ALTER TABLE wp_term_taxonomy RENAME to xzy_term_taxonomy;

ALTER TABLE wp_term_relationships RENAME to Xzy_term_relationships;

The above command is to replace the default prefix WP to XZY_, if you want to change to other prefixes, just want to xzy_ replaced with your favorite on it.

And then you need to find it in wp-config.php.

$table _prefix = ' wp_ ';

Replace it with a

$table _prefix = ' xzy_ ';

This thought it was done, but the background found that landing does not go up, so again look at the database, found that the options table has a wp_user_roles, there are usermeta the following five items also need to be modified:

Wp_capabilities

Wp_user_level

Wp_user-settings

Wp_user-settings-time

wp_dashboard_quick_press_last_post_id

In these four items Autosave_draft_ids is the log automatic save, User_roles and User_level is the permission aspect, that capabilities does not understand what, but the intuition tells me also needs to revise, therefore continues to run the following command under the SQL:

Update xzy_options Set option_name = replace (option_name, ' wp_user_roles ', ' xzy_user_roles ');

Update Xzy_usermeta Set meta_key = replace (Meta_key, ' wp_capabilities ', ' xzy_capabilities ');

Update Xzy_usermeta Set meta_key = replace (Meta_key, ' wp_user_level ', ' xzy_user_level ');

Update Xzy_usermeta Set meta_key = replace (Meta_key, ' wp_capabilities ', ' xzy_user-settings ');

Update Xzy_usermeta Set meta_key = replace (Meta_key, ' wp_user_level ', ' xzy_user-settings-time ');

Update Xzy_usermeta Set meta_key = replace (Meta_key, ' wp_user_level ', ' xzy_dashboard_quick_press_last_post_id ');

In this way, our goal is also achieved, I can guarantee that the use of this method can be 100% to ensure that there is no harm to the database, try not to use the editor and other tools to replace wp_ directly, because some database plug-in name, absolute address, the article may exist wp_ this keyword.

Now my site has successfully changed the WP_ to Xzy_, everything is normal, plug-ins can also not need to close!



solve the problem that WordPress can not login after modifying database table prefix

After reinstalling WordPress found no content, to solve, found that his database has 3 kinds of prefixes, it appears to be installed multiple times without the prefix of the inconsistency caused.

So, organize the database:

Click View Size, find the table prefix with data, found that the prefix is actually a database name, very long ... It's ugly! This is intolerable for my obsessive compulsive disorder ... I will first delete all the other 2 prefixes all empty tables, leaving only the very long prefix table, and then select the use of the table prefix below the function, all the changes.

Reinstall WP, found that the original user name landing can not, prompted insufficient authority, degree of a, found a solution, this is to record!

The methods found on the Internet are as follows:


The default table prefix is WP_, if you modify the wp_config.php in the table prefix, that is, $table_prefix= after the content, you need to synchronize the database to modify the table prefix, after the modification is completed, if the login WordPress background successful, will return " You do not have sufficient privileges to access this page. "The hint, that is to say you have not finished modifying, WordPress code design is tight, not so simple let you complete."

Workaround: Open the *_options table and find the fields with the Optionname field value *_user_roles, and replace the prefix with the one you modified (* to indicate the actual prefix).

Then open the *_usermeta table, meta-key the value of the old prefix in the field, and the newer prefix is OK.

Do not know why, found in his this table search, found that there are thousands of of data! It seems that manual is not workable, had to use the SQL statement for local substitution, SQL statements as follows, after the database is selected in phpMyAdmin, click the SQL in the toolbar to execute the following statement:
Update ' New_usermeta ' set ' meta_key ' = replace (' Meta_key ', ' old_ ', ' new_ ') WHERE ' meta_key ' like '%old_% ';
1

Update ' New_usermeta ' set ' meta_key ' = replace (' Meta_key ', ' old_ ', ' new_ ') WHERE ' meta_key ' like '%old_% ';

The above example is actually modified according to the old and new table prefixes:

Among them, New_ represents the new modified prefix, old is the prefix before modification, according to the actual modification, execution can!



The actual above SQL code is also a part of MySQL replacement statement, will not come together to study:

For example, replace the welcom in the The_table table The_subject field with the Welcome:

Update ' the_table ' set ' the_subject ' = replace (' The_subject ', ' Welcome to ', ' welcome ') where InStr (' The_subject ', ' Welcome to ') > 0