1

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!

  • Which RDBMS are you using? MySQL? Which version? – James Anderson Jun 4 '15 at 19:35
  • MySQL 5.1. Sorry I should have specified that. I will edit Post to reflect. – Sezotove Jun 4 '15 at 20:28
2

Using the topology you mentioned from the MySQL Documentation

sxjn

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
  • This is exactly what I was looking for! Thanks Rolando! 1 Question though - Maybe I've missed something... The final topology after promoting and demoting Master_1 you have Master1 : 10.20.30.60 Master2 : 10.20.30.50 Slave_1 : 10.20.30.40 Should it not be 50, 60, 40? – Sezotove Jun 5 '15 at 17:17
  • You are right. I just fixed :-) – RolandoMySQLDBA Jun 5 '15 at 17:20
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.