While setting up the MySQL replication, I took the mysqldump (without --opt --routines options) 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?
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.
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:
Feb 06, 2013 : how to dump a single stored procedure from a databaseOct 02, 2011 : Can mysqldump dump triggers and procedures?Jul 25, 2011 : Dump only the Stored Procedures in MySQLJul 25, 2011 : DROP PROCEDURE IF EXISTS not included in mysqldumpIf 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
[mysqldump]
routines
triggers
That way, --routines and --triggers are the default.
