MySQL: Exporting and Importing Stored Procedures

One of my favorite pastimes is eavesdropping on programmers and developers while riding the commuter trains. You'd be amazed at the stuff I hear. Some good ideas are being bandied about but some of them are obviously wrong in thinking or in their research. I became privy to a conversation while over hearing three young professionals discuss PHP and MySQL. It caught my attention because the usual topics are discussed surrounding java or .net. This time a critical decision was made based on errant information. They decided not to use MySQL because it did stored procedures but they would have to be written over and over again. It seems that they did not know that you can in fact export Stored Procedures and Triggers. Having come from SQL Server this was one of the first things I looked for when SPs and Triggers became available in MySQL.

Since version 5 MySQL has stored procedures and triggers. You can backup and restore these function using mysqldump. Mysqldump will by default run all the triggers but leave SPs behind. You can fix this by setting the proper parameters when doing the dump. If you add the --routines command line parameter then you are set:

mysqldump mydatabase -u -p --routines > backup.sql

If you have already backed up everything and are just now reading this. You can grab your SPs with:

mysqldump --routines --no-create-info --no-data --no-create-db --skip-opt mydatabase > spbackup.sql

Run the script like normal to restore to the new db:

mysql mynewdatabase < spbackup.sql

This make me wonder why more CMS systems do not use SPs and Triggers rather than creating copies of these functions in PHP.