How to Backup Database in WordPress

Database backups contain critical information but aren’t enough on their own. You should backup your entire website manually, using cloud backup services, or a backup WordPress plugin. Still, having a separate database backup allows for quick troubleshooting after a fatal error, hacking attempt, storage or database corruption, or making wrong website changes. Additionally, doing so can help determine if restoring from a full backup is unnecessary. Even when it is, you won’t mind waiting knowing it’s the only way. With that said, let’s get into how to backup database in WordPress.

1. Check whether your hosting offers the option

Many web hosting providers provide a dedicated cPanel option that allows creating on-demand backups. Some even allow creating partial backups, at which point you can select only Databases to save time. Some examples include Bluehost, HostGator, and SiteGround. However, even if it works, you shouldn’t put all your eggs in one basket. After all, a less prominent hosting provider might go defunct at any moment. Your user account might get suspended or hacked, and the hosting can easily misplace, corrupt, or accidentally delete your data.

2. Backup database in WordPress via phpMyAdmin

Browsed the cPanel and couldn’t find the option described above? No big deal. Here’s how backing up a WordPress database using phpMyAdmin works:

1. Click on the phpMyAdmin option, typically within the “Databases” section.
2. Look for your database on the left-hand side and click on it. The default database name is “wp”.
3. You’ll see a list of tables within the database on the right-hand side.
4. After making sure the tables are selected (highlighted), click on the Export tab in the top right corner.
5. Select Custom under “Export Method”.
6. Leave all tables selected under “Table(s)” or exclude some.
7. Click on Save output to a file under “Output”.
8. Don’t change anything except selecting zipped or gzipped from the drop-down list under “Compression”.
9. Click on Go at the bottom.
10. Wait for the download to begin. Depending on your database size, this can take a while.

3. Create a database backup via Straight SQL/MariaDB

The downside to phpMyAdmin is that it cannot handle large databases. Also, you don’t get confirmation the backup was successful. Both of these issues can be resolved by creating a database backup using Straight SQL or MariaDB like this:

1. Gather necessary information

If you tried the method above, you know the database name under step 2. If not, access your WordPress website via FTP, open the wp-config.php file, and look for the following code:

/** The name of the database for WordPress */
define('DB_NAME', 'my_db_name');
/** MySQL database username */
define('DB_USER', 'my_db_user');
 /** MySQL database password */
define('DB_PASSWORD', 'my_db_password');

2. Configure database file output directory

You’ll have to enter this before entering any of the commands below. It puts you inside the directory whose address you enter. That way, you don’t have to enter the full file path before the output file name and extension. With that said, replace the bolded part with the actual directory:
user@linux:~> cd /save/to/file/path
user@linux:~/save/to/file/path>

2. Run the command you need

Now’s the time to run a command that fits your needs best. We intentionally left out my_db_password from the code, because adding it is bad security practice. Instead, you’ll type your password after you execute the command. For security reasons, you likely won’t see any indicators that you’re typing. With that out of the way, enter one of the following commands:

• Backup all tables in the database to a file name:
  mysqldump -u my_db_user -p my_db_name > db_backup_file.sql
• Backup only certain tables from the database file name
  mysqldump -u my_db_user -p my_db_name table_name table_name2 > db_backup_file.sql
• Backing up a full database hosted on an external server:
  mysqldump -h mysql_hostserver -p port_number -u my_db_user -p my_db_name > db_backup_file.sql
• Add “bzip2” or “gzip” compression:
  • After the backup command is executed (replace bzip2 with gzip and vice versa):
    user@linux:~/save/to/file/path> bzip2 db_backup_file.sql
  • As an option during the backup command:
    mysqldump -u my_db_user -p my_db_name | bzip2 -c > db_backup_file.sql.bz2 / mysqldump -u my_db_user -p my_db_name | gzip -c > db_backup_file.sql.gz

3. Backup database in WordPress via MySQL GUI tools

Not comfortable executing commands in fear of messing up? MySQL tools have a GUI (Graphical User Interface) and can backup larger databases than web-based phpMyAdmin. There are many options available, but we’ll use Oracle’s MySQL Workbench for demonstration. We’re not affiliated; it’s free and beginner-friendly. Here’s what to do:

1. Launch MySQL Workbench and sign in with your website’s hostname and port.
2. Click on Database > Connect Database in the top menu.
3. Enter your database username and password.
4. Click on Data Export in the left sidebar.
5. Select the databases you want to backup under “Object Selection”.
6. Optional. Expand the database name to select specific tables under “Tables to Export”.
7. Select the export target directory under “Export Options”.
8. Tip. Make sure the folder has the necessary write permissions. Solve file/folder permissions error, if needed.
9. Click on Start Export and wait for the process to finish.

4. Make a WordPress database backup using a plugin

Don’t feel like using cPanel, executing commands, or downloading MySQL tools? No problem; you can install a plugin in WordPress as an alternative. We selected WP-DB-Backup because it’s user-friendly, lightweight, and open-source. With that said, follow these steps to backup the database via WordPress plugin:

1. Install and activate WP-DB-Backup (https://wordpress.org/plugins/wp-db-backup/).
2. In the left sidebar of your WordPress Admin Section, click on Tools > Backup.
3. Core database tables are selected automatically. Put a checkmark in front of additional tables you want to backup.
4. Under “Backup Options”, select whether to download or have the file e-mailed to you.
5. Click on Backup Now! to begin the backup procedure.

5. Use a third-party backup service

This is by far the easiest, yet the most expensive method. Third-party backup services create regular or real-time backups for a (usually) monthly fee. Backups are stored in the cloud, and multiple versions are available for download. Starting is simple after you sign up for an account. Depending on the service, you must install a WordPress plugin or provide your hosting’s login credentials. They’ll take it from there.