How we refactored the MySQL charms and created `mysql-shell-client` in the process

announcements and community
1

The MySQL charms are old. They have seen it all: started their life as Ensemble Formulas back in 2011 (which used to be a bunch of Bash scripts), evolved to Python functions decorated using the charms.reactive library, and became one of the first family of charms rewritten with the ops Python framework in 2019.

Since their last rebirth, our Data Platform team has matured, and our shared understanding of the best practices on how to develop resilient Charms for Juju has evolved significantly. In addition, a tiny but brave MySQL team was maintaining charms for both Kubernetes and Machines substrates in separate repositories, which meant increased toil for any non-trivial change and an ever growing body of duplicated, almost-identical code and docs.

In summary: the MySQL charms had accumulated a bit of technical debt over the years.

In late 2025, we started a major refactoring with the goal of separating charm-agnostic operations to a separate package. These efforts culminated with the creation of a new Python package called mysql-shell-client, which has just reached 1.0.0. Do you want to know what we did exactly, and how? Read on!

The beauty of MySQL Shell

In the MySQL charms we make heavy usage of MySQL Shell, “an advanced client and code editor for MySQL”. Think of it this way: if the standard MySQL CLI is classical sh, MySQL Shell is like Zsh.

This tool, available as the mysql-shell Ubuntu package, has three execution modes: a regular SQL one, plus Python and JavaScript modes that ship several convenient high-level APIs.

For example, with MySQL Shell you can easily obtain the status of a InnoDB Cluster using the built-in REPL as follows:

$ mysqlsh --uri ...@localhost:3306
...
 MySQL  localhost:3306 ssl  SQL > \py
