How to Use MySQL Database Command-Line Client

Before I ever touched a GUI database tool, I learned MySQL entirely through its command-line client, and honestly, I still reach for it constantly — for quick diagnostics, scripting, and situations where a GUI simply isn’t available (SSH-ing into a production server at 2 AM is not the time to wish you had a GUI installed). In this article, I’ll walk through the mysql command-line client in depth: connecting, navigating, scripting, and troubleshooting.

What Is the MySQL Command-Line Client?

The mysql client is a text-based interactive (or non-interactive/batch) tool that ships with MySQL, letting you connect to a server and run SQL statements, administrative commands, and scripts directly from a terminal. It’s lightweight, scriptable, and available essentially everywhere MySQL itself is installed.

Where the CLI Fits in MySQL’s Architecture

graph LR
    A[mysql CLI Client] -->|TCP/Socket Connection| B[mysqld Server Process]
    B --> C[SQL Layer]
    C --> D[Storage Engine]
    D --> E[(Data Files)]
    F[mysql Command History] --> A
    G[.my.cnf Config File] --> A

The client itself doesn’t process any data — it’s purely a connection and rendering layer. Every query you type is sent over the wire (TCP or a Unix socket) to mysqld, which parses, optimizes, and executes it, then streams results back for the client to display as ASCII tables.

Connecting to a MySQL Server

Basic connection:

mysql -u root -p

You’ll be prompted for a password, then dropped into the interactive shell:

mysql>

Connecting to a specific host and port (useful for remote or containerized databases):

mysql -h 192.168.1.50 -P 3306 -u app_user -p my_database

Connecting via a Unix socket (often faster for local connections, avoiding TCP overhead):

mysql -u root -p --socket=/var/run/mysqld/mysqld.sock

Connecting directly to a specific database:

mysql -u root -p my_database

Essential Navigation Commands

Once connected, these are the commands I use dozens of times a day:

SHOW DATABASES;
USE my_database;
SHOW TABLES;
DESCRIBE customers;
SHOW CREATE TABLE customers;

Sample output of DESCRIBE:

+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int          | NO   | PRI | NULL    | auto_increment |
| name       | varchar(255) | NO   |     | NULL    |                |
| email      | varchar(255) | YES  | UNI | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

I also frequently check the current connection’s context:

SELECT DATABASE(), USER(), VERSION(), @@hostname;

Client-Specific Commands (Not SQL)

The mysql client supports its own set of “backslash commands” that aren’t SQL and don’t need a semicolon:

The \G terminator is one I use constantly for wide tables, since horizontal ASCII tables become unreadable once you have more than a handful of columns:

SELECT * FROM customers WHERE id = 1\G

*************************** 1. row ***************************
       id: 1
     name: Sarah Ahmed
    email: sarah@example.com

Running Scripts and Batch Mode

Executing a .sql file from within the client:

SOURCE /home/user/setup.sql;

Or directly from the shell without entering interactive mode:

mysql -u root -p my_database < setup.sql

Piping output to a file:

mysql -u root -p -e "SELECT * FROM customers" my_database > customers_export.txt

I use the -e flag (execute) heavily for quick, scriptable one-liners in shell scripts and cron jobs, since it avoids the interactive prompt entirely:

mysql -u root -p -e "SHOW PROCESSLIST;"

Useful Command-Line Flags

I keep --safe-updates in my personal alias for production connections, since it’s saved me from at least one career-ending mistake:

alias mysql_prod="mysql -u prod_user -p --safe-updates -h prod-db.internal"

With --safe-updates enabled, this fails safely instead of wiping the table:

DELETE FROM customers;

ERROR 1175 (HY000): You are using safe update mode and you tried to update a table
without a WHERE that uses a KEY column.

Batch/Tab-Delimited Output for Scripting

When piping MySQL output into other tools (awk, grep, Python scripts), I use --batch to strip the ASCII table formatting:

mysql -u root -p -B -e "SELECT id, email FROM customers" my_database

id	email
1	sarah@example.com
2	bilal@example.com

Combined with --skip-column-names when I want raw data only:

mysql -u root -p -B -N -e "SELECT COUNT(*) FROM customers" my_database

1042

Using a Configuration File Instead of Flags

Typing credentials on every command is tedious and leaves them visible in shell history. I use a ~/.my.cnf file instead:

[client]
user = app_user
password = my_secure_password
host = 127.0.0.1

chmod 600 ~/.my.cnf

Now I can simply run:

mysql my_database

with no explicit credentials on the command line — and critically, this keeps passwords out of ps aux output and shell history, which matters on shared or multi-user servers.

Real-World DBA Workflows

Quick Health Check

mysql -u root -p -e "SHOW STATUS LIKE 'Threads_connected'; SHOW STATUS LIKE 'Uptime';"

Exporting Query Results to CSV

mysql -u root -p -B -e "SELECT * FROM orders WHERE created_at > '2026-07-01'" my_database \
  | sed 's/\t/,/g' > orders_july.csv

Monitoring Running Queries Live

SHOW FULL PROCESSLIST;

