At some point, you’re going to want to change your WordPress site around a little. It might be just a simple theme change or maybe it’s a full transfer of your site from one host to another — or from a local installation to a live one. Maybe you’ve swapped URLs from a .com to a .design. You might even need to change a user’s username or any number of other values. Doing these things manually can be a huge pain and a massive time sink. Thankfully, there’s an easier way. In today’s post we’ll show you how to use a free plugin to get the job done quickly and easily.
Search and Replace, The Old Way
Typically, there’s no way to search and alter your WordPress database from within your dashboard. That’s by design because the db is the nerve-center of your website. Everything is stored there, from usernames, emails, to the web address and WooCommerce api keys and tokens. The DB is important. And being able to edit it all willy-nilly is dangerous.
Normally, you need to edit database tables through a tool like phpMyAdmin. In general, you can find this in the cPanel of your host under, you guessed it, databases.
Once inside, you have a smorgasbord of options and tables and numbers and tabs and…well, you can see for yourself.
All of those to the left are databases for your host, generally representing one WP install each. And then when you click into one of those, you see all the options you have for all the different kinds of data being stored.
And now, you hit Search, and you see the data in that table. Now you can start with the finding and replacing.
That’s a…lot. And it is really easy to get baffled and confused and completely turned around like this. So, the Better Search and Replace plugin may be a good answer for you if you need to edit, change, or migrate any kind of data in your database.
This is where our free and easy plugin solution comes in!
Search and Replace, The Better Way
When you install the plugin from the WordPress plugin repository, you get a new settings menu that appears under Tools called Better Search Replace.
Inside this new addition, you get a much friendlier user interface than phpMyAdmin. And the best part is that this plugin limits the database to this WordPress installation. So there are no fears of affecting any other sites. There are three main things here to pay attention to.
1. Search For / Replace With
Pretty straight-forward, really. Whatever values you’re looking for, no matter which table (or how many), are going to be entered here. For instance, if you’re going to edit all comments that contain the name of a particular website you don’t want mentioned, you can enter the website name in the first field, and the redaction would go in the second.
2. Select Tables
Depending on the depth and breadth of your search, you can choose which tables you want to alter. For instance, if you are only looking to alter the comments areas, you can select the ones labeled with comments and commentsmeta with a CTRL/CMD click.
3. Run as Dry Run
I think this is the most important option they give you. Since the database is so sensitive, performing a dry run gives you a simulation or what would happen. Just enter your info and keep this checked to get a report on which tables and which fields will be changed. It is imperative (if you ask me) to do this first, before you make any permanent changes to your site.
Using the Plugin
Using the plugin itself is really simple. For an example, I will walk you though changing a WordPress administrator username. Yes, the same kind of username that comes defaulted as admin or has a warning beside it in the profile that says it can’t be changed. This is the kind of use-case for DB manipulation that makes Better Search and Replace shine.
First off, you can see the list of users for the WP site.
My goal is to change Nolan Sorento’s username from The Top Dawg to IOI-655321. By doing so, he will log in using the replacement, and anywhere that the username was used in the site will be replaced as well.
I am
- highlighting all of the tables with a CTRL/CMD-click
- entering my search and replace terms in the fields, and
- making sure that I do a dry run first
As you can see, the only instances of the original username were in users and usermeta tables. Perfect. Now, uncheck the dry run and perform the real search and replace. Afterward, you will see in the banner that the cells were actually updated, which is verified in the modal, too. Additionally, you can see that instead of .001 seconds, the operation tool 0.004 and 0.003 seconds, so you can tell there was actual computation happening behind the scenes.
When we go back to the Users page, you can see the change has taken hold and the username is completely new.
You can even go into the user itself and see that the grayed out field has been changed. Usernames cannot be changed, huh? I’ll show you, WordPress documentation!
And There You Have It
I think that’s a much nicer, more elegant way of working with your WordPress databases. You can use this for any number of changes that you want to make, including web addresses (changing all file locations away from localhost.dev to elegantthemes.com, for instance). Being able to understand and work with the WordPress database is essential for you to really grow as a developer (and I’d say user, too). But some of the tools out there are pretty intimidating. Hopefully Better Search and Replace has shown you that there’s no reason whatsoever to be intimidated.
What is your preferred method of database manipulation?
Article featured image by hanss / shutterstock.com
Will this work to find and replace text on pages? I may have several hundred replacements to make…
It should as those texts are saved in the database.
I used the “better” way many times using migratedb pro that is able to do the same job.
Just one important thing when it’s a divi site : don’t forget to clear the style cache of Divi !
This is a good topic.
I’m not sure I would trust a plugin run directly from within WordPress. What if WordPress gets so corrupted that you can’t even run the plugin again ?
For many years, I’ve used this free PHP script provided by interconnect/it and it never failed me. There’s no configuration to do as it automatically loads settings from wp-config.php
https://interconnectit.com/products/search-and-replace-for-wordpress-databases/
As mentioned above:
1. Backup your files and database
2. Do a dry run
3. Do a live run
You missed the point about serialised data! If you use a ‘standard’ search and replace you will in no time at all totally screw up your database! Much of the data stored in the database tables is in serialised form.
E.g. if you replace “test” with “tests” you will then have a corrupt field!
Serialised data means that the data is stored along with a length of the string. So in the first instance “test” is stored as s:4test (e.g. a string of 4 characters – actually not quite this but you get the idea). If you now replace “test” with “tests” it still thinks it’s 4 characters and corrupts.
Be warned! it has screwed several databases in the past and will continue whilst people think a search and replace in WP tables is simple…
That’s why you have things like ‘better search and replace’ and “WP Migrate” tools – they do the legwork of sorting this mess out!
Cheers
I wonder if you changed the username, did it change the username’s permalink as well?
I still use the good old way.. :/
Hi,
I am going to change my site domain extension to another in the near futur and my host support told me that I should consult a developer for changing the addresses in database and I was really confused what to do.
But now that I have found this plugin, I think it is really easy to change addresses in database.
Thank you very much for introducing this plugin and making my task easier.
I have forgotten how many times I have used this. It’s a great plugin! The only criticism I would have is that it has sometimes changed https to httpss so I then have to search and replace httpss and change to https, it’s only a small inconvenience though for what this plugin does when moving to a localhost pc development to moving it to a server using duplicator (another great plugin which for me has had a better success rate than Backupbuddy). It gets a 9.5 out of 10 from me. Thanks for a great article…
I used to use Delicious Brains plugins for everything. That is until I started working with MultiSite builds. I switched over to All In One WP Migration and bought the plugin for handling MultiSite and larger database file loads. With DB I would sometimes run into issues with serialized data not transferring correctly and that is one of the other reasons that I moved away from them. I’m sure they fixed the issue by now, but it only took one or two times of having a site migration or search and replace fail and I moved on very quickly.
I can still do a search and replace and full migration with All In One WP Migration and actually have fewer steps to do it. Plus, anytime I want to make a backup of a site it’s just the click of a button.
Also the WP-CLI search-replace command is an effective alternative to plugins.
https://developer.wordpress.org/cli/commands/search-replace/
Better Search and Replace is a fine plugin. I would like to add three extra steps:
1. Backup
2. Backup
3. Backup
Don’t ever manually edit or use a tool to help you edit a database without having backups! Ideally, you should have:
1. Daily backups
2. You should have some kind of local snapshot (using something like cPanel Backup or Softaculous)
3. You should export a copy of your Database itself just before you try and manipulate your database using phpMyAdmin or Softaculous or any other preferred method.
Why so many backups? Backups can fail to be taken(even though it looks OK) or they can be corrupted, etc. For your protection, three layers of backups should cover most accidents, emergencies and calamities.
That’s true. I have backups upon backups of my stuff. Local, Google Drive, and Dropbox for me.
I’ve used this plugin on several sites that went from http to https. All the images you have already placed in posts will still refer to the http:// address, thus generating an insecure warning on the browser. I just selected the posts table then replaced all http:// with https:// and problem solved. You will manually have to change the site image and favicon urls to complete the fix. This is much better than using a plugin that changes the protocols on the fly but leaves the original post still wrong.
Gary, please, help!
I gave up.
Images on my site are still http://
When I check them in Media library they all have https://
So where the hell I can change them?
Thank you, Gary!
sorted!
Generally speaking, it’s dangerous to “just” replace “http://” with “https://”
What if you got a plugin that’s pulling something from somewhere and the URL is NOT your WordPress URL? If said site then doesn’t have a valid SSL certificate, the plugin is no longer working.
Granted, this is a bit of a bad example as it’s not best practice to pull contents from third party servers, so I would be suspicious of such a plugin, but it’s definitely not unheard of.
So I’d instead advice to change http://yoururl.com to https://yoururl.com
(and while you’re at it, also change http://www.yoururl.com as well as https://www.yoururl.com to https://yoururl.com – gotta catch them all). 🙂
For that particular reason I would better use Velvet Blues plugin, it’s made for it (replace urls by other urls all over your site). You have the option to choose where to replace them too.
There’s also a plugin called “Insecure Content Fixer” that works for that, too. It’s been a life-saver for me. https://wordpress.org/plugins/ssl-insecure-content-fixer/
Yes, but as I mentioned, this does not “fix” the problem but only hides it. If you uninstall the plugin the problem is still there. I prefer to change the post so it’s pointing to the correct url.
Better to do it that way than use the Insecure Content Fixer plugin.