7

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?

Improve this question

3 Answers 3

Reset to default
14
+50

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.

Improve this answer
5
  • 1
    But how about --add-drop-table and stuff like that mentioned on the codex page? codex.wordpress.org/… - are you sure they aren't necessary?
    – its_me
    Dec 6, 2012 at 11:56
  • 2
    It's not needed, with the options I use the dump files all have this before the create table and subsequent inserts: DROP TABLE IF EXISTS <wp_tablename>; so the tables are always dropped and then created, then data inserted into them
    – Steve Claridge
    Dec 6, 2012 at 21:18
  • 1
    Concurring with Steve, your original commands of mysqldump -u USERNAME -p PASSWORD DATABASE_NAME > filename.sql and mysql -u USERNAME -p PASSWORD DATABASE_NAME < filename.sql would work just fine. The only thing to be really wary of is running string replaces in between export and import if moving between domains.
    – neil_pie
    Dec 10, 2012 at 9:23
  • 1
    As an update regarding the options: this is from the mysqldump documentation: Some mysqldump options are shorthand for groups of other options: Use of --opt is the same as specifying --add-drop-table, --add-locks, --create-options, --disable-keys, --extended-insert, --lock-tables, --quick, and --set-charset. All of the options that --opt stands for also are on by default because --opt is on by default.
    – neil_pie
    Dec 10, 2012 at 9:30
  • also useful for migration is adding --compatible=mysql40
    – MAQ
    Jan 17, 2018 at 19:16
2

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
Improve this answer
0

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.