Design of query function for master-slave architecture of database cluster


According to any IP or domain name, it is a very common and essential function in DBA operation and maintenance to find the cluster architecture, whether it is the master database or the slave database. The implementation of the function described in this paper has been applied to an Internet company. At present, it runs stably and provides services continuously. Here, I’d like to share with you how to realize it and learn with you.

1. Preconditions
In order to find all cluster architectures in a unified way, these machines need to have a unified account and password that can access all the machines. DB is used here_ In addition, the master-slave synchronization needs to use a unified account and password. Here, repl is used.
As an example, the following cluster structure is used here:
Design of query function for master-slave architecture of database cluster

Here, a, B, C are the slave database of the master database, which are the primary slave database; D and E are the slave database of the slave database C, and they are the secondary slave database. If there are other more complex structures, so on.

2. Search and search
Usually, you can search any IP or domain name. Because the domain name cannot be marked in the cluster, you need to convert the domain name to IP for searching. According to any IP search, you will not know its location in the cluster. In order to get the structure of the whole cluster, you need to backtrack to the main database of the whole cluster.

3. Backtracking the main database
DB is used in backtracking master database_ Login MySQL with admin account and execute the “show slave status” command to find the main database information of the current database for sorting.
For the previous cluster structure, the common backtracking types are as follows:
Design of query function for master-slave architecture of database cluster

Design of query function for master-slave architecture of database cluster

Design of query function for master-slave architecture of database cluster

Finally, the main library instance is to be obtained. The implementation code of Python is as follows:

# curr_ Node current search node
#Lisense unified access account and password
# sub_ Node child node
def traceback_root(curr_node, lisense, sub_node):
    query_process = 'SHOW SLAVE STATUS'
    master_row = None
    conn = None
        #Connect to the current database to find the main database information
        conn = get_connection(
            curr_node.get('ip'), curr_node.get('port'),
            lisense.get('account'), lisense.get('passwd'))
        with open_cursor(conn) as cursor:
            master_row = cursor.fetchone()

    except Exception as e:
        return None

    if master_row:
        return curr_node
    #Read main database information
    io_run = master_row.get('Slave_IO_Running', '')
    sql_run = master_row.get('Slave_SQL_Running', '')
    master_ip = master_row.get('Master_Host', '')
    master_port = master_row.get('Master_Port', '')

    #Check the status of the master database. If the status is not correct, return to the current node
    if io_run.lower() == 'yes' and sql_run.lower() == 'yes':
        may_master_node = dict()
        may_master_node['ip'] = master_ip
        may_master_node['port'] = master_port

        #Check whether the parent node instance information is the same as the child node,
        #It is used to avoid the double master database backtracking falling into the wireless loop
        if sub_node and sub_node.get('ip', '') == master_ip  and\
                sub_node.get('port', '') == master_port:
            return None

        #The parent node of the current library is only the possible primary database because of the dual master situation,
        #Use the parent node to continue backtracking the master database instance
        master_node = traceback_root(
            may_master_node, lisense, curr_node)
        #If it traces back to the master database, it returns; otherwise, it returns the current node
        if master_node:
            return master_node
            return curr_node

            return curr_node

4. Traverse the slave library
After finding the master database, start from the master database and find all the slave databases under the master database. Find slave library using DB_ Admin logs in to MYSQL to execute the “show processlist” command, and then filters the results according to whether the user is a repl or not. If the user is a repl slave database, connect to it and use the method used by the backtracking master database to verify whether the master database of the slave database is the database instance of the current query. If yes, join the cluster; otherwise, give up. For the found slave libraries, continue to use the above method to find the slave libraries until all the slave libraries are found.

5. Return to the cluster structure
The found cluster structure is organized in JSON format, which can be returned to the required services or displayed on the page after sorting. The following figure shows a simple display of the whole cluster structure found:
Design of query function for master-slave architecture of database cluster

6. Conclusion
This is the general process of the whole query. There are not too many coding details. In the actual use process, it can be adjusted according to the needs, such as adding multi threads to improve the query speed.

Recommended Today

Summary of recent use of gin

Recently, a new project is developed by using gin. Some problems are encountered in the process. To sum up, as a note, I hope it can help you. Cross domain problems Middleware: func Cors() gin.HandlerFunc { return func(c *gin.Context) { //Here you can use * or the domain name you specify c.Header(“Access-Control-Allow-Origin”, “*”) //Allow header […]