Tag Archives: replication



This is very interesting topic from sysops’ point of view. Honestly – It’s not that common to find a well organised HA MySQL environment (I’m talking here about good failover solution in master – slave scenario).

I won’t write here details for the most known solutions / scenarios – I think it’s enough to point those that one can check if it fits his needs.

  1. Master – master replication. In this scenario we don’t need automated failover – when we have some outage of one of those masters then it’s no problem as second one is still working. It’s good to use some LB layer between application and MySQL hosts that makes the app always hit the working server.
  2. MySQL cluster. This is very comfortable scenario as MySQL cluster takes care of almost everything – this is “no single point of failure” solution. You can read about those here http://www.mysql.com/products/cluster/ and here http://dev.mysql.com/downloads/cluster/ –
  3. MySQL Galera cluster – for synchronous, active – active multi – master topology: http://www.codership.com/content/using-galera-cluster
  4. Failover – for now I know 2 solutions worth mentioning:
    1. Matsunobu Yoshinori created mysql-master-ha (working on MySQL servers 5.0, 5.1 and later) – this is very good tool, that handles automatic, manual and semi – manual failovers (even when slaves are in different relaylog positions). In addition You can use it when MySQL master host migration is needed. I recommend watching those slides: http://www.slideshare.net/matsunobu/automated-master-failover
    2. MySQL utilities – this is quite simple daemon which primary job is to perform automated failover when needed. You can read about it here: http://www.clusterdb.com/mysql/mysql-utilities-webinar-qa-replay-now-available/

MySQL statement based replication with triggers, events, procedures, functions and variables

Before starting statement based replication in MySQL database We have to be aware of some specific behaviors for this environment. This kind of replication (statement based) writes each query that modifies data to the Binary Log in order to replicate them on the slave or to use as a point-in-time recovery (PITR). Because of this kind of query logging We should be aware how MySQL replication engine behaves with some special queries like triggers, functions, procedures or events.


Function calls are logged directly to Binary Log, so If You forget to create on slave any function that is created on master – You will break your replication and probably You’ll see error like below:

When promoting slave to master no additional steps according to functions are required – everything is needed is having functions defined in both: master and slave.


Procedure calls are not replicated as in functions – this is important to know. Only the queries inside the procedures get logged to the Binary Log, so You don’t have to create procedures on slaves.

In order to promote slave to master you should have procedures created on slave – so it is wise to have all the procedures created on both – master and slaves.


Events created on master server get replicated to the slave with the DISABLE ON SLAVE option – that’s why those events are not reexecuted on every slave in our MySQL architecture and we have no duplicated and corrupted data. MySQL logs only queries from inside the event so only those queries are replicated via Binary Log.

In order to promote slave to master according to events we have to do some more job. I’ve created a simple event below (We create it on the master):

Now it’s replicated on slave via Binary Log – below I’ve placed replication entry for that event from Binary Log:

And how it looks like after replication on the slave:

So now with this knowledge a little procedure to promote slave to master using events:

  • Disabling event manager on slave with SET GLOBAL event_scheduler = OFF;
  • Enabling all the events with ALTER EVENT event_name ENABLE – We have to do this for each event, so writing a little script is very helpful here.
  • Enabling event manager with SET GLOBAL event_scheduler = ON;

In order to demote back the master to slave You should follow the previous procedure with a little change on ALTER EVENT – here You just need to DISABLE all the events (not ENABLE).


In order to have triggers running properly on master and slaves You have to define them in both – master and slave servers. MySQL statement based replication replicates only the original query to the Binary Log – not the subsequent triggered statements.

When promoting slave to master no additional steps according to triggers are required – everything is needed is having triggers defined in both: master and slave.

Mixed triggers / procedures / functions calls

Let’s imagine that We have a trigger, that triggers a procedure which uses a function call. How will this behave in statement based replication?

  1. We should have trigger defined on both: master and slave
  2. We don’t have to have procedure defined on the slave – only on master is enough
  3. We should have function defined on both: master and slave

Despite of all – my advice is to keep function, triggers, procedures and events defined on all the servers (masters and slaves) – just to be sure, that We can always promote slave to master without any issues.

And one more thing before finishing this post. If You plan to start replication with just copying FRM, MYI, MYD and InnoDB files You should also dump any functions / triggers and stored procedures on master (or slave) and then import those on the new slave. You can do it (for every database) with:

And recreate those on the new box:

MySQL replication over SSH tunnel

Sometimes it is a good decision to replicate between datacenters. It is not for a backup purposes – as replication cannot be used for backups (maybe under some circumstances, but let’s say that for now We’re not thinking about replication as backup solution) – for now We’re using it to just have up2date data in some other datacenter.

General idea to set up this replication is to make a SSH tunnel between those two datacenters and then start transferring data using this secure transport layer. I will call “replication server” – the server that will be slave in our destination and “the master server” will be our master.

I won’t write here how to set up a replication from the scratch. Let’s say that for now there are at least two ways to do it without stopping mysql master (using another slave to take data snapshot or using Percona Xtrabackup).

Firstly we have to start SSH tunnel. We have to ensure, that this tunnel will keep alive trough any connection problems and will not be killed due to an idle (how come when there is replication stream over this?).

Let’s start with ensuring that our tunnell will keep alive. In SSH client configuration (default: /etc/ssh/ssh_config) We should add the following:

With above server maintaining the tunnel will send some keep-alive request every 300 seconds to the master (destination) server.

Now We have to open MySQL port on the master (destination) server on WAN interface. This is not secure unless We filter source IP address trying to connect to this port (let’s allow only our slave’s server IP addr. to use this port). For maximum security We can use TCP Wrappers on the master (destination) MySQL server, but this will put some overhead to the server functionality as TCP Wrappers always use some DNS resolution. In my opinion filtering MySQL port based on source IP address is enough.

Now We can start our tunnel:

Let’s explain:

  • -p 2345 – port We are using to connect over SSH (default 22, but should be changed to something else for standard security reasons)
  • -n – SSH will go to background just before command execution. Make sure, that You have SSH keys exported to the master (destination) server from the slave server / user and You will not have to enter any passwords during creating the tunnel
  • -L – turns on port forwarding – this is the core of creating SSH tunnel
  • -N – “do not execute a remote command” – just because We are just forwarding ports :)

Now We can test this tunnel. Let’s try to connect to MySQL master from the slave server:

We should be able to connect to the master server with above command. And If We really did – then We can use this connection to start the replication.

This is very simple method that should be wrapped with some monitoring, scripts that will create SSH tunnels automatically when the original tunnel dies or after server crash. We should also remember, that replication lags can be quite high using this technique – everything depends on connection quality and number of writes on master that will have to be replicated on slave. In order to tune this method of replication It can be good to use statement-based replication – because in many cases this method use a bunch less number of kilobytes to transmit replication data to the slave.