mySQL Redundancy solution (master-master replication)

[genexis]

Hi, this is what i am trying to do:

1) I have 2 directadmin servers A and B, both running mySQL 5.0+

2) A is the master server, where i have most of my websites and sql data bases running on it. Server hostname is A.com, with DNS server ns.A.com

3) B is the back up server such that when A fails, some websites that was on A will be redirected to B. Server hostname is B.com, with dns server ns.B.com

4) Server A and B are on different networks

This is my setup. Lets assume that the website which i would like to host is www.domain.com

In the domain name records @ the registrar, i'll have:
primary dns : ns.A.com
secondary dns: ns.B.com

In ns.A.com, I have records for domain.com that points to the server A
in ns.B.com, i have records for domain.com that points to the server B.
In this way, when server A dies (which means ns.A.com dies also), users who visit www.domain.com will be directed to server B because when they lookup the ip address of domain.com, it will probe from ns.B.com as ns.A.com is not responding. Is this idea flawed? I assume that there is no caching of dns resolution in the isps.

Next.. mySQL:
I want the data base of the website in A to be replicated over to B instantaneously. I know this can be done by master-slave mySQL replication. However, if server A dies, assuming that traffic is successfully directed to B, and there are changes made to the database in B (which is supposedly exactly the same as A just before A dies), i want this change to be replicated back to A when A comes alive again. Will master-master mySQL replication achieve this automatically?

Last... data files:
i'm using rsync between both servers. From A to B at time intervals. However, is there a way which i can synchronize both ways?


Please let me know of this model and if there are flaws to it. If possible, please point out to me the issues and ways to resolve it. Thank you very much.

 

[nobaloney]

Hasn't this been asked recently?

In ns.A.com, I have records for domain.com that points to the server A
in ns.B.com, i have records for domain.com that points to the server B.
In this way, when server A dies (which means ns.A.com dies also), users who visit www.domain.com will be directed to server B because when they lookup the ip address of domain.com, it will probe from ns.B.com as ns.A.com is not responding. Is this idea flawed? I assume that there is no caching of dns resolution in the isps.

The important things to consider:

While both servers are up both will be used. Why? Because all resolvers ask all nameservers, and accept the first reply. Some people will get faster replies from A, some from B.

Resolvers and nameservers will cache results; you can affect the time of the cache with your TTL entries.

Individual desktop systems and networks use resolvers which often ignore TTL; most of the time when one server goes down during a client visit it's unlikely that client will be switched to the other server, and even if it was, it's unlikely you can figure out how to make the other server aware of the transaction information. Even running rsync continually on your directory holding transaction information (often but not always /tmp) it's unlikely the second server will know about the first transaction.

Next.. mySQL:
I want the data base of the website in A to be replicated over to B instantaneously. I know this can be done by master-slave mySQL replication. However, if server A dies, assuming that traffic is successfully directed to B, and there are changes made to the database in B (which is supposedly exactly the same as A just before A dies), i want this change to be replicated back to A when A comes alive again. Will master-master mySQL replication achieve this automatically?

We've never been able to make MySQL replication work across servers separated by more than a local intranet; your experience may vary.

Last... data files:
i'm using rsync between both servers. From A to B at time intervals. However, is there a way which i can synchronize both ways?

See above about limitation of rsync; generally it cannot keep up with what you need.

Your DNS method is what I call Poor Man's Failover; generally it only works for static sites.

You can of course use a separate redundant MySQL solution (commercial and open-source solutions are available, which use storage solutions like storage area networks, network attached storage (not quite the same thing), and multiple redundant MySQL servers, along with your Poor Man's Failover but once you've done that you might as well switch to a heartbeat server solution anyway as you'll need it for your MySQL failover.

I've always said that redundancy is much more expensive than two servers, and i stand by that.

Your opinion may differ.

Others may have other opinions; I look forward to reading them.

Jeff

 

[genexis]

Hi, thanks for the reply.

As for the DNS problem that you pointed out, i guess it will help if I have a primary and secondary DNS server that is on the same network, but different servers to point to the same site. So one will be a real primary, and the other will be a real secondary.

THen i'll have a ns3 that is on another network that will point to totally another server. This way, when the network fails, which is what iam trying to be avoid here, only ns3 will respond.

Or does it not make a difference since they will ask ALL nameservers all at the same time?

As for the master-master replication, i'll like to know if it will provide the following:
1) When changes are made to server A, it will be replicated over to server B
2) At the same time, if changes are made to server B, it will replicate over to master A
3) So replication will take place both ones at the same time.

or does it work like a master-slave relationship at a time. Only when i input the SLAVE start comming then the slave will take on the role as the master, and master will become the slave?

 

[nobaloney]

As for the DNS problem that you pointed out, i guess it will help if I have a primary and secondary DNS server that is on the same network, but different servers to point to the same site. So one will be a real primary, and the other will be a real secondary.

THen i'll have a ns3 that is on another network that will point to totally another server. This way, when the network fails, which is what iam trying to be avoid here, only ns3 will respond.

Or does it not make a difference since they will ask ALL nameservers all at the same time?

All nameservers will be asked. All nameservers will return all IP#s in a round-robin fashion. Desktop resolvers will use the first IP# from the fastest nameserver to respond. I'm not sure if I could make my response more clear.

As for the master-master replication, i'll like to know if it will provide the following:
1) When changes are made to server A, it will be replicated over to server B
2) At the same time, if changes are made to server B, it will replicate over to master A
3) So replication will take place both ones at the same time.

Since I was never able to make it work at all, I simply don't know. Perhaps someone else will respond.

or does it work like a master-slave relationship at a time. Only when i input the SLAVE start comming then the slave will take on the role as the master, and master will become the slave?

I have no idea what that last quote means. Master/Slave is DNS terminology, and all it means is that the slave server will get the records from the master server.

Jeff