Python implements mysql read/write splitting and load balancing, and pythonmysql

Python implements mysql read/write splitting and load balancing, and pythonmysql

Oracle Database has a supporting rac developed by its company to achieve load balancing. Currently, the maximum number of known nodes can reach 128, but the maintenance cost is undoubtedly very high, in addition, rac stability is not particularly ideal, especially when many nodes exist.

However, compared with mysql, rac is more practical than mysql's supporting cluster software mysql-cluster. As you can see from the Internet, few companies are using mysql-cluster. Most companies choose third-party Proxy software, such as MySQL Proxy, Mycat, and haproxy, but this causes another problem: single point of failure (including mysql-cluster: Management node ). To solve this problem, you need to build a cluster for the agent software. when the traffic is very high, the dual-host or three-host cluster of the agent software will become the access bottleneck and continue to increase the number of nodes, it will undoubtedly bring about various costs.

So how can we solve this problem?

The best way to solve the above problem is to implement it in the program. This idea was also confirmed through communication with other mysql DBAs. But the question is: Will it increase development costs? Will modifications to the existing application system be significant? Will it increase the difficulty of later version upgrades? And so on.

An Application System with a well-designed architecture can answer the following question: No.

So how can we calculate an application system with a well-designed architecture?

To put it simply, the layer is reasonable, and functional modules are coupled. In my own experience, the system design can basically be divided into the following four layers:

1. entity layer: mainly defines some entity classes

2. Data Layer: it can also be called the SQL processing layer. Primarily responsible for interacting with the database to obtain data

3. Business: differentiate modules (or define different business classes) based on business processes and functions)

4. Presentation Layer: present the final result to the user

The preceding functions (mysql read/write splitting and Server Load balancer) only involve the data layer.

Strictly speaking, for a well-designed system, only one function of a class is involved: In the data layer, a connection class is generally divided separately, in addition, there is a connection function in this connection class, which needs to be changed: add a function before reading the connection string to return the required host, ip address, port number, and other information. (It may be difficult for developers to understand this section ).

The flowchart is as follows:

The Code is as follows:

