What’s missing in MySQL Replication?

One of the things that has made MySQL grow a lot is the easy setup of replication on Internet sites. This has allowed them to grow from one database server to many reasonably easily. Configuration of replication is quite simple and replication itself is extremely fast. That makes it ideal for most people.

However at a point in time you begin to notice some of the weaknesses in the current design. Here are a few comments providing ideas on what I would like to see in MySQL replication and why they are features which are important to anyone who’s database requirements grow outside of a single database instance.

My list of current issues is as follows:

  1. MySQL only allows replication from 1 to N servers.
  2. Some important aspects of MySQL Configuration are not dynamic.
  3. Replication is part of the mysqld server, not a separate process.
  4. It’s not easy to configure table replication (config issue)
  5. It’s not easy to filter the replication commands
  6. Initialisation of the databases when you first setup replication is difficult
  7. Replication configuration information is not in the database

MySQL only allows replication from 1 to N servers.

MySQL replicates from 1 to N servers. As mentioned in a previous blog the current replication design is very simple.  Each mysqld server can get replication information from a single master and copies over the binlog from that master to the slave and replays part or all of the commands contained theerein. In MySQL up to 5.0 these are the SQL statements executed on the master, and in 5.1 and later it’s also possible to configure the master to store the rows that changed and replicate these changes on a row by row basis.

This design works well and is fast and it also scales out very well. However it does have issues. It creates a single point of failure in the master, which ends up being the weakest link, the source of all db changes. If the SELECT load is mainly distributed over the slaves then the problem is not too bad. However if the master fails all slaves will be broken.

There is however a second less obvious problem. In many companies there are various databases being used in the company.  Sometimes the usage of different database servers is different and also the information which is required. However of course often there is a set of data which may be common to one or more departments. Replication is a useful way of distributing this information between the different database instances. What’s not possible is to selectively choose which databases should be replicated from a location to a location, and here the MySQL 1:N fanout is not always ideal. You can’t replicate databases from 2 different servers to a common second location. That would be very handy. It’s also not possible to replicate from multiple locations into a single dedicated “summary” database, perhaps used for global reporting. A design like this requires a little bit of care in setting up but allows independent groups of servers to be able to run separately but their results to appear in a global master system. It also helps with failures: if a branch server goes down the other satelittes offices are not affected, nor is the global reporting of the data.

I’ve used this type of replication mechanism in my last job (using Sybase) and found it to be extremely convenient.  The added flexibility of being able to mix and match databases would give the DBA more freedom to be able to setup things for the business in a more intuitive manner.  Of course keeping it simple (where possible) is a goal to aim for but N:1 and selective replication of sources databases to destionation servers would be very useful.

Some important aspects of MySQL Configuration are not dynamic.

With any “enterprise” system you want all changes within reason to be able to be performed while the system is running.  This reduces downtime and thus keeps management happy. MySQL replication has a number of things which can not be configured dynamically.  Having to temporarily stop replication to perform these tasks is in itself not an issue. Having to stop the db server where the replication is running is. This has an impact on the business and affects the DBA and also system administrators who may need to take action if the database instance goes out of service.

So what can’t be done dynamically?

As far as I’m aware the configuration of which databases should be replicated, the configuration of the different specific tables to include or exclude from replication. While this information does not change frequently if it does the impact on provoking downtime is significant. If Sun made these configuration options dynamic then we would not need to restart the mysqld server and therefore business could continue as usual. Most applications don’t notice or mind if there’s a small delay in replication and they can determine if this is the case and take appropriate action if needed. Howerver if the databse server goes away that’s much more of an issue.

Replication is part of the mysqld server, not a separate process.

I mentioned this before in http://blog.wl0.org/?p=7. Having the replication process in the same mysqld process makes it impossible without design changes to support anything except 1:N replication. Even without changing existing
functionality if you could build this as a separate process then you would be able to build N:1 replication, even if that required ‘N’ separate processes.

Also replication changes and new functionality could be added independently of the mysqld version. That would allow the groups of people within Sun who manage the mysqld and those that look at replication specific issues to do their own thing and not be hindered by each other.  It would probably also allow improvements to be made to the replication process more quickly.

It’s not easy to configure table replication (config issue)

This is probably not considered an issue by people using mysql but that’s also probably because it’s not easily done there. Sometimes it’s convenient to have in a replicated database to have various types of tables: tables which are replication in the sense typical in MySQL, but many other tables which are local to the specific instance (perhaps instance configuration tables for
example).  In MySQL’s current design managing a long list (hundreds) of specific tables to be replicated or to be excluded from replication is not very easy. Also this list is not dynamic.  Thus improving the design here would help a lot.

