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
| Option | Meaning |
|---|---|
-u orbit | Account name |
-p | Ask for the password (do not write it after -p) |
-h 127.0.0.1 | Host. Leave it out to connect through the local socket |
-P 3306 | Port (capital P) |
orbit | Default 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:
| Command | What it does |
|---|---|
\G | End a statement and print the result vertically, one column per line |
\s | Status: version, current user, database, connection, character sets, uptime |
\u db | Switch database (same as USE db;) |
\. file.sql | Run a file (same as SOURCE file.sql) |
\P less -S | Send results through a pager; \n turns it off |
\T log.txt | Copy everything to a file (tee); \t stops |
\! cmd | Run a shell command (Linux and macOS) |
\e | Edit the current statement in $EDITOR |
\W | Show warnings after every statement |
\h | Help 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.
| Statement | Shows |
|---|---|
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\G | The 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:
| Option | Effect |
|---|---|
-e "SQL" | Run the statement(s) and exit |
-B / --batch | Tab-separated output, no borders |
-N / --skip-column-names | No 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) refusesUPDATEandDELETEwithout a key inWHEREor aLIMIT. Lesson 6 shows it in action.--auto-rehashenables tab completion of table and column names, at the cost of a slower start on large schemas.
Quick reference
| Task | Command |
|---|---|
| Connect | mysql -u USER -p -h HOST -P PORT DB |
| Store credentials | ~/.my.cnf with [client], or mysql_config_editor set --login-path=... |
| Vertical output | end the statement with \G |
| Connection status | \s |
| Run a file | SOURCE file.sql; or mysql db < file.sql |
| One-off query for scripts | mysql -BN -e "SQL" db |
| Table structure | SHOW CREATE TABLE t\G, DESCRIBE t; |
| Help | help SELECT |
Exercise
You run mysql -h localhost -P 3307 but still reach the server on port 3306. Why?
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?
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?
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
-pwithout a value,~/.my.cnfwithchmod 600, or a login path; never put passwords on the command line. localhostmeans socket; use127.0.0.1for TCP.\G,\s,SOURCEandpagerare the client commands you will use most.SHOW TABLES,SHOW CREATE TABLE,SHOW PROCESSLISTandSHOW VARIABLES LIKEanswer most “what is going on?” questions.- For scripts, combine
-B,-Nand-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.