Connecting to a Server — MySQL

Connecting to MySQL

Connecting to MySQL from your application is the bridge between your code and your data. Every language has a MySQL driver, and understanding how connections, connection pooling, and error handling work is critical for building reliable applications.

This tutorial covers connecting from Node.js, Python, PHP, and Java — the four most common language ecosystems for MySQL applications — plus SSL/TLS and pooling best practices.

Connection String Anatomy

A MySQL connection string (DSN — Data Source Name) encodes all the parameters needed to establish a connection:

Bash

# General format
mysql://username:password@hostname:port/database_name?option1=value1&option2=value2

# Examples
mysql://appuser:secret@localhost:3306/myapp_db
mysql://appuser:secret@db.example.com:3306/myapp_db?ssl=true&connectTimeout=10000

Component	Default	Description
username	(required)	MySQL user account name
password	(required)	Account password — never hard-code in source code
hostname	localhost	Server hostname or IP address
port	3306	TCP port MySQL listens on
database_name	(optional)	Database to select on connect
ssl	false	Enable SSL/TLS encryption
connectTimeout	10000 ms	Milliseconds before a connection attempt times out
charset	utf8mb4	Character set for the connection

Warning

Never hard-code database credentials in source code. Use environment variables (process.env.DB_PASSWORD in Node.js, os.environ in Python) or a secrets manager like AWS Secrets Manager, Vault, or 1Password Secrets Automation.

Connecting from Node.js (mysql2)

The mysql2 package is the modern, recommended MySQL driver for Node.js. It supports prepared statements, async/await, connection pooling, and MySQL 8's default authentication plugin (caching_sha2_password).

Bash

npm install mysql2

Single Connection

Bash

// db.js — create and export a connection pool
const mysql = require('mysql2/promise');

const connection = await mysql.createConnection({
  host: process.env.DB_HOST || 'localhost',
  port: process.env.DB_PORT || 3306,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  charset: 'utf8mb4',
});

// Run a query
const [rows] = await connection.execute(
  'SELECT id, email FROM users WHERE active = ?',
  [1]
);
console.log(rows);

// Always close when done
await connection.end();

Connection Pool (Recommended for Applications)

Bash

// pool.js — reuse connections across requests
const mysql = require('mysql2/promise');

const pool = mysql.createPool({
  host:             process.env.DB_HOST,
  user:             process.env.DB_USER,
  password:         process.env.DB_PASSWORD,
  database:         process.env.DB_NAME,
  waitForConnections: true,
  connectionLimit:  10,      // max simultaneous connections
  queueLimit:       0,       // 0 = unlimited queue
  enableKeepAlive:  true,
  keepAliveInitialDelay: 0,
});

module.exports = pool;

// Usage in a route handler
const pool = require('./pool');

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

// Transactions with a pool
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(); // return connection to the pool
  }
}

Tip

Always use execute() with parameterized queries instead of query() with string concatenation. Parameterized queries prevent SQL injection and allow MySQL to cache execution plans.

Connecting from Python (mysql-connector-python)

Bash

pip install mysql-connector-python
# Or use PyMySQL: pip install pymysql
# Or use SQLAlchemy with mysqlclient: pip install sqlalchemy mysqlclient

Bash

# connect.py
import mysql.connector
import os

# Basic connection
conn = mysql.connector.connect(
    host=os.environ['DB_HOST'],
    user=os.environ['DB_USER'],
    password=os.environ['DB_PASSWORD'],
    database=os.environ['DB_NAME'],
    charset='utf8mb4',
    use_unicode=True,
)

cursor = conn.cursor(dictionary=True)  # returns dicts instead of tuples
cursor.execute("SELECT id, email FROM users WHERE active = %s", (1,))
users = cursor.fetchall()  # list of dicts

cursor.close()
conn.close()

Bash

# Connection pooling with mysql-connector-python
from mysql.connector import pooling
import os

pool = pooling.MySQLConnectionPool(
    pool_name="mypool",
    pool_size=10,
    host=os.environ['DB_HOST'],
    user=os.environ['DB_USER'],
    password=os.environ['DB_PASSWORD'],
    database=os.environ['DB_NAME'],
)

def get_user(user_id):
    conn = pool.get_connection()
    try:
        cursor = conn.cursor(dictionary=True)
        cursor.execute("SELECT * FROM users WHERE id = %s", (user_id,))
        return cursor.fetchone()
    finally:
        cursor.close()
        conn.close()  # returns to pool

Note

For Python web applications (Flask, FastAPI, Django), use SQLAlchemy as the ORM layer — it handles connection pooling, query building, and migrations. SQLAlchemy uses mysqlclient or PyMySQL as the underlying MySQL driver.

Connecting from PHP (PDO)

PHP Data Objects (PDO) is the recommended MySQL interface in PHP — it supports prepared statements, works with multiple databases, and is built into PHP core.

Bash

<?php
// db.php — create a PDO connection
function getDbConnection(): PDO {
    $dsn = sprintf(
        'mysql:host=%s;port=%s;dbname=%s;charset=utf8mb4',
        $_ENV['DB_HOST'],
        $_ENV['DB_PORT'] ?? '3306',
        $_ENV['DB_NAME']
    );

    $options = [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES   => false, // use real prepared statements
        PDO::MYSQL_ATTR_FOUND_ROWS   => true,
    ];

    return new PDO($dsn, $_ENV['DB_USER'], $_ENV['DB_PASSWORD'], $options);
}

// Using the connection
$pdo = getDbConnection();

// Parameterized query (prevents SQL injection)
$stmt = $pdo->prepare('SELECT id, email FROM users WHERE active = :active');
$stmt->execute([':active' => 1]);
$users = $stmt->fetchAll(); // array of associative arrays

