Using MySQL Shell Plugins: Replication, Locks, Profiling, InnoDB and Check Components

The author, a senior database expert with years of MySQL experience, discovered an open‑source MySQL Shell plugins repository (https://github.com/lefred/mysqlshell-plugins) that restores many utilities previously provided by the discontinued MySQL Utilities.

Installation is straightforward: create the plugins directory and copy the repository there.

mkdir -p ~/.mysqlsh/plugins
git clone https://github.com/lefred/mysqlshell-plugins.git ~/.mysqlsh/plugins

After resolving Python dependencies on Fedora Server 37, the new components become visible in the MySQL Shell, such as audit , check , config , etc.

Demonstration of selected components

1. Replication component

A three‑node replica set (3310 primary, 3311 & 3312 secondaries) is used to show how to query replication status, simulate an error, and skip the offending GTID.

MySQL> rs.status()
{
    "replicaSet": {
        "name": "rs1",
        "primary": "127.0.0.1:3310",
        ...
    }
}

MySQL> replication.status();
+--------------+-----------+------------+------------+
| channel_name | IO_thread | SQL_thread | lag_in_sec |
+--------------+-----------+------------+------------+
|              | ON        | OFF        | 20         |
+--------------+-----------+------------+------------+

MySQL> replication.error();
Applier error: ... Duplicate entry '10' for key 't1.PRIMARY' ...

MySQL> replication.get_gtid_to_skip();
+--------------+-----------------------------------------+
| channel_name | gtid_to_skip                            |
+--------------+-----------------------------------------+
|              | 923dad27-8528-11ed-8796-080027382b72:78 |
+--------------+-----------------------------------------+

MySQL> replication.skip_error();
skipping 923dad27-8528-11ed-8796-080027382b72:78 for replication channel ''...

MySQL> stop replica; start replica;
Query OK, 0 rows affected (0.0194 sec)
Query OK, 0 rows affected (0.0347 sec)

After skipping the error, replication resumes without issues.

2. Locks component

The locks.get_locks() command lists current transaction locks, their durations, memory usage, and affected rows.

MySQL> locks.get_locks()
+-----------------+--------------+-------------+-----------+----------------+-------------------+-------------------+-------------------------------------------------------------------+
| mysql_thread_id | trx_duration | cpu_latency | memory    | row_locks_held | row_locks_pending | tables_with_locks | current_statement                                                 |
+-----------------+--------------+-------------+-----------+----------------+-------------------+-------------------+-------------------------------------------------------------------+
| 25              | 4.43 min     | 0 ps        | 2.07 MiB  | 3              | 0                 | ytt.t1            | NULL                                                              |
| 29              | 17.51 s      | 0 ps        | 2.49 MiB  | 1              | 0                 | ytt.t1            | update t1 set r1 = 100 where id =10                               |
+-----------------+--------------+-------------+-----------+----------------+-------------------+-------------------+-------------------------------------------------------------------+

Further inspection shows that thread 25 holds IX and X locks on ytt.t1, while thread 29 is blocked waiting for the same X lock.

Metadata Locks:
GRANTED SHARED_WRITE on ytt.t1

Data Locks:
GRANTED TABLE (IX) LOCK ON ytt.t1
GRANTED RECORD (X) LOCK ON ytt.t1 (PRIMARY) (id=10)
BLOCKING RECORD (X,REC_NOT_GAP) LOCK ON ytt.t1 FOR 12 SECONDS (mysql_thread_id: 29)

3. Profiling component

Start profiling with profiling.start(), run a query, then retrieve detailed stage timings via profiling.get().

MySQL> profiling.get()

Last 5 events from the process list id: @@pseudo_thread_id
-----------------------------------------------------------
... (output omitted) ...
Profiling of:
select * from t1 order by rand() limit 10

duration: 0.0162
+------------------------------------------------+----------+
| Stage                                          | Duration |
+------------------------------------------------+----------+
| stage/sql/executing                            |   0.0160 |
+------------------------------------------------+----------+

4. InnoDB component

Use innodb.get_fragmented_tables_disk() to list tables with fragmentation, showing row count, data size, index size, total size, free space and fragmentation percentage.

MySQL> innodb.get_fragmented_tables_disk()
+--------+-------+-----------+------------+------------+-----------+-----------+-------------+--------+
| NAME   | ROWS  | DATA_SIZE | INDEX_SIZE | TOTAL_SIZE | DATA_FREE | FILE_SIZE | WASTED_SIZE | FREE   |
+--------+-------+-----------+------------+------------+-----------+-----------+-------------+--------+
| ytt/t1 | 28861 | 4.52 MiB  | 0 bytes    | 4.52 MiB   | 4.00 MiB  | 12.00 MiB | 7.48 MiB    | 62.37% |
+--------+-------+-----------+------------+------------+-----------+-----------+-------------+--------+

5. Check component

The check component can display binlog I/O statistics (illustrated in the original article with a screenshot).

Finally, the article includes promotional notes about the SQLE tool, contribution rewards, and community links, which are not part of the technical tutorial.