MySQL Replication – Setup Master-Master-Slave Replication

MySQLreplication

I am trying to set up a MySQL replication architecture between 3 MySQL servers by following a tutorial.

Server A as a master to Server B
Server B as a slave of server A and a master for server C
Server C as a slave of server B.

Using this architecture, the goal is if any data will be changed on Server A, both the server B and C must be updated.

After doing some tests, I got the following results:

Replication between server B and A is working well.
Any change on server A is mirrored on B but not in server C.
Testing if replication is working between server B and C (by manually editing some information in server B data), I was surprised that everything is working well, data edited in server B was edited in server C.

I can't understand why if Server A is edited and B is following it server C in that case is not following?

Best Answer

First of all, I dislike your setup. When Server B fails, the Server C will not be in replication anymore. If you want to be safe, that the data is on every server in sync, you should try to use a cluster. A cluster is minimum 3 servers and would fit your environment.

Next question for me is, why don't you use just one master (Server A) and two slaves (B+C)?

To answer your question, you need to activate the relay log, so that all changes to the slave are stored for the other slaves:

# REPLICATION slave to other slaves #
log-slave-updates              = 1
relay-log                      = /var/lib/mysql/relay-bin
slave-net-timeout              = 60

Related Solutions

MySQL Replication – How to Setup Master/Slave in MySQL 5.5.20

Please create this script

Fron the Linux prompt, open up an editor like vi

vi /root/CreateMySQLSlave.sh

and add these lines to it

MYSQL_MASTER_HOST=10.1.2.20
MYSQL_SLAVE_HOST=10.1.2.30
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
MYSQL_MASTER_CONN="-h${MYSQL_MASTER_HOST} ${MYSQL_CONN}"
MYSQL_SLAVE_CONN="-h${MYSQL_SLAVE_HOST} ${MYSQL_CONN}"

MYSQLDUMP_OPTIONS="--master-data=1"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --single-transaction"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --routines"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --triggers"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --flush-privileges"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --all-databases"

CREATE_REPL_USER="GRANT REPLICATION SLAVE ON *.* TO repluser@'%' IDENTIFIED BY 'replpass'"
mysql ${MYSQL_MASTER_CONN} -AN -e"${CREATE_REPL_USER}"

RELOAD_FILE=/root/MySQLData.sql
echo "STOP SLAVE;" > ${RELOAD_FILE}
echo "CHANGE MASTER TO master_host='${MYSQL_MASTER_HOST}'," >> ${RELOAD_FILE}
echo "master_port=3306," >> ${RELOAD_FILE}
echo "master_user='repluser'," >> ${RELOAD_FILE}
echo "master_password='replpass'," >> ${RELOAD_FILE}
echo "master_log_file='dummy-file'," >> ${RELOAD_FILE}
echo "master_log_pos=1;" >> ${RELOAD_FILE}
mysqldump ${MYSQL_MASTER_CONN} ${MYSQLDUMP_OPTIONS} >> ${RELOAD_FILE}
echo "START SLAVE;" >> ${RELOAD_FILE}

mysql ${MYSQL_SLAVE_CONN} -A < ${RELOAD_FILE}

Next, make the file executable and run it

chmod +x /root/CreateMySQLSlave.sh
/root/CreateMySQLSlave.sh

That's it !!!

Make sure you set the IP Address for MYSQL_MASTER_HOST and MYSQL_SLAVE_HOST

By the way, don't worry about the dummy-file in the master_log_file parameter for CHANGE MASTER TO. Line 29 of the dump file should have the correct setting there due to the --master-data=1. You can see it by doing this to the dump file

head -29 /root/MySQLData.sql | tail -1

Make sure binary logging is already enabled on the master. Here is how you can tell: run SHOW MASTER STATUS;. If you get nothing back, you need to enable it like this:

Add this to /etc/my.cnf

[mysqld]
log-bin=mysql-bin

then restart mysql

OK that takes care of binary logging for the Master.

You also need to make sure the Master and the Slave have different server IDs. Here is how you can tell:

Run SHOW VARIABLES LIKE 'server_id'; If the result comes up as 1, you must set it explicitly.

Goto the Master and add this to /etc/my.cnf

[mysqld]
server-id=1

Goto the Slave and add this to /etc/my.cnf

[mysqld]
server-id=2

The main thing here is to make sure server_id is explicitly set in my.cnf on both Master and Slave. You must also make sure the server_id values are different from all other servers involved in the replication topology.

If you are new to this, have a sysadmin join you to do this.

Mysql – Client to use the slave when the master is down in MySQL replication

Since you have Master/Slave, you may want to implement the use of two things

Circular Replication
Database Virtual IP (aka DBVIP)

Circular Replication

Circular Replication is nothing more than first setting up Master/Slave then performing the same steps using the Slave as the Master's Master and the Master as the Slave's Slave. It just entails

explicitly user a different server_id on each DB Server
enabling binary logging on both DB servers
making sure the replication user is defined on both DB servers

Database Virtual IP (aka DBVIP)

There are products you can download and install to setup a DBVIP. One such product I use is ucarp. Another product is Linux Heartbeat. I normally do not use such things with MySQL Circular Replication or Master/Slave. Why?

Since those products can perform automatic failover, you do not want to do that in the event a Slave is some number of seconds behind in replication lag.

You should perform manual failovers.

Here is a poor man's approach to implementing DBVIP management.

Suppose you have this setup

DB Server1 has IP 10.1.2.30
DB Server2 has IP 10.1.2.40
You want to use DBVIP 10.1.2.50

Create the Script called /usr/local/sbin/MyAppDBVIP like this

echo echo 10.1.2.50 > /usr/local/sbin/MyAppDBVIP

Create the Script called /usr/local/sbin/dbvip-up

DBVIP=`/usr/local/sbin/MyAppDBVIP`
ip addr add ${DBVIP}/24 dev eth1

Create the Script called /usr/local/sbin/dbvip-down

DBVIP=`/usr/local/sbin/MyAppDBVIP`
ip addr del ${DBVIP}/24 dev eth1

Make sure all scripts are executable

chmod +x /usr/local/sbin/MyAppDBVIP
chmod +x /usr/local/sbin/dbvip-up
chmod +x /usr/local/sbin/dbvip-down

Make sure these script exist on both DB Servers

Simply run dbvip on whichever server you choose. .

So the failover process and protocol are the following:

Run dbvip-down on the DB Server that has the DBVIP. If you cannot
Run dbvip-up on the DB Server that you want to have the DBVIP
Just remember you should not run dbvip-up on both machines
After running dbvip-up, restart apache, JBoss, or any other app server contacting MySQL via the old Master