MySQL Shell

Key Features

🔹 Getting Started

Launch MySQL Shell from your terminal and connect to MySQL servers using URI-style connection strings. Switch between SQL, JavaScript, and Python modes seamlessly. The shell provides intelligent auto-completion and command history for efficient database interaction and scripting.

# Start MySQL Shell
mysqlsh

# Connect to server
mysqlsh root@localhost:3306

# Connect with password
mysqlsh root@localhost:3306 -p

# Connect to specific database
mysqlsh root@localhost:3306/mydb

# Start in specific mode
mysqlsh --sql
mysqlsh --js
mysqlsh --py

🔹 Working with SQL Mode

Execute standard SQL commands in SQL mode, the default mode for MySQL Shell. Switch to SQL mode anytime using the \sql command. Run queries, create tables, and perform all traditional SQL operations with enhanced features like syntax highlighting and better error messages.

-- Switch to SQL mode
\sql

-- Connect to database
\use mydb;

-- Execute queries
SELECT * FROM customers WHERE country = 'USA';

-- Create table
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

-- Show tables
SHOW TABLES;

-- Describe table
DESCRIBE employees;

🔹 JavaScript Mode

Use JavaScript for scripting and automation tasks in MySQL Shell. Access the MySQL X DevAPI for modern application development. Create reusable scripts, work with collections, and leverage JavaScript's programming capabilities for complex database operations and administrative tasks.

// Switch to JavaScript mode
\js

// Get session
var session = shell.getSession();

// Get schema
var db = session.getSchema('mydb');

// Get collection (Document Store)
var coll = db.getCollection('users');

// Insert document
coll.add({
    name: "John Doe",
    email: "[email protected]",
    age: 30
}).execute();

// Find documents
var docs = coll.find('age > 25').execute();

// Execute SQL
session.sql('SELECT * FROM customers').execute();

🔹 Python Mode

Leverage Python's powerful scripting capabilities within MySQL Shell. Write Python scripts to automate database tasks, perform data analysis, and integrate with Python libraries. Python mode provides full access to MySQL functionality through familiar Python syntax and programming patterns.

# Switch to Python mode
\py

# Get session
session = shell.get_session()

# Get schema
db = session.get_schema('mydb')

# Execute SQL
result = session.sql('SELECT * FROM products').execute()

# Fetch results
rows = result.fetch_all()
for row in rows:
    print(row)

# Work with collections
coll = db.get_collection('orders')
coll.add({
    'customer_id': 101,
    'items': ['item1', 'item2'],
    'total': 99.99
}).execute()

🔹 Document Store Operations

MySQL Shell provides NoSQL capabilities through the Document Store feature. Store and query JSON documents in collections without predefined schemas. Perform CRUD operations on documents using simple API calls, combining the flexibility of NoSQL with MySQL's reliability and ACID compliance.

// Create collection
db.createCollection('products');

// Add documents
db.products.add([
    {name: 'Laptop', price: 999, category: 'Electronics'},
    {name: 'Book', price: 19.99, category: 'Books'},
    {name: 'Phone', price: 699, category: 'Electronics'}
]).execute();

// Find documents
db.products.find('price < 100').execute();

// Update documents
db.products.modify('category = "Electronics"')
    .set('inStock', true)
    .execute();

// Remove documents
db.products.remove('price > 1000').execute();

// Count documents
db.products.count();

🔹 Backup and Restore

MySQL Shell includes powerful utilities for backing up and restoring databases. Use util.dumpInstance() to create complete backups and util.loadDump() to restore them. These utilities support parallel processing, compression, and filtering options for efficient data management and disaster recovery.

// Dump entire instance
util.dumpInstance('/backup/full_backup', {
    threads: 4,
    compression: 'zstd'
});

// Dump specific schemas
util.dumpSchemas(['mydb', 'testdb'], '/backup/schemas', {
    threads: 2
});

// Dump single table
util.dumpTables('mydb', ['customers'], '/backup/table');

// Load dump
util.loadDump('/backup/full_backup', {
    threads: 4,
    resetProgress: true
});

// Check dump progress
util.loadDump('/backup/full_backup', {
    dryRun: true
});

🔹 Useful Shell Commands

MySQL Shell provides special commands for navigation and configuration. Use backslash commands to switch modes, connect to servers, manage sessions, and customize shell behavior. These commands enhance productivity and make the shell more powerful than traditional MySQL clients.

# Mode switching
\sql        # Switch to SQL mode
\js         # Switch to JavaScript mode
\py         # Switch to Python mode

# Connection
\connect root@localhost:3306
\disconnect
\reconnect

# Information
\status     # Show connection status
\help       # Show help
\quit       # Exit shell

# Options
\option --persist    # Save options
\show               # Show options
\warnings           # Show warnings

# Source files
\source script.sql
\. script.js