Lesson 03 beginner

The mysql Client Essentials

Get fluent with the mysql command-line client: connection options, option files and login paths so you never type passwords on the command line, client commands such as \G, \s, SOURCE and pager, SHOW statements to explore a server, running SQL files and one-off queries from scripts, and a prompt that tells you where you are.

The mysql client is installed everywhere MySQL is, works over SSH and is what you will reach for in an incident. A few options and client commands make it far more pleasant. You will use it for every lesson in this course.

Connecting

$ mysql -u orbit -p -h 127.0.0.1 -P 3306 orbit
OptionMeaning
-u orbitAccount name
-pAsk for the password (do not write it after -p)
-h 127.0.0.1Host. Leave it out to connect through the local socket
-P 3306Port (capital P)
orbitDefault database

Warning: -pSecret (no space) works, but the password then sits in your shell history and is visible to other users in the process list. Use -p alone, an option file or a login path.

localhost is special: the client treats it as “use the Unix socket”, not TCP. If you need TCP to the local machine, write -h 127.0.0.1 or add --protocol=TCP.

Once connected, check where you are:

SELECT VERSION(), CURRENT_USER(), DATABASE();
+-----------+-----------------+------------+
| VERSION() | CURRENT_USER()  | DATABASE() |
+-----------+-----------------+------------+
| 9.7.4     | orbit@localhost | orbit      |
+-----------+-----------------+------------+
1 row in set (0.00 sec)

Statements end with ;. If you press Enter without it, the prompt changes to -> and waits for more. Cancel a half-typed statement with \c, and leave the client with exit, quit, \q or Ctrl+D.

Option files: stop typing passwords

The client reads defaults from option files, the user-level one being ~/.my.cnf:

[client]
user=orbit
password=Orbit-dev-2026!
host=127.0.0.1

[mysql]
database=orbit
$ chmod 600 ~/.my.cnf
$ mysql

[client] applies to every MySQL tool (mysql, mysqldump, mysqladmin), while [mysql] only applies to the interactive client. The chmod matters: the password is in plain text.

Login paths: an obfuscated alternative

mysql_config_editor stores credentials in ~/.mylogin.cnf, which is obfuscated rather than plain text, and lets you name several connections:

$ mysql_config_editor set --login-path=orbit-dev --host=127.0.0.1 --user=orbit --password
Enter password:
$ mysql --login-path=orbit-dev orbit
$ mysql_config_editor print --all
[orbit-dev]
user = "orbit"
password = *****
host = "127.0.0.1"

Login paths are convenient for an administrator juggling servers. The obfuscation stops shoulder-surfing, not a determined attacker who can read the file, so keep file permissions tight either way.

Client commands

The client has its own commands that are not SQL. They start with a backslash and need no semicolon:

CommandWhat it does
\GEnd a statement and print the result vertically, one column per line
\sStatus: version, current user, database, connection, character sets, uptime
\u dbSwitch database (same as USE db;)
\. file.sqlRun a file (same as SOURCE file.sql)
\P less -SSend results through a pager; \n turns it off
\T log.txtCopy everything to a file (tee); \t stops
\! cmdRun a shell command (Linux and macOS)
\eEdit the current statement in $EDITOR
\WShow warnings after every statement
\hHelp on client commands; help SELECT shows SQL help

\G is the one you will use daily. Wide rows are unreadable as a table, but clear vertically:

SELECT user, host, plugin FROM mysql.user WHERE user = 'orbit'\G
*************************** 1. row ***************************
  user: orbit
  host: localhost
plugin: caching_sha2_password
1 row in set (0.00 sec)

Tip: pager less -SFX lets you scroll wide tables sideways with the arrow keys and quits automatically when the output fits on one screen.

Exploring a server with SHOW

SHOW statements answer the “what is in here?” questions:

SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| orbit              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

The orbit account only sees databases it has privileges on, plus the two virtual ones. As root you would also see mysql and sys.

StatementShows
SHOW DATABASES;Databases you can access
SHOW TABLES;Tables and views in the current database
SHOW FULL TABLES;Same, plus whether each is a table or view
SHOW COLUMNS FROM notes; / DESCRIBE notes;Columns, types, keys, defaults
SHOW CREATE TABLE notes\GThe exact DDL to recreate a table
SHOW INDEX FROM notes;Indexes and their columns
SHOW PROCESSLIST;Running connections and queries
SHOW VARIABLES LIKE 'max_conn%';Server settings matching a pattern
SHOW STATUS LIKE 'Threads%';Server counters
SHOW WARNINGS;Warnings from the last statement
SHOW GRANTS;Your privileges

The LIKE patterns use % for “anything”, so SHOW VARIABLES LIKE '%timeout%' lists every timeout setting.

