LogikDevelopment

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;