ChapTEr 8 ■ DEmonsTraTion of high availabiliTy TEChniquEs

CREATE DATABASE read_scaling;

CREATE TABLE read_scaling.current_slave(server_id INT, host CHAR(30), port INT);

Note: You must also have a replication topology of a master and at least

two slaves for the demo output to be meaningful. In fact, running this

on a master with no slaves will result in an error.

from future import print_function
import mysql.connector as mysql
import operator
import sys
import time

LOCK_TABLE = "LOCK TABLES read_scaling.current_slave WRITE"
SET_SERVER = "UPDATE read_scaling.current_slave SET server_id={0},host='{1}',port={2}"
UNLOCK_TABLE = "UNLOCK TABLES"
GET_CURRENT_SERVER = "SELECT server_id FROM read_scaling.current_slave"

master = {
'user': 'root',
'password': 'root',
'host': 'localhost',
'port': 13001,
}

class select_read_server(object):
def init(self):
self.servers = []
self.cur_server_id = -1;

Get the list of servers from the master and save the host,

port, and server id.

def _get_server_list(self):
conn = mysql.connect(**master)
cur = conn.cursor()
cur.execute("SHOW SLAVE HOSTS")

Save only the id, host, and port

for row in cur.fetchall():
server = {
'id': row[0],
'host': row[1],
'port': row[2],
}
self.servers.append(server)

order the servers by server_id

cur.close()
conn.close()
self.servers.sort(key=operator.itemgetter('id'))

Set the server in the database

def _set_server(self, id, host, port):