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.