Mysql – Slave DB promoted as Master DB when Master DB is down. New data added on the new master while old master is down

failoverMySQL

I am currently setting up a call center system (Vicidial) having a Master DB and a Slave DB.
When the Master would go down, I have made a script on the SlaveDB that would execute "stop slave" on the slave mysql service and make it as a new master by pointing the other servers (Web, Dialer, Archive) to the slave as their new Master database server.

When the master is up again, I would run another script on the SlaveDB that would point the servers back to the original MasterDB and would let the slave DB become slave again to the original MasterDB by executing "start slave".

My question is ~
If there are new data added on the New Master while the original master is down, how do I replicate this data to the original master when it goes back up before I start demoting the new master to slave again?

Answers and advice/constructive criticisms will be much appreciated.

P.S. I am a newbie in Databases and scripting.

Thanks in advance!

Best Answer

As @jkavalik said with your current setup the only option you have is to recreate the master as the slave of the new master and do a switchback if necessary.

Keep in mind that you will have some data loss because you can have some unreplicated transactions on the failed master unless you enable semi-sync replication.

Sidenote: you don't have to write your own scripts for moving/promoting slaves if you don't want to. There are plenty of open source solutions which can do this for you. For example you can give a try to orchestrator. I used this in production setup with over 4000 servers.

Jetpants is also a popular toolset developed by Tumblr which can also handle cloning and many other common tasks.

But there are also other solutions if you are not satisfied with these.

Further down the road

If you want to have better HA and/or more automated way of operation you can look at the following solution. (this list is not complete only some pointers to give you a start)

Percona XtraDB cluster

With XtraDB cluster you can ensure consistency and have true HA with even under 100ms failover time (depending on your failover technology).

MySQL Fabric

It's Oracle's in house HA solution. Requires client libraries so make sure your application can handle it properly.

MySQL MMM

One of the old player on the MySQL HA market but worth to mention.

I hope this helps.

Related Solutions

Mysql – Adding a new slave slowing down previous replicas

Your new slave server can possibly be slow in some respect and not able to process the queries in the binary log as quickly as the other servers.

You should also look at the possibility that you may have run a very heavy query that caused the new slave to lag as well as the other slaves.

Regarding the new slave, you may have a performance problem that needs to be resolved. Possibilities could include:

Slow hard disks (you mentioned you are buying new ones)
Bad my.cnf configuration. Flushes too often to disk? flush method isn't O_DIRECT?
Long running queries from master server - consider using row-based replication if you feel it helps your overall system performance.
Caches on the new slave aren't warm for running the queries from the master - do you use INSERT INTO ... SELECT .. FROM statements? or non-deterministic insert statements like ones with sub-queries?
Raid card with battery - do the other servers have it and the slave doesn't?

Mysql – How to resolve the master server shut down/unavailability in thesql with master – slave replication

Before you perform any mysqldump to fully restore a Slave, you should consult the output of SHOW SLAVE STATUS\G. Let's start with a sample SHOW SLAVE STATUS\G:

mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 10.48.20.253
                Master_User: replicant
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000254
        Read_Master_Log_Pos: 858190247
             Relay_Log_File: relay-bin.066069
              Relay_Log_Pos: 873918
      Relay_Master_Log_File: mysql-bin.000254
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            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: 858190247
            Relay_Log_Space: 873772
            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
1 row in set (0.00 sec)

Please notice that there are two sets of replication coordinates from the Master

(Master_Log_File,Read_Master_Log_Pos)
(Relay_Master_Log_File,Exec_Master_Log_Pos)

There is a major difference between them

(Master_Log_File,Read_Master_Log_Pos) tells you the last binlog statement from the Master's log file and log position that the Slave read from the Master and placed in its Relay Logs.
(Relay_Master_Log_File,Exec_Master_Log_Pos) tells you the last binlog statement from the Master's log file and log position that the Slave read from the Master and placed in its Relay Logs THAT IS NEXT TO BE EXECUTED ON THE SLAVE.

The timestamps from these two coordinates helps you figure out Seconds_Behind_Master.

Knowing these things, here is what you can do:

Step 01) Run SHOW SLAVE STATUS\G
Step 02) Get Relay_Master_Log_File,Exec_Master_Log_Pos from SHOW SLAVE STATUS\G (In the sample, that would be (mysql-bin.000254,858190247)
Step 03) STOP SLAVE;
Step 04) CHANGE MASTER TO master_log_file='mysql-bin.000254',master_log_pos=858190247;
Step 05) START SLAVE;
Step 06) Wait 10 seconds
Step 07) Run SHOW SLAVE STATUS\G and check Seconds_Behind_Master

If the Seconds_Behind_Master is a number and eventaully drops to zero, replication is fully reesatablished.

After doing all this, if replication breaks because of a corrupt binary log from the master, then you do the last resort:

Steo 01) On the Master, RESET MASTER; to erase all binary logs and start with a new one
Step 02) On the Master, run this

This create proper dump for the slave

echo "STOP SLAVE;" > /root/MySQLData.sql
mysqldump --all-databases --routines --triggers --flush-privileges --master-data=1 >> /root/MySQLData.sql
echo "START SLAVE;" >> /root/MySQLData.sql

Step 03) scp /root/MySQLData.sql over to the Slave and Load it in MySQL on the Slave

Give it a Try !!!

Best Answer

Further down the road

Related Solutions

Mysql – Adding a new slave slowing down previous replicas

Mysql – How to resolve the master server shut down/unavailability in thesql with master – slave replication

Related Question