Replication channel failover is discussed in the MySQL manual and it is (amazingly) easy to do. When the primary replication channel fails, all you need to do is some SQL queries, do a CHANGE MASTER TO on the new slave, START SLAVE and it should be replicating again.
Lots of MySQL Cluster users have probably already figured out this is also very useful if you need to do a rolling upgrade of your Master MySQL Cluster, or upgrading or rebooting the machine it is running on. For these operations you do not need to completely change the replication channel or move to another slave. It's possible, but why work more if you can do with less?
The MySQL manual describes what to do, but here it is again summarized in a few points:
- Stop the Slave SQL Node
- Get the last epoch on this Slave SQL Node
- Using this epoch, get the binary log file and position on the new Master SQL Node
- On the Slave SQL Node, use CHANGE MASTER TO with the file and log position from previous point
- Start the Slave SQL Node, and it should work
Few notes:
- You need to have 2 SQL nodes on the Master MySQL Cluster which are configured to do binary logging. If you haven't, please do ASAP.
- It's good to temporize between steps 2 and 3, like 10 seconds.
- Yes, SQL Node is in this case a mysqld process.
The following Python functions are just examples, but hopefully they useful for somebody (full script not provided but it is using MySQL Connector/Python!):
def _get_last_epoch(slave):
c = slave.cursor()
stmt = "SELECT MAX(epoch) AS lastepoch FROM mysql.ndb_apply_status"
c.execute(stmt)
row = c.fetchone()
c.close()
return row[0]
def _get_binary_log_info(master, lastEpoch):
c = master.cursor()
stmt = """SELECT SUBSTRING_INDEX(File, '/', -1), Position
FROM mysql.ndb_binlog_index
WHERE epoch > %s
ORDER BY epoch ASC LIMIT 1
"""
c.execute(stmt, (lastEpoch,))
row = c.fetchone()
c.close()
return row
def _change_master(slave,masterHost=None,masterPort=None,logFile=None,logPos=None):
stmtArgs = [];
params = ();
if masterHost:
stmtArgs.append('MASTER_HOST=%s')
params += (masterHost,)
if masterPort:
stmtArgs.append('MASTER_PORT=%s')
params += (masterPort,)
if logFile:
stmtArgs.append('MASTER_LOG_FILE=%s')
params += (logFile,)
if logPos:
params += (logPos,)
stmtArgs.append('MASTER_LOG_POS=%s')
c = slave.cursor()
stmt = "CHANGE MASTER TO %s" % ', '.join(stmtArgs)
c.execute(stmt, params)
c.close()
PlanetMySQL Voting: Vote UP / Vote DOWN