mycli: Enhanced MySQL Cheatsheet

What it is

A command-line interface for MySQL that provides auto-completion, syntax highlighting, and other features to make interacting with MySQL databases more efficient and enjoyable.

Installation

Linux (Debian/Ubuntu):

sudo apt update
sudo apt install mycli

Linux (Fedora/CentOS/RHEL):

sudo yum install mycli
# or
sudo dnf install mycli

macOS:

brew install mycli

Windows: Using pip (Python’s package installer):

pip install mycli

Ensure you have Python and pip installed.

Core Concepts

• Auto-completion: mycli intelligently suggests SQL keywords, table names, column names, and function names as you type. It learns from your database schema.
• Syntax Highlighting: SQL queries and database objects are colored for better readability.
• Smart Tab Completion: Pressing Tab cycles through suggestions. Holding Tab might reveal more options or execute a completion.
• Pager Integration: Long query outputs are automatically piped to a pager (like less) for easier navigation.

Commands / Usage

Connecting to a Database

Connect to a MySQL server:

mycli -h 127.0.0.1 -u root -p mydatabase

Connect to a MySQL server with a specific port:

mycli -h 127.0.0.1 -P 3307 -u admin -p production_db

Connect using a socket file:

mycli -S /var/run/mysqld/mysqld.sock -u webuser -p app_db

Connect without specifying a database (you can select one later):

mycli -h db.example.com -u readonly -p

Basic Interaction

Execute a SQL query:

SELECT * FROM users WHERE status = 'active';

Run a query and show results in a table (default behavior):

SHOW TABLES;

Describe a table structure:

DESCRIBE products;

Execute multiple statements separated by semicolons:

SELECT COUNT(*) FROM orders;
INSERT INTO logs (message) VALUES ('User logged in');

Editing and Navigation

Enter multi-line query mode (useful for complex queries):

-- (Type your query here, press Enter for new line, Ctrl+D or type QUIT; to exit)
SELECT
    u.name,
    COUNT(o.id) AS order_count
FROM
    users u
JOIN
    orders o ON u.id = o.user_id
WHERE
    u.created_at > '2023-01-01'
GROUP BY
    u.name
ORDER BY
    order_count DESC;

Cancel the current query:

Ctrl+C

Exit mycli:

QUIT;
-- or
\q
-- or
Ctrl+D

Configuration and Settings

View current mycli settings:

\confs

Set a mycli configuration option temporarily:

\set prompt_colors 'blue'

Set a mycli configuration option persistently (edits ~/.myclirc):

\set editor vim

mycli Specific Commands (Meta Commands)

Show mycli version:

\v

Show mycli configuration:

\confs

List available meta-commands:

\?

Show help for a specific meta-command:

\? set

Show SQL help (e.g., for CREATE TABLE):

\h CREATE TABLE

Execute a shell command:

\! ls -l

Change the current database:

USE new_database;

View the current connection details:

\conninfo

View history of commands:

\history

Clear the screen:

\clear

Toggle query timing display:

\timing on
-- or
\timing off

Execute a query and save output to a file:

SELECT * FROM customers LIMIT 100 \G > customers.txt

(Note: \G formats output vertically, useful for saving or single-row results. The > redirects standard output.)

Common Flags

• -h, --host <hostname>: Hostname of the MySQL server.
• -P, --port <port>: Port number of the MySQL server (default is 3306).
• -u, --user <username>: Username for connecting.
• -p, --password: Prompt for password.
• -D, --database <dbname>: Database to connect to.
• -S, --socket <socket_path>: Path to the MySQL socket file.
• --color <mode>: Force color output (always, never, auto).
• --defaults-extra-file <file>: Read configuration from an additional file.
• --skip-auto-reconnect: Do not automatically reconnect if the connection is lost.

Common Patterns

Connect to a database and run a script:

mycli -u root -p mydatabase < /path/to/my_script.sql

This executes all SQL statements in my_script.sql against mydatabase.

Connect and run a single query:

mycli -u user -p dbname -e "SELECT COUNT(*) FROM logs;"

The -e flag executes the given query and exits.

View query execution time:

\timing on
SELECT * FROM large_table WHERE column = 'value';
-- (mycli will print the execution time after the query results)

Search query history:

\history
-- (Then use Ctrl+R in the prompt to search interactively)

Connect with SSH tunneling: If your database is not directly accessible but reachable via SSH:

# On your local machine, set up the tunnel
ssh -N -L 3307:127.0.0.1:3306 user@remote_host

# Then connect using mycli to the local forwarded port
mycli -h 127.0.0.1 -P 3307 -u db_user -p

Use a different pager: mycli respects the MYSQL_PAGER environment variable.

MYSQL_PAGER="less -RF" mycli -u root -p

Configure mycli settings in ~/.myclirc:

[mycli]
user = mydefaultuser
host = localhost
database = defaultdb
prompt_colors = green
enable_auto_commit = False
editor = nano

Gotchas

• Password Prompting: If you use -p without a password immediately following it, mycli will prompt you securely. If you provide a password directly (-pmysecretpassword), it will be visible in your shell history. It’s generally better to let it prompt or use environment variables.
• Auto-commit: By default, mycli does NOT auto-commit transactions. You need to explicitly run COMMIT; or ROLLBACK;. You can change this with \set enable_auto_commit True.
• Meta Commands: Commands starting with \ (like \conninfo, \set, \h) are mycli specific and are not sent to the MySQL server. Standard SQL commands are sent.
• Auto-completion Learning: mycli builds its completion dictionary by querying your database schema. If you make schema changes (e.g., CREATE TABLE), it might take a moment for completions to reflect those changes, or you might need to restart mycli for it to fully re-index.
• Ctrl+D Behavior: Ctrl+D typically exits mycli when the prompt is empty. If you are in the middle of typing a query in multi-line mode, it might send an EOF character, which can behave unexpectedly depending on the terminal and mycli version. QUIT; or \q are more reliable exit commands.
• Shell Escaping: When using \! to run shell commands, be mindful of shell quoting and escaping rules.
• Configuration File: mycli reads its configuration from ~/.myclirc. If you encounter unexpected behavior, check this file for conflicting settings.