MySQL JDBC URL and Connection String Parameters
For Java developers and anyone who connects a tool to MySQL: the JDBC URL format, its defaults, and the connection properties that change how the driver behaves.
On this page
An application, a connection pool or a database tool asks you for a JDBC URL, and all you have is a MySQL host, a port and a database name. A MySQL JDBC URL has this form:
jdbc:mysql://host:port/database?property=value&property=value
A URL for a server on your own machine, and one for a server on the network:
jdbc:mysql://localhost:3306/billing
jdbc:mysql://db.example.com:3306/billing?sslMode=REQUIRED&connectTimeout=5000
The properties after the ? are settings of Connector/J, MySQL's JDBC driver. The errors and results below come from runs of Connector/J 26.10.0 against MySQL 9.1.0. Which jar and Maven coordinates to use is covered in mysql-connector-j vs mysql-connector-java.
The parts of the URL and their defaults
Only the protocol is required. For each other part, the table shows what the driver uses when the URL leaves it out:
| Part | Example | When left out |
|---|---|---|
| Protocol | jdbc:mysql: | Required |
| Host | db.example.com | localhost |
| Port | 3306 | 3306 |
| Database | billing | The connection opens with no database |
| Properties | ?sslMode=REQUIRED | The driver's defaults |
With both defaults, jdbc:mysql:///billing connects to the billing database on localhost:3306. Port 3306 is MySQL's default, and port 1433 is SQL Server's. An IPv6 address goes in square brackets, as in jdbc:mysql://[::1]:3306/billing.
A URL without a database connects. A query that names a table without its database then fails with "No database selected". Put the database in the URL, or write the table as billing.invoices. In Java code, Connection.setCatalog("billing") also sets the database, and the URL format reference recommends it over a USE statement.
MySQL JDBC connection string with username and password
Pass the user name and password as separate arguments, and keep them out of the URL:
Connection conn = DriverManager.getConnection(
"jdbc:mysql://db.example.com:3306/billing", "app", "pa&ss#1");
A URL gets copied into logs, configuration files in Git and screenshots in tickets, and a password inside it is copied along. A password passed as an argument also needs no encoding.
Where a tool takes only a URL, the credentials go in as the user and password properties. In the URL, a reserved character such as &, #, @, / or : has to be percent-encoded, as % and its hexadecimal code. Without the encoding, this URL failed:
jdbc:mysql://db.example.com:3306/billing?user=app&password=pa&ss#1
Access denied for user 'app'@'172.17.0.1' (using password: YES)
The driver read the password as pa: the & started a new property, and the # cut off the rest. With & written as %26 and # as %23, the driver signed in as app:
jdbc:mysql://db.example.com:3306/billing?user=app&password=pa%26ss%231
A URL can also carry the credentials before the host, as in jdbc:mysql://app:[email protected]:3306/billing. Reserved characters need the same encoding there.
MySQL JDBC connection properties worth setting
The table gives the defaults of Connector/J 26.10.0. The configuration properties reference lists all the properties.
| Property | What it sets | Default |
|---|---|---|
sslMode | Whether the connection is encrypted, and how the server is checked | PREFERRED |
connectionTimeZone | The time zone for converting date and time values | LOCAL |
connectTimeout | Milliseconds to wait for the network connection; 0 means no limit | 0 |
socketTimeout | Milliseconds to wait for each read from the server; 0 means no limit | 0 |
rewriteBatchedStatements | Sends a batch of inserts as one multi-row INSERT | false |
allowPublicKeyRetrieval | Lets the driver ask the server for its RSA public key during login | false |
characterEncoding | The session character set | None: the driver uses utf8mb4 |
zeroDateTimeBehavior | What the driver does with a 0000-00-00 DATETIME | EXCEPTION |
Property names are case-sensitive, and the driver ignores a name it does not know, without an error. With sslmode=DISABLED, in lowercase, the driver kept the default PREFERRED, and the connection was encrypted. A wrong value of a known property fails instead. The driver refused sslMode=OFF with this message:
The connection property 'sslMode' acceptable values are: 'DISABLED', 'PREFERRED', 'REQUIRED', 'VERIFY_CA' or 'VERIFY_IDENTITY'. The value 'OFF' is not acceptable.
A property also needs a value. A URL ending in ?useServerPrepStmts fails with "The value '' is not acceptable", and one ending in ?useServerPrepStmts=true connects.
sslMode replaces useSSL, which is deprecated. An old URL with useSSL=false still works, and means sslMode=DISABLED. PREFERRED encrypts when the server supports it, and REQUIRED refuses to connect without encryption. Neither checks the server's certificate. VERIFY_CA checks the certificate against your CA certificates, and VERIFY_IDENTITY also checks that the certificate names the host. These two need a trust store with the CA certificate.
With DISABLED, a login can fail with "Public Key Retrieval is not allowed". Fix Public Key Retrieval is not allowed covers that error and allowPublicKeyRetrieval.
Set connectTimeout for any server across a network. Against the unroutable address 10.255.255.1, a URL with connectTimeout=3000 failed after 3.2 seconds with "Communications link failure". The same URL without connectTimeout failed after 21 seconds, when Windows stopped waiting.
Set socketTimeout above your longest query. With socketTimeout=2000, a SELECT SLEEP(5) failed after 2 seconds, with "Read timed out" as the cause of the error.
Set rewriteBatchedStatements=true for bulk inserts. A batch of 1,000 PreparedStatement inserts then reached the server as one INSERT. Without the property, the server counted 1,000 inserts.
With the default zeroDateTimeBehavior, getTimestamp() on a 0000-00-00 DATETIME throws "Zero date value prohibited". With zeroDateTimeBehavior=CONVERT_TO_NULL, getTimestamp() returns null.
connectionTimeZone and serverTimezone
If the server and the application run in different time zones, set connectionTimeZone to the server's zone. With the default LOCAL, the driver assumes that the server's times are in the JVM's time zone. A server on UTC stored a TIMESTAMP of 10:00 UTC, which is 11:00 in Berlin. A Java application on Europe/Berlin read it back as 10:00, one hour off. With connectionTimeZone=UTC, the application read 11:00.
serverTimezone is the older name, and it still works as an alias: serverTimezone=UTC also read 11:00. Write a zone ID such as Europe/Berlin or an offset such as +01:00. An abbreviation such as CEST fails with "Could not create connection to database server.", and the cause of the error says "Unknown time-zone ID: CEST".
connectionTimeZone sets only the zone that the driver converts with. The server's session time zone, which NOW() uses, is set by forceConnectionTimeZoneToSession=true. With connectionTimeZone=UTC alone, SELECT @@session.time_zone returned SYSTEM. With both properties, it returned +00:00.
Multi-host MySQL JDBC URLs for failover, load balancing and replication
A URL can list several hosts, separated by commas. The protocol at the start of the URL decides what the driver does with the hosts:
jdbc:mysql://db1.example.com:3306,db2.example.com:3306/sales
jdbc:mysql:loadbalance://db1.example.com:3306,db2.example.com:3306/sales
jdbc:mysql:replication://source.example.com:3306,replica1.example.com:3306/sales
jdbc:mysql:with several hosts is for failover. When the first host does not answer, the driver connects to the next host in the list.jdbc:mysql:loadbalance:spreads the connections across the hosts.jdbc:mysql:replication:treats the first host as the source, which takes the writes, and the other hosts as replicas. AfterConnection.setReadOnly(true), queries go to a replica. The replication guide has the details.
We ran both with two servers. With the first host down, the failover URL connected to the second host. The replication URL read from the first host, and from the second host after setReadOnly(true).
How to check if a MySQL JDBC URL is correct
Connect once, and read the whole error with its cause, the exception that the error wraps. Different mistakes can give the same first line, and the cause tells them apart:
| Error, and its cause | What is wrong |
|---|---|
| Communications link failure, cause "Connection refused" | No server listens on that port: a wrong port, or the server is down |
| Communications link failure, cause "Connect timed out" | The host never answered: a firewall, or a wrong address |
| Access denied for user 'app'@'172.17.0.1' (using password: YES) | The server refused the login |
| No database selected | The URL has no database |
| Unknown time-zone ID | An abbreviation in connectionTimeZone |
| Public Key Retrieval is not allowed | See the fix for that error |
A wrong password and a wrong client host give the same "Access denied". The address in the message is your machine as the server sees it. If the MySQL account allows only another host, the right password still fails. If the password is in the URL, check its encoding first.
Entering a MySQL JDBC URL in the DbSchema connection dialog
DbSchema connects to MySQL with Connector/J. In its connection dialog, the Method list offers two ways to give the URL. With Standard, DbSchema builds the URL from the fields you fill in, such as Server Host and Port. With Paste a JDBC URL, you paste the whole string, such as the one a cloud console gives you:
- Open the Method list and choose Paste a JDBC URL.
- Paste the URL into JDBC URL.
- Type the Database User and Password.
- Press Test Connection, then Connect.
You can also set the properties without typing them. On the Network and Driver tab, press the pencil next to SSL & Parameters. DbSchema lists the driver's settings by group, such as SSL / TLS, each with a short explanation. Tick a group to add its settings to the URL; an unticked group keeps the driver's defaults. To see the final URL, press the eye button next to SSL & Parameters.
DbSchema downloads the MySQL driver the first time you connect. If a proxy blocks the download, upload the driver jar yourself in the JDBC Drivers Manager.
To watch the steps, see the video MySQL JDBC Driver: Download, Configure and See Your Database as an ER Diagram.
Download DbSchema, paste your JDBC URL or let the dialog build it, and connect to your MySQL server. The free Community Edition covers connecting, the SQL Editor, and reverse-engineering the database into an interactive ER diagram.
Paste your MySQL JDBC URL and connect
DbSchema builds the URL from host, port and database or takes a pasted JDBC URL, downloads the MySQL driver the first time you connect, and draws the schema as an ER diagram. Connecting and the diagrams are in the free Community Edition.