MySQL replication for demanding users

I have been working with MySQL replication for quite a while. I have dealt with simple replication setups and I have experimented with complex ones. Five years ago I wrote an article about advanced MySQL replication, which was mostly a dream on what you could do with imagination and skill, but the matter from that article is still not even remotely ready for production. Yet, since that article, I have been approached by dozens of people who wanted to know how to make the multiple master dream become reality. To all of them, I had to say, "sorry, this is just a proof of concept.Come back in a few years, it may become possible". It still isn't.
Despite its latest great technological advance, MySQL native replication is is very poor of topologies. What you can do with MySQL native replication is master-to-slave (which also includes relayed slaves), master-to-master, and circular replication.

Of these, circular replication is the closest thing to multiple masters that you can get with MySQL native replication, without the addition of third party services.
Circular replication is tricky to set up, although not unreasonably so. It works. With some patience and precision, you can build a cluster of a few nodes in circular replication. With luck, you can get them to work properly, without loops and with the data flowing to all the servers. Your luck runs out the moment one of the servers fails, or replication breaks down for whatsoever reason. Then you see that circular replication is actually more complicated than what it looks on the surface, and it is also quite brittle. That doesn't mean that circular replication is not used in production. It is. I have known several people who use it successfully, although nobody is really happy about it.
In addition to its fragility, circular replication is slow. If you insert data into master A, it has to travel across three nodes before reaching master D.
Another topology that seems to be very popular is the multiple source scheme. It is the opposite of master/slave. Instead of having one master that sends data to many slaves, it is many masters that send data to one slave. Despite its popularity, this topology is yet unimplemented with MySQL native replication. The best you can do to simulate the desired outcome is to do round-robin replication with cron.
With this background, it is no surprise that I was thrilled at the idea of working for a company that has made these dreams become reality. Tungsten replicator allows users to have real multiple masters topologies, and even the much coveted multiple source topology is now within the users grasp.
Compared to MySQL replication, the drawback of using Tungsten is that you need to deal with bigger complexity. It's only natural. With so many more features, there come more pieces to take care of.
An interesting point about multiple masters is the matter of conflict resolution. Asynchronous replication convenience and robustness are countered by lack of means to deal with conflicts. This difficulty has been used many times as the reason for not implementing multiple source replication in MySQL. I have my own ideas on this issue. I am aware of the risks, but if I were allowed to do multiple master replication, I would be glad to take charge of the risks. Updating different databases, or different tables in separate masters is one way of defining a conflict-free scenario where multiple masters or multiple sources could be used safely. If only we could ...
My colleague Robert Hodges has posted some interesting aspects in his blog. The bottom line is that we focus on empowering users with advanced replication features. Conflict resolution can wait. I am sure many users would love to have the problem of how to avoid conflicts if the more demanding problem of how to replicate from many places to one cluster could be solved. The good news is that some sort of conflict detection (and possibly resolution) are possible even now, without slowing down the operations and without complicating our lives unnecessarily. For example, a simple conflict that could be avoided using Tungsten filters is the one resulting in a master that is updating tables that it was not supposed to do. In a scenario where multiple source replication works on the assumption that each master updates a given subset of the data, we can easily detect and eventually reject offending updates. It is not much, but in many practical cases it would be the difference between having robust multiple source replication and doing data load and consolidation manually.

Anyway, back to the present day with very much real multi-master replication available for everyone. To alleviate the fear of the unknown, we are organizing webinars on a regular basis, where we cover the theoretical points and give practical demos of how to use the new features.
If you are a demanding user, this upcoming webinar is for you: MySQL Multi-Master and Multi-Source Replication With Tungsten. Tomorrow, March 31^st, 2011, at 10am PDT.

A first look at delayed replication in MySQL 5.6

If you like fresh features, you should not miss this one. MySQL 5.6.2 includes, among other improvements, the implementation of Time delayed replication, a feature that lets you tell the slave not to apply changes from the master immediately, but to wait N seconds.

The feature is documented in WL#344. (There was a manual online as well together with the binaries for MySQL 5.6.0, but they were removed after a few days for a good reason. I am confident that both the manual and some binaries will eventually show up soon).
Since as of today there are no binaries for MySQL 5.6.x, you need to get the code and compile it yourself. Just get the code from https://code.launchpad.net/mysql-server and compile it using the instructions in building MySQL 5.5 with cmake.
To get a taste of this new feature, the quickest way is to set up replication using the binaries that you have built and MySQL Sandbox.

make_replication_sandbox  mysql-5.6.2-m5-osx10.6-.tar.gz 
# the file name may change, depending on the operating system you are using

Soon you will have one master and two slaves in $HOME/sandboxes/rsandbox_5_6_2.
What you have to do is connect to one of the slaves and enter these commands:

STOP SLAVE;
change master to master_delay=60;
START SLAVE;

Let's say that you did this to slave #2.
Now whatever you do in the master will be replicated immediately in slave #1, but it will executed with 60 seconds delay in slave #2.
To be clear, the IO_THREADs of both slaves keep getting data from the master as fast as they can, same as they did until version 5.5, but slave #2 will hold the SQL_THREAD for the defined amount of seconds.
This new state is visible in the output of the SHOW SLAVE STATUS command, which lists this information after you do something in the master like creating a table or inserting data:

               SQL_Delay: 60
     SQL_Remaining_Delay: 43
 Slave_SQL_Running_State: Waiting until MASTER_DELAY seconds after master executed event

The main purpose of delayed replication is to protect the server against human mistakes. If I accidentally drop a table, the statement is instantly replicated to all the slaves, but it is not executed to the delayed slaves.

$ ./m -e 'drop table test.t1 '
$ ./use_all 'show tables from test'
# master  
# server: 1: 
# server: 2: 
Tables_in_test
t1

The table is gone in the master, and it is gone in the regular slave, but it is still there in the delayed slave. And if I detect the problem before the delayed statement gets executed (a delay time longer than 60 seconds would be advisable in this case, 3600=1 hour, seems healthier), then I may be able to recover the data.

I notice en passant that there is much more than delayed replication going on in MySQL 5.6. For example, the information_schema tables related to InnoDB have increased from 7 to 18:

 show tables from information_schema like 'innodb%';
+----------------------------------------+
| Tables_in_information_schema (innodb%) |
+----------------------------------------+
| INNODB_CMPMEM                          |
| INNODB_TRX                             |
| INNODB_BUFFER_PAGE                     | *
| INNODB_LOCK_WAITS                      |
| INNODB_SYS_TABLESTATS                  | *
| INNODB_CMP                             |
| INNODB_SYS_COLUMNS                     | *
| INNODB_CMPMEM_RESET                    |
| INNODB_SYS_FOREIGN_COLS                | *
| INNODB_BUFFER_PAGE_LRU                 | * 
| INNODB_BUFFER_POOL_STATS               | *
| INNODB_CMP_RESET                       |
| INNODB_SYS_FOREIGN                     | *
| INNODB_METRICS                         | *
| INNODB_SYS_INDEXES                     | *
| INNODB_LOCKS                           |
| INNODB_SYS_FIELDS                      | *
| INNODB_SYS_TABLES                      | *
+----------------------------------------+
18 rows in set (0.00 sec)
# (*) new tables marked with a star

What they do and how to play with them will be matter for some more investigation.