How to configure MySQL Failover using a bash script

The quick and easy solution for MySQL failover is Master Slave Replication.But in reality it needs some support for failover. Because if you are using Master Slave Replication for your application and suddenly the Master Node dies, then how your application will know about this? You have to change the database configuration file again to point it to Slave Node if Master Node died.
To solve this problem I wrote a simple bash script which will watch the instances and serve the DB through SSH tunnel to the application server. You just need to run this bash script through nohup.
One more thing you have to configure before running the script is to generate a ssh key in the app server and have to add the key in the DB Server’s authorized_keys file.
Command to generate ssh keys in app server is –

Repeat the steps for dbserver@2

Step-1:
Create a script Ex:

Add these lines to the file…

Step-2:
Create a separate file which will run with nohup and in that file we will run the mysqltunnel.sh in loop.

Add the below lines in nohup.sh

Step-3
Now we are ready to run the scripts. Just have to follow two more steps.

Now we are ready to run the script.

Now test the script by stopping the service MySQL in both server one by one.
Have a Luck 🙂

Share and Enjoy

  • Facebook
  • Twitter
  • Delicious
  • LinkedIn
  • StumbleUpon
  • Add to favorites
  • Email
  • RSS
The following two tabs change content below.

Tapas Mishra

Sr. Engineer (DevOps)
Loves to work on Opensource products. Having experience on Linux environment. Knowledge on Public cloud services like AWS, Rackspace, DigitalOcean, Linode. Please don't hesitate to give a comment on the posts. Your comments are my strength.

Leave a Reply