Replication in MySQL

One of the issues that I have in my hands right now, is to implement database replication.

To be more specific, we are talking about multi-master, asynchronous, or lazy replication.

Could it be any more complicated? yes it could, but I will leave that to another article 🙂

After searching and even asking for help in stack overflow, I was left on my own, so I decided to explore the different alternatives/approaches, starting with the popular non-FOSS database: MySQL.

For the people who are not familiar with replication and did not read the links above, what I want to do in a nutshell is to have a system of databases, that are both master and slave from each other; this is also called “circular replication”, as the changes are passed “around” the nodes. Contrarily to a master-slave system, where the modifications only occur on the master, in this case we can have changes occurring in any node of the system.

The fact that the replication is asynchronous, means that we may have nodes disconnected from the network (working offline), and changes will be passed around, as soon as they connect. Asynchronous replication is very convenient, but to use it we must accept that the system will be many times in an inconsistent state as changes are not necessarily propagated immediately to every node (and this is why it is called “lazy”). Another consequence of lazy replication, that is quite “ugly”, is the fact that conflicts may arise (but I will leave that for another post)

MySQL5 supports master-server replication “out of the box”.  The mechanism behind it uses an identifier for each node, and a binary log for the master, where every change is stored. To configure master replication, you need to setup some parameters in the [mysqld] section of my.cnf , on the master side:

server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log

You may want to create an SQL user for replication (with appropriated privileges) and then you just need to setup the master settings on the slave side:

    ->     MASTER_HOST='master_host_name',
    ->     MASTER_USER='replication_user_name',
    ->     MASTER_PASSWORD='replication_password',
    ->     MASTER_LOG_FILE='recorded_log_file_name',
    ->     MASTER_LOG_POS=recorded_log_position;

The “master_log_file” and “master_log_pos” parameters are very important as they tell you at which point of the master you want to start to replicate (current) and the path to the log where all the changes are stored; you can retrieve this values, by typing on the master:

show master status;

after that, you may start the replication listener (on the slave), by typing:

start slave;

The slave will be now listening to every change on the master, and will update accordingly. If you go offline, it will immediately start updating once you go online again.

Of course, if you want to setup circular replication, you will need to make both nodes “master” and “slave” at the same time, by reproducing the configurations above for both. In that case, you will both servers listening to each other.

If you do something like this, you want to be careful about conflicts, which may easily arise when we use auto increment keys. A good solution for that is presented on this article.

This works quite well after fiddling around with some network and permission configurations, to make sure everything is smooth. However, I am not completely satisfied with this solution for two main reasons:

  • Each server listens “blindly” to the other one as master, rather than trying to conciliate the changes with its own changes (what would be a kind of a merge); this may result in an inconsistent system. As an example, I switched the network on both servers, and updated the same row with different values; once they connected, each one grab the changes of the other, which originated two different values for the same row.
  • I am still not sure how to setup circular replication with more than two servers, which would have to fork the “change master” statement to multiple servers.



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s