MySQL connection string: mysql:// URLs, mysql flags and option files

The mysql:// URL

mysql://[user[:password]@]host[:port][/database][?option=value[&...]]

The parts before ? mean the same everywhere. The options after it don’t: each driver defines its own.

When a hosting provider gives you a URL, use it with the tool it was written for, and check that tool’s documentation for the options.

We checked the URL form with MySQL Shell 8.4.10 against MySQL 8.4.11:

mysqlsh --sql 'mysql://inlet:<password>@127.0.0.1:3306/inlet?ssl-mode=REQUIRED' \
  -e "select current_user(), database(); show session status like 'Ssl_version';"

current_user()	database()
inlet@%	inlet
Variable_name	Value
Ssl_version	TLSv1.3

localhost means the socket

MySQL clients treat the host name localhost specially on Unix: they connect through the Unix socket file, not TCP. The server sees the difference:

mysql://inlet:…@localhost/inlet        → user() = inlet@localhost
mysql://inlet:…@127.0.0.1:3306/inlet   → user() = inlet@127.0.0.1

So a server in Docker, reached through a published port, needs 127.0.0.1, not localhost; otherwise you get “Can’t connect to local MySQL server through socket” (error 2002). With the mysql client, --protocol=TCP forces TCP even for localhost.

To name a socket in a MySQL Shell URL, percent-encode the path in place of the host, or pass it as an option. Both of these connected:

mysql://inlet:<password>@/var%2Frun%2Fmysqld%2Fmysqld.sock/inlet
mysql://inlet:<password>@localhost/inlet?socket=%2Fvar%2Frun%2Fmysqld%2Fmysqld.sock

The mysql client’s flags

The mysql command-line client (and mysqldump, mysqladmin) take flags instead of a URL:

mysql -h db.example.com -P 3306 -u app -p --ssl-mode=VERIFY_IDENTITY --ssl-ca=<ca.pem> shop

A password on the command line works, with a warning, because other users on the machine may see it in the process list:

mysql: [Warning] Using a password on the command line interface can be insecure.

ssl-mode

--ssl-mode (MySQL’s clients, MySQL Shell; sslMode in Connector/J) decides whether to use TLS and what to check. The default is PREFERRED.

What each did against MySQL 8.4.11, which has TLS on with its own self-signed certificate:

DISABLED          Ssl_version	(empty)
PREFERRED         Ssl_version	TLSv1.3
REQUIRED          Ssl_version	TLSv1.3
VERIFY_CA         ERROR 2026 (HY000): SSL connection error: CA certificate is required if ssl-mode is VERIFY_CA or VERIFY_IDENTITY

With the server’s CA file, --ssl-mode=VERIFY_CA --ssl-ca=ca.pem connected, and VERIFY_IDENTITY failed because the auto-generated certificate isn’t issued for the host name we used:

ERROR 2026 (HY000): SSL connection error: error:0A000086:SSL routines::certificate verify failed

Notes:

• If you give --ssl-ca without --ssl-mode, the client uses VERIFY_CA.
• PREFERRED doesn’t encrypt Unix-socket connections; socket traffic never leaves the machine.
• Use VERIFY_IDENTITY across any network you don’t control. Hosted services document which CA to trust; some publish a CA file to download.
• TLS off and caching_sha2_password (MySQL 8’s default sign-in method): the first sign-in after the server starts, or after the password changes, needs either TLS or the server’s RSA key:

  ERROR 2061 (HY000): Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.
  

  Use TLS, or add --get-server-public-key (which trusts whatever key the server sends). With it, the same command connected.

MariaDB’s client is different

The mariadb client has no --ssl-mode:

mariadb: unknown variable 'ssl-mode=REQUIRED'

It uses --ssl and --ssl-verify-server-cert, both on by default in the MariaDB 11.4 client, and --skip-ssl to turn TLS off. Against MariaDB 11.4.13 it connected with TLS 1.3 and no flags.

Option files

An option file saves you typing the same flags. On macOS and Linux, the mysql client reads, in order (later files override earlier ones):

1. /etc/my.cnf
2. /etc/mysql/my.cnf
3. <sysconfdir>/my.cnf (for Homebrew, under the Homebrew prefix)
4. the file given with --defaults-extra-file
5. ~/.my.cnf
6. ~/.mylogin.cnf (written by mysql_config_editor)

mysql --help | grep -A1 'Default options' prints the exact list for your build. Inside it, [client] applies to every MySQL client program and [mysql] to the mysql client only:

# ~/.my.cnf  (chmod 600)
[client]
user=app
password=<password>
host=db.example.com
port=3306
ssl-mode=VERIFY_IDENTITY
ssl-ca=/Users/<you>/certs/ca.pem

[mysql]
database=shop

We fed a file like this to the client with --defaults-extra-file, and it connected over TLS as inlet@127.0.0.1 to database inlet with no other flags. Useful options (each must come first on the command line):

• --defaults-file=<path>: read only this file.
• --defaults-extra-file=<path>: read this file as well as the usual ones.
• --no-defaults: read none (except ~/.mylogin.cnf).
• --print-defaults: show what would be read, with the password masked:

  mysql would have been started with the following arguments:
  --socket=/var/run/mysqld/mysqld.sock --user=inlet --password=***** --host=127.0.0.1
  

MySQL ignores option files that everyone can write to. Make yours readable only by you, since it may hold a password.

Login paths

mysql_config_editor stores host, user, password, port and socket under a name in ~/.mylogin.cnf:

mysql_config_editor set --login-path=prod --host=db.example.com --user=app --password
mysql --login-path=prod shop

The file is obfuscated rather than plain text. MySQL’s documentation is clear that this stops accidental exposure, not a determined attacker with access to your account.

Environment variables

In Inlet

Paste a mysql:// URL into a new connection and Inlet fills in the form. Passwords go in the Keychain (or Inlet asks every time), and SSH tunnels go through the system ssh, so your ~/.ssh/config and agent work. Inlet works with MySQL and MariaDB: browsing, editing, structure, queries, an Activity monitor and accounts.