list)]
random.shuffle(conn_read_list)
for conn in conn_read_list:
#如果不可达,继续寻找其他除了主节点之外的节点
if self.get_mysqlservice_status(conn["host"], conn["port"]):
current_conn = conn
break
else:
continue
try:
#从连接池中获取当前连接
if (current_conn):
pool = PooledDB(pymysql,20, host=current_conn["host"], port=current_conn["port"], user=current_conn["user"], password=current_conn["password"],db=current_conn["database"])
conn = pool.connection()
except:
raise
if not conn:
raise("create connection error")
return conn;
if __name__ == '__main__':
#定义三个实例
conn_1 = {'host': '127.0.0.1', 'port': 3306, 'user': 'root', 'password': 'root',"database":"db01","priority":100}
conn_2 = {'host': '127.0.0.1', 'port': 3307, 'user': 'root', 'password': 'root',"database":"db01","priority":200}
conn_3 = {'host': '127.0.0.1', 'port': 3308, 'user': 'root', 'password': 'root',"database":"db01","priority":300}
conn_list = []
conn_list.append(conn_1)
conn_list.append(conn_2)
conn_list.append(conn_3)
print("####execute update on master####")
myrouter = MySQLRouter(conn_list=conn_list, operation="write")
conn = myrouter.get_connection()
cursor = conn.cursor()
cursor.execute("update t01 set update_date = now() where id = 1")
conn.commit()
cursor.close()
conn.close()
print("####loop execute read on slave,query result####")
#循环读,判断读指向哪个节点。
for loop in range(10):
myrouter = MySQLRouter(conn_list = conn_list,operation = "read")
conn = myrouter.get_connection()
cursor = conn.cursor()
cursor.execute("SELECT id,cast(update_date as char), CONCAT('instance port is: ', CAST( @@PORT AS CHAR)) AS port FROM t01;")
result = cursor.fetchone()
print(result)
cursor.close()
conn.close()
这里用过服务器的一个优先级,将写请求指向最高优先级的master服务器,读请求随机指向非最高优先级的slave,
对于更新请求,都在master上执行,slave复制了master的数据,每次读到的数据都不一样,并且每次都请求的执