Running SQL from files and scripts

Put statements in a file and run it, either from inside the client or from the shell:

$ mysql orbit < schema.sql
$ mysql -e "SELECT COUNT(*) FROM notes" orbit

For scripts, three options make the output machine-friendly:

OptionEffect
-e "SQL"Run the statement(s) and exit
-B / --batchTab-separated output, no borders
-N / --skip-column-namesNo header line
$ count=$(mysql -BN -e "SELECT COUNT(*) FROM users" orbit)
$ echo "Orbit has $count users"

A shell heredoc keeps longer SQL readable inside a script:

$ mysql orbit <<'SQL'
SELECT id, title FROM notes WHERE pinned;
SQL

The quotes around 'SQL' stop the shell from expanding $ and backticks inside the block.

--table forces bordered output even when piping, --html and --xml produce those formats, and --vertical is the command-line equivalent of \G.

A prompt that tells you where you are

When you work on several servers, a clear prompt prevents expensive mistakes. Set it in ~/.my.cnf:

[mysql]
prompt = "\u@\h [\d]> "
orbit@127.0.0.1 [orbit]>

\u is the user, \h the host, \d the current database. Many teams use a different prompt (or colour, via their terminal profile) for production.

Two more client settings worth knowing:

  • --safe-updates (alias --i-am-a-dummy) refuses UPDATE and DELETE without a key in WHERE or a LIMIT. Lesson 6 shows it in action.
  • --auto-rehash enables tab completion of table and column names, at the cost of a slower start on large schemas.

Quick reference

TaskCommand
Connectmysql -u USER -p -h HOST -P PORT DB
Store credentials~/.my.cnf with [client], or mysql_config_editor set --login-path=...
Vertical outputend the statement with \G
Connection status\s
Run a fileSOURCE file.sql; or mysql db < file.sql
One-off query for scriptsmysql -BN -e "SQL" db
Table structureSHOW CREATE TABLE t\G, DESCRIBE t;
Helphelp SELECT

Exercise

You run mysql -h localhost -P 3307 but still reach the server on port 3306. Why?

  1. -P must be lowercase
  2. With localhost, the client connects through the Unix socket and ignores the port
  3. The port can only be set in my.cnf
  4. Port 3307 is reserved for MySQL Shell
Show answer

Answer: B. localhost tells the client to use the socket file. Use -h 127.0.0.1 or –protocol=TCP to make it honour -P.

Exercise

Which is the safest way to give a cron script access to MySQL without typing a password?

  1. Put -pSecret in the crontab line
  2. Export the password in an environment variable in /etc/profile
  3. Use an option file or login path readable only by the script’s user
  4. Give the account an empty password
Show answer

Answer: C. Passwords on the command line leak through shell history and the process list. An option file or login path with restrictive permissions keeps the secret out of both.

Exercise

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

  1. Runs it in the background
  2. Groups the rows by the first column
  3. Shows the query plan
  4. Prints each row vertically, one column per line
Show answer

Answer: D. \G is a client command that terminates the statement and switches to vertical output, which is much easier to read for wide rows.

Key takeaways

  • Use -p without a value, ~/.my.cnf with chmod 600, or a login path; never put passwords on the command line.
  • localhost means socket; use 127.0.0.1 for TCP.
  • \G, \s, SOURCE and pager are the client commands you will use most.
  • SHOW TABLES, SHOW CREATE TABLE, SHOW PROCESSLIST and SHOW VARIABLES LIKE answer most “what is going on?” questions.
  • For scripts, combine -B, -N and -e, or pipe a heredoc.

FAQ

What is MySQL Shell and should I use it instead?

MySQL Shell (mysqlsh) is a newer client that speaks SQL, JavaScript and Python, and includes administration utilities: fast parallel dump and load, upgrade checks and InnoDB Cluster setup. Keep mysql for everyday SQL and use mysqlsh for those utilities; lessons 19 and 21 use it.

How do I see the warnings behind “Query OK, 1 row affected, 1 warning”?

Run SHOW WARNINGS; immediately after the statement. Warnings often reveal silent data changes such as truncated strings or invalid dates converted to zero, so do not ignore them.

Can I cancel a long-running query without closing the client?

Press Ctrl+C once: since MySQL 5.7 the client sends a KILL QUERY for the running statement and keeps the session open. From another session you can also run KILL QUERY <id> using the id from SHOW PROCESSLIST.

Why does DESCRIBE show Key values like PRI, UNI and MUL?

They summarise indexes: PRI is part of the primary key, UNI the first column of a unique index, and MUL the first column of a non-unique index where a value may appear multiple times.

↑ ↓ navigate Enter open Esc close