I run this whenever I suspect a long-running query is blocking others, then follow up with:

KILL QUERY 4821;

to terminate a specific problematic query without dropping the whole connection.

Checking Replication Status

SHOW REPLICA STATUS\G

(In older MySQL versions this was SHOW SLAVE STATUS\G.)

Performance and Optimization Tips from the CLI

I lean on the CLI heavily for diagnostics:

EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

SHOW INDEX FROM orders;

SHOW ENGINE INNODB STATUS\G

This last one gives a dense but invaluable dump of InnoDB’s internal state — buffer pool usage, lock waits, deadlocks — that I check whenever something feels off performance-wise.

Security Considerations

• Never pass passwords directly after -p on the command line (e.g., -pMyPassword) — it’s visible in shell history and process listings. Let -p prompt interactively, or use ~/.my.cnf with restricted file permissions.
• Restrict ~/.my.cnf to 600 permissions so other local users can’t read stored credentials.
• Prefer SSH tunneling or MySQL’s built-in SSL options (--ssl-mode=REQUIRED) when connecting to a remote server over an untrusted network.

mysql -u app_user -p --ssl-mode=REQUIRED -h remote-db.example.com

Troubleshooting Common Issues

Common Mistakes I See Beginners Make

Over the years, mentoring newer developers on the CLI, I’ve noticed the same handful of mistakes come up repeatedly:

1. Forgetting the semicolon. A statement without a trailing ; (or \G) just sits there waiting — the client shows a continuation prompt (->) instead of running anything, which confuses people who assume the terminal has hung.
2. Typing the password directly into the command. As covered above, -pMyPassword leaves the password in shell history and process listings. I always recommend the interactive prompt or a .my.cnf file instead.
3. Running destructive statements without a transaction or backup. Even experienced engineers occasionally run an UPDATE or DELETE against production without first checking the WHERE clause with a SELECT. I make it a personal rule to always run the equivalent SELECT first and eyeball the row count before converting it to a write.
4. Not using USE before querying. Beginners sometimes fully qualify every table name (SELECT * FROM my_database.customers) because they didn’t realize USE my_database; sets the default schema for the session.
5. Ignoring warnings. MySQL will sometimes execute a statement successfully but emit a warning (e.g., implicit type conversion, data truncation). Running SHOW WARNINGS; immediately after a suspicious statement has caught more than one silent data quality issue for me.

INSERT INTO customers (age) VALUES ('not-a-number');
SHOW WARNINGS;

+---------+------+------------------------------------------+
| Level   | Code | Message                                   |
+---------+------+------------------------------------------+
| Warning | 1366 | Incorrect integer value: 'not-a-number'   |
+---------+------+------------------------------------------+

Interview Questions on the MySQL CLI

1. How do you display query results vertically instead of in a table? Terminate the statement with \G instead of ;.
2. How would you run a single SQL command without entering the interactive shell? mysql -u user -p -e "SQL_STATEMENT" database_name.
3. Why is passing a password directly after -p (e.g., -pMyPass) discouraged? It’s exposed in shell history and process listings, which is a security risk. Prompted or config-file-based authentication is safer.
4. What’s the purpose of --safe-updates? It prevents UPDATE/DELETE statements that lack a WHERE clause using a key column, guarding against accidental full-table modifications.
5. How do you check currently running queries and kill a problematic one? SHOW FULL PROCESSLIST; to list them, then KILL QUERY <id>; to terminate a specific query.

Frequently Asked Questions

Q: Can the mysql client connect to non-MySQL databases like MariaDB? A: Yes, the mysql client is largely compatible with MariaDB servers since they share the same wire protocol lineage, though some newer MySQL-specific features may not be supported.

Q: How do I increase the query result pager for large outputs? A: Use \P less inside the client (or --pager=less at launch) to pipe results through a pager for easier scrolling.

Q: Can I use the CLI to import a large .sql dump efficiently? A: Yes — mysql -u root -p database_name < dump.sql is standard, though for very large dumps I disable foreign key checks temporarily and consider mysqlimport or LOAD DATA INFILE for raw data files instead.

Q: Does the CLI support autocomplete? A: Yes, table and column name completion works via --auto-rehash (enabled by default in most distributions), triggered with the Tab key.

Summary and Key Takeaways

The MySQL command-line client remains, in my experience, the fastest and most dependable way to interact with a database — especially for scripting, automation, and remote server diagnostics where a GUI isn’t practical.

Key takeaways:

• Use \G for readable vertical output on wide result sets.
• Store credentials in a permission-restricted ~/.my.cnf rather than typing them inline.
• Use -e for one-off, scriptable commands in shell scripts and cron jobs.
• Enable --safe-updates for an extra layer of protection against accidental destructive queries.
• Lean on SHOW PROCESSLIST, EXPLAIN, and SHOW ENGINE INNODB STATUS for hands-on diagnostics.

References

• MySQL Reference Manual: mysql — The MySQL Command-Line Client
• MySQL Reference Manual: mysql Client Commands
• MySQL Reference Manual: Connecting to the MySQL Server Using Command Options