The MySQL Command-Line Client

The mysql program is a text-based client that comes with MySQL. It is on almost every server, so it is worth knowing.

Start it

1

mysql -u root -p

• -u root is the user name.
• -p asks for the password. Type it when prompted (nothing appears as you type).

You will see something like this:

1
2
3
4
5

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.4.11 MySQL Community Server - GPL

mysql>

The mysql> prompt is waiting for a statement.

Type statements

Every statement ends with a semicolon. The client does not run the statement until it sees one. You can spread a statement over several lines.

1
2
3

SELECT
    2 + 3 AS sum,
    10 / 4 AS division;

1
2
3
4
5
6

+-----+----------+
| sum | division |
+-----+----------+
|   5 |   2.5000 |
+-----+----------+
1 row in set

If you forget the semicolon, the prompt changes to ->, meaning “still waiting for more”. Type ; and press Enter to finish.

Useful client commands

These are commands for the client program itself. They do not need a semicolon.

The \G ending is great for wide rows:

1

SELECT 'Asha' AS name, 'Pune' AS city, 'asha@example.com' AS email\G

1
2
3
4
5

*************************** 1. row ***************************
 name: Asha
 city: Pune
email: asha@example.com
1 row in set

Run a file

Put SQL in a file called setup.sql, then run it without opening the prompt:

1

mysql -u root -p learn < setup.sql

Or from inside the prompt:

1

SOURCE setup.sql

SOURCE is a client command (not SQL), so it needs no semicolon.

Useful options

For example, one quick check from a script:

1

mysql -u root -p -e "SELECT COUNT(*) FROM learn.products"

Pitfalls

• A password typed on the command line (-pSECRET) can be seen by other users on the machine and stays in your shell history. Use -p alone and type it when asked, or use an option file (see Connecting to MySQL).
• Quotes: a statement with a single quote inside a shell command must be quoted carefully. Putting SQL in a file avoids the trouble.