Find and Replace in Mysql Database

Running a site on self-hosted WordPress platform needs lot many upgrades. You should frequently update WordPress versions, theme and plugins. Generally these updates do not require any database update. But there are some changes, which need a modification in your MySQL database. In this article we will explain how to search and replace words in WordPress MySQL database.

---

Table of Contents

• Why You Need Database Modifications
  • 3 Ways to Search and Replace Content in WordPress Database
  • 1. Search and Replace Words in MySQL Database with Plugin
  • 2. Search and Replace in SQL Database Tables Using Query
  • 3. Find and Replace Words in SQL Tables
• Search and Replace at Database Level

---

Why You Need Database Modifications

Below are some of the examples; you may need modification in database.

• Moving live WordPress site localhost
• Moving localhost site to live server
• Changing the theme to a new theme
• Upgrading from HTTP to HTTPS to add SSL certificate
• Change the domain name completely
• Disabling plugins using shortcodes inserted throughout the site

In all the above cases you should search and replace relevant words in your whole site. This is an easy task on smaller sites. When your site is very big having hundreds of pages then it is impossible to find all the occurrences one by one from the front end WordPress visual editor. The only way is to find all the words from the database and replace with the required words. Take an example of upgrading SSL certificate on your server. This will change your site's URL from "http://www.yoursite.com" to "http s ://www.yoursite.com". So you need to find all the http occurrences and replace them with https to have a secured site by avoiding mixed content issue.

3 Ways to Search and Replace Content in WordPress Database

• Using a plugin – easy and you don't need to have access to your database.
• Manually editing through phpMyAdmin with SQL query – you should know exactly what you are doing and have access to your database tables.
• Using "Find and Replace" function in SQL tables.

Let us discuss all the methods in detail. Before modifying your database be clear what you are going to do and ensure to take a backup to restore it in an emergency situation. It is also a good idea to test the changes in localhost site or staging site instead of directly doing it on live site.

1. Search and Replace Words in MySQL Database with Plugin

Install and activate "Better Search and Replace" plugin from WordPress admin panel. It will create a menu item under "Settings" tab with the following options.

Search and Replace Plugin Settings

1. Go to the "Search / Replace" tab of the plugin.
2. Enter the word you want to search in "Search for" text box.
3. Enter the word you want to replace with in "Replace with" text box.
4. Choose the database table you want to change the words. You can choose multiple tables and replace the words. Always select individual table and do the changes instead of doing at database level.
5. By default the entered words are case sensitive. You can enable this checkbox to make the search query case insensitive.
6. Ensure to uncheck the option "Replace GUIDs". Global Unique Identifier should not be updated in "wp_posts" table.
7. Ensure to run in a testing mode to see how many changes will be done in which tables.
8. Hit "Run Search/Replace" button to run the query.

Let us take an example of moving your live to local server. In this case you should replace "http://www.yoursite.com" to "http://localhost" in all post content, postmeta and options tables.

• Enter http version in "Search for" text box in point 2.
• Enter https version in "Replace with" text box in point 3.
• Choose the "wp_posts", "wp_postmeta" and "wp_options" tables in point 4. If you have changed your table prefix, you should see the tables with your custom prefix.
• Leave point 5 /6 unchecked.
• Enable dry run in point 7.
• Hot the button to run query.

This will replace all occurrences of live site URL to localhost. If your query times out, decrease the max page size value under the "Settings" tab and try again

2. Search and Replace in SQL Database Tables Using Query

One of the problems in the above method is that the plugin will not show the column names of the table. In the above example, you will not see the columns you are updating except excluding GUIDs. The manual method is very easy and you will exactly know what changes you are doing though a little SQL query knowledge is required.

In the above there are three tables getting changed and the query should be something like below. This is self explanatory that you are changing the old value with the new value on the given table name for the given column.

UPDATE wp_options SET option_value = replace(option_value, 'http://www.yoursitename.com', 'http://localhost') WHERE option_name = 'home' OR option_name = 'siteurl'; UPDATE wp_posts SET post_content = replace(post_content, 'http://www.yoursitename.com', 'http://localhost'); UPDATE wp_postmeta SET meta_value = replace(meta_value,'http://www.yoursitename.com','http://localhost');

Login to your hosting account and navigate to "phpMyAdmin" section. Choose the database of your site and click on the "SQL" tab. You can enter the query and click on the "Go" button to run the query.

Running SQL Query At Database Level

You can only see the "Simulate query" button on localhost database. Most of the live site databases will not have this option.

You can also choose table by table and run the query at individual table level. This is recommended since you can see the columns name in the box. For example, choose "wp_posts" table and paste the relevant query under "SQL" tab. You can see "post_content" is one of the columns under "Columns" box. Choose any of the required columns to insert into the query but ensure the column has the value you want to replace.

Running Query at Table Level

3. Find and Replace Words in SQL Tables

The last option is to use the default "Find and Replace" function. This is an easy and error free way without plugin and SQL query knowledge. But you can replace the words in individual column of a table. Go to phpMyAdmin and to the database you want to update. Select the required table name and go to "Search" tab. Click on the "Find and Replace" button. Enter the word to be found, and the replacement word. Select the column name in which you want to replace the word.

Find and Replace Function in MySQL Table

There are many matching functions for you to match the words, you can choose = or LIKE in general.

Using Matching Functions

Click on the "Go" button to see the preview of the changes. Here you can understand how many lines will be impacted because of the replacement and check the content on each line.

Find and Replace Function in SQL Table

Again click on "Go" button to complete the replacement and then the success message will be displayed like below with the details of the executed query.

Successful Replacement of Words

Search and Replace at Database Level

All the above explained methods work at individual table level and there are no easy way to update the complete database. If you want to replace words at database level then there is a lengthy workaround available for you. Follow the below steps only if you understand, otherwise you may end up in corrupting your database and loosing the data.

• Download the complete database from phpMyAdmin as a SQL dump file.
• You can do this from cPanel with database backup options, if your hosting company offer this feature.
• You can downloaded the file as ZIP or GZIP format to make it compressed and download faster.
• Extract the compressed SQL file and open it with your favorite text editor like Notepad or TextEdit.
• Use normal find and replace function by searching the word with "Ctrl + F" in Windows or "Cmd + F" in Mac. Then replace the found word in bulk.
• Ensure to change the database name to a new in the SQL dump and save your file.
• Compress the SQL text file back into ZIP or GZIP format.
• Go to MySQL Databases section in your cPanel and create a new database with the same name as you have modified in the SQL dump file. Create a new user and assign to the database.
• Go to phpMyAdmin and select the newly created database.
• Import the compressed SQL dump back into the server.
• Now modify the details of database and username in "wp-config.php" file.

You have done now. Check you site is working and now showing error establishing database connection. If you see the error then check the password and database details in "wp-config.php" is correct.

Find and Replace in Mysql Database

Source: https://www.webnots.com/how-to-search-and-replace-words-in-wordpress-mysql-database/#:~:text=Search%20and%20Replace%20Words%20in%20MySQL%20Database%20with%20Plugin,-Install%20and%20activate&text=Go%20to%20the%20%E2%80%9CSearch%20%2F%20Replace,want%20to%20change%20the%20words.

Share this post