10.3.3 Example MySQL Shell Plugins

Example 10.3 MySQL Shell plugin containing a report and an extension object

This example defines a function show_processes() to display the currently running processes, and a function kill_process() to kill a process with a specified ID. show_processes() is going to be a MySQL Shell report, and kill_process() is going to be a function provided by an extension object.

The code registers show_processes() as a MySQL Shell report proc using the shell.register_report() method. To register kill_process() as ext.process.kill(), the code checks whether the global object ext and the extension object process already exist, and creates and registers them if not. The kill_process() function is then added as a member to the process extension object.

The plugin code is saved as the file ~/.mysqlsh/plugins/ext/process/init.py. At startup, MySQL Shell traverses the folders in the plugins folder, locates this init.py file, and executes the code. The report proc and the function kill() are registered and made available for use. The global object ext and the extension object process are created and registered if they have not yet been registered by another plugin, otherwise the existing objects are used.

# Define a show_processes function that generates a MySQL Shell report

def show_processes(session, args, options):
  query = "SELECT ID, USER, HOST, COMMAND, INFO FROM INFORMATION_SCHEMA.PROCESSLIST"
  if (options.has_key('command')):
    query += " WHERE COMMAND = '%s'" % options['command']

  result = session.sql(query).execute();
  report = []
  if (result.has_data()):
    report = [result.get_column_names()]
    for row in result.fetch_all():
        report.append(list(row))

  return {"report": report}


# Define a kill_process function that will be exposed by the global object 'ext'

def kill_process(session, id):
  result = session.sql("KILL CONNECTION %d" % id).execute()


# Register the show_processes function as a MySQL Shell report


shell.register_report("proc", "list", show_processes, {"brief":"Lists the processes on the target server.",
                                                       "options": [{
                                                          "name": "command",
                                                          "shortcut": "c",
                                                          "brief": "Use this option to list processes over specific commands."
                                                       }]})


# Register the kill_process function as ext.process.kill()

# Check if global object 'ext' has already been registered
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."})

# Add the 'process' extension object as a member of the 'ext' global object
try:
    plugin_obj = global_obj.process
except IndexError:
    # If the 'process' extension object has not been registered yet, do it now
    plugin_obj = shell.create_extension_object()
    shell.add_extension_object_member(global_obj, "process", plugin_obj,
        {"brief": "Utility object for process operations."})

# Add the kill_process function to the 'process' extension object as member 'kill'
try:
    shell.add_extension_object_member(plugin_obj, "kill", kill_process, {"brief": "Kills the process with the given ID.",
                                                              "parameters": [
                                                                {
                                                                  "name":"session",
                                                                  "type":"object",
                                                                  "class":"Session",
                                                                  "brief": "The session to be used on the operation."
                                                                },
                                                                {
                                                                  "name":"id",
                                                                  "type":"integer",
                                                                  "brief": "The ID of the process to be killed."
                                                                }
                                                                ]
                                                              })
except Exception as e:
    shell.log("ERROR", "Failed to register ext.process.kill ({0}).".
       format(str(e).rstrip()))

Here, the user runs the report proc using the MySQL Shell \show command, then uses the ext.process.kill() function to stop one of the listed processes:

mysql-py> \show proc
+----+-----------------+-----------------+---------+----------------------------------------------------------------------------------+
| ID | USER            | HOST            | COMMAND | INFO                                                                             |
+----+-----------------+-----------------+---------+----------------------------------------------------------------------------------+
| 66 | root            | localhost:53998 | Query   | PLUGIN: SELECT ID, USER, HOST, COMMAND, INFO FROM INFORMATION_SCHEMA.PROCESSLIST |
| 67 | root            | localhost:34022 | Sleep   | NULL                                                                             |
| 4  | event_scheduler | localhost       | Daemon  | NULL                                                                             |
+----+-----------------+-----------------+---------+----------------------------------------------------------------------------------+

mysql-py> ext.process.kill(session, 67)
mysql-py> \show proc
+----+-----------------+-----------------+---------+----------------------------------------------------------------------------------+
| ID | USER            | HOST            | COMMAND | INFO                                                                             |
+----+-----------------+-----------------+---------+----------------------------------------------------------------------------------+
| 66 | root            | localhost:53998 | Query   | PLUGIN: SELECT ID, USER, HOST, COMMAND, INFO FROM INFORMATION_SCHEMA.PROCESSLIST |
| 4  | event_scheduler | localhost       | Daemon  | NULL                                                                             |
+----+-----------------+-----------------+---------+----------------------------------------------------------------------------------+