How to Export WordPress Database Safely?

May 26, 2021

by

How to Export WordPress Database Safely?

May 26, 2021

by

Exporting WordPress database can be a tedious process. However, there may be times when want to export the database to keep it safe offsite for backup.

In this article, we’ll guide you on how to export WordPress database safely and reliably without worrying about the caveats involved. So, let’s get started!

TL;DR: Export your WordPress database safely and efficiently with BlogVault. It is simple to accomplish without using the command line, you can restore it easily whenever necessary, and it will work every time.

How to export WordPress database properly?

Before you move further, we recommend that you take a backup of your website to avoid any mishaps. Further, backups will play an important role in how we export the database as we will see in this section.

There are many ways using which you can export a database of the WordPress site. In this article, we are going to discuss the best methods to export the database of your WordPress site safely.

How to export WordPress database using a plugin?

You do not need to be a developer to export the database of your site. There are various plugins available in the market that allow you to export WP database easily. In this article, we are going to discuss the following four plugins:

1. BlogVault

A lot of you must be exporting your database simply to store it offsite where it remains safe in case your website or server crashes. This is why we strongly recommend using BlogVault because it eliminates the need for an external server. Even migration is a breeze, so you wouldn’t need to spend your time exporting and importing the database.

However, if you need to perform WordPress database export for other purposes, BlogVault allows you to do so simply by following along with the steps given below:

Step 1: Create an account on BlogVault, and install the plugin on your website. Once you have installed the plugin, go to its dashboard. After the installation, the plugin takes a backup of your website including a backup of your database, and notifies you via email.

Step 2: You’ll find a list of your websites under ‘Sites’. Select the website whose database you want to export. The backup panel shows you how many backups have been taken of your website, and which one is the latest. Click on the panel to view backups and options to download.

Step 3: On the new page that opens, click on the ‘Download Backup’ icon. A window will pop up. Select the backup version and expand the ‘Selective Download’ drop down.

Selecting WordPress database to export

Step 6: To export only the database of your WordPress site, select ‘Download Tables’ only. If you want to select only a few tables to download, you can even do that. Click on ‘All Tables’ and uncheck the ‘Select all’ box. Now select all the tables that you want to export and click on ‘Continue’.

Step 7: Once you confirm your selection, it will take a few minutes to export WordPress database and create a link. Then, click on the ‘Download Link’ button and save your exported WordPress database on your computer.

Benefits of using BlogVault plugin:

  • It has your website backup saved on an external server. That means, in case you lose your site’s database due to any reason, you will still be able to save your website.
  • You have the option to select or deselect tables in an easy way, without worrying about messing up the database or deleting something by mistake.

BlogVault offers up-to-date automatic backups, one-click restores, WordPress migration, staging facility, super easy updates, and you get to download your WordPress database or more easily, anytime you want.

2. All-in-One WP Migration

This plugin allows you to perform the export, import, and other functions such as take a backup or replace text in the database with another text. While exporting WordPress database, you can choose not to export spam comments, post revisions, etc.

  • Once you have installed and activated the All-in-One WP Migration plugin on your WordPress website.
  • Now, on your WordPress site dashboard, you will find All-in-One WP Migration in the left menu.
  • If you click on it, you will get a sub-menu with three options namely, Export, Import, and Backups.
  • Click on Export. Now, you will see a few options on the Export settings page.
  • Navigate to the Advanced option and click on it. Here you can deselect everything other than the database.
  • Now click on the ‘Export to’ button.

Here you can choose to save your database on file (i.e. save it on your computer) or choose to upload it on cloud servers such as Dropbox, Google Drive, Amazon S3, Onedrive, Azure Storage, etc.

Related resources:

3. WP Database Backup

This WordPress export database plugin allows you to export your database by first creating a new database backup and then downloading it to your computer. It is easy to use and comes with a scheduler to backup the database on a frequency of hourly, daily, weekly, or monthly.

  • After you have installed the WP Database Backup plugin using the method we explained earlier in this section, activate it.
  • Once the activation is complete, go to Tools on the left menu of your website account dashboard. Here you will find WP-DB Backup. Click on it to access the plugin settings.
  • First, you need to create a backup of your website, to export the database. Click on the ‘Create New Database Backup’ button.
  • This will create a database backup and will automatically add it. Now you can see settings to Download, Remove or Restore this backup. For getting your WP export database, all you have to do now is click on the Download button.
  • A pop-up window will open prompting you to save it. Click on ‘Save File’ and click OK. Now you can find your WordPress export database in the Downloads folder of your computer.

You can use your exported database while moving the WordPress site to a new domain.

