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: | Bruce Dembecki (bru...@liveworld.com) |
| Date: | 10/01/2004 09:57:34 AM |
| List: | com.mysql.lists.java |
I think one of the important things here is there are only two MySQL servers, which simplifies the solution dramatically.
Each mysql server can have a master. Use log-slave-updates (which you are already doing) and point each to slave from the other and turn replication on. The Binary log includes the server ID of the machine changing the data, and replication will not execute any changes made by by a machine with the same server ID as itself, preventing the creation of a never ending loop.
This technically works with more than two servers also, setting them up with in a circle, mysql1 -> mysql2 -> mysql3 -> mysql4 -> mysql1 sort of thing, but our experience with this type of setup is that it is fragile, and a break in the circle anywhere means the data everywhere can become incomplete. We really don't recommend you do this.
The two server replication pair described above is simple and reliable, it keeps both sets of data up to date even after a crash, and if the JDBC fails back to the original server (which it will do under most setups), you haven't got a problem, likely replication will be caught up before the JDBC fails back.
The various solutions relying on dumping the tables and loading them from the other server (and watch out here if you have a replication pair as I described, the table dump will happen on both servers and you are out of luck) don't work well on large databases, the time taken to load gigabytes of data (even across a high speed network) and the stress on the mysql servers isn't a pretty solution.
We actually run all our mysql servers in these replication pairs as described above, and run two application servers for each instance, one pointing to each database server. If a database server fails, so does it's application server, and the load balancers take it out of service. In addition we also run a mysql admin server, which simply slaves from one of the servers in the pair and keeps the data there. It is on this admin server we do things like run reports and backups and such that take time and server resources but don't affect production machines, thus avoiding the performance hit of these processes on our production machines. We actually run one mysql-admin server with multiple instances of mysqld running, each slaving from a different production server pair, that way we do all our reporting/backup in one place.
So you can run both your servers as masters for the other, and replicate between them, this is fast and clean and reliable as long as it is only being done in pairs. Building a replication circle larger than 2 servers can create problems.
Best Regards, Bruce
On 10/1/04 4:24 AM, "Martin" <lis...@mstsoft.com> wrote:
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,
martin