CertGrid CertGrid
Hands-on Lab·MySQL

MySQL Command-Line Client

The same query, four ways: a box-drawn table, tab-separated for scripts, one field per line, and a file piped in. Plus `STATUS`, which answers where you are connected and how - the question every other connection problem starts with.

Foundations Guide 3 of 45 Beginner

Written against the versions above. Client behaviour here is stable across MySQL 8.x and is the same client Ubuntu ships as `mysql-client`.

Every command on this page ran on db-a01.
Server NameIP AddressOSRolesCPURAMHDD
db-a01192.168.0.81Ubuntu 26.04 LTSPrimary / Source / Replica Set Member 12 Core4 GB50 GB

Before you start

  1. Run one statement and exit

    -e runs a statement and returns you to the shell. It is the form worth learning first because it is what goes in scripts, cron jobs and health checks.

    Three functions worth knowing from the start. VERSION() is the server, not the client. CURRENT_USER() is the account the server matched you to. DATABASE() is empty because no database was selected - a fresh connection is not in one.

    bash Example session 
    sudo mysql -e "SELECT VERSION(), CURRENT_USER(), DATABASE()"VERSION()	CURRENT_USER()	DATABASE()8.4.10-0ubuntu0.26.04.1	root@localhost	NULL

    Expected resultVersion 8.4.10, root@localhost, and an empty database column.

    Success conditionYou can run a statement without entering the interactive client.

  2. Choose an output format on purpose

    The same query twice. --table draws the box borders you see in the manual; without it the client emits tab-separated values.

    The default is the tab-separated form whenever output is not a terminal, which is why a query that looks like a neat table by hand arrives as bare columns in a script. That is deliberate: it is the format cut, awk and a CSV importer can read.

    Ask for --table when a human is reading, and leave it off when a program is.

    bash Example session 
    sudo mysql --table -e "SELECT user, host FROM mysql.user LIMIT 3"+------------------+-----------+| user             | host      |+------------------+-----------+| appuser          | localhost || debian-sys-maint | localhost || mysql.infoschema | localhost |+------------------+-----------+sudo mysql -e "SELECT user, host FROM mysql.user LIMIT 3"user	hostappuser	localhostdebian-sys-maint	localhostmysql.infoschema	localhost

    Expected resultA bordered table, then the same three rows separated by tabs.

    Success conditionYou can produce output for a person or for a program deliberately.

  3. Turn a wide row on its side

    \G instead of a semicolon prints one field per line with the column name on the left. On a three-column query it is a convenience; on SHOW ENGINE INNODB STATUS or a row with thirty columns it is the difference between readable and not.

    Worth committing to memory now, because most of the diagnostic output later in this path is unreadable without it.

    bash Example session 
    sudo mysql -e "SELECT user, host, plugin FROM mysql.user WHERE user='root'\G"*************************** 1. row ***************************  user: root  host: localhostplugin: auth_socket

    Expected resultA * 1. row * header, then user, host and plugin one per line.

    Success conditionYou can read a wide result without horizontal scrolling.

  4. Ask the client where it is connected

    STATUS is the first command to run when anything about a connection is in doubt, and it answers several questions at once.

    The line that matters most here is Connection: Localhost via UNIX socket. That is the transport, and it is why this connection works without a password: it arrived over the unix socket, which is what auth_socket authenticates against. SSL: Not in use follows from the same thing - a unix socket is not a network.

    Current user reading root@localhost confirms which account the server matched, which is not always the one you asked for.

    bash Example session 
    sudo mysql -e "STATUS" | head -14--------------mysql  Ver 8.4.10-0ubuntu0.26.04.1 for Linux on x86_64 ((Ubuntu)) Connection id:		25Current database:Current user:		root@localhostSSL:			Not in useCurrent pager:		stdoutUsing outfile:		''Using delimiter:	;Server version:		8.4.10-0ubuntu0.26.04.1 (Ubuntu)Protocol version:	10Connection:		Localhost via UNIX socketServer characterset:	utf8mb4

    Expected resultServer 8.4.10, Current user: root@localhost, and Connection: Localhost via UNIX socket.

    Success conditionYou can establish who you are and how you got in, in one command.

  5. Connect as a password account, and see how little it can see

    appuser from the previous guide, with -p and the password attached.

    First, the warning. Using a password on the command line interface can be insecure is printed because the password is visible in the process list and in shell history. It is correct, and it appears on every command in this guide that uses one. In a script, use an option file or MYSQL_PWD; here it is shown plainly so you can see exactly what ran.

    Then the important part: SHOW DATABASES returns two rows. appuser was created with no grants, so it can see information_schema, which every account sees, and performance_schema. Not mysql, not sys. An account with no privileges is not an account that sees nothing - it is one that sees the metadata everyone gets.

    bash Example session 
    mysql -u appuser -p'S7rong-Pass!2026' -e "SELECT CURRENT_USER(), USER()" ; echo "exit=$?"mysql: [Warning] Using a password on the command line interface can be insecure.CURRENT_USER()	USER()appuser@localhost	appuser@localhostexit=0mysql -u appuser -p'S7rong-Pass!2026' -e "SHOW DATABASES" ; echo "exit=$?"mysql: [Warning] Using a password on the command line interface can be insecure.Databaseinformation_schemaperformance_schemaexit=0

    Expected resultThe password warning, appuser@localhost for both functions, then a two-row database list.

    Success conditionYou have connected as an unprivileged account and seen its horizon.

  6. Run a file instead of a statement

    Anything longer than one statement belongs in a file. Redirecting it into the client runs every statement in order and prints each result in turn.

    Two results from one invocation, each with its own header. This is the shape every schema migration, seed script and scheduled report takes - and because the file is a file, it can be reviewed, diffed and kept in version control, which a shell history entry cannot.

    bash Example session 
    printf 'SELECT NOW() AS run_at;\nSELECT COUNT(*) AS accounts FROM mysql.user;\n' | sudo tee /tmp/report.sqlSELECT NOW() AS run_at;SELECT COUNT(*) AS accounts FROM mysql.user;sudo mysql --table < /tmp/report.sql+---------------------+| run_at              |+---------------------+| 2026-08-27 12:31:17 |+---------------------++----------+| accounts |+----------+|        6 |+----------+

    Expected resultThe file's contents echoed by tee, then two separate result tables.

    Success conditionYou can run a multi-statement script and read each result.

Troubleshooting

Official sources

← All MySQL guides