CODEWITHSUNDEEP
mysqldatabase-replication
user1935449
user1935449

Reputation: 317

MySQL Replication: Row based?

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.

Upvotes: 0

Views: 419

Answers (3)

symcbean
symcbean

Reputation: 48387

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.

Upvotes: 0

praseodym
praseodym

Reputation: 2423

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.

Upvotes: 1

hexist
hexist

Reputation: 5250

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

Upvotes: 0

Related Questions