MySQL Dump all rountines except one

Question

We want to export a large amount of tables and routines and we've do this often. Each database, table and routine should be dumped with one exception: a procedure with a specific name. So I was wondering, is there a way to exclude only procedure from the dump?

For tables there is an "ignore-table" option in MySQL. Is there something similar or a workaround to do this for one procedures in particular?

Answer 1

There is no built-in way to do so with mysqldump. However, all is not lost, you should be able to get away with it using sed.

Let's say your routine is a function, which is called split_token. The following code:

mysqldump --all-databases ... | sed "/DROP FUNCTION IF EXISTS \`split_token\`/,/^DELIMITER ;$/d"

will disregard the lines starting with DROP FUNCTION... and until the DELIMITER statement which ends the function's definition.

The above does not verify:

1. A function of the same name may exist in multiple databases

2. The text may appear within a string -- need better regexp matching (try and match the code starting the line using ^ -- look at the following line:

   /*!50003 DROP FUNCTION IF EXISTS split_token */;

Leaving this up to you to find the best regular expression.