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?
Answers:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
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