Connect to a MySQL database from the command line, safely

The mysql command-line client is still the quickest way to check that a database is reachable, run a query, or load a SQL file. Connecting is one command, but the details decide whether it works and whether it is safe: which host and protocol the client really uses, whether the connection is encrypted and verified, and where your password lives. This guide covers the classic mysql client and MySQL Shell (mysqlsh), option files and login paths, TLS, socket versus TCP, and the errors you are most likely to see.

Updated for MySQL 8.4 LTS and 9.7 LTS. Tested with the MySQL 9.7.1 server and client and MySQL Shell 26.7.1 on a scratch server; every error message below was reproduced there. Commands and options are the same on 8.4.

Step 1: Check the client

Install the client from your operating system's packages, the MySQL APT/YUM repositories, Homebrew, or the MySQL Installer on Windows, then check it:

mysql --version
mysql -h 127.0.0.1 -P 3306 -u app -p shop

-p with no value makes the client prompt for the password, so it never appears in your shell history or the process list. Writing --password=secret works, but the client warns "Using a password on the command line interface can be insecure", and it is right.

If mysql --version mentions MariaDB, you have the MariaDB client. It connects to MySQL for basic work, but some options differ (MariaDB uses --ssl-verify-server-cert rather than --ssl-mode), so install the MySQL client if you follow this guide.

Step 2: Connect and choose a database

The last argument (shop above) selects the default database, which saves typing USE shop;. Once you see the mysql> prompt, end each statement with ;:

SELECT DATABASE(), CURRENT_USER(), @@version;
SHOW TABLES;
SELECT id, total FROM orders ORDER BY id LIMIT 10;

Type status (or \s) at the prompt to see what you are connected to. This is the real output from our test connection:

mysql> status
Current user:      app@localhost
SSL:               Cipher in use is TLS_AES_128_GCM_SHA256
Connection:        127.0.0.1 via TCP/IP

Check three things: the account the server matched (Current user), whether TLS is in use (SSL), and the transport (Connection). Leave with exit or \q.

Socket or TCP: what "localhost" means

On Linux and macOS, -h localhost (or no -h at all) does not use the network. The client connects through a Unix socket file, such as /var/run/mysqld/mysqld.sock on Debian and Ubuntu or /tmp/mysql.sock on Homebrew. -h 127.0.0.1 uses TCP. The two can reach different accounts and, on a machine with more than one server, even different servers.

# Unix socket (only works on the database host itself)
mysql -u app -p -S /var/run/mysqld/mysqld.sock shop

# Force TCP even when the host is "localhost"
mysql -h localhost --protocol=TCP -P 3306 -u app -p shop

On Windows, the client uses TCP by default. Socket connections are fast and never leave the host, so TLS is not used on them; the MySQL server treats a socket as a secure transport for require_secure_transport.

Encrypt and verify the connection

The client's default --ssl-mode is PREFERRED: it encrypts if the server supports TLS, but it does not check the server's certificate, so it cannot detect a machine in the middle. For any connection that crosses a network, verify the server:

mysql -h db.example.com -P 3306 -u app -p \
  --ssl-mode=VERIFY_IDENTITY \
  --ssl-ca=/etc/ssl/certs/db-ca.pem \
  shop

Managed services (Amazon RDS, Azure Database for MySQL, Google Cloud SQL) publish their CA bundles; download the bundle from the provider and pass it with --ssl-ca. A self-hosted server's automatically generated certificate has a name like MySQL_Server_9.7.1_Auto_Generated_Server_Certificate, which will never match your host name, so VERIFY_IDENTITY fails against it. Issue a proper certificate for the server's DNS name instead of dropping to REQUIRED. To force encryption for an account on the server side, create it with REQUIRE SSL.

Keep credentials out of commands: option files and login paths

Typing the same host, port and TLS options every time is how mistakes happen. Put them in ~/.my.cnf; options in [client] apply to every MySQL client program, options in [mysql] only to the mysql client:

[client]
host=db.example.com
port=3306
user=app
ssl-mode=VERIFY_IDENTITY
ssl-ca=/etc/ssl/certs/db-ca.pem

[mysql]
database=shop

chmod 600 ~/.my.cnf
mysql --print-defaults        # shows the merged options (masks the password)
mysql -p                      # connects using the file, prompts for the password

You can add password= to the file, but then anyone who can read it has the password. MySQL ignores world-writable option files on Unix; it does not refuse world-readable ones, so set chmod 600 yourself.

A login path stores host, user, password, port and socket in ~/.mylogin.cnf (on Windows, %APPDATA%\MySQL\.mylogin.cnf), written by mysql_config_editor, which prompts for the password:

mysql_config_editor set --login-path=shop-ro \
  --host=db.example.com --port=3306 --user=app --password
mysql_config_editor print --all
mysql --login-path=shop-ro shop

The file is obfuscated rather than plain text, which stops casual reading, but it is not strong protection: treat it like a password file. --login-path also works with mysqldump and mysqladmin, which makes it handy for cron jobs. Options are read in a fixed order, and the last value for an option wins:

Run SQL from scripts

# One query, tab-separated output, no header: good for scripts
mysql --login-path=shop-ro --batch --skip-column-names shop \
  -e "SELECT id, total FROM orders"

# Run a file of statements; stop at the first error (the default)
mysql --login-path=shop-ro shop < migrate.sql

For backups and restores, use the dedicated tools; see MySQL backups with mysqldump, MySQL Shell and point-in-time recovery.

MySQL Shell (mysqlsh)

MySQL Shell is the newer client. It speaks SQL, JavaScript and Python, connects with URIs, and has utilities for dumps, upgrades and InnoDB Cluster. The mysql:// scheme uses the classic protocol (port 3306 by default); mysqlx:// uses the X Protocol (port 33060).

mysqlsh --sql mysql://app@db.example.com:3306/shop \
  --ssl-mode=VERIFY_IDENTITY --ssl-ca=/etc/ssl/certs/db-ca.pem

# JSON output for scripts
mysqlsh --sql mysql://app@db.example.com:3306/shop \
  --result-format=json/array -e "SELECT id, total FROM orders"

MySQL Shell can save passwords in a secret store (the macOS keychain, Windows Credential Manager, or a login-path file) after asking you; add --save-passwords=never on shared machines. One detail we noticed in testing: with --result-format=json/array, a DECIMAL value such as 49.00 was printed as 49, so do not use that output where exact decimal formatting matters.

Common connection errors and fixes

All of these came from our test server:

If remote access means opening port 3306 to the internet, stop and reconsider: an SSH tunnel, a VPN or a private network is safer. Our server deployment and cloud setup service can put MySQL on a private network with verified TLS and least-privilege accounts.

Production checklist

• Give each person and each application its own account with only the privileges it needs; avoid root for daily work.
• Use VERIFY_IDENTITY for every connection that leaves the host.
• Keep passwords out of shell history and scripts; use prompts, option files with mode 600, or login paths. The MYSQL_PWD environment variable is deprecated and visible to other processes on some systems.
• Use --safe-updates (alias --i-am-a-dummy) for interactive sessions on production; it refuses UPDATE and DELETE without a key in the WHERE clause or a LIMIT.

When not to use the command line

• For repeatable schema changes, use migrations in your application or a migration tool, not hand-typed SQL.
• For large exports and imports, use MySQL Shell's dump and load utilities, which run in parallel.
• For choosing a server version, see MySQL 8.4 LTS, 9.7 LTS or 26.7 Innovation.

Cloudsoft Zone sets up and hardens MySQL servers and cloud databases, including private networking, TLS, accounts and backups. See our server deployment and cloud setup service.