mysql replication

[Jesore]

I've read myself a bit into mysql replication and have a question. is it possible to have an equal servers setup instead of master/slave? It would really help with my task if I could do update queries on both servers.
Another dbms is not possible at the moment as the whole system already runs on an mysql/php basis and we don't have the resources to start from scratch (or do some extensive reprogramming).

Before I forget - the servers are in different locations (actually different countries)

Any ideas?

Jesore

[kashani]

Here's the scenario

mysql0 is the master
mysql1 is a slave
mysql2 is a slave
mysql3 is a slave

You can do any data reads (like SELECT) from any machine, generally you'd want to only do them on the slaves.
You can only do writes (DELETE INSERT) only on the master.

That's as good as it gets without changing your db and throwing some serious money at the problem.

kashani
_________________
Will personally fix your server in exchange for motorcycle related shop tools in good shape.

[Jesore]

this is ... not good.
The scenario is the following. At the moment we have a single server used for writing tests online (like LPI certification - basically). All training centers use the same server.
As the number of centers grows (and the tests with it) a backup would be nice, so if one server dies we could just move to another and write the test there. Ideally in the middle of the test with all already answered questions there. That's where the need of writes comes in.

Hmm, would it be possible to switch the roles of the servers fast in case of an emergency? Turn the slave into a single server, write tests there and when the old server is back write all new datsa back and then turn it into the master again.
I'm no expert in this field yet (got the task yesterday) so if this is a stupid idea bear with me.

Jesore

[Kirigoe]

quoting http://www.mysql.com/doc/en/Replication_FAQ.html:

[Jesore]

The database gets updated each time one user answeres a question. With 50 questions/user and a rising number of tests written simultaniously, there are quite a lot of writes.
Basically without inserts/updates the application stops working.

Btw. do you think they'd come over to europe to arrest me because I used master/slave

[kashani]

You might think about having your PHP write to flat file initially. Then have a second process handle the db logic every 10 minutes or so. You could lose a bit of data if a server failed, but breaking it out like this might allow you to play db games in the background without having to get fancy.

kashani
_________________
Will personally fix your server in exchange for motorcycle related shop tools in good shape.

[Jesore]

That would be possible.
But after what I've found out, the boss told me to just setup the master/slave replication for backup purposes. If the master dies we have an exact mirror we can dump and restore to the master.
The whole app will be rewritten from scratch over the next year if all goes well (after all we're trying to hire a java expert - keep fingers crossed that he comes).

jesore

[Wilhelm]

Hi guys i was reading this thread because i want a DNS server running MyDNS and thus the master slave thing should work for me since adding entries is not a widespread usage.

To comment on Jesore

With distributed systems it's all down to consistency and it can never be achieved however it depends on what relaxed version of consistency you allow. As i can read your looking for causal consistency on your reads and writes which is possible however mysql doesn't support it as read by the Kirigoe. Causal consistency means that your messages have a natural ordering.
You should think about what reads or writes will disrupt the usage of your system because if not done properly you could get failures causing a reply to a thread to be added whilst the posting was lost which is not at all wanted.


My simple solution for your problem is to attack the problem from the other side. If you stop the master server from going down in the first place you've soleved your problem. By running one expensive server and inexpensive slaves. With expensive i mean

- Redundancy by RAID (hotpluggable disks)
- UPS for power failures (even if nessecary backed up by a generator)
- Multiple CPU's
- etc.

It will be highly unlikely that your server will bork and your system should be running ok and consistent. Only real threat would be hackers but if your master is a pure MySQL machine with no other access but physical or tightly monitored access your safe.

It all depends on the money your willing to invest.

[kashani]

Wow. Nice little blast from the past.

For something that does very little updating or something that can handle latencies of a sec or so data sync between their databases I would now recommend circular replication.

Master A -> Slave B
Master B -> Slave A

Master A beign also Slave A, etc. There are a number of caveats with circular replication, but with something that's likely to be low impact such as DNS serving I think it would work well and cause very little problems.

On the other hand, as long as you did replication your servers would be fine until you got the master back so maybe it's more trouble than it's worth.

kashani
_________________
Will personally fix your server in exchange for motorcycle related shop tools in good shape.

[Wilhelm]

Yes i'm just setting up 1 master and 1 slave. If the master (A) goes down i can either fix it or temporarily make the slave (B) the master.

Circular replication on the otherhand is a kewl idea ... never knew it could be done.

Thnx


PS. Hmm never checked how old those postings were