MySQL database backup and restore with mysqldump

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.

Database backup

Ok, lets look at basic example:

mysqldump -u user -ppassword -h host database_name > database_dump_file.sql

Where:

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

Practice:

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:
http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html

Database restore

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.