I need to configure mysql master/master replication with 3 nodes. Currently I have master/slave setup and I need to move it to master/master with 3 nodes. I have gone through some posts about configuring it in ring structure, I'm little nervous about it.
If anyone has the similar setup on production, would be great if you can suggest me how to achieve this.
There are multiple reason why you wouldn't want to do multimaster replication in a circular fashion, but most of them can be summarized in one: You will have 3 different single points of failure. Check this article (which is exactly what you asked for, but probably not what you want). Standard replication is very prone to drifts in data, as it is asynchronous, producing -if you are lucky- the replication to stop for all nodes or -if you are unlucky- replication to continue with different data between nodes.
While 5.6 GTID and other features minimized those consistency problems, you still have the 3 single points of failure, as replication is single-master (multi-source replication is available only in MariaDB and MySQL 5.7, negating the need for circular replication).
If you want a setup that is multimaster (write anywhere), I would highly recommend going for a different technology that manages conflicts between nodes. Galera (you may also find it under the names of Percona XtraDB Cluster or MariaDB Cluster) is probably the way to go. It works on WANs, "resolves" conflicts (rollbacks them and retries the transaction) is multithreaded, and can be used to substitute regular replication and/or for clustering. Very recommended if your goal is HA or read scaling. It is free and open source, very widespread (I've helped several banks and hosting companies using it), compatible with standard replication and uses standard InnoDB -not a different engine- for storage.
The biggest cons, of course, are that it is a different technology which may take some time to understand (although probably easier than other clustering technologies), and it has its small quirks. But, in my very own opinion it is worth the time learning about it to make things "work properly".
Can you setup circular replication? Sure, in the article I mentioned above you have the typical recommendations of log-slave-updates, auto_increment_increment and auto_increment_offset for each node. However, the few people that may be running this have to still either avoid multi-master writes or run it in a very controlled environment, where you cannot execute updates and deletes to the same tables in concurrency. Some people also over-engineer solutions involving GTID and semi-sync replication, etc., but generally not all companies have the dedication and knowledge to put patches over a protocol that is not natively prepared for this.
I have dealt with Circular Replication with three nodes before. Here are my posts:
May 07, 2012 : Setting Circular Replication in mysql (From Scratch)Sep 24, 2012 : Setting up MySQL circular replication in existing replication topology? (Takes a Master with Two Slaves and converts the topology to Three-Node Circular Replication)I remember a client I used to have who had 800 client databases (about 2TB) in three-node Circular Replication. The client did the writes for 267 databases at one DB server, 267 databases at the second DB server, and the last 266 at the third database. There were heavy writes. There were long intervals of Replication Lag between node. When the peak business time was past, all nodes were back to normal with Seconds_Behind_Master at 0. They did not need auto_increment_increment and auto_increment_offset since all writes to a specific database was restricted to a specific DB Server.
In your case, if you have a small-to-moderate sized database that is not write-intensive, Circular Replication is OK. I just would restrict all DB writes to one box and load balance your SELECTs.
Please keep in mind that both @jynus and @AaronBrown are right in saying you have multiple points of failure. You also have to exercise great care in taking backups, restricting writes of specific databases to a fixed DB server, making sure your app is Cluster aware. You must also design strict methods for doing failovers, taking nodes out of the ring, inserting nodes back in the ring, and restoring a single database without increasing server load.
I agree with previous answer from Jynus. MariaDB is pretty straightforward to install and there are many how to topics to do inplace replacements of MySQL (You dont need to worry much, all the utilities and commands you know are the same) For example, from MariaDB site itself MariaDB Upgrade. You can also simulate a MariaDB/Percona/MySQL configuration with your hardware settings from Several Nines site (search for the configurator)
I advise you to do a prior reading in Galeracluster's own site so that you can evaluate if features presented are really what are you looking for. Personally I think Galera as a great "enabler" for HA on MySQL (and "derivatives") without changing significantly the way you deal with databases.
Please Note: If your going with Galera (and not NDB type cluster) consider using Percona XtraBackup instead of the proposed rsync or mysqldump for SST (state snapshot transfer)
Bear in mind all the alternatives mentioned above is synchronic replication, meaning the latency between your nodes will directly affect performance since any write has to be confirmed by the other nodes before being 'done', so if you have a latency of 100ms or 400ms, depending on where servers are located, you may experience serious drop in speed of your application, where as master-master will write to a bin log and then update to the other master, making a huge performance difference if your servers aren't all in the same location.
