Technology sharing how to write MySQL Shell plug-ins

Author: Hong bin The person in charge and technical service director of akerson South District, MySQL ACE, is good at database architecture planning, fault diagnosis, performance optimization analysis, with rich practical experience, helping customers in various industries to solve MySQL technical problems, and providing customers in finance, operators, Internet and other industries with MySQL overall solutions. Source: reproduced from official account - fun MySQL *Aikesheng is produced by the open source community. The original content cannot be used without authorization. Please contact the editor for reprint and indicate the source.

MySQL Shell is not only used to deploy InnoDB cluster and Replica set. After 8.0.17, it has the ability of custom extension. DBAs can write extensions with javascript and python, encapsulate some common SQL scripts into their own toolbox, and even build the DBA's DevOps operating platform around it.

Adding extensions to MySQL Shell is not complicated. There are two existing extension methods, one is report, which is mainly used for various queries. You can use two built-in commands to call custom reports, and the other is plugin, which can define any function function.

  • \show is a common one-time output. Many common indicators have been built in. Please refer to the brain chart.
  • \watch is similar to the top mode. It is more convenient to continuously output information and write simple monitoring scripts.

Custom extension

Put the customized JS or py script in the directory ~ /. mysqlsh/plugin and ~ /. mysqlsh/init.d. It is recommended to put it in the plugin directory. The directory can be named according to the function category. The directory must have init.py Or init.js The file is used to initialize the extension. In the code, the top-level directory name can be used as the global object and the secondary directory as the member object.

hongbin@MBP ~/.m/plugins> tree
.
└── ext
└── table
└── init.py

2 directories, 1 file

Here is an example code

# init.py
# -------
# Demonstrate two ways to register report and plugin
# Define a query function to get a table with no primary key or unique index
def report_table_without_pk(session):
    query = '''SELECT tables.table_schema , tables.table_name
                FROM information_schema.tables
                LEFT JOIN (
                SELECT table_schema , table_name
                FROM information_schema.statistics
                GROUP BY table_schema, table_name, index_name HAVING
                    SUM( case when non_unique = 0 and nullable != 'YES' then 1 else 0 end ) = count(*) ) puks
                ON tables.table_schema = puks.table_schema and tables.table_name = puks.table_name
                WHERE puks.table_name is null
                AND tables.table_type = 'BASE TABLE' AND Engine="InnoDB";'''
    result = session.run_sql(query)
    report = []
    if (result.has_data()):
        report = [result.get_column_names()]
        for row in result.fetch_all():
            report.append(list(row))
  # Register as report, need to return dictionary type
    return {"report": report}

# The function is the same as above. Here is a demonstration of redefining the function in plugin f mode. The difference between the two is mainly in the output mode
def plugin_table_without_pk(session):
    query = '''SELECT tables.table_schema , tables.table_name
                FROM information_schema.tables
                LEFT JOIN (
                SELECT table_schema , table_name
                FROM information_schema.statistics
                GROUP BY table_schema, table_name, index_name HAVING
                SUM( case when non_unique = 0 and nullable != 'YES' then 1 else 0 end ) = count(*) ) puks
                ON tables.table_schema = puks.table_schema and tables.table_name = puks.table_name
                WHERE puks.table_name is null
                AND tables.table_type = 'BASE TABLE' AND Engine="InnoDB";'''
    result = session.run_sql(query)
    shell.dump_rows(result)
    return

# Register a function to add a primary key field to the table. The function is usually used for the change class operation
def _add_pk(table, columns, session=None):
    query = 'ALTER TABLE %s ADD PRIMARY KEY (%s)' % (table, columns)
    if session is None:
        session = shell.get_session()
        if session is None:
            print("No session specified. Either pass a session object to this "
                  "function or connect the shell to a database")
            return
    # session = shell.get_session()
    result = session.run_sql(query)

# Register the report function, report name, return format type, function name and description defined above
shell.register_report("table_without_pk", "list", report_table_without_pk, {
                      "brief": "Lists the table without primary key."})

# The global object defined here can be regarded as a namespace. ext is the object name of the default community extension, or other custom object names
if 'ext' in globals():
    global_obj = ext
else:
    # Otherwise register new global object named 'ext'
    global_obj = shell.create_extension_object()
    shell.register_global("ext", global_obj,
                          {"brief": "MySQL Shell extension plugins."})

# Sub objects can be added under global objects by category
try:
    plugin_obj = global_obj.table
except IndexError:

    plugin_obj = shell.create_extension_object()
    shell.add_extension_object_member(global_obj,
                                      "table",
                                      plugin_obj,
                                      {"brief": "Utility object for table operations."})

# Add function function to custom plug-in object, parent object name, call function name, defined function, description, parameter name / type accepted by function / required / description
try:
    shell.add_extension_object_member(plugin_obj,
    "add_pk",
    _add_pk,
    {"brief":
    "Add a primary key to the table",
    "parameters": [
        {
            "name": "table",
            "type": "string",
            "required": True,
            "brief": "table name."
        },
        {
            "name": "columns",
            "type": "string",
            "required": True,
            "brief": "column name."
        },
        {
            "name": "session",
            "type": "object",
            "class": "Session",
            "required": False,# If you do not want to pass the session parameter alone, you can get the current session object in the function
            "brief": "The session to be used on the operation."
        }
    ]
    })

except Exception as e:
    shell.log("ERROR", "Failed to register ext.table.add_pk ({0}).".format(
        str(e).rstrip()))

# Add plugin_table_without_pk
try:
    shell.add_extension_object_member(plugin_obj,
    "get_without_pk",
    plugin_table_without_pk,
    {"brief":
    "Lists the table without primary key.",
    })
except Exception as e:
    shell.log("ERROR", "Failed to register ext.table.get_without_pk ({0}).".format(
        str(e).rstrip()))

usage method

After logging in mysqlsh, automatically search and initialize the plug-in. When the -- log level parameter is specified, detailed debugging information can be recorded to ~ /. mysqlsh/mysqlsh.log , if the load fails, you can view the log analysis reason.

For report, use the \ show or \ watch command to specify the report name to output the result.

For plugin, call the function directly.

Here is a brief introduction to the MySQL Shell plug-in extension function, more interesting usage to be found.

reference resources: https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-plugins-examples.html https://github.com/lefred/mysqlshell-plugins

Tags: Database shell Session MySQL

Posted on Thu, 28 May 2020 02:07:16 -0700 by huzefahusain