MySQL Shell Administration Guide

MySQL Shell (mysqlsh) is the advanced command-line client for MySQL, providing JavaScript, Python, and SQL interfaces along with powerful administration capabilities. It replaces the traditional mysql client with features like AdminAPI for InnoDB Cluster management, utilities for data import/export, and integrated diagnostics. This guide covers essential MySQL Shell workflows for daily database administration.

Installation

# Ubuntu/Debian
sudo apt install mysql-shell

# Rocky Linux/RHEL
sudo dnf install mysql-shell

# Verify installation
mysqlsh --version

Connection Modes

# Classic MySQL protocol (port 3306)
mysqlsh --sql mysql://root@localhost:3306

# X Protocol (port 33060, preferred)
mysqlsh --sql mysqlx://root@localhost:33060

# Connect and switch modes
mysqlsh root@localhost
\sql    -- Switch to SQL mode
\js     -- Switch to JavaScript mode
\py     -- Switch to Python mode

SQL Mode Operations

\sql
-- Standard SQL queries work as expected
SHOW DATABASES;
USE myapp;
SELECT COUNT(*) FROM users;

-- Multi-line queries
SELECT
    table_name,
    table_rows,
    ROUND(data_length / 1024 / 1024, 2) AS data_mb
FROM information_schema.tables
WHERE table_schema = 'myapp'
ORDER BY table_rows DESC;

JavaScript Mode Administration

\js
// Get current session
var session = shell.getSession();

// Run SQL from JavaScript
var result = session.runSql("SELECT COUNT(*) as cnt FROM myapp.users");
var row = result.fetchOne();
print("User count: " + row.cnt);

// List all schemas with sizes
session.runSql(`
    SELECT table_schema, 
           ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS size_mb
    FROM information_schema.tables
    GROUP BY table_schema
    ORDER BY size_mb DESC
`).fetchAll().forEach(r => print(r[0] + ": " + r[1] + " MB"));

Data Import and Export Utilities

Parallel Table Import

// Import a large CSV file in parallel (much faster than LOAD DATA)
util.importTable("/path/to/large_data.csv", {
    schema: "myapp",
    table: "events",
    dialect: "csv",
    skipRows: 1,        // Skip header row
    threads: 8,         // Parallel threads
    bytesPerChunk: "50M",
    showProgress: true
});

Instance Dump and Load

// Dump entire instance (parallel, compressed)
util.dumpInstance("/backup/full-dump", {
    threads: 4,
    compression: "zstd",
    showProgress: true
});

// Dump specific schemas
util.dumpSchemas(["myapp", "analytics"], "/backup/schemas-dump", {
    threads: 4,
    compression: "zstd"
});

// Dump specific tables
util.dumpTables("myapp", ["users", "orders"], "/backup/tables-dump");

// Load a dump
util.loadDump("/backup/full-dump", {
    threads: 8,
    showProgress: true,
    resetProgress: true,
    ignoreExistingObjects: true
});

InnoDB Cluster Administration

\js
// Check instance configuration
dba.checkInstanceConfiguration("root@node1:3306");

// Get cluster handle
var cluster = dba.getCluster();

// Check cluster status
cluster.status({extended: 1});

// Add a new instance
cluster.addInstance("root@node4:3306", {recoveryMethod: "clone"});

// Remove an instance
cluster.removeInstance("root@node4:3306");

// Set primary instance
cluster.setPrimaryInstance("root@node2:3306");

// Force quorum (when majority of nodes are down)
cluster.forceQuorumUsingPartitionOf("root@node1:3306");

Diagnostics and Reporting

// Check for configuration issues
util.checkForServerUpgrade();

// Generate diagnostic report
shell.reports.threads();  // Active threads
shell.reports.query("SELECT * FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_TIMER_WAIT DESC LIMIT 10");

// Performance Schema queries
\sql
SELECT DIGEST_TEXT, COUNT_STAR, ROUND(AVG_TIMER_WAIT/1000000000, 2) as avg_ms
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = 'myapp'
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

Useful Shell Commands

# Run a script file
mysqlsh root@localhost --file=/path/to/script.js
mysqlsh root@localhost --file=/path/to/script.sql --sql

# Execute inline
mysqlsh root@localhost --sql -e "SHOW PROCESSLIST"

# Output formatting
mysqlsh root@localhost --sql --result-format=json -e "SELECT * FROM myapp.users LIMIT 5"
mysqlsh root@localhost --sql --result-format=table -e "SHOW DATABASES"

# Save session log
mysqlsh root@localhost --log-file=/tmp/mysqlsh.log

Plugin and Extension Support

// Create custom reports
shell.registerReport("bigTables", "list", function(session) {
    var result = session.runSql(`
        SELECT table_schema, table_name, table_rows,
               ROUND((data_length + index_length) / 1024 / 1024, 2) AS size_mb
        FROM information_schema.tables
        WHERE table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
        ORDER BY data_length + index_length DESC
        LIMIT 20
    `);
    return {report: result.fetchAll()};
});

// Run the custom report
\show bigTables

Best Practices

• Use MySQL Shell instead of the legacy mysql client for all administration tasks
• Use util.dumpInstance for backups — it is significantly faster than mysqldump due to parallel execution
• Use JavaScript or Python mode for scripting complex administration tasks
• Always use dba.checkInstanceConfiguration before adding nodes to InnoDB Cluster
• Use util.checkForServerUpgrade before upgrading MySQL versions
• Store frequently used scripts in files and execute them with --file
• Enable logging with --log-file for audit trails of administration activities