I want to present you very useful tool, which not every developer uses: mysqldump - application for database backups.
It's very useful tool when you want to export (backup) your database. Of course you can use well known phpMyAdmin, but when it comes to export very large database it becomes pretty useless (long export time, timeouts, browser freezings, etc...)
So lets use mysqldump. It should be installed with mysql installation, so you don't have to bother finding it, extra downloads etc. By default its in your mysql_installation_directory/bin/ If you have it on your PATH list, simply type in console mysqldump.
Ok, lets look at basic example:
mysqldump -u user -ppassword -h host database_name > database_dump_file.sql
- user - database user
- password - database user password (there's no space between -p and your password!)
- host - host where your database is located (by default it's localhost, so this parameter is optionally, use it when you want to export database from external host)
- database_name - database name you want to backup
- database_dump_file.sql - file with exported data
mysqldump -u astaz3l -psupersecretpassword1234 wp_database > 2013_06_18.sql
After some time (depends on the database size) in 2013_06_18.sql file we will have complete database dump from wp_database. I didn't use -h parameter as it's localhost.
Except of -u, -p, -h we can use more parameters.
- --no-data will export only structures(tables, fields, etc...) without data
- --no-create-info will export only data without structure. Be careful when you will use this parameter. You must be sure, that you have exactly the same structure in database when you want to import such dump.
- --all-databases pretty useful option when you want to backup all databases
More options you can find in mysqldump manual here:
Ok, now we know how to export database. How to import dumped database? Using mysql command.
mysql -u user -ppassword -h host database_name < database_dump_file.sql
It's very similar to making database backup, isn't it? Only difference is tool name and < sing. Parameters are exactly the same as during export. Real life usage:
mysql -u astaz3l -psupersecretpassword1234 wp_database < 2013_06_18.sql
It is possible to restore database with mysqldump file and phpMyAdmin. Simply choose import tab from phpMyAdmin and use exported file.