4. Import Export WordPress Users and WooCommerce customers

If you have a WooCommerce website, exporting a database can be a tricky process. Not all plugins allow selective downloads and so you may have to deal with unnecessary junk.

If downloading users is all you are looking for, then this plugin is the one to go for. You can also take a look at our list of top WooCommerce plugins to improve your site.

  1. Install and activate the plugin for your WooCommerce site.

2. Now navigate to your WordPress dashboard and on the left menu mouse-over to Users. A sub-menu will appear. Click on the User Import Export option.

3. You will see a page with the option to select what you want to export. Select User/Customer’ from the drop-down menu.

4. Next, you need to select an export method. Three options appear:

  • Quick export: Exports all the basic fields.
  • Pre-saved template: This option exports data as per the specifications(filters, selective column, mapping, etc) from the previously saved file.
  • Advanced export: This option exports data after a detailed process of data filtering, column selection, and other advanced settings. You can also save this file for future use.

5. Once you have selected the method, you can choose filters, if required. The plugin also provides you an option to select the fields that you may want to download from the user database. These contain information such as user-id, name, status, role, etc.

6. Once you select all the parameters you want, go ahead to select the batch size for each iteration.

You can now save these settings as a template and continue to download WordPress database to a file on your computer.

Pros of using plugin method to export WordPress database

  • It is easy to use and it takes just a few simple steps to export WordPress database using plugins.
  • You don’t have to worry about messing up a command line (code) or interacting directly with the database, so human-error is significantly reduced.
  • A few plugins may allow you to set your export settings as a template for future use.

Cons of using plugin method to export WordPress database

  • Finding the right plugin can be challenging especially if you have specific requirements in mind.
  • If you have a WooCommerce website, exporting a database can be tricky as many plugins may not allow you to select the components of the database that you want to export. So, you may have to deal with unnecessary data.

Exporting WordPress database manually using phpMyAdmin

Before you export your website database, make sure you take a fresh backup of your WordPress website so that you have the option to revert to the previous version immediately in case anything goes wrong.

WordPress databases can be exported through phpMyAdmin. WordPress uses PHP to access, change and store data. During installation, WordPress asks you to provide host, username, password, and database name, which is stored in wp-config.php.

In this section, we will discuss how you can use GoDaddy and BlueHost to export the database of your website cPanel. 

Export WP database using phpMyAdmin from BlueHost cPanel

First, select the database you want to export by clicking on the database name in the left sidebar. 

choose wp database in PhpMyAdmin panel

Then click on ‘Export’ from the top menu. Now, you will get two options – Quick & Custom.

To export the entire database, select Quick. If you want to select tables to export only specific data, then select Custom.

In Format, make sure SQL is selecting. In case you chose Custom export, you can select the tables that you want to export and then continue.

Depending on the size of your website, it’ll take a few minutes to export the database.

Export WP database using phpMyAdmin from GoDaddy cPanel

GoDaddy allows users to access the database through phpMyAdmin using the following method:

  1. On your GoDaddy account product page, navigate to My Products > Manage All.

  

Select Settings

2. Next to the website you want to export data from, click on the three-dots icon to go to Settings.

3. Now, under ‘Production site’, click on ‘Show more’ and click on ‘View’ in the Database details section.

Provide login details in database details panel to access website database

4. A new page will open up with the complete Database details shown. Make a note of the username and password. Now click on the URL provided on the top, under phpMyAdmin URL and use the login details to access the  website database.

5. Once you’ve logged into the phpMyAdmin, look for your database name in the left-hand menu. In case you don’t know your WordPress DB name, you can search for wp-config.php and right-click on it to view it. Then, look for the DB_NAME value.

Choose wp database to export in PhPMyAdmin panel

6. Once you locate it in the left menu, click on it. Now, on the right side, you will see all the data saved in your WordPress database. You will also find an ‘Export’ button there. Simply click on it.

7. You have two options to select from: Quick and Custom. Select the quick option and click ‘Go’. You can now save the database on your computer.

Export WordPress database using PhPMyadmin in GoDaddy

8. If you want to perform a custom export, you can click on Custom. You will be prompted to select some options according to your preference. In the Format section, choose SQL. 

9. Once you have set your preferences, go to the bottom of the screen and download WordPress database.

That’s it!

How to import WordPress databases using phpMyAdmin

Here’s a quick step by step procedure to easily import databases via phpMyAdmin. 

Login to phpMyAdmin

Step 1: Log in to phpMyAdmin and click on the database name in the left-hand column. On the right hand column, select the ‘Check all’ option and select ‘Drop’ from the drop down menu. Now click on ‘Go’.

