Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is it possible for the client to use the slave when the master is down in mysql replication?

I have already setup a simple mysql replication having a master and a slave.

The client connects to the master to access the database, any changes made to the master server is replication onto the slave. How shall I configure that when the master server is down, the client automatically connects to the slave server?

I am using MySQL Server 5.1 and MySQL workbench 5.2 CE. I edited the my.ini files for both master and slave.

The client uses a J2EE application.

What additional commands must I input in the master my.ini file to make the changes?

Also is there a way to increase or decrease the time interval between replications?

share|improve this question
    
Hi Dwayne. Welcome to the site! Is it possible to expand the second aspect of your question into it's own question? "Also is there a way to increase or decrease the time interval between replications?" We like to keep questions as one question per thread. –  Derek Downey Feb 16 '12 at 15:17

2 Answers 2

Since you have Master/Slave, you may want to implement the use of two things

  1. Circular Replication
  2. Database Virtual IP (aka DBVIP)

Circular Replication

Circular Replication is nothing more than first setting up Master/Slave then performing the same steps using the Slave as the Master's Master and the Master as the Slave's Slave. It just entails

  • explicitly user a different server_id on each DB Server
  • enabling binary logging on both DB servers
  • making sure the replication user is defined on both DB servers

Database Virtual IP (aka DBVIP)

There are products you can download and install to setup a DBVIP. One such product I use is ucarp. Another product is Linux Heartbeat. I normally do not use such things with MySQL Circular Replication or Master/Slave. Why?

Since those products can perform automatic failover, you do not want to do that in the event a Slave is some number of seconds behind in replication lag.

You should perform manual failovers.

Here is a poor man's approach to implementing DBVIP management.

Suppose you have this setup

  • DB Server1 has IP 10.1.2.30
  • DB Server2 has IP 10.1.2.40
  • You want to use DBVIP 10.1.2.50

Create the Script called /usr/local/sbin/MyAppDBVIP like this

echo echo 10.1.2.50 > /usr/local/sbin/MyAppDBVIP

Create the Script called /usr/local/sbin/dbvip-up

DBVIP=`/usr/local/sbin/MyAppDBVIP`
ip addr add ${DBVIP}/24 dev eth1

Create the Script called /usr/local/sbin/dbvip-down

DBVIP=`/usr/local/sbin/MyAppDBVIP`
ip addr del ${DBVIP}/24 dev eth1

Make sure all scripts are executable

chmod +x /usr/local/sbin/MyAppDBVIP
chmod +x /usr/local/sbin/dbvip-up
chmod +x /usr/local/sbin/dbvip-down

Make sure these script exist on both DB Servers

Simply run dbvip on whichever server you choose. .

So the failover process and protocol are the following:

  • Run dbvip-down on the DB Server that has the DBVIP. If you cannot
  • Run dbvip-up on the DB Server that you want to have the DBVIP
  • Just remember you should not run dbvip-up on both machines
  • After running dbvip-up, restart apache, JBoss, or any other app server contacting MySQL via the old Master
share|improve this answer

In what way do you mean 'to use the slave when the master is down'? When the master goes down, the process of switching the slave to become the master is what we call 'failover'.

But once your application starts using the old slave in this failover scenario, it becomes the new master. When you get the old master online again it must be set up as a slave, because your data has changed.

Failover for your situation (a single slave) is going to be mainly handled in your application. You will need to implement logic to check that the application is connecting. If it is not, have the application connect to the slave instead.

A few things to keep in mind:

  • Again, once your application connects to the slave and modifies data, you need to ensure it never tries to connect to the master again.
  • You will probably want to have some alert monitoring setup (using Nagios or some other suite), so you know when the master fails.
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.