Switching to Python mode...
 MySQL  localhost:3306 ssl  Py > cluster = dba.get_cluster()
 MySQL  localhost:3306 ssl  Py > status = cluster.status({"extended": True})
 MySQL  localhost:3306 ssl  Py > import json
 MySQL  localhost:3306 ssl  Py > json.dumps(status, indent=2)
{
  "clusterName": "cluster-1c284954d9adc65318bff3b32735b933",
  "clusterRole": "PRIMARY",
  "defaultReplicaSet": {
    "name": "default",
    "primary": "mysql-k8s-1.mysql-k8s-endpoints.testing.svc.cluster.local.:3306",
    "ssl": "REQUIRED",
    "status": "OK",
    "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
    "topology": {
      "mysql-k8s-0": {
        "address": "mysql-k8s-0.mysql-k8s-endpoints.testing.svc.cluster.local.:3306",
        "memberRole": "PRIMARY",
        "mode": "R/W",
        "readReplicas": {},
        "role": "HA",
        "status": "ONLINE",
        "version": "8.4.8"
      },
      "mysql-k8s-1": {
        "address": "mysql-k8s-1.mysql-k8s-endpoints.testing.svc.cluster.local.:3306",
        "memberRole": "SECONDARY",
        "mode": "R/O",
...

Without this abstraction, you could assemble the status of the cluster by querying the Performance Schema replication tables, but it would be more cumbersome.

The right timing

We had grand plans for the MySQL charms ahead of the Ubuntu 26.04 LTS release. In particular, we wanted to add support for MySQL 8.4, the most recent LTS release at the time, on top of the already existing support for the 8.0 version.

The problem was that, over the years, a lot of tech debt had accumulated, and the team wanted to do some refactor right before bifurcating the codebase to avoid duplicating our efforts later on.

Eventually it was decided to extract the charm-agnostic operations from the server charms first, and our colleague @sinclert began doing so in the last weeks of 2025.

The design of mysql-shell-client

Slice by slice, commit by commit, Sinclert walked through the different parts of the charm (the MySQL, async, backups and TLS charm libs, the charm code itself, and various helper modules) and extracted the MySQL operations that could make use of MySQL Shell.

mysql-shell-client 0.1 was tagged early in the process, and it already featured a layered design that has been largely unmodified since then:

  • mysql_shell_client.models.ConnectionDetails holds the connection variables and performs some lightweight validation on them.
  • The module mysql_shell.executors defines an interface with execute_sql and execute_py methods, as well as a LocalExecutor class implementing it.
  • Query builders in mysql_shell.builders define methods to assemble templated SQL queries for various important tasks (table creation, lock acquisition and release, privilege granting, logs flushing, and more).
  • Quoters in mysql_shell.builders.quoting encapsulate logic to properly quote or escape arguments and parameters in Python programs and SQL statements.
  • Clients in mysql_shell.clients include InstanceClient and ClusterClient, abstracting high level operations on instances and clusters respectively by making use of executors and quoters.

Each and every one of these layers is independent from Juju and charms, which makes its unit and integration tests considerably simpler.

This is how retrieving the status of an InnoDB cluster works using mysql-shell-client:

import os

from mysql_shell.clients import ClusterClient
from mysql_shell.executors import LocalExecutor
from mysql_shell.models import ConnectionDetails

conn_details = ConnectionDetails(
    username=os.environ["MYSQL_USERNAME"],
    password=os.environ["MYSQL_PASSWORD"],
    host=os.environ["MYSQL_HOST"],
    port=os.environ["MYSQL_PORT"],
)

executor = LocalExecutor(conn_details, "/usr/bin/mysqlsh")
client = ClusterClient(executor)

print(cluster_client.fetch_cluster_status())

A more sophisticated example: to query instance users on particular user attributes, you can do

from mysql_shell.builders import QueryQuoter
from mysql_shell.clients import InstanceClient

quoter = QueryQuoter()
client = InstanceClient(executor, quoter)

users = client.search_instance_users(
    name_pattern="%", attrs={"created_by": "root"}
)
print(users)

Neat!

Moving faster with a better foundation

The integration of mysql-shell-client fundamentally changed how administrative tasks were written in the charms:

Administrative Dimension Legacy Shell-Wrapper Pattern Modern Library-Client Pattern
Execution Layer Custom subprocess or pebble.exec() wrappers with manual timeout tracking. Standardized LocalExecutor integrated with custom error parsers.
Query Generation Raw string interpolation and manual SQL query formatting. Programmatic QueryBuilder and validated StringQueryQuoter instances.
Data Schemas Loose dictionaries or untyped lists representing database hosts and credentials. Typed ConnectionDetails models utilizing strict Pydantic validation.
Error Handling Catching generic ExecError or parsing raw stderr text inside the charm. Structured exceptions mapped directly to MySQL Shell errors at the executor layer.
Testing Boundary Mocking low-level process executions or complete integration tests. Isolated unit-testing using standard Python mocks of the client and builder interfaces.

The refactor crystallized in two slightly intimidating but beautifully self-contained pull requests targeting both our K8s and VM charms. After a few iterations, which included shipping a few interim releases of mysql-shell-client itself, all unit and integration tests passed, and the refactor was merged in late January 2026.

After that, the 8.0 and 8.4 branches were bifurcated as expected, and we continued adding functionality and fixing bugs on both, now with a much more solid architecture.

Celebrating 1.0 and the way forward

In anticipation of the upcoming stable release of the Charmed MySQL 8.4 (stay tuned!), we are proud to announce that we have released mysql-shell-client 1.0!

mysql-shell-client has zero mandatory dependencies other than the standard library and is carefully designed to be independent from Juju and charms, so we hope that the community makes good use of it for all sorts of MySQL administrative tasks.

The new version includes a mysql_shell_contrib namespace, which is where we intend to place implementation-specific builders, executors, and such.

The road doesn’t end here though. At the time of writing these lines, we are

  • Discussing how to support MySQL 8.4+ only (and eventually 9.7+ only) methods
  • Integrating mysql-shell-client into our MySQL Router charms (both 8.0 and 8.4)
  • Accepting contributions for the newly created mysql_shell_contrib namespace, which we bootstrapped with some (optional) Pebble and charm-specific executors and query-builders.

Install mysql-shell-client with pip, uv, poetry, or any other PyPI-compatible tool of your liking, and give it a try today!

$ uv add mysql-shell-client
$ # or, alternatively,
$ pip install mysql-shell-client

I’d like to close this blog post by sending a token of appreciation to my colleague Sinclert for the excellent job he did combing through all the codebase and elevating the quality of the product :clap:t3:

Until next time,

Juan Luis Cano

Developer Success Engineer in residence

Charmed MySQL team

Contact the Canonical Data team with all your further questions, let’s collaborate!

Interested in joining the team? Canonical is hiring! Apply for career opportunities!

8 Likes