Select drop button

Step 2: Next, in the pop that appears, confirm by clicking on ‘Drop’. You may have to repeat these steps until you see the ‘No tables found in database’ notification.

Click on go to successfully import WordPress database

Step 3: Now click on ‘Import’ > ‘Choose File’ and navigate to the database saved on your computer. Click ‘Go’.

That’s it! Once the database is successfully imported, you will see the tables under the database name. 

Pros of using phpMyAdmin method

  • The Quick export option allows you to export the whole database.
  • You can easily select the tables that you want to export with the Custom feature.

Cons of using phpMyAdmin method

  • You will have to deal with the database directly from the cPanel of your host, which may be intimidating for a few.
  • Accessing host cPanel and phpMyAdmin may be a time-consuming task, and can also be complicated for those who are doing this for the first time.

What can go wrong while importing a WordPress database?

  1. Import file is too large: The standard size for importing with phpMyAdmin is 50 MB. If your database is larger (>50 MB) you may get a time out error which will stop the importing.
  2. Can’t create database/ database exist error: If you do not have CREATE DATABASE permissions, the import may fail. To fix this, you will have to open the SQL files with such a command and delete all the lines starting with it.
  3. Access denied: If you do not have proper permissions you may see the error that says: Access denied; you need the SUPER privilege for this operation. You can remove the DEFINER mentioned in the .sql file on the line where the error occurs.




Exporting WP database using WP-CLI

There are manual methods to export website databases other than using phpMyAdmin, such as using SQL commands with WP-CLI. 

WP-CLI is a command-line interface from which you can administrate your website. As it is meant to be an alternative to wp-admin, all functionality on the dashboard has a corresponding command on WP-CLI.

Using it, you can easily configure installations, update plugins, export databases, etc., without even using a web browser. You can connect to your website remotely via SSH so that you can run commands securely.

Note: Before you go ahead with this method, make sure that you have backed up your website and can easily restore it. Many backup plugins may take a backup of your website but not offer an option to easily restore it later. 

First, take a backup using the BlogVault plugin. Once your backup is secure, go on, download WP-CLI. Install it and then follow the steps below.

Step 1: Connecting to server via SSH

Firstly, to use WP-CLI, you need to connect to your server remotely via SSH. If you are an administrator with root access, you can use a SSH Client, such as PuTTY, to log in to your server. cPanel users can log in with built-in SSH Terminal.

Step 2: Locate wp-config.php

Secondly, you need to connect to the root directory, i.e. wp-config.php for WP-CLI to work. The wp-config.php file is located in the public_html directory, which can be accessed with the following command.

cd applications/<your_application_name>/public_html/

Make sure to replace <your_application_name> with your database name.

Step 3: Export database

To export the database, execute the command given below:

wp db export <file>.sql

Make sure to replace <file> with the name of the SQL file you want to export.

The downloaded file will be located in the same directory where you are running WP-CLI. In this example, it is public.html. From here, you can save it on your local computer.

Pros of using WP-CLI

  • WP-CLI enables you to export databases without even hopping over to phpMyAdmin.
  • It saves time as shorthand commands can be used.

Cons of using WP-CLI

  • You should be comfortable with code to use it.
  • Not all WordPress hosting accounts support SSH access. Check with your hosting provider for more information.

What is a WordPress Database?

A WordPress database is the repository of all the necessary information of your WordPress website. The data is stored in the form of tables and includes your pages, posts, comments, user details, and much more besides. When your site loads, it fetches data from the database. 

Needless to say, your database is a very important component of your website. Arguably, the most important. 

What does the WordPress database contain?

A WordPress database is where all the information that you have on your website is stored. We mean literally everything. 

The database stores the following data in the form of tables:

  • Posts
  • Pages
  • Categories
  • Tags
  • User data
  • Comments
  • Custom fields
  • Plugin-related data
  • Theme customisations
  • Settings

E-commerce stores are a special beast altogether. 

Apart from the usual data stored in all WordPress website databases, a WooCommerce site has a lot of other information pertaining to orders and customers, such as order details, payment information, purchase history, product information, etc. This makes a WooCommerce database much more complex as well as valuable.

Why export database from WordPress?

There can be various reasons to export the database of your site, some of which are as follows:

  1. To create an offsite backup: You may want to create an offsite backup to ensure that if your host server crashes, you still have a backup safe to be restored easily. Moreover, this will save your website if your website’s security is compromised, or in any other situation that could make your website go kaput. In case you are confused about what should you be backing up, we recommend backing up your entire website, not just the database. 
  2. You need website data: You may want to export certain types of data from your website such as all posts or pages, or perhaps your marketing team requires the user information. You may want to be able to select only the information you want so that you don’t have to deal with unnecessary data. In this case, exporting specific tables from the database is the best way to get all the information you need.
  3. To import it to a new site: You may want to export your database to import it into a new version of the same site you are building. If you are migrating your website to a new url or host then exporting your database from your current site and importing it to the new site may be one way to go about it.

