Connect to MySQL

Overview

Connecting to MySQL trips people up in the same three places: picking the right driver, getting SSL and authentication right (MySQL 8 changed the default auth plugin), and pooling connections so the server doesn’t hit max_connections. Here is a working setup in Python, JavaScript, and Java that you can copy and adapt.

When to Use

Use this resource when:

• Building an app or API whose primary store is MySQL 8.x (or MariaDB 10.x, which shares the wire protocol and most drivers)
• Writing scripts that import or export data through a MySQL connection
• Migrating a codebase to a different driver or adding connection pooling to code that opens a connection per query
• Pointing a service at a managed instance such as AWS RDS, Aurora, or Cloud SQL

If you need document storage, multi-region writes, or deeper JSON querying, MySQL is the wrong default — see Connect to PostgreSQL for the closest alternative.

Solution

Python

import os
import mysql.connector

conn = mysql.connector.connect(
    host=os.getenv("DB_HOST", "localhost"),
    database=os.getenv("DB_NAME", "mydb"),
    user=os.getenv("DB_USER", "user"),
    password=os.environ["DB_PASSWORD"],
    ssl_ca="ca.pem",            # required in production
    ssl_verify_cert=True,
    connection_timeout=5,
    charset="utf8mb4"
)

cursor = conn.cursor(dictionary=True)
cursor.execute("SELECT * FROM users WHERE id = %s", (1,))
row = cursor.fetchone()
cursor.close()
conn.close()

JavaScript

const mysql = require('mysql2/promise');

const pool = mysql.createPool({
    host: process.env.DB_HOST || 'localhost',
    database: process.env.DB_NAME || 'mydb',
    user: process.env.DB_USER || 'user',
    password: process.env.DB_PASSWORD,
    ssl: { ca: require('fs').readFileSync('ca.pem') },
    connectionLimit: 10,
    waitForConnections: true,
    queueLimit: 0,
    connectTimeout: 10000
});

async function getUser(id) {
    const [rows] = await pool.execute('SELECT * FROM users WHERE id = ?', [id]);
    return rows[0];
}

Java

import java.sql.*;

public class MySQLConnect {
    public Connection connect() throws SQLException {
        String url = "jdbc:mysql://localhost:3306/mydb"
            + "?sslMode=VERIFY_CA&serverTimezone=UTC"
            + "&connectTimeout=5000&socketTimeout=30000";
        return DriverManager.getConnection(url, "user", "pass");
    }

    public void queryUser(int id) throws SQLException {
        try (Connection conn = connect();
             PreparedStatement stmt = conn.prepareStatement("SELECT * FROM users WHERE id = ?")) {
            stmt.setInt(1, id);
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                System.out.println(rs.getString("email"));
            }
        }
    }
}

Explanation

All three examples run user input through prepared statements (%s in Python, ? in mysql2 and JDBC), so the driver escapes values and SQL injection is off the table. Each mysql.connector.connect() call opens a fresh TCP socket and authenticates, which is fine for a script but too slow per request — the pool examples below fix that. The mysql2 pool keeps a fixed set of connections alive and queues work when they’re all busy. The Java version relies on com.mysql.cj.jdbc.Driver (Connector/J 8.x), which reads serverTimezone and sslMode straight from the JDBC URL.

Two details that burn people later. MySQL’s utf8 is a 3-byte encoding, not real UTF-8 — set utf8mb4 (as in the Python example, or charset: 'utf8mb4' in mysql2, characterEncoding=UTF-8 in JDBC) or emoji and 4-byte characters get mangled. And wait_timeout defaults to 28800 seconds on the server: idle pooled connections get killed underneath you, so give the pool a shorter idle timeout or keepalive (see Production Tips).

Once connected, you can run raw SQL or move to an ORM for CRUD operations.

What Works

