Writing mysqlsh Scripts

https://ift.tt/2NuTdxi

The new MySQL Shell or mysqlsh has provisions for loading user plugins in Python or JavaScript that are loaded when the shell starts up. I am just taking my initial steps into this area and wanted to share with you how easy it is to create a plug-in to produce a report.

The ability to write and run your own scripts for information that you want on a regular basis is very useful. The idea behind this was a discussion with a community member who had issues with a locked account that unknowingly locked and knowing when passwords where changed. This is a typical bit of work for a DBA that would be better automated and saved as a script for future uses.

The Query

The query collects several vital facets of information for dealing with passwords and accounts.

SELECT concat(User, ‘@’,Host) as User,

password_expired as Expired,

password_last_changed as Changed,

password_lifetime as Lifetime,

account_locked as Locked,

User_attributes FROM mysql.user

Nothing too exotic as far as a query goes.

The Code

Most of the Python code deals with registering the registering the passwordData function that houses the query as report named userList. The query itself is in purple below and the registration ‘boilerplate is in green. The shell.register_report is what ties the new function to the extension called userList.

# Define a passwordData function that generates a MySQL Shell report

def passwordData(session):

query = "SELECT concat(User, ‘@’,Host) as User, password_expired as Expired, password_last_changed as Changed, password_lifetime as Lifetime, account_locked as Locked, User_attributes FROM mysql.user"

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}

# Register the userList function as a MySQL Shell report

shell.register_report("userList", "list", passwordData, {"brief":"Lists the user password status on the target server."})

# 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 ‘userList’ extension object as a member of the ‘ext’ global object

try:

plugin_obj = global_obj.process

except IndexError:

The Report

The output is pretty much as expected and could use some careful tailoring. It would be nice to add information on how many days until a password expires and parsing the User_attributes field for key/values in a pretty fashion. But in a few minutes work I had a quick little utility that will save me time in the future.

JS > \show userList

+—————————-+———+———————+———-+——–+———————————————————————————-+

+—————————-+———+———————+———-+——–+———————————————————————————-+

| demo@localhost | N | 2020-04-09 10:42:06 | NULL | N | NULL |

| foobar@localhost | N | 2020-04-06 09:38:50 | NULL | N | {"Password_locking": {"failed_login_attempts": 3, "password_lock_time_days": 2}} |

| mysql.infoschema@localhost | N | 2020-01-13 11:57:33 | NULL | Y | NULL |

| mysql.session@localhost | N | 2020-01-13 11:57:33 | NULL | Y | NULL |

| mysql.sys@localhost | N | 2020-06-12 10:20:33 | NULL | Y | NULL |

| root@localhost | N | 2020-01-13 11:57:37 | NULL | N | NULL |

+—————————-+———+———————+———-+——–+—————————————————————————–

You Try

I recommended grabbing LeFred’s Github repo and reading through his code. You can create your own directory under your ~/.mysqlsh/plugins/ext/ directory and using the above code saved as init.py as an exercise.

I would love to see what similar scripts or reports others are creating so please share!

All opinions expressed in this blog are those of Dave Stokes who is actually amazed to find anyone else agreeing with him

technology

via Planet MySQL https://ift.tt/2iO8Ob8

June 24, 2020 at 03:47PM