Replication with MySQL

R

Ro87

Well-Known Member
Joined
Oct 22, 2009
Messages
133
Reaction score
0
Hi guys,

Who has replication working in a production environment?

I'm busy testing it with version 5.5 on two servers and it seems to be working well.
It's setup as master-master two-way replication.

Let me know what problems you guys had or your general setup.
 
Ro87 said:
Hi guys,

Who has replication working in a production environment?

I'm busy testing it with version 5.5 on two servers and it seems to be working well.
It's setup as master-master two-way replication.

Let me know what problems you guys had or your general setup.
Click to expand...

Just had issues in the beginning tweaking the log file sizes but once I sorted that also worked quite well. Also if it goes out of sync its a bitch to do it all over with multiple databases.
 
DrJohnZoidberg said:
Just had issues in the beginning tweaking the log file sizes but once I sorted that also worked quite well. Also if it goes out of sync its a bitch to do it all over with multiple databases.
Click to expand...

I haven't touched the log file sizes so they are on default values for now.

When I unplugged one of the servers and then plugged it in again, the sync took place automatically.
How did you sort out the sync?
 
Ro87 said:
I haven't touched the log file sizes so they are on default values for now.

When I unplugged one of the servers and then plugged it in again, the sync took place automatically.
How did you sort out the sync?
Click to expand...

Depending on how many databases you have those log files can get massive if left unchecked. I set mine to max 250MB per file and expire after a day.

You basically have to start from scratch to resync, at least if you want to be totally sure you have everything replicated properly. The more db's you have the more missions it is, but it shouldn't really go out of sync to start with unless there is some error that causes the slave not to write.
 
Having a couple of days worth of log files makes it easier to start slaving again if a slave drops off, instead of having to rebuild the slave if it is missing some replication logs that is not on the master any more. One day's replication logs seem very tight to me, unless you have major disk space issues. My team keeps 14 days.

Good book to read on the topic:
http://www.amazon.com/MySQL-High-Availability-Building-Centers/dp/0596807309/ref=sr_1_1

There are also some other interesting options like using Galera replication, e.g:
http://www.percona.com/software/percona-xtradb-cluster

I agree if you get a write error on an RO slave (which should not happen in normal operation), best to rebuild the slave.
 
gkm said:
Having a couple of days worth of log files makes it easier to start slaving again if a slave drops off, instead of having to rebuild the slave if it is missing some replication logs that is not on the master any more. One day's replication logs seem very tight to me, unless you have major disk space issues. My team keeps 14 days.

Good book to read on the topic:
http://www.amazon.com/MySQL-High-Availability-Building-Centers/dp/0596807309/ref=sr_1_1

There are also some other interesting options like using Galera replication, e.g:
http://www.percona.com/software/percona-xtradb-cluster

I agree if you get a write error on an RO slave (which should not happen in normal operation), best to rebuild the slave.
Click to expand...

This :(
 
My log files are 100MB with 10 days expiry. I think that default is fine for us but will monitor with heavy load.

That percona-xtradb-cluster looks very interesting indeed :)

I just added auto-increment-offsets to both servers so that auto-inc keys do not clash.
It is working very well currently.
 
Looks like percona-xtradb-cluster needs conversion of myisam tables...
 
DrJohnZoidberg said:
This :(
Click to expand...

Why the sad face?

Ro87 said:
Looks like percona-xtradb-cluster needs conversion of myisam tables...
Click to expand...

Yes, if you have multi-master it is very important to sort out your increment offsets, otherwise you are going to be very sad. Also, most cases I have seen, only one of the masters is set to write-able, the other one have read_only set to true ("ON"). If you then wish to switch masters, you first make the current master read_only, then check that replication is up to date on both sides, then set read_only "OFF" on the other server that must now be your RW master. If you have both masters as write-able and have a conflicting update, your replication blows up in a very bad way. You do not ever want to go there. Galera clusters like the Percona one is a different story, with multiple writable masters being possible and I guess usually desirable.

If you have any myisam tables, the cluster would not be the only reason to consider conversion in my opinion. :)
 
gkm said:
Yes, if you have multi-master it is very important to sort out your increment offsets, otherwise you are going to be very sad. Also, most cases I have seen, only one of the masters is set to write-able, the other one have read_only set to true ("ON"). If you then wish to switch masters, you first make the current master read_only, then check that replication is up to date on both sides, then set read_only "OFF" on the other server that must now be your RW master. If you have both masters as write-able and have a conflicting update, your replication blows up in a very bad way. You do not ever want to go there. Galera clusters like the Percona one is a different story, with multiple writable masters being possible and I guess usually desirable.

If you have any myisam tables, the cluster would not be the only reason to consider conversion in my opinion. :)
Click to expand...

Thanks this is really good info :)
I have both set to writable currently because I was going for some sort of seamless failover route.
Will investigate the read_only flag.
I also like the idea of one server handling writes and another handling reads, like load balancing.
 
Lets talk about replication mode.
I am using statement based replication currently.
Anyone using mixed mode replication?
 
Ro87 said:
Thanks this is really good info :)
I have both set to writable currently because I was going for some sort of seamless failover route.
Will investigate the read_only flag.
I also like the idea of one server handling writes and another handling reads, like load balancing.
Click to expand...

I am pretty sure you are going to be sad if you are going to be writing to both masters in a normal dual master MySQL setup, unless the stuff you write is perfectly split between the hosts so that you never have any chance of updating the same row on both servers, causing a replication clash.

With the read_only flag you can open connections to both servers and only write to the one where read_only is off and if your writes suddenly fail because the connection became read_only, switch to the other one or some solution like that. I suspect you might find code on the internet that does all that for your programming language of choice.

Alternatively, sounds like what you want needs one of the clustering solutions where it is safe(ish) to write to multiple servers sharing the same data. Just be sure to read the doc for the solution you choose to be aware of any limitations doing this.

The replication mode you choose depends on what kind of writing you do. For example, small update statements that hit a lot of rows works better with statement based replication, while statements that hits a small number of rows (especially at the end of a complex where clause) works better with row based replication. If it doubt, probably go for row based replication, rather than statement based replication.
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X