Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

up vote 6 down vote accepted
+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.

share|improve this answer
    
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 '12 at 11:56
1  
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 '12 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 '12 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 '12 at 9:30

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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