How to change WordPress domain name from SQL (MySQL)

---

---

 

If you are moving a WordPress website to a new domain, you have to do much more than just move directories. While there are many plugins available to do the hard work, some webmaster prefer migrating the WordPress site manually to the new domain

 

When you are migrating website manually, you need to pay special attention to URL strings in MySQL database.  You have to update URL strings in different MySQL databases when migrating a website.  The method we are discussing uses the whole SQL database and you would not be using Import/Export options offered in WordPress.

 

Switching WordPress Database

First, you need to create a new MySQL database on the new server and then perform the task of exporting old MySQL database from the old server to new database on the new server.  You can modify all WordPress site URL’s in the MySQL database tables using PHPMyAdmin. Here are the steps to follow

Old URL – http://oldsite.com

New URL –http://newsite.com

1. Log into your PHPMyAdmin profile
2. Select the database you would like to edit
3. Execute the following SQL queries

#main replace

UPDATE wp_options SET option_value = replace(option_value, ‘http://www.oldsite.com’, ‘http://www.newsite.com’) WHERE option_name = ‘home’ OR option_name = ‘siteurl’;

 

# replace www and non-ssl

UPDATE wp_posts SET guid = replace(guid, ‘http://www.oldsite.com’,’http://www.newsite.com’);

UPDATE wp_posts SET post_content = replace(post_content, ‘http://www.oldsite.com’, ‘http://www.newsite.com’);

UPDATE wp_postmeta SET meta_value = replace(meta_value,’http://www.oldsite.com’,’http://www.newsite.com’);

UPDATE wp_options SET option_value = replace(option_value, ‘https://www.oldsite.com’, ‘http://www.newsite.com’) WHERE option_name = ‘home’ OR option_name = ‘siteurl’;

#replace www & SSL

UPDATE wp_posts SET guid = replace(guid, ‘https://www.oldsite.com’,’https://www.newsite.com’);

UPDATE wp_posts SET post_content = replace(post_content, ‘https://www.oldsite.com’, ‘https://www.newsite.com’);

UPDATE wp_postmeta SET meta_value = replace(meta_value,’https://www.oldsite.com’,’https://www.newsite.com’);

UPDATE wp_options SET option_value = replace(option_value, ‘http://oldsite.com’, ‘https://newsite.com’) WHERE option_name = ‘home’ OR option_name = ‘siteurl’;

#replace non-www

UPDATE wp_posts SET guid = replace(guid, ‘http://oldsite.com’,’http://newsite.com’);

UPDATE wp_posts SET post_content = replace(post_content, ‘http://oldsite.com’, ‘http://newsite.com’);

UPDATE wp_postmeta SET meta_value = replace(meta_value,’http://oldsite.com’,’http://newsite.com’);

UPDATE wp_options SET option_value = replace(option_value, ‘https://oldsite.com’, ‘http://newsite.com’) WHERE option_name = ‘home’ OR option_name = ‘siteurl’;

#replace non-www and SSL

UPDATE wp_posts SET guid = replace(guid, ‘https://oldsite.com’,’https://newsite.com’);

UPDATE wp_posts SET post_content = replace(post_content, ‘https://oldsite.com’, ‘https://newsite.com’);

UPDATE wp_postmeta SET meta_value = replace(meta_value,’https://oldsite.com’,’https://newsite.com’);

 

– Once you have modified the URL’s and table prefix, you can run the SQL query by pressing the Go button at the bottom.

The next step is updating your WordPress config file (wp-config.php) to reflect the above changes. The configuration file should be in your web document root. You need to change the username, password database name, and host values. Here are the steps to follow.

 

Updating your wp-config.php file

1. Using your hosting account editor, open your wp-config.php file.
2. Add two lines to the file which defines the new location of your website.
3. define(‘WP_HOME’,’http://mynewsite.com’);
4. define(‘WP_SITEURL’,’http:// mynewsite.com’);

Locate for a section that looks like this

5. define(‘DB_NAME’, ‘yourdbnamehere’);

/** MySQL database username */

6. define(‘DB_USER’, ”usernamehere’);

/** MySQL database password */

7. define(‘DB_PASSWORD’, ‘passwordhere’);

/** MySQL hostname */

8. define(‘DB_HOST’, ‘localhost’);

Note – enter the database information from your database as follows

• yourdbnamehere= your MySQL database name
• usernamehere is your MySQL Database Name
• yourpasswordhere is your MySQL Password
• localhost is your MySQL Host Name

9. Save modifications to wp-config.php file

 

Fixing the Serialized data issue

After migrating the WordPress site, you may face some issues due to serialized data which arises when the PHP data is encrypted with the URL. Since the URL data changes, the whole data becomes unusable. There are two excellent tools that can handle the problem arising out of serialized data efficiently.  It searches on the old and new database for the old URL  and makes the necessary changes and leaves the serialized intact and usable.

 

Interconnectit

It is a script that you run after you have migrated and imported the old database into new. The script will then make necessary changes in the serialized data.

 

WP Migrate Pro

It is a powerful plugin that needs to be installed on your original site and run from there.  The plugin then searches and replaces on URL string and webroot.  A new database is exported which has the necessary changes done in the serialized data.  Then you can import the new URL hosted database into a new domain.