Mysql – Client to use the slave when the master is down in MySQL replication

MySQLmysql-5.1mysql-workbenchreplication

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?

Best Answer

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

Circular Replication
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

STEP01 : Activate Binary Logging on the Old Server

Step01-a) Add this to /etc/my.cnf on the Master

[mysqld]
server-id=101120
log-bin=mysql-bin

Step01-b) # service mysql restart

After STEP01, you should see mysql-bin.000001 and mysql-bin.index in /var/lib/mysql

STEP02 : Perform mysqldump

On the Master, you can mysqldump the data and record from what point in time it happened.

# service mysql restart --skip-networking --skip-grants
# mysqldump --single-transaction --master-data=2 --all-databases --routines --triggers > MySQLData.sql
# service mysql restart

What this does is record the Master Log File and Position the moment the mysqldump started as a comment. You can visibly see it when you view line 22:

# head -22 MySQLData.sql | tail -1

STEP03 : Load the mysqldump into the Slave

Execute the mysql client loading the mysqldump into the Slave's mysql instance

# mysql -h10.1.1.30 -uroot -p < MySQLData.sql

STEP04 : Create MySQL Replication User on the Master

# mysql -uroot -p -e"GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replicator'@'10.64.51.%' IDENTIFIED BY 'replpassword'"

STEP05 : Setup the Slave with a Separate Server ID

Add this to /etc/my.cnf on the Slave

[mysqld]
server-id=101130

and # service mysql restart

STEP06 : Setup the Replication on the Slave

Goto the mysql client and run the following command

mysql> CHANGE MASTER TO
    MASTER_HOST='10.1.1.20',
    MASTER_PORT=3306,
    MASTER_USER='replicator',
    MASTER_PASSWORD='replpassword',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=1;

STEP07 : Setup the point-in-time Master Log and Position

Back in STEP02, I mentioned viewing the point-in-time position using

# head -22 MySQLData.sql | tail -1

You should see something like this:

#CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000015', MASTER_LOG_POS=122957100;

Run it as command in the mysql client on the Slave

mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000015', MASTER_LOG_POS=122957100;

Afterwards, run this command

mysql> SHOW SLAVE STATUS\G

You should see something like this:

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.64.113.232
                  Master_User: replicant
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000015
          Read_Master_Log_Pos: 122957100
               Relay_Log_File: relay-bin.003666
                Relay_Log_Pos: 122957100
        Relay_Master_Log_File: mysql-bin.000015
             Slave_IO_Running: No
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 106
              Relay_Log_Space: 106
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
1 row in set (0.00 sec)

STEP08 : Launch Replication

Start up replication with this:

mysql> START SLAVE;

Afterwards, run this command again

mysql> SHOW SLAVE STATUS\G

If you see this

             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

CONGRATULATIONS, MySQL Replication is Working !!!