How To Export/Import WordPress (MySQL) Database Properly Via Command-Line?

Normally, a MySQL database can be exported and imported using these simple SSH commands:

Export:

mysqldump -u USERNAME -p DATABASE_NAME > filename.sql

Import:

mysql -u USERNAME -p DATABASE_NAME < filename.sql

But it’s not that simple when it comes to WordPress. From what I see, additional parameters need to mentioned, such as --add-drop-table for instance.

The WordPress Codex does provide some info, but it looks cluttered and not clear enough.

It would be great if someone who uses command-line can share the proper commands to export and import a WordPress database, with some explanation (of any new parameters used).

Also, are there any reasons why it wouldn’t be advisable to use command-line when dealing with database, and instead go with a GUI like phpMyAdmin?

Solutions Collecting From Web of "How To Export/Import WordPress (MySQL) Database Properly Via Command-Line?"

It is that simple for WordPress too. I use the following to back up my WP sites:

mysqldump -u <user> -p<pass> --quick --extended-insert <db-name> > backup.sql

The mysqldump document gives the details on all the parameters.

--extended-insert is quicker when updating a DB from a dump file and makes the dump file smaller.

--quick makes the creation of the dump quicker.

You don’t really need to use either of those, just makes things a bit quicker and writing an insert for each row just makes me feel happier – your original dump syntax would be just fine.

Don’t forget though that there is domain-specific entries in the DB so if you are using this method to backup/restore then you are fine but if you want to move from one.com to two.com then you will need to edit entries in wp_options after you restore your dump.

Having invested the time to learn the basics of the command line, I am using phpMyAdmin less and less now.
However, I do find it easier when I need to browse or edit an entry (such as editing the wp_options entries, as Steve mentions above).
I also used to go to the GUI when I wanted to exclude a particularly large table (such a plugin’s logs) which didn’t need backed up. But I’ve discovered you can just add

--ignore-table=my_db_name.my_table_name my_db_name

For copying a site to a new location.

use –add-drop-table during export, if you’re importing to an empty database.
omit –add-drop-table during export, if you’re importing to merge the database.

Remember to update wp_options after import each time, since path is different.
Also, update the .htaccess file with new path.