May 08 2013

Include Stored Procedures in Mysql Backups

Published by at 6:49 pm under Mysql

Most of us use mysqldump utility to backup Mysql databases. Be aware because mysqldump includes triggers by default but stored procedures and functions are ignored.
Stored procedures and functions are called routines in Mysql all together. Run mysqldump –help and you will see FALSE for routine’s default value.

You can include stored procedures and functions adding the –routines switch when running a backup.

$ mysqldump -u user -p my_database --routines backup.sql

Better, if you don’t want to type in the option – or forget – everytime you run a backup, Mysqldump default’s behaviour can be changed adding a mysqldump section in the my.cnf file, usually in your homedir.

$ cat ~/.my.cnf
routines = true

Make sure mysqldump default behaviour is now what you think:

$ mysqldump --print-defaults 
mysqldump would have been started with the following arguments:

Check your backup scripts before you run into a disaster recovery!

No responses yet

Trackback URI | Comments RSS

Leave a Reply