Backup/Restore mysql databases using mysqldump

Sometimes we want to have a dump of all our databases in mysql and restore it later to somewhere else or in the same server. here’s how…

In your terminal:

Backup database…


mysqldump -uuser -ppassword database_name>database_name.sql


where user is your mysql db user, and password. Note: No spaces should be inserted after -u and -p. Specially in -p for password.

To restore database…


mysql -uuser -ppassword database_name<database_name.sql


while we can do the above command for restoring. We can also do it safely by using the source keyword in mysql.

In terminal, do:


mysql -uuser -ppassword



When in mysql console, execute:


USE database_name;
SOURCE database_name.sql;


Make sure you change to the path of the file database_name.sql where it resides. Otherwise, you will get an error similar to: Failed to open file ‘database_name.sql’, error: 2

Also, we can back-up a table instead of the whole db.. you can do:


mysqldump -uuser -ppassword database_name table_name>database_name.sql



and should be able to restore it too.


Leave a Reply