What can go wrong with a WordPress database export?

While you have all the information at hand on how to export database and the best way to do it, we find it necessary that you understand the dangers of exporting WordPress site data. A lot can go wrong and put your website at risk, such as:

  • The full database may not get exported properly or successfully and you may end up with missing information. The exported file may be damaged or incomplete due to some overlooked issues which may occur while exporting.
  • Data once lost cannot be restored easily unless you have a backup. Therefore, it is risky to interact with the database directly through code or phpMyAdmin, especially if you are a novice at this. 
  • Human error is bound to happen anywhere. In case any of your tables get deleted, you may end up scratching your head without any clue as to why some things are not working as they should.
  • In case of migration to another site or host, the import may not work as expected. If you are already struggling with migration issues, this complete guide to migrating WordPress from localhost to online server may help you resolve them.
  • Other than this, privacy issues can also arise. The exported database is easily transferable and therefore, can get into the wrong hands. Emailing the documents may put the database at risk of being exposed to hackers.

Best practices to export WordPress database

Manual methods for WP database export are not easy. One wrong setting and you may not get the data you want. Moreover, manual methods require you to access the database yourself.

That means, one wrong move can lead to deletion or alteration of your database tables. This will affect your content and customisations, which may even break your WordPress website. There are two ways to avoid this:

  1. Backup your files before you use the manual method to export tables:

It is a good practice to keep your backups up to date, especially when you are going to access your database. Accidents happen all the time, and you don’t want one to destroy everything your website has, from customisations to content. Backing up your website is easy using a plugin such as BlogVault. More so because it offers one-click restore in case anything happens. 

Consider backing up your website a necessary step in case you are logging in to phpMyAdmin for the first time. phpMyAdmin offers direct access to all of your website data. Messing it up could land you in serious trouble.

2. Use a plugin to export WordPress database :

Better yet, we would suggest going with the plugin method for exporting WordPress tables. It is easier, takes just a few clicks and your database will be downloaded securely without you worrying about messing it up.

Conclusion

The plugin method is the easiest and secure method to export the WP database. BlogVault plugin offers tons of services to secure your website and database such as regular incremental backups of your field and tables, history logs to see changes made to the tables, and quick restore in case the database is down.

Thus, it eliminates the need of undertaking a long and error-prone process of exporting databases. However, if you need to download certain data for any purpose, the plugin allows you to export the data that you need without the worry of handling the extra junk that is exported with other plugins.

FAQs

Do you need to be a developer to export WordPress database?

No. You do not need to be a developer to export the database of your site. However, we would recommend that if you are not a developer, you shouldn’t use the manual methods as they put you in direct access to the database. Any error can lead to a massive change on your website. Try using a plugin such as BlogVault to backup your website and export your website database.

Why is database critical for your website?

The database contains all of the information related to your posts, pages, comments, etc. This is stored on the hosting server called the database host. When a user visits your WordPress website, the database host receives a request from the user’s browser.

The host sends the data to the browser. If the connection is broken and the database becomes unavailable your website will appear broken. Therefore, it is important to ensure that nothing goes wrong when it comes to your WordPress website database.

How to export WordPress database without phpMyAdmin?

As you see, some hosting providers offer alternate ways to download the database for your WordPress website. In case you are not comfortable using the manual methods, you can always go back and check out the plugin method. We have explained how to export tables using BlogVault.

How to export WP database from cPanel?

All you have to do is access your database via cPanel. To do so, go to your website cPanel and click on the the phpMyAdmin button.

Once you have entered phpMyAdmin, all you have to do is follow the steps given in our section on how to export WordPress tables using phpMyAdmin. That’s it! You can easily download all the tables or selected ones on your computer or an external drive.

How to export user data for WooCommerce websites?

Install and activate the BlogVault plugin. Now navigate to the left sidebar on your WordPress dashboard to access the Blogvault dashboard. Select your site from the Sites tab. Now, under the Backups tab, click on the green text which shows the number of Backups.

On the new page that opens up, click on Download. Now on the poop-up, select the correct backup version, and uncheck the Download Files box.

Next, click on the All Tables and uncheck the Select All box. Here, you can select data related to users to download them. Once you have selected all the necessary tables, click confirm and continue.