// Transactions
try {
    $pdo->beginTransaction();
    $pdo->prepare('UPDATE accounts SET balance = balance - ? WHERE id = ?')
        ->execute([$amount, $fromId]);
    $pdo->prepare('UPDATE accounts SET balance = balance + ? WHERE id = ?')
        ->execute([$amount, $toId]);
    $pdo->commit();
} catch (Exception $e) {
    $pdo->rollBack();
    throw $e;
}

Warning

Always set PDO::ATTR_EMULATE_PREPARES => false to use native MySQL prepared statements. Emulated prepares do client-side escaping which is less secure and bypasses MySQL query plan caching.

Connecting from Java (JDBC)

Bash

<!-- Maven dependency for MySQL JDBC driver -->
<!-- Add to pom.xml -->
<!--
<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>8.4.0</version>
</dependency>
-->

Bash

// Basic JDBC connection
import java.sql.*;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

// With HikariCP connection pool (production recommended)
HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:mysql://" + System.getenv("DB_HOST")
    + ":3306/" + System.getenv("DB_NAME")
    + "?useSSL=true&serverTimezone=UTC&characterEncoding=UTF-8");
config.setUsername(System.getenv("DB_USER"));
config.setPassword(System.getenv("DB_PASSWORD"));
config.setMaximumPoolSize(10);
config.setMinimumIdle(2);
config.setConnectionTimeout(30000);
config.setIdleTimeout(600000);
config.setMaxLifetime(1800000);

HikariDataSource dataSource = new HikariDataSource(config);

// Using the pool
try (Connection conn = dataSource.getConnection();
     PreparedStatement stmt = conn.prepareStatement(
         "SELECT id, email FROM users WHERE active = ?")) {
    stmt.setInt(1, 1);
    try (ResultSet rs = stmt.executeQuery()) {
        while (rs.next()) {
            System.out.println(rs.getInt("id") + ": " + rs.getString("email"));
        }
    }
}

Note

HikariCP is the fastest and most widely used Java connection pool. Spring Boot uses it by default. Always configure maxLifetime to be less than MySQL's wait_timeout (default 8 hours) to prevent stale connection errors.

Connection Pooling Best Practices

Pool size: A common formula is (2 * number_of_CPU_cores) + effective_spindle_count. For most web apps, 5–20 connections per application instance is appropriate.
Don't over-pool: More connections are not always better. MySQL has overhead per connection. 1000 connections × 1MB memory = 1GB just for connection overhead.
Connection validation: Configure the pool to test connections before use (e.g., <code>testOnBorrow=true</code> or <code>connectionTestQuery=SELECT 1</code>).
Idle timeout: Set an idle connection timeout shorter than MySQL's <code>wait_timeout</code> (default 8h) to avoid "MySQL server has gone away" errors.
Max lifetime: Rotate connections periodically to handle server-side timeout resets and load balancer reconnects.
Release promptly: Always return connections to the pool in a finally block or use a try-with-resources pattern.

SSL/TLS Connections

Always encrypt MySQL connections when the server is remote — especially for cloud databases.

Bash

// Node.js — SSL connection
const pool = mysql.createPool({
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  ssl: {
    // For AWS RDS, download the CA bundle from docs.aws.amazon.com
    ca: fs.readFileSync('/path/to/rds-ca-bundle.pem'),
    rejectUnauthorized: true,  // verify the server certificate
  },
});

Bash

# Python — SSL connection
conn = mysql.connector.connect(
    host=os.environ['DB_HOST'],
    user=os.environ['DB_USER'],
    password=os.environ['DB_PASSWORD'],
    database=os.environ['DB_NAME'],
    ssl_ca='/path/to/ca.pem',
    ssl_verify_cert=True,
)

Tip

Cloud-managed MySQL services (AWS RDS, Google Cloud SQL, Azure Database for MySQL, PlanetScale) provide SSL by default and supply CA certificate bundles for download. Always enable SSL verification — don't set ssl_verify_cert=False or rejectUnauthorized=false in production.

Handling Connection Errors

Bash

// Node.js — robust connection with retry logic
const mysql = require('mysql2/promise');

async function createPoolWithRetry(maxAttempts = 5) {
  for (let attempt = 1; attempt <= maxAttempts; attempt++) {
    try {
      const pool = mysql.createPool({
        host:            process.env.DB_HOST,
        user:            process.env.DB_USER,
        password:        process.env.DB_PASSWORD,
        database:        process.env.DB_NAME,
        connectionLimit: 10,
      });
      // Test the connection
      await pool.execute('SELECT 1');
      console.log('Database connected successfully');
      return pool;
    } catch (err) {
      console.error(`Connection attempt ${attempt} failed: ${err.message}`);
      if (attempt === maxAttempts) throw err;
      // Exponential backoff: 1s, 2s, 4s, 8s...
      await new Promise(r => setTimeout(r, Math.pow(2, attempt - 1) * 1000));
    }
  }
}

Error Code	Meaning	Common Cause
ER_ACCESS_DENIED_ERROR (1045)	Wrong username or password	Incorrect credentials or user does not exist for that host
ER_BAD_DB_ERROR (1049)	Unknown database	Database name typo or database not created yet
ECONNREFUSED	Connection refused	MySQL not running or wrong host/port
ER_CON_COUNT_ERROR (1040)	Too many connections	Pool size too large or max_connections too low
CR_SERVER_GONE_ERROR (2006)	Server has gone away	Idle connection timed out by server
ER_LOCK_WAIT_TIMEOUT (1205)	Lock wait timeout exceeded	Long-running transaction holding a row lock