MySQL Master-Master Replication

[Jackd]

I've got MySQL master-master replication setup fine on two servers. I was wondering if theres a solution to this, if one server goes down it ends up out of sync with the other server. Is there any way automatically on startup for it to try and get up to date with the other server.

 

I know i could do that manually but it would be time consuming and ideally i would like it to be automated.

 

Thanks

Jack

[matt40k]

Dare I ask why a 14 yr old boy has a domain name with his DNS pointing to a "server" he has hosting off his BT broadband and is asking about MySQL load balancing?

 

Anyway, I would got for the Master\Slave approach. I'm guessing your two server can't working out who actually is correct.

 

PS: Look at prgmr.com, assuming you fancy Linux.

[kmount]

Dare I ask why a 14 yr old boy has a domain name with his DNS pointing to a "server" he has hosting off his BT broadband and is asking about MySQL load balancing?

 

Anyway, I would got for the Master\Slave approach. I'm guessing your two server can't working out who actually is correct.

 

PS: Look at prgmr.com, assuming you fancy Linux.

 

Didn't you do something similar at that age?

 

How else is the 14 yr old boy going to learn?

[matt40k]

Well I didn't have it open to people on the internet.

 

We'll ignore the fact I had a colo'd server when I was 17.

[kmount]

He's trying to learn and there's no faulting him in that.

[matt40k]

I think your missing my point. Let me recap my points so far.

 

1/ I've recommend Master\Slave rather then Master\Master, you normally require high end kit for this (load balancing)

 

2/ I stated running stuff of your broadband is a bit foolish. (Do you want a list?)

 

3/ I stated a CHEAP vps provider which is a great way to learn, easy to restore your mistakes and keeps your home broadband secure.

[kmount]

No, I'm perfectly clear in the points made, just think the way they were conveyed was a little off.

 

1/ Fair point.

2/ To learn, why pay for the pleasure on a remote system if you don't need to.

3/ A VPS provides no more functionality than one can achieve on their own boxes at home; but the comment about broadband is sound if availability is important otherwise it isn't.

[matt40k]

I totally agree people who have strong interest in IT. However some limits should be made.

 

I've had a cobalt webserver which was great, it's pretty straight forward and has loads of people who have created scripts that help automate it, plus you could pick one up cheap and hosting it in a london datacenter was around £10. Can't get anything less then £30 + VAT nowadays.

 

One of the main things about having a internet based server (dedi\colo\vds) is that it's open 24/7 and WILL get attacked from the outside. How well his firewall is setup is another thing.

 

One thing he is going to learn about is email. His currently got it on his home "server", which is a mistake for two reasons. One spam (open relay) and the amount your gonna get without some good rules and two, what happens when your server\internet goes down? I know companies that blacklist your MX servers if they are offline too long.

 

This is before we start on BT and there love bandwidth limiting \ caps.

 

Bah... anyway, mysql > master\slave

 

Openbsd > pf > load balance

[kmount]

I take on what you're saying but in "our day" perhaps things were perhaps more economically viable, but given he's not hosting anything commercial requiring a high uptime etc there's no harm in playing how he is now.

 

He's yet to learn about open relays, blacklisting, null routing etc and the only way he'll learn is when it happens to him; or when he asks questions here and people with experience post back and help him.

 

I think we both share a similar view and that when funds permit he should look at a VPS or so initially to start with but until then, he can continue to play with his implementation as is until he hits problems.

[Jackd]

It isnt specifically for load-balancing. But in master-slave i cant write to the database if the master is down can i?

 

I prefer having the stuff hosted at home i'm not doing anything requiring high-bandwidth so its fine. The mail server itself is also fine, ive checked it and its not an open relay, and all my mail is relayed through BT's mail servers so i don't get mail blocked.

[Jay]

Have you looked at the MySQL clustering edition, if your after load balancing *and* failover thats is your answer.

 

Master/Slave(s) replication is pretty much for performance only and performing tasks such as backup without affecting the master.

 

Master/Master is impossible as far as I'm aware, (I'm guessing what you really mean is clustering).

 

The only other solution I can think of would be to use a heartbeat and switch the slave to master when the heartbeat fails (so it can accept updates).

 

Don't know about the last option, but clustering should be documented on the mysql website.

[Jackd]

Master Master is possible.

I'll have a look about clustering

 

Search

[Jay]

I can see what you mean about master->master, its really just two master/slave combinations in opposition to each other. Each machine is both master and slave, master 1 sending updates to slave 2, slave 1 accepting updates from master 2. Both master and slave sharing the same tables on the same machine. Can't see how you would get around collisions with autonumbers though?

 

I might have a read later on.

[matt40k]

From what little I know, in the master>master setup you would have to setup the failed server up as a slave, get it to sync, then turn it back into a master. I suppose you could have it setup as a slave on startup (mysql service), then have another one which switches back to a master once it's sync (manually ran)

 

Basically when the server comes back online it thinks it's right, when it isn't. You either have to tell it's wrong or setup heartbeat\CARP

 

The heartbeat is the load balancing which starts to hit the enterprise ed. (i think). I think CARP would do it.