1. Size the pool so connectionLimit × app instances stays under the server’s max_connections (151 by default) with headroom for admin sessions
2. Verify the server certificate in production (ssl_verify_cert=True, sslMode=VERIFY_CA); sslmode=require encrypts but skips identity checks
3. Give the pool an idle timeout shorter than MySQL’s wait_timeout, or idle connections come back dead on the next query
4. Use execute() / prepared statements for anything user-supplied; plain query() concatenation is how injections happen
5. Keep credentials in environment variables or a secrets manager — on AWS RDS you can skip passwords entirely with IAM database authentication

Common Mistakes

1. Installing the deprecated mysql package on Node.js — it hasn’t been maintained for years; use mysql2
2. Forgetting serverTimezone=UTC in the JDBC URL, which shifts TIMESTAMP values by the JVM’s local offset
3. Opening a new connection inside a request loop instead of borrowing from a pool — you’ll hit “Too many connections” (error 1040)
4. Letting the pool outlive wait_timeout, then serving PROTOCOL_CONNECTION_LOST errors to users
5. Assuming utf8 is enough — it stores at most 3 bytes per character; you need utf8mb4

Advanced Examples

Python with connection pool and context manager

import mysql.connector
from mysql.connector import pooling
from contextlib import contextmanager

mysql_pool = pooling.MySQLConnectionPool(
    pool_name="mypool",
    pool_size=10,
    host="localhost",
    database="mydb",
    user="user",
    password="pass",
    ssl_ca="ca.pem",
    ssl_verify_cert=True
)

@contextmanager
def get_db_cursor():
    conn = mysql_pool.get_connection()
    try:
        cursor = conn.cursor(dictionary=True)
        yield cursor
        conn.commit()
        cursor.close()
    except Exception:
        conn.rollback()
        raise
    finally:
        conn.close()  # returns the connection to the pool

# Usage
with get_db_cursor() as cur:
    cur.execute("SELECT * FROM users WHERE active = %s", (True,))
    rows = cur.fetchall()
    for row in rows:
        print(row)

JavaScript with transaction handling

const mysql = require('mysql2/promise');

const pool = mysql.createPool({
    host: 'localhost',
    database: 'mydb',
    user: 'user',
    password: 'pass',
    ssl: { ca: require('fs').readFileSync('ca.pem') },
    waitForConnections: true,
    connectionLimit: 10,
    queueLimit: 0,
    enableKeepAlive: true,
    keepAliveInitialDelay: 10000
});

async function transferBalance(fromId, toId, amount) {
    const conn = await pool.getConnection();
    try {
        await conn.beginTransaction();

        await conn.execute(
            'UPDATE accounts SET balance = balance - ? WHERE id = ?',
            [amount, fromId]
        );

        await conn.execute(
            'UPDATE accounts SET balance = balance + ? WHERE id = ?',
            [amount, toId]
        );

        await conn.commit();
    } catch (err) {
        await conn.rollback();
        throw err;
    } finally {
        conn.release();
    }
}

Java with HikariCP connection pool

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import java.sql.*;

public class MySQLPool {
    private static final HikariDataSource ds;

    static {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:mysql://localhost:3306/mydb?sslMode=VERIFY_CA&serverTimezone=UTC&rewriteBatchedStatements=true");
        config.setUsername("user");
        config.setPassword("pass");
        config.setMaximumPoolSize(20);
        config.setMinimumIdle(5);
        config.setIdleTimeout(30000);
        config.setConnectionTimeout(5000);
        config.setKeepaliveTime(300000); // refresh connections before wait_timeout kills them
        ds = new HikariDataSource(config);
    }

    public static Connection getConnection() throws SQLException {
        return ds.getConnection();
    }

    public static void batchInsert(List<String> emails) throws SQLException {
        try (Connection conn = getConnection();
             PreparedStatement stmt = conn.prepareStatement(
                 "INSERT INTO users (email) VALUES (?)")) {
            for (String email : emails) {
                stmt.setString(1, email);
                stmt.addBatch();
            }
            stmt.executeBatch(); // fast thanks to rewriteBatchedStatements=true
        }
    }
}

Python with PyMySQL (pure Python alternative)

import pymysql
from contextlib import contextmanager

