Backup/Restore a MySQL database

From Cosmin's Wiki

Home > MySQL > Backup/Restore a MySQL database

Backup

Backup of a MySQL database is done with the mysqldump utility. For most of the situation, the simple command

mysqldump --opt <database> > <output-file-name>

will do, provided you have sufficient rights. If not, you should pass authentication credentials to mysqldump like this:

mysqldump -u<user> -p<password> --opt <database> > <output-file-name>

MySQL 5 has introduced some new interesting features, like stored procedures and triggers. I will show in this small post how we can backup and restore these components using mysqldump

mysqldump will backup by default all the triggers but NOT the stored procedures/functions. There are 2 mysqldump parameters that control this behavior:

• routines - by default set to FALSE

• triggers - by default set to TRUE

Thus, should you want to include in your backup also the triggers and stored procedures, you only need to add the --routines flag to the above backup command like this:

mysqldump -u<user> -p<password> --opt --routines <database> > <output-file-name>

Please be advised that --opt is an aggregator of several options (--add-drop-table, --add-locks, --create-options, --quick, --extended-insert, --lock-tables, --set-charset, --disable-keys). The --opt parameter is enabled by default and can be disabled with --skip-opt. For more detailed information about each and every parameter please consult the mysqldump man pages.

Restore

There are several ways of restoring a database, and I will write here about 2 of them. Suppose you have a dump file (/home/cosmin/mydb.sql)you want to restore on database mydb. One way to do this is simply sending this file as an input file to the mysql command:

mysql -u<user> -p<password> <database> < /home/cosmin/mydb.sql

Another way, which I use when I want to have some more control over what's happening, is to log in on the database

mysql -u<user> -p<password>

and then:

mysql>use mydb;
mysql>source /home/cosmin/mydb.sql