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
| Character | In a URL | Why it breaks |
|---|---|---|
| @ | %40 | Ends the user name and password: everything after the first unencoded @ is read as the host. |
| : | %3A | Separates user from password, and host from port. |
| / | %2F | Starts the database name. |
| ? | %3F | Starts the options. |
| # | %23 | Starts a fragment: the rest of the string is thrown away. |
| % | %25 | Starts an escape itself, so a literal % must be escaped too. |
| & | %26 | Separates options. |
| + | %2B | Some parsers (PyMongo among them) read + as a space. |
| space | %20 | Ends 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.
More on MySQL
Other databases
PostgreSQL connection strings
postgresql://user:password@host:5432/database?sslmode=verify-full
MariaDB connection strings
jdbc:mariadb://host:3306/database?user=…&password=…&sslMode=verify-full
SQL Server connection strings
Server=tcp:host,1433;Database=…;User Id=…;Password=…;Encrypt=True;
Oracle connection strings
host:1521/service_name
MongoDB connection strings
mongodb://user:password@host:27017/database?authSource=admin&tls=true
SQLite connection strings
sqlite:////var/data/app.db