Export / import Wordpress database

Question

Is there a proper way of exporting and importing a big WordPress MySQL database to a new database?

When I tried to do that in the standard way I run into 2 issues:

1. All widgets disappeared and I had to create them from scratch

2. I had issues with special characters such as ' and I had to search and replace the gibberish letters

This is quite important as I need to move to a managed server from shared hosting. All apache, php, MySQL versions will be the same.

Ps: this will have to be SSH commands as phpadmin times out due to the database size.

Answer 1

I normally use mysqldump and sed:

on old server:

mysqldump -u user -p OldWpdatabase > wpdb.sql

sed -i 's/oldurl/newurl/g' wpdb.sql

on new server:

mysql -u user -p NewWpdatabase < wpdb.sql

sed is used to search and replace all the hard coded occurrences of the website if needed.

Answer 2

Import the database on the new server.

When your domain name or URLs change there are additional concerns. The files and database can be moved, however references to the old domain name or location will remain in the database, and that can cause issues with links or theme display.

If you do a search and replace on your entire database to change the URLs, you can cause issues with data serialization, due to the fact that some themes and widgets store values with the length of your URL marked. When this changes, things break. To avoid that serialization issue, you have three options:

1. Use the [Velvet Blues Update URLs][1] or Better Search Replace plugins if you can access your Dashboard.
2. Use WP-CLI's search-replace if your hosting provider (or you) have installed WP-CLI.
3. Use the Search and Replace for WordPress Databases Script to safely change all instances. (If you are a developer, use this option. It is a one step process as opposed to the 15-step procedure below)