Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

While setting up the MySQL replication, I took the mysqldump (without--opt --routinesoptions) unaware of the fact that in mysqldump, --routines it is NOT enabled by default.


  • Is there any way to just insert the routines into the slave without disturbing the replication?

  • Do I have to do the whole thing again by taking a new dump with --routines option?

share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

STEP 01) The simplest thing you can do is this:

MYSQLDUMP_OPTIONS="--routines --all-databases --no-create-info --no-data"
echo "STOP SLAVE;" > StoredProcedures.sql
mysqldump -u... -p... ${MYSQLDUMP_OPTIONS} >> StoredProcedures.sql
echo "START SLAVE;" >> StoredProcedures.sql

STEP 02) Move StoredProcedures.sql over to the Slave

STEP 03) Load the Stored Procedures onto the Slave from the Master

mysql -u... -p... < StoredProcedures.sql

Give it a Try !!!


No need to use --single-transaction since Stored Procedures are stored in mysql.proc, which is a MyISAM table.

I have earlier posts on dumping Stored Procedures:

If you feel more adventurous, try this on the Slave:

service mysql stop
scp MasterIP:/var/lib/mysql/mysql/proc.* /var/lib/mysql/mysql/.
service mysql start

Quick and Dirty, eh ???


You could place mysqldump options within my.cnf


That way, --routines and --triggers are the default.

share|improve this answer
So, does inserting routines changes the master/slave bin log positions? –  chandank Feb 14 '13 at 16:55
+1 for --no-create-info. I've somehow managed to never notice that. I still like --single-transaction no matter what you're doing since it is incompatible with --lock-all-tables and therefore prevents you from unintentionally locking down the server, even if what you're doing doesn't involve transactional tables. –  Michael - sqlbot Feb 14 '13 at 18:00
No, inserting routines has no effect on a Slave since binlog files and positions are interpreted from relay logs. It can be Yes if you drop and recreate a Stored Procedure on a Master. It will just replicate to the Slave, anyway. It can also be Yes if you have binary logging enabled on the Slave, which only affects the Slave's binary logs. –  RolandoMySQLDBA Feb 14 '13 at 18:36
add comment

You may be able to stop the slave, create the routines, and restart the slave without a problem.

MySQL replication relies on the master and the slave servers being identical, but it does not matter how they came to be identical. You could theoretically clone a master server by hand, one table and row at a time -- as long as the slave was identical (structures, data, AUTO_INCREMENT values, etc.) to what appeared on the master at the instant in time represented by the binlog coordinates where you started replication, everything would be fine. Not practical, but technically no different than what we accomplish with mysqldump.

Now, having said that the servers must be identical, replication will not stop or detect a problem if the servers are not identical as long as none of the non-identical elements are referenced in any way by replication events.

If a table is altered, dropped, etc., on the slave, no replication error will occur as long as no inserts, updates, or deletes to that table are done on the master.

The extension of this concept is that if the routines (which means stored procedures and stored functions -- scheduled events and triggers are not "routines") have not been referenced by replication events, their absence may not yet have caused any problems.

If your master is configured with a binlog_format of MIXED or ROW, then it's entirely possible that no replication events may have been sent across that depended on those routines being present, even if your application uses them extensively. This is because whenever the master chooses row-based logging for a replication event (as it is very likely to do most of the time in MIXED mode) the procs and functions on the slave don't execute -- instead, the row changes that occurred on the master as a result of the procs and functions are replicated row-by-row to the tables on the slave. In MIXED mode, this is a decision made by the optimizer on the master when the query is executed.

The same thing is true of triggers. In my environment, it's extremely rare for a trigger to fire on a slave, since the vast majority of things replicate in ROW format (with configuration set to MIXED).

On the other hand, if a query did get logged in STATEMENT mode that needed one of these routines to be present, replication should have stopped with an error when the procedure or function referenced in the statement was not found. As long as you haven't done anything like set global sql_slave_skip_counter = 1 or configured your slave to ignore errors (don't), then you should be able to define the routines now, and START SLAVE;. The failed query should re-execute.

If you have a lot of routines on the master, the closest thing I can think of for getting them would be:

mysqldump --routines --no-data --single-transaction --all-databases --skip-add-drop-table | perl -pe 's/^CREATE\sTABLE\s/CREATE TABLE IF NOT EXISTS /g' > routines.sql

This will generate a dump file with all of your table definitions and routines, but it will not have the DROP TABLE before each CREATE TABLE and the CREATE TABLE statements will be changed to CREATE TABLE IF NOT EXISTS which will skip creating the tables and instead throw warnings that the table was already there... and it will not have row data. I'm not aware of a way to get mysqldump to skip the tables entirely, though that might be a nice feature (if not a bit ironic).

Open this file with a text editor and review it before applying it to your server.

As with any replication setup, never confuse the lack of errors with total consistency. Verify the consistency of all your data on an ongoing basis, especially if anyone has write privileges on the slave (which they shouldn't). A lack of errors only means no replication events have touched anything that resulted in a different outcome than what occurred on the master.

share|improve this answer
Thanks for the detailed explanation. From what I understand is I have to try it out in my test environment first and then on the production. –  chandank Feb 14 '13 at 16:53
I like this answer because it is actually a good failsafe in case any stored procedure performs INSERTs, UPDATEs, and DELETEs. Nice critique of binlog_format as well. +1 !!! –  RolandoMySQLDBA Feb 14 '13 at 18:15
add comment

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.