@contextmanager
def get_db():
    conn = pymysql.connect(
        host="localhost",
        database="mydb",
        user="user",
        password="pass",
        ssl={"ca": "ca.pem"},
        charset="utf8mb4",
        cursorclass=pymysql.cursors.DictCursor
    )
    try:
        yield conn
        conn.commit()
    except Exception:
        conn.rollback()
        raise
    finally:
        conn.close()

# Usage
with get_db() as conn:
    with conn.cursor() as cur:
        cur.execute("SELECT * FROM users WHERE id = %s", (1,))
        row = cur.fetchone()
        print(row)

Variants

Technology	Driver	Async	Pooling	Notes
Python	mysql-connector-python	No	MySQLConnectionPool	Official Oracle driver
Python	PyMySQL	No	Manual	Pure Python, installs without C extensions
Python	aiomysql	Yes	Built-in	Async MySQL for asyncio
JavaScript	mysql2	Yes (Promise)	createPool	Recommended for Node.js
JavaScript	mysql	No (callback)	createPool	Legacy callback driver — avoid for new code
Java	JDBC + HikariCP	No	HikariCP	Industry standard — the Spring Boot pooling choice
Go	go-sql-driver/mysql	Yes	database/sql	Standard Go driver

Production Tips

1. Enable enableKeepAlive in mysql2. Idle connections get dropped by the server (wait_timeout, 28800s by default) or by network gear in between; keepalive probes stop the pool from handing you a dead socket:

const pool = mysql.createPool({
    // ... other options
    enableKeepAlive: true,
    keepAliveInitialDelay: 10000
});

2. Use LOAD DATA INFILE for bulk loads. It’s orders of magnitude faster than row-by-row INSERT for large datasets; both the server and the client driver need local_infile enabled:

LOAD DATA LOCAL INFILE '/tmp/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(id, email, name);

3. Tune wait_timeout and interactive_timeout on the server. Lower them to recycle idle connections sooner, or raise them deliberately if the pool holds connections open — either way, keep the pool’s idle timeout below the server’s:

SET GLOBAL wait_timeout = 300;
SET GLOBAL interactive_timeout = 300;

4. Use IAM authentication on AWS RDS. It replaces stored passwords with 15-minute auth tokens, generated on demand (SSL is required to use the token):

aws rds generate-db-auth-token \
  --hostname mydb.abc123.us-east-1.rds.amazonaws.com \
  --port 3306 \
  --username myuser

5. Watch SHOW PROCESSLIST for runaway queries. It shows what every connection is doing right now, so you can spot and kill the query blocking the rest:

SHOW PROCESSLIST;
KILL <thread_id>;

Troubleshooting

Error	What it means	How to fix
ECONNREFUSED	Nothing listening on host:port, or a firewall/SG blocks it	Check mysqld is up, port 3306 is open, and the client host is allowed
ETIMEDOUT / PROTOCOL_CONNECTION_LOST	Network drop, or the server killed an idle connection at wait_timeout	Enable keepalive in the pool and set its idle timeout below wait_timeout
ER_ACCESS_DENIED_ERROR (1045)	Wrong password, or no grant for that user@host pair	MySQL auth is per user+host — recreate the grant for the client’s real IP/host
ER_BAD_DB_ERROR (1049)	The database name doesn’t exist (or the user can’t see it)	Create the schema or fix the name in the connection config
Too many connections (1040)	max_connections (default 151) exhausted	Shrink pools across app instances, or raise max_connections
Authentication plugin 'caching_sha2_password' cannot be loaded	An old client/driver talking to MySQL 8+	Upgrade the driver; as a last resort, ALTER USER ... IDENTIFIED WITH mysql_native_password (deprecated, off by default in 8.4)
SSL certificate errors	Missing or outdated CA bundle	Point ssl_ca / sslMode=VERIFY_CA at a current CA bundle

See Also

• MySQL Connector/Python Developer Guide — connection options, pooling, SSL flags
• node-mysql2 documentation — pool options, prepared statements, keepalive
• HikariCP — pool sizing and keepalive settings for Java
• AWS docs: IAM database authentication for RDS — token-based logins without stored passwords