The question:
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?
The Solutions:
Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.
Method 1
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.
Method 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
Method 3
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.
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0