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.