Vous vous demandez comment modifier le préfixe de la base de données de WordPress pour votre site ?
Modifier le préfixe de la base de données peut protéger les données de votre site contre les injections SQL et autres attaques de pirates en ligne. Il peut s’agir d’une étape importante dans l’amélioration de la sécurité de WordPress.
Dans ce tutoriel, nous allons vous afficher comment modifier le préfixe de la base de données de WordPress pour améliorer la sécurité.
Pourquoi modifier le préfixe de la base de données de WordPress ?
La base de données WordPress est comme le cerveau de tout votre site WordPress, car toutes les informations et tous les fichiers y sont stockés.
Cela fait de la base de données la cible préférée des pirates informatiques. Les spammeurs et les pirates peuvent exécuter des codes automatisés pour des injections SQL et pénétrer dans votre base de données WordPress.
Malheureusement, de nombreuses personnes oublient de modifier le préfixe de la base de données lors de l’installation de WordPress. Il est donc plus facile pour les pirates de planifier une attaque de masse en ciblant le préfixe par défaut wp_.
La façon la plus simple de protéger votre base de données WordPress est de modifier le préfixe de la base de données, ce qui est très simple à faire sur un site que vous êtes en train de configurer.
Il faut quelques étapes supplémentaires pour modifier le préfixe de la base de données WordPress correctement pour votre site établi sans le gâcher complètement. Ceci étant dit, nous allons vous afficher comment modifier le préfixe de votre base de données WordPress et améliorer votre sécurité WordPress.
Tutoriel vidéo
Si vous n’aimez pas la vidéo ou si vous avez besoin de plus d’instructions, alors continuez à lire.
Comment modifier le préfixe de la base de données de WordPress ?
Nous vous recommandons de sauvegarder votre base de données WordPress avant de faire quoi que ce soit suggéré dans ce tutoriel. Il est également important de conserver des sauvegardes quotidiennes de votre site WordPress à l’aide d’une extension comme Duplicator.
Nous vous recommandons également de rediriger vos internautes vers une page de maintenance temporaire pendant que vous modifiez le préfixe de la base de données. Dans le cas contraire, vous risquez de provoquer une mauvaise expérience pour les utilisateurs/utilisatrices de votre site.
Comment modifier le préfixe de la table dans le fichier wp-config.php ?
Tout d’abord, vous devrez vous connecter à votre site à l’aide de FTP ou de l’application Gestionnaire de fichiers de votre compte d’hébergement WordPress.
Vous devez ensuite ouvrir votre fichier wp-config.php, qui est situé dans le répertoire racine de WordPress. Ici, vous pouvez modifier la ligne de préfixe de table de wp_ à quelque chose d’autre comme ceci wp_a123456_.
La ligne ressemblerait donc à ceci :
$table_prefix = 'wp_a123456_';
Note : Vous pouvez modifier le préfixe du tableau uniquement à l’aide de chiffres, de modifications et de tirets bas.
Modifier tous les noms des tables de la base de données
Ensuite, vous devez vous connecter à votre base de données à l’aide de l’outil phpMyAdmin. Si votre hébergeur utilise le tableau de bord cPanel, vous y trouverez facilement phpMyAdmin.
Il existe au total 11 tables WordPress par défaut, les modifier manuellement serait donc fastidieux.
Au lieu de cela, vous devez cliquer sur l’onglet « SQL » en haut de la page.
Vous pouvez ensuite saisir la requête SQL suivante :
RENAME table `wp_commentmeta` TO `wp_a123456_commentmeta`;
RENAME table `wp_comments` TO `wp_a123456_comments`;
RENAME table `wp_links` TO `wp_a123456_links`;
RENAME table `wp_options` TO `wp_a123456_options`;
RENAME table `wp_postmeta` TO `wp_a123456_postmeta`;
RENAME table `wp_posts` TO `wp_a123456_posts`;
RENAME table `wp_terms` TO `wp_a123456_terms`;
RENAME table `wp_termmeta` TO `wp_a123456_termmeta`;
RENAME table `wp_term_relationships` TO `wp_a123456_term_relationships`;
RENAME table `wp_term_taxonomy` TO `wp_a123456_term_taxonomy`;
RENAME table `wp_usermeta` TO `wp_a123456_usermeta`;
RENAME table `wp_users` TO `wp_a123456_users`;
N’oubliez pas de modifier le préfixe de la base de données pour qu’il corresponde à celui que vous avez modifié lors de la modification du fichier wp-config.php.
Vous devrez peut-être aussi ajouter des lignes pour d’autres extensions qui ajoutent leurs propres tables dans la base de données de WordPress. L’idée est que vous modifiez tous les préfixes des tables pour les remplacer par celui que vous souhaitez.
Le tableau des options
Nous devons ensuite rechercher dans la table des options tous les autres champs qui utilisent wp_ comme préfixe afin de les remplacer.
Pour accélérer le processus, vous pouvez utiliser cette requête :
SELECT * FROM `wp_a123456_options` WHERE `option_name` LIKE '%wp_%'
Vous obtiendrez un grand nombre de modifications et vous devrez les parcourir une à une pour modifier ces lignes et leurs préfixes.
Tableau UserMeta
Ensuite, nous devons rechercher dans usermeta tous les champs qui utilisent wp_ comme préfixe, afin de pouvoir le remplacer.
Vous pouvez utiliser cette requête SQL pour cela :
SELECT * FROM `wp_a123456_usermeta` WHERE `meta_key` LIKE '%wp_%'
Le nombre d’entrées peut varier en fonction du nombre d’extensions WordPress que vous utilisez sur votre site. Il suffit de modifier tout ce qui a wp_ par le nouveau préfixe.
Vous êtes maintenant prêt à tester votre site. Si vous avez suivi les étapes précédentes, tout devrait fonctionner correctement.
Nous vous recommandons d’effectuer une nouvelle sauvegarde de votre base de données par mesure de sécurité.
Nous espérons que cet article vous a aidé à apprendre comment modifier le préfixe de la base de données WordPress. Vous pouvez également consulter notre guide sur la façon d’optimiser votre base de données WordPress et nos choix d’experts des meilleures extensions de base de données WordPress.
Syed Balkhi says
Hey WPBeginner readers,
Did you know you can win exciting prizes by commenting on WPBeginner?
Every month, our top blog commenters will win HUGE rewards, including premium WordPress plugin licenses and cash prizes.
You can get more details about the contest from here.
Start sharing your thoughts below to stand a chance to win!
Dave van Hoorn says
Update the SQL for renaming the prefixes please. WordPress adds the ‘wp_termmeta’ table now. It’s included in the SQL below.
RENAME table `wp_commentmeta` TO `wp_yoursitename_commentmeta`;
RENAME table `wp_comments` TO `wp_yoursitename_comments`;
RENAME table `wp_links` TO `wp_yoursitename_links`;
RENAME table `wp_options` TO `wp_yoursitename_options`;
RENAME table `wp_postmeta` TO `wp_yoursitename_postmeta`;
RENAME table `wp_posts` TO `wp_yoursitename_posts`;
RENAME table `wp_termmeta` TO `wp_yoursitename_termmeta`;
RENAME table `wp_terms` TO `wp_yoursitename_terms`;
RENAME table `wp_term_relationships` TO `wp_yoursitename_term_relationships`;
RENAME table `wp_term_taxonomy` TO `wp_yoursitename_term_taxonomy`;
RENAME table `wp_usermeta` TO `wp_yoursitename_usermeta`;
RENAME table `wp_users` TO `wp_yoursitename_users`;
Prabhudatta Sahoo says
When I am renaming my tables in the database all the images in the gallery are going away, I do not understand the reason. Could anyone please help me fixing this issue?
Terry Thorson says
This issue will occur if you do not update the serialized data strings (used for your gallery images) correctly in the database. A good way to do this is to use the plugin WP Migrate DB. There is an excellent tutorial for this on Lynda.com (although be sure to use the same prefix for your target database as your source database).
I learned this the hard way. Trying to start afresh, I discovered my backup was faulty as well. Luckily my webhost had an older backup I could use to restart my migration. WP Migrate DB did the trick.
Cameron Jones says
I can’t find any fields in the _usermeta or _options tables that would require updating. Unless they are specifically referencing a table, they shouldn’t need to be updated. It’s a table prefix, not a variable prefix.
Cameron Jones says
Actually, I stand corrected. There are a couple that will be part of a default WordPress install:
In prefix_options
prefix_user_roles
In prefix_usermeta
prefix_capabilities
prefix_user_level
prefix_dashboard_quick_press_last_post_id
prefix_user-settings
prefix_user-settings-time
You should be careful regarding updating any other fields. Plugins may either use the defined prefix or `wp_` as a prefix. Always make a backup and test on a dev or staging environment.
kapil says
hi,
i have a query. assume that i have changed all my prefix from wp_something to some other name. these changes will be done to the existing fields in the database only. but wont the codes in my wordpress .php files remain the same??? so next time for any new user registration or some other registration, the entities will again be saved as wp_something as the main code in the .php files remains unchanged… ???
thanks….
tech says
UPDATE `wp_a123456_options` SET `option_name`=REPLACE(`option_name`,’wp_’,’wp_a123456_’) WHERE `option_name` LIKE ‘%wp_%’;
UPDATE `wp_a123456_usermeta` SET `meta_key`=REPLACE(`meta_key`,’wp_’,’wp_a123456_’) WHERE `meta_key` LIKE ‘%wp_%’;
I do changes but after doing this i again run following query it shows prefix not changed
SELECT * FROM `wp_a123456_options` WHERE `option_name` LIKE ‘%wp_%’
Wiem says
Thank you for the queries
Nathan WHite says
This post and the responses to the comments leaves out a very important component. Does the table need to begin with wp_ ?
Coming upon another discussion in wordpress.org indicated that it indeed did not need to. It would have helped me if this question was answered by the moderator.
Also, dismissed_wp_pointers questions were not clearly answered. I changed mine.
Clare Wood says
Hi guys,
I followed these steps, now when I try to see the back-end or front-end of my site I get this:
ERROR: $table_prefix in wp-config.php can only contain numbers, letters, and underscores.
I’m positive I only have lowercase letters and an underscore as my table prefix.
Any ideas? The site is on localhost.
Cheers.
Paul says
Fantastic and logically prepared article on Wp security.
Thomas says
Thanx a bunch! I tried to restore my old database, but to no avail. Then I figured out that my new database prefix was different from old. Made all that you recommended and vuala!
Divyesh says
Thanks a Lot!!!
It worked like a charm
Nikhil says
I am getting this error……. »You do not have sufficient permissions to access this page » after implementing above procedure…..how to solve it?????
Saz says
These instructions have been followed but now role assignment for new users has disappeared…
Tom says
Thanks for a great tips .
I have a question.
Do I need to change « wp_ …. » used in post_meta table as well?
johnny says
or this plugin http://wordpress.org/plugins/db-prefix-change/
savagemike says
For the wp_options and wp_usermeta tables, why not dump the database and use sed to replace « wp_ » with the new prefix? Example:
sed -i ‘s/wp_/wp_1234/g’ > filename.sql
Then, simply import the modified dump. Easier and faster than changing cells one-by-one.
gcreator says
Attacker can simple use ‘%wp_%’
I mean that is not fully secure at all…
because he knows the table names that wordpress generates he can simply use ‘_%users’ for wp_anything_users OR ‘_%posts’ for ‘wp_anything_posts’ ..etc…
Jim says
gcreator…
For 99% of the attacks against WP databases, the skiddies are using pre-built tools and default settings. This gets you out of their crosshairs.
if you are under focused attack then yeah, simple obfuscation will only slow them down, not completely protect you.
javed says
thanks a lot
gabe says
I got syntax error when following this (my version of SQL is 5.5.x).
I had success after referring to the SQL manual. Needed to leave the quotes out of the query:
[WRONG] RENAME table ‘wp_links’ TO ‘wp_xx_links’;
[RIGHT] RENAME TABLE wp_links TO wp_xx_links;
RosellaBird says
Thanks! That saved me a lot of time
I had the same error too
Sepster says
You were using « standard » quotes ‘. The correct syntax to identify object names in mySql is to use « backquotes » ` (ie the key in the top left of a standard-US keyboard, left of the number 1)
Marcello Nuccio says
The problem is that you are using the wrong quotes. You must use the backtick character around table names, not the apostrophe. In SQL, the apostrophe is used to delimit strings.
Karen says
I have changed the prefixes of a new install and then built a whole new site! I suddenly realised that I might not be able to update wordpress as normal from the admin panel..
Does changing the prefixes affect being able to update wordpress as normal???
Pablo says
Nice.
You can use this as well:
UPDATE `wp_a123456_options` SET `option_name`=REPLACE(`option_name`,’wp_’,’wp_a123456_’) WHERE `option_name` LIKE ‘%wp_%’;
UPDATE `wp_a123456_usermeta` SET `meta_key`=REPLACE(`meta_key`,’wp_’,’wp_a123456_’) WHERE `meta_key` LIKE ‘%wp_%’;
Haary says
Please answer » How to create a plugin for take a backup of speific table in wordpress database? » in the stackoverflow
Haary says
It is a nice tutorial. Please see the link http://stackoverflow.com/questions/21546786/how-to-create-a-plugin-for-take-a-backup-of-speific-table-in-wordpress-database
David Appleby says
Very nice guide thanks.
Andrew Rickards says
Thanks for the useful info. I just tried changing my DB prefix and everything seems to have worked perfectly.
John says
Thank you for doing the work to inform us on this topic. I have zero experience with WordPress, mySQL and PHP, so your help is greatly appreciated. A couple of questions:
You have a graphic right below the words « There are a total of 11… », with SQL circled. Am I supposed to check all the checkboxes?
In the section titled « The Options Table », which I’m getting to next, you say « This will return a lot of results, and you need to go one by one to change these lines. » How is this done (or will it be perfectly obvious)?
WPBeginner Support says
John, you need to click on the SQL which will open a Text Area, copy and paste the query given below the circled screenshot into SQL textarea and click Go button.
When updating options table you will run another SQL query to search for fields which have wp_ in them and replace those fields with your new database prefix. The query will return a number of rows you need to click on the Edit button next to each row to edit it and manually replace wp_ with your new database prefix.
Administrateur
Iftekhar says
Dear writer, I have tested this in my local server. I am having problem to get access in my admin panel after changing table prefix. I have found « dismissed_wp_pointers » this in my database. Do I need to change it also?
Thanks in advance
WPBeginner Support says
No we don’t think you need to change that.
Administrateur
Iftekhar says
Problem solved :). Actually I forgot to change option table. Thanks for reply.
AMSGATOR says
`dismissed_wp_pointers` shows up when querying SELECT * FROM `wp_a123456_usermeta` WHERE `meta_key` LIKE ‘%wp_%’
So I changed it since this says to change all the wp_ to the new prefix. I hope it doesn’t break anything.
Kobbe says
Is this tutorial for an already installed blog…? Please kindly brief me on how to do this on a FRESH installations.
AMSGATOR says
If you have already installed WordPress (regardless of how much you published) and you want to change the prefix then follow this tutorial.
blurped says
Great guide, works like a charm. One question- why did you leave ‘wp_’ in the new prefix? Seems like a whole lot of effort to change your table prefixes but still leave that fragment in there. Just remove it completely or replace it with something else more random (like ‘eh_’ or whatever)
yerom says
Well, everything is just fine… But when i’m go back to my site, it makes me the 5 minutes install again…
I think i missed something.
Anyone had the same issue ?
Tks !
WPBeginner Support says
Check your wp-config.php file, it seems like you forgot to update
$table_prefixvalue.Administrateur
ideal ismail says
hi Admin,
Regarding the naming convention for the table prefix, « Note: You can only change it to numbers, letters, and underscores. Feel free to mix uppercase and lowercase. »
this is not true. You CAN’T use uppercase as it will wreak havoc with your database entries. i personally encountered this and the solution is to restrict to using numbers, underscores and lowercase letters.
many other people have encountered this. a quick google search gave me the following:
http://wordpress.org/support/topic/case-sensitive-wp_table_prefix?replies=1
http://stackoverflow.com/questions/9827164/wordpress-keeps-redirecting-to-install-php-after-migration
http://esdev.net/wordpress-error-you-do-not-have-sufficient-permissions-to-access-this-page/#.Ui_pHtJkMwB
hope that helps.
Editorial Staff says
Updated the article.
Administrateur
Steve says
Couldn’t you just back everything up,
export the DB to a DBbackup.sql file
open it with a text editor.
do a global search and replace and replace wp_ with mynewprefix_
Save the file,
drop all the tables in the DB
and import the new DBbackup.sql?
Editorial Staff says
You could do that
Administrateur
Steve says
Update – The global search and replace works. However, it might work too good. One of the side effects is that it returns all of your widgets to the default (fresh install) state.
Luckily – it returns them to the “inactive section” so you don’t have to completely re-do them. My lesson learned was to take a screen shot of the dashboard (before) so it’s easier to remember where you had them all.
Ahsan says
Hey after changing table prefix and table name from mysql when i refresh the website it says website has a redirect loop, what should i do?
Andrew says
leave the database changes to the professionals…………..
GReg says
Update the prefix definition in config.php
Katie says
Tried to do this on a multisite database install… totally failed. I seemed to put all the queries in correctly, but I got errors and at the end of all the steps my site was just redirecting itself indefinitely…
Mike says
I did these changes as instructed but now I can’t get to my admin page.
Mark Pescatrice says
Well after about 30 minutes of sweating bullets, I was able to do this. I made one tiny typo on wp-config.php. but otherwise it went smoothly. I did use Duplicator to create a backup before starting all of this.
I recommend users to do the following additional steps:
Before starting, put a dummy index.html in the root folder of your WP install, and renaming index.php to index.php.tmp (or something similar). After making a tiny typo in the wp-config.php file, I found myself at the WP install page.
After you are done, rename index.php.tmp to index.php and remove or rename the index.html page.
Thanks for the great article. I’m curious to see how the changes will affect the spam count.
Mark Pescatrice
Al Lemieux says
In terms of process, do I make these security changes locally first? Or do I make them on WordPress?
Editorial Staff says
You make them on WordPress.
Administrateur
Dana Nourie says
After changing the table prefixes, I can log in and all the content is there, but I’m not getting the admin interface. Any idea what I should check?
Thank you!
Dana
Dana Nourie says
Nevermind, I had to change entries in the options table.
Corey says
What about things like this? Do we need to change the wp in this, or only when it starts with wp?
dismissed_wp_pointers
Editorial Staff says
You should be able to change it to whatever you like.
Administrateur
ana says
I am confussed about ‘ _site_transient_timeout_wporg_theme_feature_list ‘ these, do I have to change wp here as well? and if yes then plz give an example.
AMSGATOR says
You should only have to change it if wp is followed by an underscore (i.e., wp_)
Eric says
Awesome information security for wp anti thief..But is there any free plugin or software to automate these processes?
Editorial Staff says
Don’t think there is.
Administrateur
Yann says
Thanks for the tutorial.
Here a plugin to automate these processes : http://wordpress.org/extend/plugins/wp-security-scan/
Daniel Garneau says
All In One WP Security plugin has a Database Security option for editing the prefix of an existing WordPress database. I have been using the plugin for several months now, but I have not used this feature yet.
Orion says
just tried this out, everything changed according to your instructions, hopefully this keeps the russians out….for a while at least.. Thank you for posting.
Benno says
Thanks, worked great on my new blog!
Debra says
I must be a total idiot because I sure can sort this out. Can’t even find the wp database. Geez this is frustrating
Editorial Staff says
Which web hosting service are you using?
Administrateur
Scott Semple says
Successfully changed the database prefixes, but now I can’t sign in?
My ##_capabilities in ##_usermeta is for an admin: a:1:{s:13: »administrator »;s:1: »1″;}
Thoughts on why I still can’t sign in? Thanks!
Editorial Staff says
It loads the website properly, but you can’t sign in? What error does it say… Incorrect password? or incorrect username?
Administrateur
mckenzie says
thanks so much! i searched all over the internet and you are the only blog entry to get this right on the spot!!
rawalbaig says
Please Help me I’m not able to process the last two steps For « The Option Table » I’m Here http://imageshack.us/photo/my-images/819/img00.png
& ‘ »UserMeta Table » Here http://imageshack.us/photo/my-images/84/img002o.png
What to do next I’m not able to login my wordpress account
TrentJessee says
Excellent security post, and well written. What would you charge to do this service for people? Thanks!
http://trentjessee.com
WesHopper says
@wpbeginner @WesHopper
wpbeginner says
@WesHopper You just manually change it. Because the number vary based on the plugins you have.
Keith Davis says
Hi Admin
Good clear instructions but I’ve never had the confidence to attempt a database prefix change – just in case!
You boys provide some great stuff – much appreciated.
Leonco says
Very interesting security steps…
But surely there has to be a security plugin that addresses
the issue of preventing hacking.
João says
There are several plugins that do this, but the truth is that it’s always good to know how to do this yourself.
For example I had an (apparently) buggy plugin change my WordPress database prefix just now, and i was locked out of my own WP installation.
This simple guide showed me how to undo the damage.
Vivek Parmar says
Thanks a lot for sharing this. it is essential to secure WordPress first before posting any content your blog.