May 08 2013
Include Stored Procedures in Mysql Backups
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
[mysqldump]
routines = true
Make sure mysqldump default behaviour is now what you think:
$ mysqldump --print-defaults
mysqldump would have been started with the following arguments:
--routines=true
Check your backup scripts before you run into a disaster recovery!