How To ExportImport WordPress (MySQL) Database Properly Via Command-Line?

admin管理员组

文章数量:1125047

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?

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?

• database
• mysql
• import
• export
• command-line

Share Improve this question edited Sep 14, 2013 at 14:27 its_me asked Dec 6, 2012 at 10:28 its_meits_me 4,51299 gold badges6666 silver badges114114 bronze badges

Add a comment  | 

3 Answers 3

Sorted by: Reset to default 15 +50

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

mysqldump -u USER -pPASSWORD --quick --extended-insert DBNAME > 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.

本文标签： How To ExportImport WordPress (MySQL) Database Properly Via CommandLine