Backing up stored routines

[Stretchwickster]

MySQL version: 5.0.67-community-log

I've recently added a significant number of stored routines (i.e. stored procedure and stored functions) to my database. I checked the output of my automatic backup PHP script (which executes a call to mysqldump) today and discovered that the stored routines are not dumped.

I read the MySQL 5.0 Reference Manual (

http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html#option_mysqldump_routines)

but adding the "-R" parameter to the mysqldump call of my backup script only outputs the following (after all tables and their data have been dumped):

--
-- Dumping routines for database 'prefix_dbname'
--
DELIMITER ;;
DELIMITER ;

Unfortunately, no stored routines in sight!

Does anyone have any ideas on what I'm doing wrong?

Your advice would be much appreciated.

Clive

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
"To err is human, but to really foul things up you need a computer." (Paul Ehrlich)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
To get the best answers from this forum see: faq102-5096

 

[whosrdaddy]

from the manual:

Dump stored routines (procedures and functions) from the dumped databases. Use of this option requires the SELECT privilege for the mysql.proc table

I assume the user you are using has this privilege?

/Daddy

-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!

 

[Stretchwickster]

Thanks for your reply Daddy. I did read that, but I must confess I wasn't sure how to go about doing that until I spent a bit of time looking it up.

On trying the following SQL:

GRANT SELECT ON MySQL.Proc to user@host;

...I discovered I don't have permissions to do this on my shared web hosting account.

My web host was able to run "mysqldump -R db_name > filename" and it successfully dumped the stored routines. So it appears it is down to permissions.

I found a workaround by setting up a Backup Project in MySQL Administrator. The "Backup Content" section includes a list of tables and stored routines which you can include in the backup.

Clive

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
"To err is human, but to really foul things up you need a computer." (Paul Ehrlich)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
To get the best answers from this forum see: faq102-5096