MySQL Connection

Overview

Connecting to MySQL is the first step in working with databases. This chapter covers various methods to connect to MySQL server, connection parameters, and troubleshooting connection issues.

Connection Methods

MySQL supports multiple connection methods:

Command-line client (mysql)
MySQL Workbench
Programming language connectors
**APIs and ODBC

MySQL Client Connection

Basic Connection

# Connect to local MySQL server
mysql -u root -p

# Connect with specific database
mysql -u root -p database_name

# Connect to remote server
mysql -h hostname -u username -p database_name

# Connect with specific port
mysql -h hostname -P 3307 -u username -p database_name

# Connect using socket
mysql -u root -p --socket=/var/run/mysqld/mysqld.sock

# Connect via tunnel
ssh -L 3306:localhost:3306 user@server
mysql -u user -p

Connection Parameters

| Parameter |-----------------|------------------| | -u, --user | Username | -p, --password | Prompt for password | -h, --host | Server hostname | -P, --port | Port number | -D, --database | Database name | -e, --execute | Execute command | -E, --vertical | Vertical output | -t, --table | Table output | -v, --verbose | Verbose mode | --socket | Socket file | --protocol | Connection protocol | --ssl-ca | SSL CA certificate | --default-character-set | Character set

Quick Commands

# Execute query without interactive mode
mysql -u root -p -e "SELECT VERSION(), CURRENT_USER();"

# Execute from file
mysql -u root -p database_name < script.sql

# Execute and format output
mysql -u root -p -t -e "SELECT * FROM users LIMIT 5;"

# Vertical output for detailed results
mysql -u root -p -E -e "SELECT * FROM users WHERE id = 1;"

# Use default configuration file
mysql --defaults-file=/path/to/my.cnf

# Connect and run multiple commands
mysql -u root -p <<EOF
SELECT VERSION();
SELECT DATABASE();
SHOW TABLES;
EOF

Connection Configuration

MySQL Configuration File

[client]
user = root
password = your_password
host = localhost
port = 3306
socket = /var/run/mysqld/mysqld.sock
default-character-set = utf8mb4

[mysql]
prompt = "MySQL [\\d]> "
default-character-set = utf8mb4
show-warnings

[mysqldump]
quick
max_allowed_packet = 16M

Environment Variables

# Set MySQL user
export MYSQL_USER=root

# Set MySQL password (not recommended)
export MYSQL_PWD=password123

# Set MySQL host
export MYSQL_HOST=localhost

# Set MySQL port
export MYSQL_PORT=3306

# Set default database
export MYSQL_DATABASE=mydb

MySQL Options File

# User-specific options
~/.my.cnf
~/.my.cnf.d/

# Server-specific options
/etc/my.cnf
/etc/mysql/my.cnf
/etc/mysql/mysql.conf.d/mysqld.cnf

# Example ~/.my.cnf
[client]
user = root
password = your_password
host = localhost

[mysql]
prompt = "\\u@\\h [\\d]> "
pager = less -i -S

SSL

SSL Connection

# Connect with SSL
mysql -u root -p --ssl-ca=/path/to/ca.pem \
    --ssl-cert=/path/to/client-cert.pem \
    --ssl-key=/path/to/client-key.pem

# Require SSL
mysql -u root -p --ssl-mode=REQUIRED

# Verify server certificate
mysql -u root -p --ssl-mode=VERIFY_CA \
    --ssl-ca=/path/to/ca.pem

# Disable SSL (not recommended)
mysql -u root -p --ssl-mode=DISABLED

SSL Configuration

# Generate SSL certificates
mysql_ssl_rsa_setup --datadir=/var/lib/mysql

# Check SSL status
mysql -u root -p -e "SHOW VARIABLES LIKE '%ssl%';"

SSH Tunnel Connection

Create SSH Tunnel

# Local port forwarding
ssh -L 3306:localhost:3306 user@remote_host

# With specific local port
ssh -L 3307:localhost:3306 user@remote_host

# Persistent tunnel with autossh
autossh -M 0 -N -L 3306:localhost:3306 user@remote_host

# SSH tunnel with key
ssh -i /path/to/private_key -L 3306:localhost:3306 user@remote_host

Connect Through Tunnel

# After SSH tunnel is established
mysql -u user -p -h 127.0.0.1 -P 3306

Programming Language Connections

Python (mysql-connector-python) / Python

import mysql.connector

# Basic connection
conn = mysql.connector.connect(
    host='localhost',
    user='username',
    password='password',
    database='database_name',
    port=3306
)

# Using connection pool
from mysql.connector import pooling

pool = pooling.MySQLConnectionPool(
    pool_name="mypool",
    pool_size=5,
    host='localhost',
    user='username',
    password='password',
    database='database_name'
)

conn = pool.get_connection()

# Execute query
cursor = conn.cursor()
cursor.execute("SELECT * FROM users")
results = cursor.fetchall()

# Close connection
cursor.close()
conn.close()

PHP (mysqli) / PHP

<?php
// Basic connection
$conn = new mysqli('localhost', 'username', 'password', 'database_name');

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

echo "Connected successfully";

// Execute query
$result = $conn->query("SELECT * FROM users");
while ($row = $result->fetch_assoc()) {
    echo $row['name'];
}

$conn->close();
?>

PHP (PDO) / PHP (PDO)

