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

Close Menu