Correctly Exporting the Database from a WordPress Site
There are a lot of options when it comes to exporting a WordPress database for a website to import on a new hosting provider. Here are the three methods to export I’ve used the most with the best success:
#1 Export directly from phpMyAdmin
I find this method to be less messy than using a plugin that has to be installed and then uninstalled. You simply select your database at then go to the export tab in phpMyAdmin.
The options to select for the export:
- Export Method: Custom – display all possible options
- Table(s): Select All
- Output: Save out to a file
- Character set utf-8
- Compression: gzipped
- Format: SQL
- Format-specific options:
- Check Display comments
- Database system or older MYSQL server to maximize output compatibility with NONE
- Dumb table: structure and data
- Object creation options
- Check Add DROP TABLE / VIEW /PROCEDURE / FUNCTION/ EVENT statement
- Check Add CREATE /PROCEDURE / FUNCTION/ EVENT statement
- Check CREATE TABLE options:
- Check IF NOT EXISTS
- Check AUTO-INCREMENT
- Enclose table and field names with quotes (Protects field and table names formed with special characters or keywords)
- Data dump options:
- Function to use when dumping data: INSERT
- Syntax to use when inserting data:
- both of the above
- Maximal length of created query 50000
- Check Dump binary columns in hexadecimal notation (for example, “abc” becomes 0x616263)
- Check Dump TIMESTAMP columns in UTC (enables TIMESTAMP columns to b dumped and reloaded between servers in different time zones)
The settings for the database export
The settings for the database import
#2 Use the Duplicator plugin
For those who are intimidated by the phpMyAdmin panel or don’t have access to it for some reason, The Duplicator plugin works well.
#3 Export from InfiniteWP
If you have more than 1 or 2 WordPress sites you can save yourself a lot of time by checking out InfiniteWP. Once you’re up and running you can simply export the database from your dashboard.
Go to Protect and select backups and create a new backup. You can choose whether to backup your files and DB or just your database.
This is a wonderfully detailed description of how to best export and import with phpMyAdmin. The part I’m fuzzy on is, before you import, you have to create a new database, right? Are there any choices to be made there that affect the success?
Also, I’m wondering if there is any chance this export-import could fix any problems with a database that might be a bit messed up?
Thanks
If there’s problems in your data, you’ll likely import them with your export. Can’t think of anything that would be fixed by this process. But to answer your first question, yes, you’d need a database to import into. Hosts with a cPanel usually have the option to create a WordPress install with Softaculous and that will setup a database for you. Then you can drop all the tables in it and import your data. Or refer to the codex on WordPress.org for the database setup here: https://codex.wordpress.org/Installing_WordPress#Famous_5-Minute_Installation