How to MySQL Dump and Recovery

By | 2011/07/26

MySQL has the ability to dump or backup an entire database to a single recoverable file. I’ll show you how to dump and also recover!

Dump a MySQL database

To dump a mysql database, the command is:

$ mysqldump -u mysqlusername -p databasename > mybackup.sql --lock-tables=false
Enter password: (enter your mysqlusername's mysql password)

For example if there is a database as follows:

Database name: mydatabase
Database user: user1
Database pass: password for this database user

…issue this command:

$ mysqldump -u user1 -p mydatabase > 072611backup.sql
Enter password: (enter user1's mysql password)

You can also dump ALL databases into one blob for backup purposes. This requires the root mysql user.

$ mysqldump -u root -p --all-databases > db.sql --lock-tables=false

Restore a MySQL database

To recover or restore a mysql dump, use the command mysql as follows:

$ mysql -u mysqlusername -p databasename < backup.sql
Enter password: (enter your mysqlusername password)