0

Let us say that I have two datacenters where two instances of a MySQL database are hosted. In order to have high availability, I can implement master-master replication between these two instances so that both the databases always have the latest information. But master-master replication comes with its own sets of problems and I am trying to avoid it. In my case I know about the following characteristic of the data -

If a user table has two records with keys U1 and U2, then U1 will only be updated in one and only instance. So the writes for a given record are done only in one instance. All, I need to do is to replicate this record to other instances so that in case I later want to make the other instance as master it has the latest record. Also, it is possible that at certain point in time, instance 1 is master for U1 and at another time instance 2 is master for U1.

I think this is one step below true master master replication where the write for U1 can happen in any of the two database instances.

In light of this, is there a better way to implement replication for this scenario. I would like to avoid master-master replication as in future we might have more than 2 data-centers and implementing multi-master replication is not straight-forward.

Charles
  • 50,943
  • 13
  • 104
  • 142
user1935449
  • 307
  • 1
  • 2
  • 10

3 Answers3

1

You are effectively describing a sharded database, which is possible but not recommended because it involves large changes to your application. The disadvantages are outlined in another Stack Overflow post.

The MySQL Cluster CGE software is said to do transparent sharding, but it incurs additional licensing fees.

Community
  • 1
  • 1
praseodym
  • 2,134
  • 15
  • 29
  • Yes, you are right, from my description it sounds lot like sharding now that I think about it more. The only difference is that I am thinking more in terms of a dynamic shard where entries in a shard change dynamically. So Shard 1 might be responsible for key U1 right now but it is possible that at a later point in time Shard 2 is responsible for U1. – user1935449 Jan 11 '13 at 00:54
  • That'd be expensive resharding in a regular sharing approach, but if you use an additional layer between the application and database to manage the sharding you could indeed do this dynamically. However, the disadvantages still apply (like killing a fly with a… 4U server, or so). – praseodym Jan 11 '13 at 01:00
0

It sounds like you want to have a master-slave setup, then have the capability at some point to promote a slave to a master and make the other servers slaves to the new master.

This is possible and documented, see: http://dev.mysql.com/doc/refman/5.0/en/replication-solutions-switch.html

hexist
  • 5,151
  • 26
  • 33
  • I don't want to make an instance master or slave for all the rows in User table. What I would like to do is to make instance 1 the master for a certain set of rows and instance 2 the master for a completely disjoint set of rows in the User table. – user1935449 Jan 11 '13 at 00:11
  • Oh I see. Yeah see what @praseodym wrote, you're talking about sharding.. you probably don't want to do it. There are almost always better ways of scaling up. – hexist Jan 11 '13 at 00:14
0

If you mean that MySQL asynchronous master-master replication does not scale well beyond 2 nodes then that's correct - but most of the rest of your question is very confused.

While it is possible to have something which looks like a table but which is effectively partitioned between 2 database servers such that both have 100% read access but each node can only write to specific rows in the database, then that's entirely possible, however in practice it will be difficult to manage. It's certainly a lot more complex than master-master asynch - NOT simpler.

Multi-master synchronous replication has a very different set of advantages and disadvantages from asynchronous replication. IMHO its on a par with MMA in terms of complexity - if you think it's a lot more complex then you've failed to understand at least one of the technologies.

Maybe all you need is federated tables and views. or a mix of local only and shared databases.

You've still got a bit of a journey ahead of you before you start asking the right questions; you might be better paying for some good quality consultancy than betting the future of your data assets on answers here.

symcbean
  • 47,736
  • 6
  • 59
  • 94
  • Yes, I am still trying to understand this problem and come up with some possible solutions. This would get evaluated by others so my limited knowledge should not be a problem. I am trying to come up with some solution myself before that. What would be your choice of replication technique if you have to do master-master replication between 3 nodes? – user1935449 Jan 11 '13 at 02:03
  • Definitely mult-master replication. I'd recommend Percona too - in addition to providing some great tools (most of which are open-source, so you can play around with them without having any financial commitment) they also offer professional support and consultancy – symcbean Jan 11 '13 at 11:43