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.