Re: MySQL Replication, Failover and high availability with JDBC - Frank Gates - com.mysql.lists.java

9 messages in com.mysql.lists.javaRe: MySQL Replication, Failover and h...

From	Sent On	Attachments
Martin	01 Oct 2004 04:24
Alec...@Quantel.Com	01 Oct 2004 06:10
Martin	01 Oct 2004 06:38
Alec...@Quantel.Com	01 Oct 2004 07:41
Frank Gates	01 Oct 2004 09:23
Eric	01 Oct 2004 09:30
Bruce Dembecki	01 Oct 2004 09:57
Frank Gates	01 Oct 2004 10:22
Martin	04 Oct 2004 00:58

Subject:	Re: MySQL Replication, Failover and high availability with JDBC
From:	Frank Gates (fga...@yahoo.com)
Date:	10/01/2004 09:23:10 AM
List:	com.mysql.lists.java

Hi Alec,

Just a question about the ex-master reappearing... Do you suspend the current Master or queue the writes until the ex-master is synchronized with the current master? My assumption is that there will be significant time for the ex-master to drop its tables and reload from the current master and that new inserts and updates will still be ongoing on the current master which may not be replicated onto the ex-master unless the updates are queued. I realize that this may be a question due to my ignorance about replication.

Frank

----- Original Message ----- From: <Alec...@Quantel.Com> To: <lis...@mstsoft.com> Cc: <ja...@lists.mysql.com> Sent: Friday, October 01, 2004 8:10 AM Subject: Re: MySQL Replication, Failover and high availability with JDBC

The way I handle this is as follows:

Both MySQL systems start up *without* the slave thread running. It is up to the monitoring application to check the two systems, decide which was most recently master and slave by checking their slave status, and start the slave thread on the appropriate one of the two machines.

When the Master fails, the monitoring application stops the ex-slave thread on the slave processor, changes it to have no master and routes all write accesses to it, so that the ex-slave is now master. As it does so, it increments a counter in a special table in the slave.

When the ex-master reappears, the monitoring application determines, by reading the counter, that it is less up-to-date than the current Master. It therefore drops all the databases on the ex-Master, changes it to become a slave of the new Master by using CHANGE MASTER TO, and copies the Master database by doing LOAD DATA FROM MASTER. When this completes, the new slaves slave tread is started and replication resumes. This is generalisable to an N-way linear, not circular, replication.

Alec

Martin <lis...@mstsoft.com> wrote on 01/10/2004 12:24:54:

Hi List,

i am playing with MySQL Replication and automatic failover and would appreciate input on this topic. I haven´t been able to find something about how to manage the following setup in the documentation, so as I think this is a recurring issue, maybe you could point me somewhere?

What I am trying to do is:

* I have two (n) MySQL Servers * They both have binary logging enabled and log-slave-updates * Both have a replication user * One is the Master, the other is the slave

* I access the Databases with an application that uses the JDBC Driver and the failover feature * When the slave database fails, i can restore it from the binary log, thats great * When the master database fails, the Application fails over to the slave

So far, so good. What i want now is, if the Master failes and failover occured, i want my application to write to the slave (which works well) and my Administrator to reconfigure the setup, so the old master will become a slave of the old slave (pingpong ;-). I read this somewhere and

don`t remember where it was. After that, operation can continue as normal. I did not lose any but one write and didn´t have any downtime when reading. I have been trying to do this, but i am unsure about several things:

* What will be the exact procedure of recovery for the crashed node? In my opinion it is the same than setting up replication, one will have to stop the running server to create a consistent snapshot and a position in the binlog. The downtime will not be that long. Ok, this is not really the issue.

* Will the JDBC Driver failback? Can i tell him not to? What happens if the Driver will see the Admin bringing up the crashed server, before it is restored or the setup changed? It will fail back and i have big trouble. In my opinion the Driver should fail over only when a server fails and stay there until this one fails and then try the next one in the list... that is, round-robin-failover.

* Can i prevent the Driver to throw a SQLException when it does a SELECT

Statement and the node crashes? I understand there has to be an Exception when the crash occurs within a transaction. Or does this normally not happen and my testing code is faulty?

Has anyone been doing this somewhen and can tell me something about this

kind of setup? Are there any hooks for external Code in the Connector/J Codebase so that i maybe could modify the failover routines and not change the Implementation of the driver? I know, it´s open source, but i

don`t really want to taint it.

Thank you for your help & input,