MySQL replication status notification

Using the below simple bash script you can get an alert message from the replication server if replication is down or not working.

#!/bin/bash

PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin

#script checking the replication is running or not.

#If replication is down then sent the alert mail.

slave_server_hostname=192.168.77.2

###check if already notified###

cd /root

if [ -f replication_problem.txt ]; then

rm -rf /root/replication_problem.txt

exit 1;

fi

###Check if slave running or not?###

(

echo “show slave status \G;”

) | mysql -u username -h $slave_server_hostname -ppassword 2>&1 | grep “Slave_IO_Running: No”

if [ “$?” -ne “1” ]; then

echo “Replication Failed”

echo “Replication failed” > /root/replication_problem.txt

fi

###Send notification if replication is down###

cd /root

if [ -f replication_problem.txt ]; then

#mail -s “Replication problem” admin@mysqlhost.com < /root/replication_problem.txt

echo “Problem in replicaition”

fi

Setup this script in conrtab of Master server

#chmod +x /path/to/replicationstatus.sh

#crontab -e

* * * * * /path/to/replicationstatus.sh

How to Change the MySQL replication master host

Here’s a little post on how to change the master database host that the replication slave in a MySQL replication setup uses. This can happen if there is a change in network addresses or when you want to switch over to using different internal network. The main thing to keep in mind is that when you change the replication user host on the slave MySQL will think that you are now replicating from a different database. Therefore we need to force MySQL to continue replicating the changes where it left off before the change.

First we need to create a new user on the master database server with the new IP address like this:

GRANT REPLICATION SLAVE ON *.* TO ‘repl’@’10.0.0.1’ IDENTIFIED BY ‘password’;

The rest of the operations are on the replication slave. We need to stop the replication and figure out where to restart the replication after changing the user:

STOP SLAVE;
SHOW SLAVE STATUS\G

From the slave status pick out the 2 settings for Master_Log_File and Read_Master_Log_Pos. Those items will look something like this:

Master_Log_File: mysql-bin.000101
Read_Master_Log_Pos: 591523680

Those settings need to be used in the following statement:

CHANGE MASTER TO MASTER_HOST=’10.0.0.1′, MASTER_LOG_FILE=’mysql-bin.000101′, MASTER_LOG_POS=591523680;
START SLAVE;

The “start slave” command starts the replication again. Make sure you do a “show slave status” afterwards to make sure that replication is running again without errors.

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 – 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 :)

Steps to configure MySQL Master Slave Replication

Setup Master Configuration File:-

Step-1:-

Opne my.cnf file

vi /etc/mysql/my.cnf

comment lines like below

#skip-networking

#bind-address = 172.0.0.1

Step-2:-

We have to tell MySQL for which database it should write logs (these logs are used by the slave to see what has changed on the master)

Add these lines to my.cnf

log-bin = /var/lib/mysql/mysql-bin.log

binlog-do-db=database_name_to_replication

binlog-ignore-db=mysql

server-id=1

Step-3:-

Then we have to restart the MySQL server

/etc/init.d/mysql restart

Step-4:-

Then we have to login to mysql

mysql –u root –p

Create a user with “replication slave on” privileges

mysql> UNLOCK TABLES;

mysql> GRANT REPLICATION SLAVE ON *.* to ‘replication_user’@’%’ IDENTIFIED BY ‘password’;

mysql> FLUSH PRIVILEGES;

mysql> GRANT RELOAD ON *.* TO ‘replication_user’@’%’

mysql> GRANT SUPER ON *.* TO ‘replication_user’@’%’

mysql> FLUSH PRIVILEGES;

Then have to flush tables in our database:

USE database_name_to_replication;

FLUSH TABLES WITH READ LOCK;

SHOW MASTER STATUS;

You will see like this:-

+———————+———-+—————–+——————–+

| File                       | Position  | Binlog_Do_DB | Binlog_Ignore_DB |

+———————+———-+—————–+——————–+

| mysql-bin.000010  |      106   | db to replicate | mysql                  |

+———————+———-+—————–+——————–+

1 row in set (0.01 sec)

==============================================

Setup Slave configuration File:-

Step-1:-

Vi /etc/mysql/my.cnf

Add the following commands to the files

server-id=2

master-host = master_host_ip_address

master-user = replication_user

master-password = password

master-port = 3306

master-connect-retry=60

replicate-do-db=database_name_to_replication

Step-2:-

connect to MySQL

mysql

CREATE DATABASE database_name_to_replication;

Step-3:-

Restart mysql server

/etc/init.d/mysql restart

Again connect to the MySQL

Mysql

Run the bellow commands

mysql>STOP SLAVE;

mysql>RESET SLAVE;

mysql>START SLAVE;

mysql>SHOW SLAVE STATUS;