It’s not easy to filter the replication commands

On some servers it might be nice to use it as a historic server containing all data, but on the master you might need to delete older data. Having a way of manipulating/filtering certain commands so that they can be eliminated or modified would allow this type of behaviour. For example filtering (and removing) commands (or the results of commands such as DELETE, UPDATE, TRUNCATE TABLE) could provide this sort of functionality.  Again if the replication process were separate from mysqld this would be much easier to implement.

Initialisation of the databases when you first setup replication is difficult

Creating a new slave can be done in many ways. However it’s quite hard with a busy server especially when making the first slave to do so without stopping the master. That’s especially true as normal mysql dumps don’t guarantee a consistent database is generated nor is it easy to work out exactly the point in replication to use a dump from. What would be nice would be to setup a database as a copy of the master but have  the mysql replication process fill the slave’s tables directly from the master with no extra help.  At a certain size this may not be practical but for smaller database sizes this avoids a lot of extra complexity.

Replication configuration information is not in the database

Various parts of the replication configuration are outside of the database proper.  The configuration is stored in separate text files (master.info relay-log.info) and not in the database itself. Where there are issues with MySQL’s mixed storage engines and thus how replication might work after recovery from a crash it would still be nicer if the data were stored in the database and thus hopefully the recovery after a crash would be consistent.

2012-02-20: Update

Quite a long time has passed and little has changed. One thing I did not add in my original post was a list of mysql bug reports which request the type of functionality mentioned above. Let me know of others I might have missed.

  • http://bugs.mysql.com/35611 please make all slave replication options dynamic
  • http://bugs.mysql.com/57560 please make all binary log options dynamically settable
  • http://bugs.mysql.com/62825 Option to replicate at the database level instead of the server level

Published by

Simon J Mudd

Born in England, I now live in Spain, but spent a few years living in the Netherlands. I previously worked in banking (financial markets) both in IT and as a broker, but IT has always had a stronger influence. Now working at booking.com as a Senior Database Administrator. Other interests include photography, and travel. Simon is married, with two children and lives in Madrid.

5 thoughts on “What’s missing in MySQL Replication?”

  1. Hi Simon!

    Please have a look at Tungsten Replicator (http://www.continuent.com/community/tungsten-replicator). Tungsten Replicator is an open source replacement for MySQL Replication with flexible replication topologies, event filtering, etc. already covered. Also, we have a lot more than just replication–we have components for proxying, SQL routing, and (very soon) cluster management.

    I look forward to your feedback and hope you will try it out. Meanwhile, thanks for the article!

    Cheers, Robert
    CTO of Continuent, all disclaimers apply 😉

    1. Hi Robert. I’ve read about Tungsten before and it does sound a lot more complete. I don’t know of anyone who uses it, nor how well it scales. What put me off from trying it when I looked several months ago was the use of Java. Probably unjustified. The functionality your product documentation says it provides does look nice and perhaps it would fit our requirements better. It also looks quite similar to how Sybase Replication server works. I’ve not used other replication products so perhaps they do the same thing too. Fundamentally the problem of any replication product is the same so the solutions are likely to be similar.

      I’ll have to see if I can at least make some time to look at Tungsten and see how well it behaves and how well it scales. Thanks for the pointer.

  2. It’s not easy to filter the replication commands ? SET sql_log_bin Syntax

    SET sql_log_bin = {0|1}

    Disables or enables binary logging for the current connection (sql_log_bin is a session variable) if the client has the SUPER privilege. The statement is refused with an error if the client does not have that privilege.

    1. Hi Tom.

      it’s true that to turn the bin log on or off is easy (on the source server), but that affects all slaves.

      I’ve worked in a location where I was able to selectively filter out DELETE statements for a specific table so that these statements weren’t applied on one slave while they might have been applied on another one. The same is true of UPDATE, or TRUNCATE TABLE commands or even filtering on commands like DROP TABLE.

      As far as I’m aware none of this is possible with MySQL replication.

      Common usage of this is: a topology consisting of a master and one or more “normal slaves” and a “log slave”.
      The log slave has DELETEs disabled/filtered out yet the normal slaves and the master don’t.

      So with this it’s possible to run a daily job on the master to “clean up” (delete old data). This gets replicated in such a way that the normal slaves have the same content but the “log slave” keeps the entire history. So historic reporting can be done on this box and the main master/normal slaves just have the upto date “current state”.

      Where I work now something like this sort of functionality would simplify our setup signficantly. It’s not possible.

  3. You know, the thing about SQL is, that there is virtually nothing that can replace it.

    Does anyone know if a substitute exists for sql? I mean besides MS SQL and Oracle and all that jazz. Thanks.

Leave a Reply