Failover on master/slave replication

--=-wyC/yhtjkTk7qtjJFr/x
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: 7bit

Good day all

I am hoping someone has had some experience in this to assist
me.

I have been going through lots of forums and documentation and
can find a lot of information on how to setup a master/slave replication
on MySQL.

The question I have however is how do you fail over to the slave
server in the event that the master server is unavailable and then how
to revert back to the master server once the server is available again.

This is one thing I can't seem to get much info on unless I am
looking in the wrong places.


I would really appreciate help on this one.


Regards
Machiel

--=-wyC/yhtjkTk7qtjJFr/x--

Re: Failover on master/slave replication

Quoting Machiel Richards :

>
> The question I have however is how do you fail over to the slave
> server in the event that the master server is unavailable and then how
> to revert back to the master server once the server is available again.
>

Hi,

to fail over to the slave, you dont need to do anything (except be
sure that replication is off). That is, tell your apps etc to use the
slave IP/hostname, change the slave server IP to that of the master or
update DNS so that all connections are made to the slave instead of
the master.

To reinstate the master after a failover, you must copy your live
database (now on slave) to the master and reinitiate replication
pretty much as you did when replication was initially setup. Copying
the live DB can be achieved via restoring a backup (from the live
slave) or you could look at Maakit which can analyse table differences
and copy only changed data (useful for very large DBs, Ive not
personally used it tho).

Thats about it I think, unless anyone wants to correct me on that...

thanks Andy.




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Failover on master/slave replication

--20cf301cc4ba5d2f250492f5fe50
Content-Type: text/plain; charset=ISO-8859-1

That's pretty much it, indeed. You need to make absolutely sure that no more
connections can be made to the old, broken master, though - even if you have
to physically pull the network or power cable. Failover services refer to
this as STONITH: Shoot The Other Node In The Head.

Don't think "but it went down anyway, that's why I failed over" - sometimes
the service unexpectedly manages to recover itself after you've switched;
and any data that gets inserted into the wrong system is as good as lost,
unless you want to go through a manual reconciliation before reinitialising
the replication.

On Tue, Oct 19, 2010 at 11:55 AM, wrote:

> Quoting Machiel Richards :
>
>
>> The question I have however is how do you fail over to the slave
>> server in the event that the master server is unavailable and then how
>> to revert back to the master server once the server is available again.
>>
>>
> Hi,
>
> to fail over to the slave, you dont need to do anything (except be sure
> that replication is off). That is, tell your apps etc to use the slave
> IP/hostname, change the slave server IP to that of the master or update DNS
> so that all connections are made to the slave instead of the master.
>
> To reinstate the master after a failover, you must copy your live database
> (now on slave) to the master and reinitiate replication pretty much as you
> did when replication was initially setup. Copying the live DB can be
> achieved via restoring a backup (from the live slave) or you could look at
> Maakit which can analyse table differences and copy only changed data
> (useful for very large DBs, Ive not personally used it tho).
>
> Thats about it I think, unless anyone wants to correct me on that...
>
> thanks Andy.
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=vegivamp@tuxera.be
>
>


--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--20cf301cc4ba5d2f250492f5fe50--

Re: Failover on master/slave replication

--0016367f99826f07c20492f6475e
Content-Type: text/plain; charset=ISO-8859-1

You may also want to take a look at MySQL MMM which makes use of
Active/passive masters to makes MySQL failover very simple.



On 19 October 2010 11:45, Johan De Meersman wrote:

> That's pretty much it, indeed. You need to make absolutely sure that no
> more
> connections can be made to the old, broken master, though - even if you
> have
> to physically pull the network or power cable. Failover services refer to
> this as STONITH: Shoot The Other Node In The Head.
>
> Don't think "but it went down anyway, that's why I failed over" - sometimes
> the service unexpectedly manages to recover itself after you've switched;
> and any data that gets inserted into the wrong system is as good as lost,
> unless you want to go through a manual reconciliation before reinitialising
> the replication.
>
> On Tue, Oct 19, 2010 at 11:55 AM, wrote:
>
> > Quoting Machiel Richards :
> >
> >
> >> The question I have however is how do you fail over to the slave
> >> server in the event that the master server is unavailable and then how
> >> to revert back to the master server once the server is available again.
> >>
> >>
> > Hi,
> >
> > to fail over to the slave, you dont need to do anything (except be sure
> > that replication is off). That is, tell your apps etc to use the slave
> > IP/hostname, change the slave server IP to that of the master or update
> DNS
> > so that all connections are made to the slave instead of the master.
> >
> > To reinstate the master after a failover, you must copy your live
> database
> > (now on slave) to the master and reinitiate replication pretty much as
> you
> > did when replication was initially setup. Copying the live DB can be
> > achieved via restoring a backup (from the live slave) or you could look
> at
> > Maakit which can analyse table differences and copy only changed data
> > (useful for very large DBs, Ive not personally used it tho).
> >
> > Thats about it I think, unless anyone wants to correct me on that...
> >
> > thanks Andy.
> >
> >
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe: http://lists.mysql.com/mysql?unsub=vegivamp@tuxera.be
> >
> >
>
>
> --
> Bier met grenadyn
> Is als mosterd by den wyn
> Sy die't drinkt, is eene kwezel
> Hy die't drinkt, is ras een ezel
>



--
John Daisley

Certified MySQL 5 Database Administrator
Certified MySQL 5 Developer
Cognos BI Developer

Telephone: +44 (0)7918 621621
Email: john.daisley@butterflysystems.co.uk

--0016367f99826f07c20492f6475e--

RE: Failover on master/slave replication

The short answer is this: You need special software outside of MySQL to do
this. This software has to

- Keep a heartbeat going between the two servers. Whether or not you do this
by checking only MySQL, or the machines themselves, depends upon what other
applications might be running (web server, etc.)
- In the event of a failure, it should cause a shutdown of the primary server
MACHINE.
- The secondary MACHINE should adopt the IP address of the failed primary.

Switching the machines back to the original configuration is done (or at least
initiated) by hand, because you need to identify and fix whatever the problem
was.

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: jerry@gii.co.jp
Web site: www.the-infoshop.com


>-----Original Message-----
>From: Machiel Richards [mailto:machielr@rdc.co.za]
>Sent: Tuesday, October 19, 2010 5:15 AM
>To: mysql mailing list
>Subject: Failover on master/slave replication
>
>Good day all
>
> I am hoping someone has had some experience in this to assist
>me.
>
> I have been going through lots of forums and documentation and
>can find a lot of information on how to setup a master/slave replication
>on MySQL.
>
> The question I have however is how do you fail over to the slave
>server in the event that the master server is unavailable and then how
>to revert back to the master server once the server is available again.
>
> This is one thing I can't seem to get much info on unless I am
>looking in the wrong places.
>
>
> I would really appreciate help on this one.
>
>
>Regards
>Machiel




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Failover on master/slave replication

On Tue, Oct 19, 2010 at 19:06, John Daisley wrote:
> You may also want to take a look at MySQL MMM which makes use of
> Active/passive masters to makes MySQL failover very simple.
+10000 We could not do much of our daily work without MMM. It makes
the whole HA/Failover thing a breeze. We have it in use at many of our
customers and wouldhave a much harder time managing them (and
failures) without it. Don't try to do failovers by hand, use MMM
instead.

Cheers,

--
Walter Heck
Engineer @ Open Query (http://openquery.com)
Exceptional services for MariaDB and MySQL at a fixed budget

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org