Tuesday, May 11, 2010

Backup and restore mysql databases

To backup(dump) single mysql database to a file:

mysqldump -u foo -p foodb > foodb.sql

where -u is for username, -p is for mysqldump to ask for password, foodb is the name of the database and foodb.sql is the file to dump the database.


To backup(dump) all databases to a file:

mysqldump -u foo -p --all-databases > alldb.sql

where -u is for username, -p is for mysqldump to ask for password, --all-databases is to tell mysqldump to dump all databases available and alldb.sql is the dumpfile name.


To restore back mysql database from dumpfile:


1. From terminal

mysql -u foo -p foodb < foodb.sql

-u and -p are similar to above, foodb is the name of the database and foodb.sql is the name of the dumpfile.


2. From inside mysql console where you have to access mysql console first:

mysql> source foodb.sql
or
mysql> \. foodb.sql

where foodb.sql is the dumpfile