2

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!

Improve this question
1
  • 1
    You have to rebuild the master after such failover.
    – jkavalik
    May 21, 2016 at 16:24

2 Answers 2

Reset to default
1

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.

Improve this answer
0

Were log_slave_updates and log_bin turned on before the split? If so, then a CHANGE MASTER should be sufficient to make the old Master be a Slave to the New Master.

If not, keep this in mind for the future -- use Dual-Master, Single Writer so that failover is a little easier.

Meanwhile (if either of those was OFF), you must dump the new Master to rebuild the old one.

Or you could try to use pt-table-sync to figure out the differences and fix them.

(My favorite multi-master, HA solution is Galera, which is found in MariaDB and PXC.)

Improve this answer
7
  • In case , if PN DB is down and there are some transactions which are not yet replicated to Slave . Can we copy those unreplicated logs from PN and get it executed to SN ? is it possible ??? can we use mysqldump with read logs from server ? what are your views here ? once these logs got executed on slave , we will not have any data loss
    – simplifiedDB
    May 7, 2017 at 12:33
  • @user21546 - You are describing the main flaw in "asynchronous" replication. Let's take the problem a step further: What if the Master committed some work, but crashed before the Slave could grab it and you cannot recover the Master's disk.
    – Rick James
    May 7, 2017 at 15:52
  • this is one step ahead of what i ask ? what you think about my approach which i wrote in my comment ?
    – simplifiedDB
    May 8, 2017 at 7:53
  • Your step is complex, but probably possible. (I have not tried such, nor would I want to have to.) My "one step ahead" is to suggest that you would be doing a lot of work that might be in vain.
    – Rick James
    May 8, 2017 at 14:05
  • my above approach got failed because mysqlbinlog cannot copy binlogs from Primary node (PN) if PN is down. So i have copied the unreplicated logs from PN and get the delta part executed . What do you think about this approach ? If you dont prefer then why so ?
    – simplifiedDB
    May 9, 2017 at 6:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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