Exporting databases using WP-CLI

A fast and easy way to export large databases using WP-CLI.

I like exporting with WP-CLI because it’s very easy to use and especially helpful for exporting large databases (that crash when exporting via phpMyAdmin). WP-CLI is always more convenient to use than MYSQL shell…and requires much fewer commands.

1. Log into SSH

I won’t cover how to do this. What I will talk about is whether to login with server “root” user or the user account for the site.

• Using “root” user can be faster if you’re already logged in, and don’t have the site-user credentials handy. However, using “root” user might require an extra step…which is changing ownership for the exported DB and moving it elsewhere.
• Using site user account is faster since the file exports to an easily-accessibly directory (the site home directory) and with proper ownership already set.

This guide assumes you’re using “root” user…which has more details to consider and I’ll leave you to follow only the steps that matter to you.

2. Navigate to directory of the site (whose database you want to export)

cd /home/user123/public_html/

• Obviously, the above example is a CENTOS server. A directory path for a site on Ubuntu server might be more like /var/www/domain.com/html.

3. Export DB using WP-CLI command (to user home directory)

wp db export dbname.sql --allow-root

• The command above is if you’re using “root” user. For non-root users, you can leave out the --allow-root at the end.
• You can choose whatever exported filename you want. It doesn’t have to match the database name.
• You can also specify an exact directory if you prefer. Put an absolute directory path like /somewhere/somewhere/dbname.sql.
• Can also specify a different exported DB name.

4. Navigate to home directory

Use cd if you exported to your home directory. If you specified another directory, then navigate there instead (e.g. /somewhere/somewhere).

From here, you can:

• Change ownership if you want the file accessible by another user. chown -R user456:user456 dbname.sql
• Move the exported DB file elsewhere (like to a certain user directory). mv dbname.sql /path/to/dir