How to dump MySQL functions and stored procedures


I discovered something interesting about MySQL the other day. Interesting enough to share it with you. 😉
I was in the process of moving a MySQL database from a server to another. In order to do this, I created a dump file from the source database using the command mysqldump and I then restored the database on the destination server using the previously created dump file.

Below is the command I used to create the dump file:

mysqldump -u dbuser -p dbname > dump.sql

And here is the command used to restore the database:

mysql -u dbuser -p dbname2 < dump.sql

The interesting thing is that the functions and stored procedures have not been migrated using these commands… 🙁
To be honest with you, this was the first time I encountered this problem. Which probably means that it is the first time I migrate a database containing functions and/or stored procedures.

Checking the man page of the mysqldump command, I found the following:

--routines, -R
Dump stored routines (procedures and functions) from the dumped databases. Use of this option requires the SELECT privilege for the mysql.proc table. The output generated by using --routines contains CREATE PROCEDURE and CREATE FUNCTION statements to re-create the routines. However, these statements do not include attributes such as the routine creation and modification timestamps. This means that when the routines are reloaded, they will be created with the timestamps equal to the reload time.
If you require routines to be re-created with their original timestamp attributes, do not use --routines. Instead, dump and reload the contents of the mysql.proc table directly, using a MySQL account that has appropriate privileges for the mysql database.
This option was added in MySQL 5.0.13. Before that, stored routines are not dumped. Routine DEFINER values are not dumped until MySQL 5.0.20. This means that before 5.0.20, when routines are reloaded, they will be created with the definer set to the reloading user. If you require routines to be re-created with their original definer,
dump and load the contents of the mysql.proc table directly as described earlier.

Alright, that looks straight forward! We simply need to add the parameter --routines to the mysqldump command in order to include the functions and stored procedures to our dump file.
For example:

mysqldump -u dbuser -p dbname --routines > dump.sql

We now have our dump file containing the functions and stored procedures.
I thought I was done at this stage. But I got the following error message while restoring the database on the destination server:

ERROR 1419 (HY000) at line 1140: You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

The error message is pretty clear. Binary logging needs to be enabled in order to restore the functions. There are two ways for doing this:

  • Execute the following command into the MySQL console:
    	SET GLOBAL log_bin_trust_function_creators = 1;
    	
  • Add the line log_bin_trust_function_creators = 1 to the mysql.ini configuration file

N.B. Execute the following commands to display the list of stored procedures and functions:

SHOW PROCEDURE STATUS;
SHOW FUNCTION STATUS;

, , , , , ,


  1. #1 by npn on 15 Aug 2013 - 16:11

    Thanks! This works – not sure why mysqldump doesn’t export procedures by default.

  2. #2 by wt on 28 Jan 2014 - 09:56

    “The error message is pretty clear. Binary logging needs to be enabled in order to restore the functions”

    That is not true!!
    It is *because* binary logging was enabled on the second server (probably replication was configured), the server had trouble with importing procedures/functions/triggers. This is because when replication is enabled, you must have SUPER privilege to create them (or have log_bin_trust_function_creators option set, as mentioned).

    Please have a look at: http://dev.mysql.com/doc/refman/5.0/en/stored-programs-logging.html

  3. #3 by Roberto on 03 Feb 2014 - 17:38

    If you dont need recover through binary log and dont use master/slave (replication), so set log_bin_trust_function_creators = 1. SET too false to SGDB dont do the bin log (remember, bin log is good to recover in case of disaster)
    This will affect all databases. Other option is to edit your functions and stored procedures signing whith ‘NO SQL’. this will affect bin log only of your database.

    If you will need to recover by binary log, or think in use master/slave, so you need to do revision on your MySQL strategy and application, because if you have a function that is signed with ‘Contains SQL’ so all operations inside go to bin log and are replicated. Rethink your application if you really need de replication, somthings is impossible to do, ex.: if your function contains insert into … UUID().., the UUID is impossible to do replication, so that function you need to sign as ‘no sql’ and to think how to do replication other way, as a normal insert p exemple. insert into …. ‘ab45cf…’ …

    see too http://dev.mysql.com/doc/refman/5.5/en/stored-programs-logging.html

  4. #4 by rss245 on 20 Feb 2014 - 14:56

    Where did this variable come from. Odd I could not find mention of it in MySQL 5.6 manual but it works like a charm.

  5. #5 by rss245 on 20 Feb 2014 - 15:00

    Re: #1 Not true mysqldump does dump procedures and functions but it requires the -R or –routines option as of 5.6 anyway. I believe it does as well for earlier versions as well but I would need to check on that. You can always save your code in sql files as you create the procedures and functions as well.

  6. #6 by jagan on 12 Dec 2017 - 19:18

    thanks am also facing this issue you cleared me very clear thanks your are saviour

(will not be published)