<?php
try {
    $conn = new PDO(
        "mysql:host=localhost;dbname=database_name",
        'username',
        'password',
        [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
    );
    
    $stmt = $conn->query("SELECT * FROM users");
    $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    print_r($results);
} catch (PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}
?>

Java (JDBC) / Java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class MySQLConnection {
    public static void main(String[] args) {
        try {
            // Load driver
            Class.forName("com.mysql.cj.jdbc.Driver");
            
            // Connection string
            String url = "jdbc:mysql://localhost:3306/database_name";
            String user = "username";
            String password = "password";
            
            // With SSL
            String sslUrl = "jdbc:mysql://localhost:3306/database_name" +
                "?useSSL=true&requireSSL=true&verifyServerCertificate=true";
            
            Connection conn = DriverManager.getConnection(url, user, password);
            
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM users");
            
            while (rs.next()) {
                System.out.println(rs.getString("name"));
            }
            
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Node.js (mysql2) / Node.js

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

// Basic connection
const connection = await mysql.createConnection({
    host: 'localhost',
    user: 'username',
    password: 'password',
    database: 'database_name'
});

// Execute query
const [rows] = await connection.execute('SELECT * FROM users');
console.log(rows);

// Connection pool
const pool = mysql.createPool({
    host: 'localhost',
    user: 'username',
    password: 'password',
    database: 'database_name',
    waitForConnections: true,
    connectionLimit: 10,
    queueLimit: 0
});

const [rows] = await pool.query('SELECT * FROM users');

C# (.NET) / C#

using MySql.Data.MySqlClient;

string connectionString = 
    "Server=localhost;Database=database_name;" +
    "Uid=username;Pwd=password;SslMode=Preferred;";

using var conn = new MySqlConnection(connectionString);
conn.Open();

var cmd = new MySqlCommand("SELECT * FROM users", conn);
using var reader = cmd.ExecuteReader();
while (reader.Read())
{
    Console.WriteLine(reader.GetString("name"));
}

Connection Pooling

Python Connection Pool

from mysql.connector import pooling

pool = pooling.MySQLConnectionPool(
    pool_name="mypool",
    pool_size=10,
    pool_reset_session=True,
    host='localhost',
    user='username',
    password='password',
    database='database_name'
)

# Get connection from pool
conn = pool.get_connection()
cursor = conn.cursor()
cursor.execute("SELECT * FROM users")
# ... operations
cursor.close()
conn.close()  # Returns to pool

PHP Persistent Connections

<?php
// Persistent connection
$conn = new mysqli('p:localhost', 'username', 'password', 'database_name');
?>

Troubleshooting Connections

Common Errors

# Access denied
ERROR 1045 (28000): Access denied for user 'user'@'host'

# Unknown host
ERROR 2005 (HY000): Unknown MySQL server host 'hostname'

# Connection refused
ERROR 2003 (HY000): Can't connect to MySQL server on 'host'

# Can't connect to local MySQL server
ERROR 2002 (HY000): Can't connect to local MySQL server through socket

# Too many connections
ERROR 1040 (08004): Too many connections

Diagnostic Commands

# Test connection
mysql -u username -p -h hostname

# Check if MySQL is running
sudo systemctl status mysql
sudo netstat -tlnp | grep 3306

# Test port connectivity
telnet hostname 3306
nc -zv hostname 3306

# Check MySQL listening ports
sudo netstat -tlnp | grep mysql

# Test remote connectivity
mysql -h hostname -u username -p -e "SELECT 1;"

Debug Connection Issues

-- Check user host permissions
SELECT User, Host FROM mysql.user WHERE User = 'username';

-- Check user privileges
SHOW GRANTS FOR 'username'@'host';

-- Check connection limits
SHOW VARIABLES LIKE 'max_connections';
SHOW STATUS LIKE 'Threads_connected';

-- Check current connections
SHOW PROCESSLIST;

Firewall and Network

# Check if port is accessible
telnet hostname 3306

# Test with nc
nc -zv hostname 3306

# Check firewall rules
sudo iptables -L -n | grep 3306
sudo firewall-cmd --list-all

# Allow MySQL port
sudo ufw allow 3306/tcp
sudo firewall-cmd --permanent --add-service=mysql

Connection Security

Best Practices

# Use strong passwords
mysql -u username -p  # Prompts for password

# Use SSL connections
mysql -u username -p --ssl-mode=REQUIRED

# Use SSH tunnel for remote connections
ssh -L 3306:localhost:3306 user@server
mysql -u username -p -h 127.0.0.1

# Don't expose MySQL port directly
# Use firewall to restrict access
sudo ufw allow from 192.168.1.0/24 to any port 3306

Connection Timeouts

-- Set connection timeout
SET GLOBAL connect_timeout = 10;

-- Set wait timeout (inactive connections)
SET GLOBAL wait_timeout = 28800;

-- Set interactive timeout
SET GLOBAL interactive_timeout = 28800;

-- Check current settings
SHOW VARIABLES LIKE '%timeout%';

Summary

MySQL connection methods include:

CLI Connection: mysql client with various options
GUI Tools: MySQL Workbench, phpMyAdmin
Programming APIs: Python, PHP, Java, Node.js, C#
Connection Pooling: For efficient resource management
Security: SSL

Previous: Administration

Next: Create Database

#MySQL Connection

#Overview

#Connection Methods

#MySQL Client Connection

#Basic Connection

#Connection Parameters

#Quick Commands

#Connection Configuration

#MySQL Configuration File

#Environment Variables

#MySQL Options File

#SSL

#SSL Connection

#SSL Configuration

#SSH Tunnel Connection

#Create SSH Tunnel

#Connect Through Tunnel

#Programming Language Connections

#Python (mysql-connector-python) / Python

#PHP (mysqli) / PHP

#PHP (PDO) / PHP (PDO)

#Java (JDBC) / Java

#Node.js (mysql2) / Node.js

#C# (.NET) / C#

#Connection Pooling

#Python Connection Pool

#PHP Persistent Connections

#Troubleshooting Connections

#Common Errors

#Diagnostic Commands

#Debug Connection Issues

#Firewall and Network

#Connection Security

#Best Practices

#Connection Timeouts

#Summary