Skip to content
Tutorials Center
My learning

Topics

Lesson 4 of 75 ยท Getting Started with MySQL

Using the mysql Command-Line Client

Get fluent in the mysql client: SHOW and DESCRIBE, USE, source, vertical output with \G, pager, batch mode with --execute, custom prompt and history.

The mysql command-line client is the tool you will reach for when something is wrong on a server at 2 a.m., so it is worth knowing well. It is small, installed everywhere MySQL is, and scriptable. This lesson covers the commands you will use daily, how to read wide results, and how to run SQL from files and shell scripts.

The client sessions below are read-only examples; they show what a MySQL 8.4 server prints. The live blocks use SQLite equivalents of SHOW TABLES and DESCRIBE, so you can see the same information.

Two kinds of commands

Inside the client you type two different things:

  1. SQL statements, which go to the server and must end with ; (or \g, or \G).
  2. Client commands, which the client itself handles and which need no semicolon, such as use, source, status, pager and exit.

Both kinds are shown here. Statements can span lines. The client waits for the terminator, and the prompt changes to -> while it waits:

TEXT
mysql> SELECT 1 +
    -> 2;
+-------+
| 1 + 2 |
+-------+
|     3 |
+-------+
1 row in set (0.00 sec)

If you realize you made a typo in a multi-line statement, type \c to cancel it without sending anything.

Looking around: SHOW, USE and DESCRIBE

SQL
SHOW DATABASES;
USE lab;
SHOW TABLES;
DESCRIBE customers;

SHOW DATABASES lists the databases your account can see. USE lab makes lab the default, so you can write customers instead of lab.customers. SHOW TABLES lists the tables of the default database. DESCRIBE customers (or DESC, or SHOW COLUMNS FROM customers) prints one row per column:

TEXT
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int unsigned | NO   | PRI | NULL    | auto_increment |
| email      | varchar(190) | NO   | UNI | NULL    |                |
| created_at | datetime     | NO   |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

The Key column tells you about indexes (PRI for the primary key, UNI for a unique index, MUL for a non-unique one), and Extra shows auto_increment and generated-column notes. For the full definition including defaults, engine and charset, use:

SQL
SHOW CREATE TABLE customers\G

That last \G is explained next.

SQLite stand-ins you can run

SQLite has no SHOW, but it can answer the same questions. sqlite_master is the catalog, and PRAGMA table_info is its DESCRIBE:

SQL Stand-in for SHOW TABLES

Press Run (or Ctrl + Enter) to see the result here.

You get customers and orders, the same list SHOW TABLES would print. Note the filter on sqlite_%: SQLite creates internal tables that MySQL would never show.

SQL Stand-in for DESCRIBE

Press Run (or Ctrl + Enter) to see the result here.

The three rows mirror the MySQL output above, with id marked PRI. SQLite does not report UNI here; it exposes unique indexes through pragma_index_list.

Vertical output with \G

Tables with many columns wrap badly in a terminal. End the statement with \G instead of ; and each row becomes a block, one line per column:

TEXT
mysql> SELECT * FROM customers WHERE id = 1\G
*************************** 1. row ***************************
        id: 1
     email: [email protected]
created_at: 2026-03-14 09:30:00
1 row in set (0.00 sec)

Use \G for SHOW CREATE TABLE, SHOW ENGINE INNODB STATUS, SHOW REPLICA STATUS and EXPLAIN on wide plans. Do not add a semicolon after \G, because that would send an empty second statement and print an error.

Running files: source, redirects and batch mode

Anything you can type, you can put in a file. From inside the client:

SQL
source /path/to/schema.sql

(\. is the short form.) From the shell, redirect the file into the client:

Terminal
mysql -u lab_app -p lab < schema.sql

The client stops at the first error unless you pass --force. For restores, that default is what you want.

To run one statement without opening an interactive session, use -e (--execute):

Terminal
mysql -u lab_app -p lab -e "SELECT COUNT(*) AS customers FROM customers"

When output is not a terminal (a pipe or file), the client switches to batch mode: tab-separated values with a header row. Add -N (--skip-column-names) to drop the header, and -B to force batch format even in a terminal. This makes shell scripting straightforward:

Terminal
mysql -u lab_app -p lab -N -B -e "SELECT email FROM customers" > emails.txt

Other useful flags for scripts: -s (silent, less decoration), --raw (do not escape tabs and newlines), -X (XML output) and --vertical (the \G layout for every statement).

The pager

Long results scroll past before you can read them. The pager command pipes the output through any program:

TEXT
mysql> pager less -S
PAGER set to 'less -S'
mysql> SELECT * FROM orders;

less -S stops line wrapping, so wide tables scroll sideways. Other tricks:

TEXT
mysql> pager grep -i error
mysql> pager md5sum
mysql> nopager

md5sum is surprisingly useful: it hides the rows and prints a checksum, which lets you quickly compare whether two queries return identical results. nopager turns it all off. On Windows, pager needs an external program on your path, so it is less common there.

Prompt, history and quality-of-life options

The default prompt mysql> tells you nothing. Customize it to show the user, host and default database, which prevents running a statement against the wrong server:

TEXT
mysql> prompt \u@\h [\d]>
PROMPT set to '\u@\h [\d]>'
[email protected] [lab]>

To make it permanent, put it in ~/.my.cnf under [mysql]:

INI
[mysql]
prompt = \u@\h [\d]>\_
safe-updates

safe-updates (also --i-am-a-dummy) is a safety net: the server rejects UPDATE and DELETE statements that have no WHERE on a key column, and it adds an automatic LIMIT to large SELECTs. It has saved many production tables.

The client keeps a history file, ~/.mysql_history. Use the arrow keys or Ctrl+R to search it. Because it stores everything you type, including IDENTIFIED BY '...' passwords, clear it or set MYSQL_HISTFILE=/dev/null on shared machines. Statements that contain password are partly filtered by default, but do not rely on that.

A few more commands worth remembering:

Command Does
status or \s Connection id, current user, server version, charset, uptime
warnings / nowarnings Print warnings after every statement
SHOW WARNINGS; Show the warnings from the last statement
tee out.txt / notee Copy everything you see into a file
system ls or \! ls Run a shell command
exit, quit or Ctrl+D Leave the client

After any statement that reports "N warnings", run SHOW WARNINGS;. Truncated data and implicit conversions show up there, not as errors. SQL Modes and Strictness explains when MySQL fails instead of warning.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does ending a statement with \G instead of ; do?

    \G sends the statement and formats the result vertically, which is much easier to read for wide rows.
  2. 2. Which command runs one statement from a shell script and prints tab-separated rows with no header?

    -e executes a statement, -B selects batch (tab-separated) output and -N skips the column names.
  3. 3. What does the safe-updates option protect you from?

    With safe-updates the server refuses UPDATE and DELETE statements that cannot use a key in the WHERE clause, so a forgotten WHERE does not hit every row.
  4. 4. Why can ~/.mysql_history be a security problem?

    Everything typed in interactive sessions is saved, so secrets in statements can end up on disk. Clear the file or disable it on shared machines.

Summary

  • Statements end with ;, \g or \G. Client commands such as use, source, pager and status need no terminator.
  • SHOW DATABASES, SHOW TABLES, DESCRIBE and SHOW CREATE TABLE are your map of the server. SQLite spells them sqlite_master and pragma_table_info.
  • \G prints rows vertically, -e runs a statement, -N -B gives clean scriptable output.
  • pager, prompt, tee and safe-updates make daily work safer and faster.
  • Check SHOW WARNINGS after statements that report warnings.

Next: Your First Database and Table.