Import mmapimport jsonimport randomimport mysql. connectorimport time # public variable # dbinfos = {# "db0": {'host': '2017. 168.42.60 ', 'user': 'root', 'pwd': 'abc1234', 'My _ user': 'root', 'My _ pwd': 'abcd. 1234 ', "port": 3306, "database": "", "role": "RW", "weight": 10, "status": 1 }, # "db1": {'host': '100. 168.42.61 ', 'user': 'root', 'pwd': 'abc1234', 'My _ user': 'root', 'My _ pwd': 'abcd. 1234 ', "port": 3306, "database": "": "R", "weight": 20, "status": 1 ##} Dbinfos ={} mmap_file = Nonemmap_time = None ## this function returns a string in json format, which is also a place for initializing database information ## json format is used to facilitate data conversion, from string --- binary -- string --- dictionary # If you use other methods to share dbinfos, do not use this method # configure the library address def get_json_str1 (): return json. dumps (dbinfos) # Read def get_json_str (): try: global dbinfos cnx = mysql. connector. connect (user = 'root', password = 'abcd. 1234 ', host = '2017. 168.42.60 ', database = 'rwlb') cursor = cnx. cursor () Explain string = "select * From rwlb "cnt =-1 cursor.exe cute (route string) for (host, user, pwd, my_user, my_pwd, role, weight, status, port, db) in cursor: cnt = cnt + 1 dict_db = {'host': host, 'user': user, 'pwd': pwd, 'My _ user': my_user, 'My _ pwd ': my_pwd, "port": port, "database": db, "role": role, "weight": weight, "status ": status} dbinfos ["db" + str (cnt)] = dict_db cursor. close () cnx. close () return json. dumps (dbinfos) before T: cursor. close () cnx. close () r Eturn "" # determine whether the database can be normally connected to def check_conn_host (): try: cnx = mysql. connector. connect (user = 'root', password = 'abcd. 1234 ', host = '2017. 168.42.60 ', database = 'rwlb') cursor = cnx. cursor () selected string = "select user ()" cnt =-1 cursor.exe cute (selected string) for user in cursor: cnt = len (user) cursor. close () cnx. close () return cnt failed T: return-1; # select is a read operation. Others are write operations. Here we can divide them into more details, such as def analyze_ SQL _state (SQL) for execution of stored procedures): If "select" in SQL: return "R" else: return "W" # Read time information def read_mmap_time (): global mmap_time, mmap_file mmap_time.seek (0) # inittime = int (mmap_time.read (). translate (None, B '\ x00 '). decode () # current time endtime = int (time. time () # time Difference dis_time = endtime-inittime print ("dis_time:" + str (dis_time) # re-read data if dis_time> 10: # print (str (check_conn_host () if check_conn_host ()> 0: print ("re Ad data again ") mmap_time.seek (0) hour (0) mmap_time.write (B '\ x00') mmap_file.write (B' \ x00') get_mmap_time () get_mmap_info () else: print ("can not connect to host") # do not re-read data else: print ("do not read data again") # read Information from memory, def read_mmap_info (SQL): read_mmap_time () print ("The data is in memory") global mmap_file, dict_db mmap_file.seek (0) # convert binary to The string info_str = mmap_file.read (). translate (None, B '\ x00 '). decode () #3 converts a string to json format to convert it to a dictionary later. Use infos = json. loads (info_str) host_count = len (infos) # weight list listw = [] # total weight quantity wtotal = 0 # database role dbrole = analyze_ SQL _state (SQL) # initialize a list based on the weight. This is a relatively simple algorithm, so the weight and control are better at less than 100 ---- Here you can choose other better algorithms for I in range (host_count ): db = "db" + str (I) if dbrole in infos [db] ["role"]: if int (infos [db] ["status"]) = 1: w = infos [db] ["weight"] wtotal = wtotal + w for j in range (w): listw. append (I) if wtotal> 0: # generate a random number rad = random. randint (0, wtotal-1) # Read the data dbindex = listw [rad] # determine which db = "db" + str (dbindex) is selected) # assign a value to dict_db, that is, the information of the selected db dict_db = infos [db] return d Ict_db else: return {}## if there is no time information in the memory, write the time information def get_mmap_time () to the memory Red (): global mmap_time # The second parameter 1024 is the set memory size, in bytes. If the content is large, you can tune mmap_time = mmap. mmap (-1, 1024, access = mmap. ACCESS_WRITE, tagname = 'share _ Time') # Read the number of valid bits, excluding empty bits cnt = mmap_time.read_byte () if cnt = 0: print ("Load time to memory") mmap_time = mmap. mmap (0, 1024, access = mmap. ACCESS_WRITE, tagname = 'share _ Time') inittime = str (int (time. time () mmap_time.write (inittime. encode () # if there is no corresponding information in the memory, write the information to the memory for the next call. def get_mmap_info (): global mmap_file # Second Parameter 1024 is the set memory size, in bytes. If the content is large, you can tune mmap_file = mmap. mmap (-1, 1024, access = mmap. ACCESS_WRITE, tagname = 'share _ mmap') # Read the number of valid bits, excluding empty bits cnt = mmap_file.read_byte () if cnt = 0: print ("Load data to memory") mmap_file = mmap. mmap (0, 1024, access = mmap. ACCESS_WRITE, tagname = 'share _ mmap') mmap_file.write (get_json_str (). encode () # test function def test1 (): get_mmap_time () get_mmap_info () for I in range (10 ): SQL = "select * from db" # SQL = "update t set col1 = a where B = 2" dbrole = analyze_ SQL _state (SQL) dict_db = read_mmap_info (SQL) print (dict_db ["host"]) def test2 (): SQL = "select * from db" res = analyze_ SQL _state (SQL) print ("select:" + res) SQL = "update t set col1 = a where B = 2" res = analyze_ SQL _state (SQL) print ("update:" + res) SQL = "insert into t values (1, 2) "res = analyze_ SQL _state (SQL) print (" insert: "+ res) SQL =" delete from t where B = 2 "res = analyze_ SQL _state (SQL) print (" delete: "+ res) # similar to the main function if _ name __= =" _ main _ ": test2 ()

Test results:

From the results, we can see that only data is loaded to the memory for the first time, and load balancing is achieved by weight.

Because the test function test1 () writes a fixed statement, the read/write splitting result is not displayed.

In addition, the database table structure and data used for testing are as follows:

 desc rwlb;+---------+-------------+------+-----+---------+-------+| Field | Type  | Null | Key | Default | Extra |+---------+-------------+------+-----+---------+-------+| host | varchar(50) | YES |  | NULL |  || user | varchar(50) | YES |  | NULL |  || pwd  | varchar(50) | YES |  | NULL |  || my_user | varchar(50) | YES |  | NULL |  || my_pwd | varchar(50) | YES |  | NULL |  || role | varchar(10) | YES |  | NULL |  || weight | int(11)  | YES |  | NULL |  || status | int(11)  | YES |  | NULL |  || port | int(11)  | YES |  | NULL |  || db  | varchar(50) | YES |  | NULL |  |+---------+-------------+------+-----+---------+-------+select * from rwlb;+---------------+------+----------+---------+-----------+------+--------+--------+------+------+| host   | user | pwd  | my_user | my_pwd | role | weight | status | port | db |+---------------+------+----------+---------+-----------+------+--------+--------+------+------+| 192.168.42.60 | root | Abcd1234 | root | Abcd.1234 | RW |  10 |  1 | NULL | NULL || 192.168.42.61 | root | Abcd1234 | root | Abcd.1234 | R |  20 |  1 | NULL | NULL |+---------------+------+----------+---------+-----------+------+--------+--------+------+------+

Summary

The above section describes how to implement mysql read/write splitting and load balancing in python. I hope it will be helpful to you. If you have any questions, please leave a message, the editor will reply to you in time!