mysqldump stored procedures without locking tables

Question

I'm trying to create a dump of a mysql databases stored procedures and table 'shapes' without locking any rows.

mysqldump -uread_only -p --routines -d --skip-add-locks the_db_name

This doesn't work because I don't have permission to lock the table. I shouldn't need to, as I don't want to copy the data itself. The stored procedures only change every year or so, so I would be unlucky to catch a conflict!

Many thanks.

Answer 1

mysqldump --skip-lock-tables 

face-palm right there at the top of the man page

Answer 2

Please keep in mind that you have to do that because Stored Procedures (SP) live in mysql.proc. That is a MyISAM table. SELECTs against mysql.proc create an implicit lock. Other databases may have SPs that need to be changed (Small possibility but possible nonetheless).

To ensure that no attributes about any SP is changed, an explicit lock is issued against mysql.proc. Doing --skip-lock-tables is OK to do, as long as you know that no other SP is being changed.