MySQL连接

概述

连接到MySQL是使用数据库的第一步。本章涵盖连接到MySQL服务器的各种方法、连接参数和连接问题故障排除。

连接方法

MySQL支持多种连接方法：

命令行客户端（mysql） MySQL工作台 编程语言连接器 JDBC** / API和ODBC/JDBC

MySQL客户端连接

基本连接

# Connect to local MySQL server

# Connect with specific database

# Connect to remote server

# Connect with specific port

# Connect using socket

# Connect via tunnel

连接参数

参数	Description / 描述
用户名
提示输入密码
服务器主机名
端口号
数据库名
执行命令
垂直输出
表格输出
详细模式
套接字文件
连接协议
SSL CA证书
字符集

快速命令

# Execute query without interactive mode

# Execute from file

# Execute and format output

# Vertical output for detailed results

# Use default configuration file

# Connect and run multiple commands

连接配置

MySQL配置文件

环境变量

# Set MySQL user

# Set MySQL password (not recommended)

# Set MySQL host

# Set MySQL port

# Set default database

MySQL选项文件

# User-specific options

# Server-specific options

# Example ~/.my.cnf

TLS Connections / SSL/TLS连接

SSL连接

# Connect with SSL
    --ssl-cert=/path/to/client-cert.pem \
    --ssl-key=/path/to/client-key.pem

# Require SSL

# Verify server certificate
    --ssl-ca=/path/to/ca.pem

# Disable SSL (not recommended)

SSL配置

# Generate SSL certificates

# Check SSL status

SSH隧道连接

创建SSH隧道

# Local port forwarding

# With specific local port

# Persistent tunnel with autossh

# SSH tunnel with key

通过隧道连接

# After SSH tunnel is established

编程语言连接

Python (mysql-connector-python) / Python

# Basic connection

# Using connection pool

# Execute query

# Close connection

PHP (mysqli) / PHP

PHP (PDO) / PHP (PDO)

Java (JDBC) / Java

Node.js (mysql2) / Node.js

C# (.NET) / C#

连接池

Python连接池

# Get connection from pool
# ... operations

PHP持久连接

连接故障排除

常见错误

# Access denied

# Unknown host

# Connection refused

# Can't connect to local MySQL server

# Too many connections

诊断命令

# Test connection

# Check if MySQL is running

# Test port connectivity

# Check MySQL listening ports

# Test remote connectivity

调试连接问题

-- Check user host permissions

-- Check user privileges

-- Check connection limits

-- Check current connections

防火墙和网络

# Check if port is accessible

# Test with nc

# Check firewall rules

# Allow MySQL port

连接安全

最佳实践

# Use strong passwords

# Use SSL connections

# Use SSH tunnel for remote connections

# Don't expose MySQL port directly
# Use firewall to restrict access

连接超时

-- Set connection timeout

-- Set wait timeout (inactive connections)

-- Set interactive timeout

-- Check current settings

小结

MySQL连接方法包括：

CLI连接：带有各种选项的mysql客户端 GUI工具：MySQL Workbench、phpMyAdmin 编程API：Python、PHP、Java、Node.js、C# 连接池：用于高效资源管理 TLS, SSH tunneling / 安全：SSL/TLS、SSH隧道

---

上一个：管理

下一个：创建数据库