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
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
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
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.