Connecting your app to MySQL
A MySQL connector is the driver library that sits between your application code and the MySQL server, translating SQL statements into packets on the wire and translating result sets back into typed objects your language understands.
The 3 main families are Connector/J (the official JDBC driver for Java), the ODBC bridge, and the native C/C++ client libraries, each with a different protocol layer and a different way of managing the underlying TCP or socket connection. Picking the right one and tuning its pool is what separates a stable production service from a connection-leak incident at 3 a.m.
If you are running a Java service, start with Connector/J; for a Python or PHP workload, the native client library is the default; and for a legacy .NET or C++ desktop tool that predates the dedicated driver, the ODBC path still works. See MySQL in practice for the day-to-day connection patterns that repeat across every stack, and follow the MySQL Workbench walkthrough if you want to verify your connection parameters before you wire them into code. When the question is whether MySQL is the right database at all, Choosing between MySQL and PostgreSQL covers the trade-offs head to head. And if a runaway query hogs a connection long enough to exhaust your pool, learn to Kill a Process on Linux before you have to restart the whole service.
How Connector/J works and how to configure it
Connector/J is the JDBC 4.2 driver that ships inside every MySQL release, and since version 8.0 it has been split into the mysql-connector-j artifact (for Java 8 and later) and a legacy build for Java 7. You add it to your classpath with a single Maven dependency, then hand your connection parameters to java.sql.DriverManager. The connection string you build in code looks like this:
jdbc:mysql://db.internal:3306/orders?useSSL=true&serverTimezone=UTC&rewriteBatchedStatements=true
The query parameters after the question mark carry the real configuration. useSSL=true forces the TLS handshake that MySQL 8.0 performs by default on port 3306; rewriteBatchedStatements=true rewrites a batch of INSERTs into a single multi-row statement, which the MySQL team reports cuts round trips from one per row to one per batch of up to 10,000 rows. For a service that sends 50,000 inserts per hour, that shift drops network round trips by roughly a factor of 1,000. The driver also exposes a built-in connection pool (com.mysql.cj.jdbc.MysqlConnectionPoolDataSource) that is fine for a quick prototype, but most production Java shops replace it with HikariCP, which uses a faster, lock-lighter design and is the default pool in Spring Boot 3.
Setting up a connection pool around the JDBC driver
To size the pool correctly, match the pool ceiling to the MySQL server's max_connections limit (151 by default) minus the headroom you reserve for the monitoring tool and the DBA's ad hoc sessions. A practical rule is to set the pool max to no more than one quarter of max_connections per application instance, then multiply across instances. For a 2-node deployment against a 200-connection server, a pool of 40 connections per node leaves 120 slots open for other services and emergency logins.
Three HikariCP parameters matter most:
- Set maximumPoolSize to the per-node figure you just calculated
- Set connectionTimeout to 3000 ms so a thread waiting for a slot fails fast instead of stalling the request
- Set idleTimeout to 600000 ms (10 minutes) so idle connections are recycled before MySQL's wait_timeout (28,800 seconds by default) kills them server-side
Connector/J also supports the MySQL Connector/J connection pool's own keepalive interval, but HikariCP's connectionTestQuery and the driver's autoReconnect flag handle that job more reliably. Leave autoReconnect at false; the pool's validation query (SELECT 1) is a cleaner way to detect a dead socket.
ODBC for legacy and cross-language access
The ODBC route for MySQL is the MySQL ODBC Connector (mysql-odbc), a C library that implements the ODBC 3.5 spec on top of the same MySQL C client protocol that the native driver uses. You register the driver through the Windows ODBC Data Source Administrator or, on Linux, through a DSN file in /etc/odbcinst.ini. A typical DSN entry points at the same 3306 port and carries the same SSL parameters as the JDBC string, just expressed as key=value pairs instead of a query string. The practical difference is the API surface: ODBC exposes SQLAllocHandle, SQLConnect, SQLExecDirect, and SQLFetch, a set of roughly 80 C functions that date to the 1990s, whereas JDBC gives you the typed ResultSet and PreparedStatement interfaces that Java developers expect. If your application is a .NET 2.0 desktop tool or a COBOL batch program that predates the dedicated MySQL .NET driver, the ODBC bridge is still the path of least resistance; for anything newer, the native or JDBC driver is faster because it skips the ODBC translation layer entirely.
Native drivers and the C client protocol
The native path is the libmysqlclient (or libmariadb for MariaDB-compatible builds) C library, and it is the foundation layer that every higher-level driver ultimately calls. In C and C++, you link against it directly and work with mysql_init, mysql_real_connect, and mysql_real_query, a set of about 60 functions that map one-to-one onto the MySQL client-server protocol. PHP's PDO_MySQL and mysqli extensions wrap these same C calls, and Python's PyMySQL and mysqlclient packages do the same over the Python C-API. The protocol itself is a binary handshake over TCP: the server sends a greeting packet with its protocol version (10 as of MySQL 8.0), the client replies with an authentication packet using the caching_sha2_password scheme by default since 8.0, and every subsequent query is a 4-byte length-prefixed packet. Understanding that framing helps when you read a hex dump in tcpdump and need to tell a malformed packet from a normal one.
Comparing the connector families side by side
The table below lines up the 3 families on the dimensions that matter when you are choosing a driver for a new service.
| Attribute | Connector/J (JDBC) | ODBC (mysql-odbc) | Native C client |
|---|---|---|---|
| Primary language | Java 8+, Kotlin, Scala | Any ODBC-aware language | C, C++, PHP, Python |
| Protocol version | MySQL protocol 10, full 8.0 auth | MySQL protocol 10, 8.0 auth in 8.0+ builds | MySQL protocol 10, 8.0 auth |
| Connection pooling | Built-in pool; HikariCP common | OS-level pool (Windows) or none | Application-level pool required |
| Typical use case | Spring Boot / Jakarta EE web services | Legacy .NET, desktop tools | PHP, Python, C++ services |
The protocol layer is the same in all 3 rows, so query performance differences are almost entirely a function of the client-side encoding and the pool design, not the wire format.
Troubleshooting a dead or misbehaving connection
To diagnose a connection problem, work from the socket outward. The first thing to check is whether the TCP handshake completes: run nc -zv db.internal 3306 from the application host and confirm you get a 200 ms round trip or less. If the handshake succeeds but the driver times out, the next suspect is the authentication handshake; MySQL 8.0's caching_sha2_password requires a TLS channel for the first login of a session, so a driver that does not pass useSSL=true will stall after the greeting packet. Check the MySQL error log for the line "Access denied for user" or "Authentication plugin 'caching_sha2_password' cannot be loaded" and match the error to the driver version. If the pool shows zero available connections but the server's SHOW PROCESSLIST is empty, the connections leaked on the client side; grep the application log for a "HikariPool-1 - Connection is not available" warning and trace the thread that held the last checkout. As a last resort, restart the MySQL service with systemctl restart mysqld, which resets the connection table without dropping the data files, and then watch the process list rebuild over the next 60 seconds to confirm the pool reconnects cleanly.