← Connection strings

MySQL connection strings

MySQL has no single native string: every connector has its own. The mysql:// URI is what Node, Prisma and most hosting dashboards use; Java has a JDBC URL with sslMode; Go has a DSN that is not a URL at all; .NET has key=value pairs. Below is each one for the same database, and the three things that cause most "Access denied" errors.

Build yours (MySQL)

The parts of a MySQL connection string

The shape

mysql://user:password@host:3306/database
mysql://
mysql+pymysql:// for SQLAlchemy; jdbc:mysql:// for Java.
user:password@
Percent-encoded in a URL. Not in the Go DSN, which takes the password as it is.
host:3306
localhost on the mysql command line means the Unix socket, not TCP.
/database
Optional: without it you connect with no default database.

One MySQL database, every form

User app_user, password s3cret-Pa55, database shop on db.example.com, tls, verify the certificate.

URI

Node (mysql2), Prisma, DATABASE_URL · connected with on a real server

mysql://app_user:s3cret-Pa55@db.example.com:3306/shop

TLS is not part of this URL: each library sets it its own way (an ssl option in mysql2 and the mariadb connector).

JDBC (Connector/J 8+)

Java, DBeaver, IntelliJ, Spring

jdbc:mysql://db.example.com:3306/shop?user=app_user&password=s3cret-Pa55&sslMode=VERIFY_IDENTITY

SQLAlchemy

Python with SQLAlchemy and PyMySQL · connected with on a real server

mysql+pymysql://app_user:s3cret-Pa55@db.example.com:3306/shop

For TLS pass connect_args={"ssl": {...}} to create_engine; PyMySQL takes no TLS setting in the URL.

Go DSN (go-sql-driver/mysql)

Go database/sql

app_user:s3cret-Pa55@tcp(db.example.com:3306)/shop?parseTime=true&tls=true

Not a URL: the password goes in as it is, with no percent-encoding.

.NET (MySqlConnector)

C#, EF Core (Pomelo)

Server=db.example.com;Port=3306;Database=shop;User ID=app_user;Password=s3cret-Pa55;SslMode=VerifyFull

mysql command

Shell · connected with on a real server

mysql -h db.example.com -P 3306 -u app_user -p --ssl-mode=VERIFY_IDENTITY shop

-p with nothing after it asks for the password, so it stays out of your shell history.

A password with special characters

The same database with the password p@ss/w#rd 1. URLs encode it, key=value forms quote it, and each form does it differently:

URI

mysql://app_user:p%40ss%2Fw%23rd%201@db.example.com:3306/shop

JDBC (Connector/J 8+)

jdbc:mysql://db.example.com:3306/shop?user=app_user&password=p%40ss%2Fw%23rd%201&sslMode=VERIFY_IDENTITY

SQLAlchemy

mysql+pymysql://app_user:p%40ss%2Fw%23rd%201@db.example.com:3306/shop

Go DSN (go-sql-driver/mysql)

app_user:p@ss/w#rd 1@tcp(db.example.com:3306)/shop?parseTime=true&tls=true

CharacterIn a URLWhy it breaks
@%40Ends the user name and password: everything after the first unencoded @ is read as the host.
:%3ASeparates user from password, and host from port.
/%2FStarts the database name.
?%3FStarts the options.
#%23Starts a fragment: the rest of the string is thrown away.
%%25Starts an escape itself, so a literal % must be escaped too.
&%26Separates options.
+%2BSome parsers (PyMongo among them) read + as a space.
space%20Ends the string on a command line, and is trimmed by some parsers.

TLS: what each setting looks like

Shown in the JDBC (Connector/J 8+) form; the builder writes the matching setting for every other one.

No TLS

jdbc:mysql://db.example.com:3306/shop?user=app_user&password=s3cret-Pa55

TLS, don't check the certificate

jdbc:mysql://db.example.com:3306/shop?user=app_user&password=s3cret-Pa55&sslMode=REQUIRED

TLS, verify the certificate

jdbc:mysql://db.example.com:3306/shop?user=app_user&password=s3cret-Pa55&sslMode=VERIFY_IDENTITY

What catches people out on MySQL

A user is a user AND a host

'app'@'%' and 'app'@'localhost' are different accounts with different passwords. "Access denied for user 'app'@'10.0.0.5'" usually means no account matches the address you are connecting from.

localhost is not 127.0.0.1

mysql -h localhost talks to the Unix socket file; -h 127.0.0.1 uses TCP. In a container, or with skip-networking off, one works and the other doesn't.

MySQL 8 and caching_sha2_password

The default authentication plugin in MySQL 8 needs TLS or the server's RSA key on the first login. Connector/J without TLS fails with "Public Key Retrieval is not allowed" until you add allowPublicKeyRetrieval=true or turn TLS on — TLS is the better fix.

useSSL is the old switch

Connector/J 8 replaced useSSL=true/false with sslMode: DISABLED, PREFERRED, REQUIRED, VERIFY_CA or VERIFY_IDENTITY. VERIFY_IDENTITY is the only one that checks you reached the right host.

Go: parseTime=true or you get bytes

Without parseTime=true, go-sql-driver/mysql returns DATETIME and TIMESTAMP columns as []byte, not time.Time.

Prisma and most ORMs take the URL as-is

So a password copied unencoded from a dashboard into DATABASE_URL breaks on the first @ or #. Encode it once, or build the URL with the tool on this page.

Connected. Now what?

Paste the same string into DBShifts to browse MySQL with sensitive columns masked and every read logged, or to migrate it to another database with each table counted and checksummed afterwards.

Questions

What is the MySQL connection string format?

As a URL: mysql://user:password@host:3306/database. In Java: jdbc:mysql://host:3306/database?user=…&password=…&sslMode=REQUIRED. In Go: user:password@tcp(host:3306)/database?parseTime=true. In .NET: Server=…;Port=3306;Database=…;User ID=…;Password=….

How do I use a password with an @ in a MySQL connection string?

In a URL, write it as %40 (and # as %23, / as %2F, % as %25). The Go DSN is the exception: it is not a URL, and the password goes in exactly as it is.

How do I require TLS?

sslMode=REQUIRED (encrypted) or VERIFY_IDENTITY (encrypted and the certificate checked) in JDBC, --ssl-mode on the mysql client, tls=true in Go, SslMode=Required or VerifyFull in MySqlConnector.

What is the default MySQL port?

3306. The X Protocol used by MySQL Shell's mysqlx:// is on 33060.