June 25, 2012
by tapasmishra
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 – ssh-keygen -t rsa
Then scp $HOME/.ssh/id_rsa.pub user@dbserver@1 hostname:$HOME
ssh user@dbserver’s hostname
cat id_rsa.pub >> $HOME/.ssh/authorized_keys
Repeat the steps for dbserver@2
Step-1:
Create a script Ex: vi /etc/mysqaltunnel.sh
Add these lines to the file…
#!/bin/bash
node1=(192.168.2.101)
node2=(192.168.2.100)
nodestatus1=$(telnet $node1 3306 < /dev/null | wc -l)
nodestatus2=$(telnet $node2 3306 < /dev/null | wc -l)
mailuser=”tapas.mishra@wordpress.com”
if [ $nodestatus1 -eq 1 ]; then
pidnode1=`ps aux | grep -v grep | grep “ssh -f -L 3306:$node1:3306 root@$node1 -N” | awk ‘{print $2}’`
kill -9 $pidnode1
fi
if [ $nodestatus2 -eq 1 ]; then
pidnode2=`ps aux | grep -v grep | grep “ssh -f -L 3306:$node2:3306 root@$node2 -N” | awk ‘{print $2}’`
kill -9 $pidnode2
echo “The script found Secondary Node is down. However trying to connect the Primary Node.” | mail -s Secondary Node is unstable $mailuser
fi
if
ps aux | grep -v grep | grep “ssh -f -L 3306:$node1:3306 root@$node1 -N”; then
echo “Process is running”
exit
else
if
ps aux | grep -v grep | grep “ssh -f -L 3306:$node2:3306 root@$node2 -N”; then
echo “Process is running”
exit
else
echo “Starting the Primary Node”
if [ $nodestatus1 -eq 3 ]; then
ssh -f -L 3306:$node1:3306 root@$node1 -N
exit
else
echo “Node is unstable”
echo “The script found Primay Node is down. However trying to connect the Secondary Node.” | mail -s Primary Node is unstable $mailuser
echo “Trying with Secondary Node”
if [ $nodestatus2 -eq 3 ]; then
ssh -f -L 3306:$node2:3306 root@$node2 -N
exit
else
echo “Both the nodes are unstable”
echo “The script found both nodes are down. Please chek manually and fix the problem.” | mail -s Primary Node is unstable $mailuser
fi
fi
fi
fi
exit
Step-2:
Create a separate file which will run with nohup and in that file we will run the mysqltunnel.sh in loop.
vi /etc/nohup.sh
Add the below lines in nohup.sh
#!/bin/bash
while(true)
do
/bin/sh /etc/mysqltunnel.sh 1>&2
sleep 1;
done
Step-3
Now we are ready to run the scripts. Just have to follow two more steps.
chmod +x /etc/mysqltunnel.sh
chmod +x /etc/nohup.sh
Now we are ready to run the script.
nohup sh /etc/nohup.sh > custom-out.log &
Now test the script by stooping the service MySQL in both server one by one.
Have a Luck :)