Master/Master/Slave Replication

Question

Just trying to get some information on the best route to setup replication for this DB I've got. I'm using MySQL 5.1

I found this and it seems like the idea we have in mind.

We're looking to have the Main Master be replicated by a Master/Slave and then a final machine replicating from the Master/Slave. That solution sounds wonderful and turning on --log-slave-updates on the Master/Slave machine sounds like what I want.

The question I have is when the Main Master(Master1) goes down we tell the services to write to Master/Slave(Master2) and then what happens with Master1? We rebuild it etc.. but where in the link does it go? Does Master/Slave(Master2) become the new Main Master? And does the Slave only machine get moved to Master/Slave?

Or does Master1 go down and gets rebuild and just becomes Master2(Master/Slave) keeping the Slave machine just that, a slave?

I feel like it's the latter of the 2 options. Just looking for some advice.

Thanks!

Answer 1

Using the topology you mentioned from the MySQL Documentation

Let's setup the first scenario

EXAMPLE IPs for Each DB Server

• Master1 : 10.20.30.40
• Master2 : 10.20.30.50
• Slave_1 : 10.20.30.60
• replication user is repluser@'%'
• replication password is replpass

Make sure binary logging is enabled on all the Slaves

We will do the following

• Promote Master2 to Master1
• Promote Slave_1 to Master2
• Demote Master1 to Slave1

Step 01 : On Master2, run the following

mysql> SET GLOBAL read_only = 1;
mysql> STOP SLAVE;
mysql> RESET SLAVE;
mysql> CHANGE MASTER TO master_host='';
mysql> FLUSH TABLES;
mysql> SET GLOBAL read_only = 0;

Step 02 : Move your CNAME/VIP to Master2

Step 03 : On Slave_1, run mysql> RESET MASTER; FLUSH TABLES;

Step 04 : On Slave_1, dump the data

NEW_MASTER_HOST="10.20.30.60"
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
MYSQLDUMP_OPTIONS="--single-transaction"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --routines"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --triggers"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --master-data=1"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --all-databases"
echo "STOP SLAVE;" > MySQLData.sql
echo "CHANGE MASTER TO master_host='${NEW_MASTER_IP}'," >> MySQLData.sql
echo "master_port=3306," >> MySQLData.sql
echo "master_user='repluser'," >> MySQLData.sql
echo "master_password='replpass'," >> MySQLData.sql
echo "master_log_file='bogus'," >> MySQLData.sql
echo "master_log_pos=1;" >> MySQLData.sql
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} >> MySQLData.sql
echo "START SLAVE;" >> MySQLData.sql
gzip MySQLData.sql

Step 05 : When Master1 comes back up, rsync or scp MySQLData.sql.gz from Slave_1 to Master1

Step 06 : Login to MySQL on the Master1 and setup it up to replicate from Slave_1

Don't worry about the real binary log filename and position.

Using --master-data=1 embeds CHANGE MASTER TO command with the real coordinates on line 23 of a standard dump.

You can see it with the following

less MySQLData.sql.gz | head -35 | tail -1

Step 07 : Load the data into Master1

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
gzip -d < MySQLData.sql.gz | mysql ${MYSQL_CONN}

Step 08 : Login to MySQL on Master1

mysql> SHOW SLAVE STATUS\G

and Make sure Slave_IO_Running and Slave_SQL_Running both say Yes

EPILOGUE

When Done, the topology should be

• Master1 : 10.20.30.50
• Master2 : 10.20.30.60
• Slave_1 : 10.20.30.40

DISCLAIMER

Please try this out on Test Servers before deploying to Production

GIVE IT A TRY !!!

Note: I would advise that you have multiples slaves

• One for Nightly Backups
• The others for load balancing SELECTs

Answer 2

Plan A: The topology as drawn by Rolando:

If Master 2 goes down, you have a mess.
If Master 1 goes down, you have no valid slaves until you fix the links.

Plan B: Dual master, single writer:

Slave(s) <- Master1 <-> Master2 -> MoreSlave(s)

AND Write to only one of the Masters at a time. That will minimize the repair steps.

Plan C: MHA

Master -> Slaves

MHA will monitor the system and promote one of the Slaves if the Master dies. It also handles all the CHANGE MASTER calls.

Plan D: Galera (Percona XtraDB Cluster or MariaDB 10 or hand-rolled on Oracle)

3 or more "Nodes" replicating every-which way. Write to any node. If any node goes down, the "cluster" continues running. When you add a node (such as by fixing the crashed one), the cluster automatically repairs itself, even if it means cloning all the data.

Furthermore, if your nodes are in 3 geographic locations, you have HA, even against earthquakes, floods, tornadoes, etc.

In my opinion, D is better than C is better than B is better than A.