IT Tips and Tricks

I have been doing replicating from master to Slave for quite a while and sometimes found it a bit limited especially when the master in down and ‘writes’ need to be done to the databases. Here is a method where no matter which server is down you can always write to it and the changes will get replicated to the other one. Even if the other server is down, the changes will get synchronized automatically when it comes back up.
Note: This method only works between 2 MySQL servers where each server is the master and the slave of the other.
This method has proven very stable no matter which server is brought down and back up, the replication has always worked well.
Very useful to make backups of all databases on both servers without any service downtime.

Here I will name the 2 servers: A-server(192.168.0.84) and B-server(192.168.0.85).
Install standard Mysql server in both servers:
apt-get install mysql-server mysql-client
Create a replication user replicant and add a password for it.
Make sure the name given (here is replicant given)and passwords are the same on both servers.
This will be used to allow loop replication between the 2 servers.
In those commands below only the replication privileges are given to the replication user.
mysql -uroot -p
(enter the MySQL root pawssowrd)
mysql> CREATE USER 'replicant'@'%' IDENTIFIED BY 'ChangeMe';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replicant'@'%';
mysql> GRANT REPLICATION CLIENT ON *.* TO 'replicant'@'%';
mysql> GRANT SUPER ON *.* TO 'replicant'@'%';
mysql> FLUSH PRIVILEGES;
mysql> quit;

In A-server:/etc/mysql/my.cnf
After the ‘Login and Replication’ section add the following lines:

#------------------------------- MASTER area ---------
server-id = 84
# ------------------------------ SLAVE area ----------
slave-skip-errors=1045,126,1062,1242
relay-log = /var/log/mysql/relay.log
relay-log-info-file = /var/log/mysql/relay-log.info
relay-log-index = /var/log/mysql/relay-log.index
# ----------------------------------------------------
log-bin = /var/log/mysql/mysql-bin.log
log-error = /var/log/mysql/mysql-bin.err
log-bin-index = /var/log/mysql/log-bin.index

In B-server:/etc/mysql/my.cnf
After the ‘Login and Replication’ section add the following lines:

#------------------------------- MASTER area ---------
server-id = 85
# ------------------------------ SLAVE area ----------
# Preventing Replica-stop when duplicates are replicated
slave-skip-errors=1045,126,1062,1242
relay-log = /var/log/mysql/relay.log
relay-log-info-file = /var/log/mysql/relay-log.info
relay-log-index = /var/log/mysql/relay-log.index
# ----------------------------------------------------
log-bin = /var/log/mysql/mysql-bin.log
log-error = /var/log/mysql/mysql-bin.err
log-bin-index = /var/log/mysql/log-bin.index


Start both MySql servers and issue the following command to see if the replication is already in sync.
It might be helpful beforehand to issue a few SQL writes commands to one of the servers to see if the replication does work.


echo "SHOW SLAVE STATUS\G;" | mysql --host=192.168.0.84 --port=3306 -u replicant --password=ChangeMe | egrep "Slave_IO_State:" | head -n 1 | cut -d: -f2
echo "SHOW SLAVE STATUS\G;" | mysql --host=192.168.0.85 --port=3306 -u replicant --password=ChangeMe | egrep "Slave_IO_State:" | head -n 1 | cut -d: -f2

If all is good then both results should be: Waiting for master to send event
If you want to see the whole information regarding the replication, issue the following command:
echo "SHOW SLAVE STATUS\G;" | mysql --host=192.168.0.84 --port=3306 -u replicant --password=ChangeMe
echo "SHOW SLAVE STATUS\G;" | mysql --host=192.168.0.85 --port=3306 -u replicant --password=ChangeMe
The replication is doing good when the following information in the results is found:
Waiting for master to send event
Read_Master_Log_Pos should be the same value as for Exec_Master_Log_Pos
Example:
Read_Master_Log_Pos: 106
Exec_Master_Log_Pos: 106
That should imply as well the following result:
Seconds_Behind_Master: 0