Writing MySQL Shell Plugin (REPORT)

MySQL Shell is a powerful tool.  It allows Javascript, Python and SQL access to MySQL.

Usage of MySQL Shell can be
1. As admin tool to MySQL Server
2. As Scripting Engine to run Javascript or Python or SQL to access MySQL Server
3. As a tools to allow Document Store access (like collection API) with MySQL Server
4. As MySQL InnoDB Cluster admin Tooling

Customization and extensible framework is essential to a tool as such DB administrators can create customized report and extension to do the daily job.

This tutorial is written to provide a short summary how we can create a MySQL Shell Report in Python.

What is MySQL Shell Report
MySQL Shell report can be executed thru "\show"


 There are 3 'report's coming with MySQL Shell - namely 'query', 'thread', 'threads'.

Query Report :
Getting the "help" with a report can simply to run "\show query --help"


Running a Report with a connected session as such the query is executed and printed on the screen (e.g. Running "SELECT 1" query)





Creating a New Report in Python - DB Size

MySQL Shell allows customization based on the $HOME/.mysqlsh/plugins/ folder

When MySQL Shell is launched, it looks for all initialization script (init.py / init.js) within the "plugins" folder.

File : $HOME/.mysqlsh/plugins/ext/dbutil/init.py

---

# The function definition is given as follows - show_dbsize (  3 arguments ) def show_dbsize(session, args, options):   query = "SELECT table_schema AS 'Database', ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)', count(table_name) as '# of tables' FROM information_schema.TABLES GROUP BY table_schema"   if (options.has_key('limit')):     query += ' limit ' + str(options['limit'])   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))   return {"report": report} # Register the show_dbsize function as a MySQL Shell report shell.register_report("dbsize", "list", show_dbsize,         {         "brief":"To list the tables without Primary Key.",         'details': ['You need the SELECT privilege on INFORMATION_SCHEMA.TABLES and INFORMATION_SCHEMA.STATISTICS.'],         'options': [             {                 'name': 'limit',                 'brief': 'The maximum number of rows to return.',                 'shortcut': 'l',                 'type': 'integer'             }         ],         'argc': '0'     } )

---

Once the file is created under the $HOME/.mysqlsh/plugins/ext/dbutil/init.py, MySQL Shell looks up NEW report and the "\show" shows the added "dbsize" report.



With a CONNECTED session, and Running the report can be simply to execute "\show dbsize"



There is OPTION "limit" which we put into the Python init.py.  To show only 3 lines, we can add option (-l 3) as shown as follows.



Enjoy Reading!