Every time someone signs into WordPress, the information is stored in the WP database.
Sometimes it is desirable to save information in a different location. For that reason, WordPress comes with an import-export feature.
This is especially useful when backing up user data or migrating them to a different website. There are many other situations in which this function is practical.
Backing up data from a WordPress site can be a tedious process. This article will show how to export the WordPress database correctly.
It will help users to avoid common pitfalls and makes sure they do it right every time.
How to Export WordPress Database Properly
There are different ways of exporting a WordPress user database. This article focuses on the best ways to do it.
It is important to do it safely too. These are the methods included in this article:
- Export WordPress database using a plugin
- Export WordPress database using phpMyAdmin
- Export WordPress database using SSH
- Export WordPress database using WP-CLI
Some may want to rely on their hosting company to take care of their backups. There are some reasons why that is not a good idea:
- Most hosting companies’ backup policies and procedures are not optimized for WordPress sites. This may result in compatibility issues.
- The company’s backup schedule may be irregular. It is usually not enough to provide the desired level of data safety in the case of an unexpected disaster.
- Sometimes only a single page needs restoring. This can be tedious if a single user account covers various sites.
- Sometimes backup failures occur, which are not resolved. Not all hosting companies notify their users if that happens.
Exporting with Plugins
There are several plugins available that make exporting a WordPress database easy. So, there is no need to be a developer to do this task.
Here is a small selection of the options:
Import Export WordPress Users
This plugin allows WordPress and WooCommerce users to import and export databases. It works fast and is easy to use.
This plugin comes in very handy when moving a WordPress or a WooCommerce website.
All-in-One WP Migration
This plugin has functionalities for importing and exporting. It can also replace parts of a database with parts of a backup.
One option allows users to export a WordPress database while filtering certain types of information. Think here about unwanted spam comments, post revisions, and the like.
WP Migrate DB
WP Migrate DB exports WordPress databases in the form of a MySQL data dump. It can replace data from within the plugin’s interface.
To enable it to save the information on a computer as an SQL file it handles serialized data.
WP Database Backup
The WP Database Backup plugin works in two steps. It first generates a new database backup.
The second step is to download it to a local computer. It is simple and straightforward in its use.
A nice benefit is the scheduling feature, which allows the user to set up an automatic backup program. This can be monthly, weekly, daily, or even hourly.
BlogVault
Blogvault is a multipurpose plugin. It includes security and maintenance features.
It also makes backups and offers staging solutions for any kind of WordPress site. The backup feature is very complete.
Besides the basic backup option, it has restoration, migration, managing, and other functions.
Exporting with PhpMyAdmin
The phpMyAdmin feature of WordPress is another way of exporting a WP database. PHP is the protocol that WordPress uses to access, store, and change data.
Information like database name, host, password, and username are all stored in the wp-config.php file. When installing WP, it will ask the user to enter all this information manually.
The phpMyAdmin tool is a way to interact with the WordPress database.
To get access to it, it’s necessary to have cPanel. Most hosts use it.
- In cPanel go to Databases
- Click phpMyAdmin
On the left is a panel that shows which sites are currently hosted. Click on one of the websites to get a detailed view of the current database.
Clicking the + sign next to its name expands the database.
At the top of the window are a couple of options, including Reading, Import, and Export. Clicking Export will export the selected database as an SQL file.
There are not too many settings, so it is only a matter of clicking Go to finalize the request. phpMyAdmin will then save the file to the personal computer or laptop.
There is also the option of exporting the database as CSV. This is more practical for exploring the database on a local computer.
Simple spreadsheet software can open a CSV file.
The Import feature of phpMyAdmin allows the user to import one database into another. Clicking this option gives the choice of which files to import.
Backing Up a Database with SSH
The previous method is fine but does not work with large database files. WooCommerce sites are usually very big and with these the phpMyAdmin fails most of the time.
It also happens that WordPress sites have very large databases. Certain plugins create a database of a backup and add it to the existing database, which then grows larger and larger.
For sites with large databases, SSH is a better choice. The downside is that using it requires some technical knowledge and experience.
To start with, it also requires some understanding of SQL too. To attempt using SSH, the user needs to be familiar with the mysqldump command.
By default, this command generates an SQL file, which is a list of available SQL commands. Running all the SQL commands in the file generates an exact copy of the database.
Before starting the process, make sure to have the following at hand:
- The name of the database.
- The database username.
- The database password.
Type the following line in the command prompt to run the mysqldump command:
mysqldump -u my_db_user -p my_db_name > /path/to/save/backup_file.sql
Exporting WP database with WP-CLI
There are other manual methods of exporting a WordPress database. One of them is phpMyAdmin, as discussed above.
Another one is WP-CLI. WP-CLI is an interface for managing a website.
It requires a bit of familiarity because it works only with commands. It is an alternative to WP-Admin.
For every operation in WP-Admin, there is a command that does the same in WP-CLI. It is very practical for installation configuration, plugin updates, and exporting databases.
An advantage is that it also works without an internet browser. It is possible to connect remotely and securely to the website using SSH.
To use it, follow these steps:
- Download WP-CLI.
- Install
- Login to the server via SSH. Use the login details of the cPanel user and domain owner, or as the root user.
The following assumes an SSH connection. The same steps work if using a different connection like Windows SSH GUI, or TTY.
- Find the wp-config.php. It is located in the public_html directory. Use the following command to do this:
cd applications/<your_application_name>/public_html/
In the line above, replace <your_application_name> with the personal database name.
- Export the WordPress database using this short command:
wp db export <file>.sql
In this case, be sure to replace <file> in the line above with the desired name of the resulting SQL file. WP-CLI will save the dump file in the same directory as from which WP-CLI is running.
From this folder, save the exported file to a local computer.
Restoring a WordPress Database from an Exported Database File
To restore a database from a backup, first, go to the phpMyAdmin console of the WordPress site that needs restoring. This process works whether there is an existing database file or not.
Go to the screen that shows the tables of the database, as previously described.
- Select all the tables, or use the Check All button if one is available. If it is a new database, there will be no tables to check.
- Click the drop-down menu that says With Selected.
- Click Drop.
- Then click Yes to confirm the operation. The operation that follows takes some time.
- After completion, go to the Import tab
- Choose File and select the backup file
- To finalize the operation click Go.
Why Export the WordPress Database
WordPress databases collect information and arrange them in the form of variables and tables.
Imagining them as some sort of spreadsheet is thinking in the right direction. The database, in addition to tables, has links between them.
There are several reasons to export the WordPress database. These include:
- Make offsite backups.
Having a backup is a lifesaver in case of a host server crashing. A local backup gives the most security.
- Download specific website data.
These could be posts or pages. A marketing team uses it to extract user information.
- To import the database to a new site.
Sometimes it is useful to use parts of an old website on a new one.
If you enjoyed reading this article on export the WordPress database, you should check out this one on how to delete WordPress themes.
We also wrote about a few related subjects like how to watermark images, how to check the WordPress version, WordPress database plugins, creating a comparison table with WordPress plugins, how to upload an HTML file in WordPress, where are WordPress pages stored and why is WordPress so hard to use.
