One of the better kept secrets about MySQL Cluster appears to be the flexibility available when setting up replication. Rather than being constrained to implementing a single replication scheme, you can mix and match approaches.
Just about every Cluster deployment will use synchronous replication between the data nodes within a node group to implement High Availability (HA) by making sure that at the point a transaction is committed, the new data is stored in at least 2 physical hosts. Given that MySQL Cluster is usually used to store the data in main memory rather than on disk, this is pretty much mandatory (note that the data changes are still written to disk but that’s done asynchronously to avoid slowing down the database).
MySQL Cluster Replication
MySQL asynchronous replication is often used for MySQL Cluster deployments in order to provide Geographic Redundancy. At the same time as the synchronous replication within a Cluster, the changes can be replicated asynchronously to a second Cluster (or to more than one) at a remote location. Asynchronous rather than synchronous replication is used so that the transaction commit is not delayed while waiting for the remote (could be thousands of miles away, connected by a high latency WAN) Cluster to receive, apply and acknowledge the change. A common misconception is that changes being made through the NDB API will not be replicated to the remote site as this replication is handled by a MySQL Server instance – the reality is that the MySQL Replication implementation will pick up the changes even when they’re written directly to the data nodes through the NDB API.
A third use of replication is to store the Cluster’s data in a seperate database – for example to have a read-only, up-to-date copy of the data stored within the MyISAM storage engine so that complex reports can be generated from it. And the best news is that this can be done at the same time as the local HA and remote Geographic Redundancy replication!
Johan’s Blog provides the technical details around configuring replication in order to provide some extra scaling by setting up non-Cluster slave databases that pick up all changes from the Cluster database.
[…] This post was Twitted by dbmoore – Real-url.org […]
[…] This post was Twitted by merv – Real-url.org […]
[…] storage engine (MyISAM) but it’s possible to mix and match (for example, take a look at MySQL Cluster – flexibility of replication). As this is intended as a simple introduction to replication, I’m keeping life simple by […]
I’m the first time look at your blog, that is very great!
I am interested in MySQL NDB API, but I encountered many trouble, that made me so difficult. I’d like to write somthing to support the one which have experienced it – like you! But I don’t know how to ask your some questiones, but now just write this.
Hi Jacky,
you can always post specific questions on the MySQL Cluster forum at http://forums.mysql.com/list.php?25 or here.
Regards, Andrew.
Hello Andrew,
I need one help from you. Please can you help me to resolve replication with following topology.
I want to implement replication in MySQL.
My topology for replication are as below:
I have different branch and all branches are have different database and they are replicate with each other. This way replication I have done with my current system.
But now I want to replicate branch’s database to different till’s database connected with it.
For example: I have branches: Ho, B1, B2, B3.
Currently I have done replication is following flow: Ho->B1, B1-> B2, B2->B3, B3->Ho and its working fine.
But now I want to replicate My branch database to tills database connected with it.
So for example: I have 3 Tills at Branch B1 than Brnach and Head Office should work as above scenarios but My Brnach B1 should replicate like
B1 – > Till 1, B1- > Till 2 , B1-> Till 3.
And same for other brnach having till than branch’s main Database will replicate with Till database.
But HO and Branches repliaction should work as currently working scenarios as above.
Please let me how I can work with such a scenarios.
Thanks in Advance.
Hi Nils,
Firstly – is this using MySQL Cluster or ‘regular’ MySQL Servers?
In either case it should be pretty straight-forward as there is no issue with a single master having multiple slaves. Just have the tills connect as slaves to the local branch master.
Andrew.
Hello Andrew,
Thank your for your response.
I am using Regular MySql.
And soory, I forgotted that All till have local database and when any till will active with Brnach’s Master database than Till database should be replicate with Brnach’s master database.
Also Branch Database is also in replication with other branches and Head office.
So if you have sample code than please give me that and also please let me know which wat I can replicate this topology.
Hope this information will be ok for you to assist me.
Thanks in Advance.
Hi Nilesh,
unless you’re using MySQL Cluster, each MySQL Server can only have a single master. If you need to simulate multiple masters then you can write a script which over time switches from one master to another (using CHANGE MASTER command).
Regards, Andrew.