Mysql – Running Triggers on Slave with Row-based Events in MySQL >=5.6

mariadbMySQLmysql-5.6

We would like to have a setup where a slave has triggers that are not present on the master for the purpose of updating some summary tables or run a specific udfs.sys

If one uses statement-based replication, they can just create the required triggers on the slave. The slave will run the statements from the binary log, which will cause the triggers to be invoked. With (the suggested) row-based, quote:

If under row-based replication the slave applied the triggers as well
as the row changes caused by them, the changes would in effect be
applied twice on the slave, leading to different data on the master
and the slave.

https://dev.mysql.com/doc/refman/5.6/en/replication-features-triggers.html

Is there a way to run triggers on slave only with row-based replication, similar to what mariaDB have?

Best Answer

Perhaps instead, you require a Stored Procedure on your slave that you can run from a cron job mysql -e "call sp_udfs" to do the processing across the tables to alter that data. Events disable by default in a slave You could do a lot of work with that approach. This way you can control its behaviour much better than triggers. Other benefit, you can run any form of replication you wish, row, statement or mixed

Related Solutions

MySQL Master/Slave Replication – Structure Not Replicating

Using replicate-wild-do-table=% is not the correct way to get complete replication. That would seem sensible, but here's why it isn't:

The replicate-* options are restrictive by their presence. The *-do-* options seem to be telling the server what to "do," but in fact they are telling the server what to only do.
The complete absence of any replicate-* configuration variables means "replicate everything."

In the simplest case, when there are no --replicate-* options, the slave executes all statements that it receives from the master. Otherwise, the result depends on the particular options given. -- http://dev.mysql.com/doc/refman/5.5/en/replication-rules.html

That, I think, is the point you are needing. It holds true for all MySQL 5.x.

Once you enable binary logging, set the server-id values on each machine, synchronize the data sets, CHANGE MASTER TO ..., and then START SLAVE, then you should have a working configuration where all DML and DDL will be replicated and your servers will be identical replicas of each other.

If you started out with identical data sets, everything should behave exactly as you would expect it to.

What MySQL replication does best and simplest is replicate entire data sets among servers without any restrictions. Trying to restrict replication to a subset of the data is a process that should carry a warning label that ends with the phrase "...unless you really know what you're doing."

When you use the --replicate-* options, the document cited above also offers this tip:

it is recommended that you avoid mixing “do” and “ignore” options, or wildcard and nonwildcard options

You are using a mix of these, which adds complexity and may explain why your DDL didn't replicate as you expected it to.

Mysql – Question on having a MySQL 5.1 replicate from a MySQL 5.6 DB

Believe it or not, I once wrote a post about why you should not do that (How can I disable utf8mb4 entirely on MySQL 5.5?). However, in the spirit of my old post and the commentary in it from @ChristopherSchultz, I will go out on a limb and tell you how you can do it, then tell you why you should not.

I once wrote a post about the home position of any empty binary log:

120 in MySQL 5.6
107 in MySQL 5.5
106 in MySQL 5.1
98 in MySQL 4.1/5.0
MySQL master binlog corruption

Over the years in this forum, I learned from someone (I think it was either Aaron Brown or Morgan Tocker) that there is a universal position for all binary logs regardless of the MySQL Version: position 4.

I once put that in an answer (Mar 05, 2013 : MySQL Replication without stopping master). In Step 06 from my answer I wrote this:

CHANGE MASTER TO
MASTER_HOST='10.1.20.30',
MASTER_PORT=3306,
MASTER_USER='repluser',
MASTER_PASSWORD='replpass',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=4;

I also used position 4 in these other posts

Apr 02, 2014 : How do I fix Replication event checksum verification failed while reading from network. Error_code: 1743
Mar 05, 2014 : Risk of changing clocktime on MySQL server host

Rarely do I repeat this info in any other posts for a reason. Personally, I fear that binlog events might be represented differently from version to version in terms of the size (in bytes) of each event. Believe it or not, over the past two weeks I have been upgrading DB Servers from MySQL 5.5. to MySQL 5.6. Due to mixed mode binary logging, there have been rare events when replication breaks and you cannot reset it from binlog files and positions by standard replication techniques. I have had to hose binary logs on Master, copy data, and setup replication from scratch a few times (5 out 400 VMs, but it still happened 5 times). I am very sure that replicating from a new Master to an old Slave would cause many more problems along these lines.

Therefore, I can only say that you can do it theoretically and MySQL may not object, that is, until MySQL Replication encounters a binlog event that is in a format it does not recognize and cannot interpret.

GIVE IT A TRY AT YOUR OWN RISK !!!

UPDATE 2014-11-18 22:32 EST

Just for official reference, this example CHANGE MASTER TO command

CHANGE MASTER TO
  MASTER_HOST='master2.mycompany.com',
  MASTER_USER='replication',
  MASTER_PASSWORD='bigs3cret',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='master2-bin.001',
  MASTER_LOG_POS=4,
  MASTER_CONNECT_RETRY=10;

appears in the MySQL 5.6 Documentation. It's also in the MySQL 4.1 Documentation.

Thus, position 4 has always been known (I have only known a couple of years). Notwithstanding, I trust MySQL Replication from old Master to new Slave (but not on a permanent basis). I do not trust MySQL Replication from new Master to old Slave.

UPDATE 2014-11-19 17:47 EST

Please don't go down the Circular Replication path as it just adds to the risk of lost binlog events due to different versions. You should always replicate one direction to a newer version. Then, just failover to the newer version.