# 推荐的连接池配置
pool_config = {
    'min_idle': 5,              # 最小空闲连接数
    'max_size': 50,             # 最大连接数
    'max_idle_time': 300,       # 空闲连接最大存活时间 (秒)
    'max_lifetime': 1800,       # 连接最大生命周期 (秒)
    'connection_timeout': 5,    # 获取连接超时 (秒)
    'validation_timeout': 3,    # 连接验证超时 (秒)
    'idle_test_interval': 60,   # 空闲检测间隔 (秒)
    'test_on_borrow': True,     # 借出前验证
    'test_on_return': False,    # 归还时验证 (开销考虑)
    'test_while_idle': True,    # 空闲时验证
}

def validate_connection(conn):
    """验证连接是否健康"""
    try:
        # 方法一：COM_PING (推荐，最轻量)
        conn.ping()

        # 方法二：简单查询
        # conn.execute("SELECT 1")

        return True
    except Exception:
        return False

# 不推荐: 每次都预处理
for user_id in user_ids:
    cursor.execute("SELECT * FROM users WHERE id = %s", (user_id,))
    # 每次执行都发送 COM_STMT_PREPARE + COM_STMT_EXECUTE + COM_STMT_CLOSE

# 推荐: 预处理一次，执行多次
stmt = conn.prepare("SELECT * FROM users WHERE id = %s")
for user_id in user_ids:
    stmt.execute((user_id,))
    # 只发送 COM_STMT_EXECUTE
stmt.close()

# 不推荐: 逐条插入
for row in data:
    cursor.execute("INSERT INTO t VALUES (%s, %s, %s)", row)

# 推荐: 批量插入 (使用 MULTI_STATEMENTS)
values = ", ".join(
    f"('{row[0]}', {row[1]}, '{row[2]}')" for row in data
)
cursor.execute(f"INSERT INTO t VALUES {values}")

# 或使用 LOAD DATA LOCAL INFILE (最快)
cursor.execute("""
    LOAD DATA LOCAL INFILE '/tmp/data.csv'
    INTO TABLE t
    FIELDS TERMINATED BY ','
""")

# 不推荐
cursor.execute("SELECT * FROM users")

# 推荐
cursor.execute("SELECT id, name, email FROM users")

# 不推荐: 全表扫描
cursor.execute("SELECT * FROM orders WHERE status = 'pending'")

# 推荐: 分批获取
cursor.execute("SELECT * FROM orders WHERE status = 'pending' LIMIT 1000")

-- 使用合适的字符集
SET NAMES utf8mb4;  -- 一次设置，减少协商

-- 调整 max_allowed_packet
SET GLOBAL max_allowed_packet = 64 * 1024 * 1024;  -- 64 MB

-- 使用 DEPRECATE_EOF 减少包数量 (MySQL 8.0)
-- 客户端在握手时设置 CLIENT_DEPRECATE_EOF 标志

-- 使用安全的认证插件
ALTER USER 'app'@'%'
  IDENTIFIED WITH caching_sha2_password BY 'StrongP@ss!';

-- 强制 SSL 连接
ALTER USER 'app'@'%' REQUIRE SSL;

-- 限制连接来源
ALTER USER 'app'@'10.0.1.%' IDENTIFIED BY 'password';

-- 密码过期策略
ALTER USER 'app'@'%' PASSWORD EXPIRE INTERVAL 90 DAY;

-- 只授予必要的权限
GRANT SELECT, INSERT, UPDATE ON mydb.users TO 'app'@'%';

-- 避免使用 GRANT ALL
-- GRANT ALL PRIVILEGES ON *.* TO 'app'@'%';  -- 不推荐!

-- 使用角色管理权限 (MySQL 8.0)
CREATE ROLE 'app_read', 'app_write';
GRANT SELECT ON mydb.* TO 'app_read';
GRANT INSERT, UPDATE, DELETE ON mydb.* TO 'app_write';
GRANT 'app_read', 'app_write' TO 'app'@'%';

# 不推荐: 禁用 SSL 验证
ssl_config = {
    'ssl_disabled': False,
    'ssl_verify_cert': False,  # 不验证证书!
}

# 推荐: 完整的 SSL 配置
ssl_config = {
    'ssl_disabled': False,
    'ssl_ca': '/path/to/ca.pem',
    'ssl_cert': '/path/to/client-cert.pem',
    'ssl_key': '/path/to/client-key.pem',
    'ssl_verify_cert': True,
    'ssl_verify_server_cert': True,
}

# 不推荐: 字符串拼接
sql = f"SELECT * FROM users WHERE id = {user_id}"
cursor.execute(sql)  # SQL 注入风险!

# 不推荐: 格式化字符串
sql = f"SELECT * FROM users WHERE name = '{name}'"
cursor.execute(sql)  # SQL 注入风险!

# 推荐: 使用参数化查询
cursor.execute("SELECT * FROM users WHERE id = %s", (user_id,))

# 推荐: 使用 ORM
user = session.query(User).filter(User.id == user_id).first()

RETRYABLE_ERRORS = {
    1205,  # ER_LOCK_WAIT_TIMEOUT (锁等待超时)
    1213,  # ER_LOCK_DEADLOCK (死锁)
    2006,  # ER_SERVER_GONE_ERROR (连接断开)
    2013,  # ER_SERVER_LOST (查询期间丢失连接)
    2003,  # ER_CONN_HOST_ERROR (连接主机错误)
    2002,  # CR_CONN_SOCKET_ERROR (socket 错误)
}


def execute_with_retry(conn, sql, params=None, max_retries=3):
    """带重试的查询执行"""
    for attempt in range(max_retries):
        try:
            cursor = conn.cursor()
            cursor.execute(sql, params)
            return cursor.fetchall()
        except MySQLdb.OperationalError as e:
            error_code = e.args[0]
            if error_code in RETRYABLE_ERRORS and attempt < max_retries - 1:
                print(f"[重试] 第 {attempt + 1} 次重试: {e}")
                time.sleep(2 ** attempt)  # 指数退避
                if error_code in (2006, 2013, 2003, 2002):
                    conn.reconnect()
            else:
                raise
        except MySQLdb.IntegrityError as e:
            # 唯一键冲突等，不重试
            raise

class ResilientConnection:
    """具有自动重连能力的连接"""

    def __init__(self, **config):
        self.config = config
        self.conn = None
        self._connect()

    def _connect(self):
        """建立连接"""
        self.conn = mysql.connector.connect(**self.config)

    def execute(self, sql, params=None):
        """执行查询，自动重连"""
        try:
            if not self.conn.is_connected():
                self._connect()
            cursor = self.conn.cursor()
            cursor.execute(sql, params)
            return cursor
        except mysql.connector.errors.OperationalError as e:
            if e.errno in (2006, 2013):
                self._connect()
                cursor = self.conn.cursor()
                cursor.execute(sql, params)
                return cursor
            raise

    def close(self):
        if self.conn:
            self.conn.close()

-- 连接相关
SHOW STATUS LIKE 'Threads_connected';      -- 当前连接数
SHOW STATUS LIKE 'Threads_running';         -- 活跃线程数
SHOW STATUS LIKE 'Connections';             -- 总连接次数
SHOW STATUS LIKE 'Aborted_connects';        -- 中断的连接

-- 查询相关
SHOW STATUS LIKE 'Questions';               -- 总查询数
SHOW STATUS LIKE 'Slow_queries';            -- 慢查询数
SHOW STATUS LIKE 'Com_select';              -- SELECT 次数
SHOW STATUS LIKE 'Com_insert';              -- INSERT 次数

-- 复制相关
SHOW SLAVE STATUS\G                         -- 从库状态
SHOW STATUS LIKE 'Slave_open_temp_tables';  -- 从库临时表

-- InnoDB 相关
SHOW STATUS LIKE 'Innodb_buffer_pool%';     -- 缓冲池
SHOW STATUS LIKE 'Innodb_row_lock%';        -- 行锁

def diagnose_connection(host, port):
    """诊断 MySQL 连接"""
    results = {}

    # TCP 连接测试
    try:
        sock = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
        sock.settimeout(5)
        start = time.time()
        sock.connect((host, port))
        tcp_time = time.time() - start
        results['tcp_connect'] = f"✓ {tcp_time*1000:.1f}ms"
        sock.close()
    except Exception as e:
        results['tcp_connect'] = f"✗ {e}"

    # MySQL 握手测试
    try:
        sock = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
        sock.settimeout(5)
        sock.connect((host, port))
        start = time.time()
        handshake = read_packet(sock)
        handshake_time = time.time() - start
        results['handshake'] = f"✓ {handshake_time*1000:.1f}ms"
        results['server_version'] = parse_version(handshake)
        sock.close()
    except Exception as e:
        results['handshake'] = f"✗ {e}"

    return results

def detect_server_version(handshake_data):
    """从 HandshakeV10 检测服务器版本"""
    null_pos = handshake_data.index(b'\x00', 1)
    version = handshake_data[1:null_pos].decode('ascii')

    parts = version.split('.')
    major = int(parts[0])
    minor = int(parts[1])
    patch = int(parts[2].split('-')[0])

    return {
        'version_string': version,
        'major': major,
        'minor': minor,
        'patch': patch,
        'is_mysql8': major >= 8,
        'default_auth': 'caching_sha2_password' if major >= 8 else 'mysql_native_password',
    }

# 使用 tshark 捕获并解码 MySQL 协议
sudo tshark -i lo -f "tcp port 3306" -Y "mysql" \
  -T fields -e mysql.query -e mysql.response.code

# 保存为 pcap 文件
sudo tshark -i lo -f "tcp port 3306" -w mysql_debug.pcap

# 使用 Python 脚本解析 pcap
pip install dpkt

-- 启用通用查询日志（记录所有 SQL）
SET GLOBAL general_log = 'ON';
SET GLOBAL log_output = 'TABLE';  -- 记录到表

-- 查看日志
SELECT * FROM mysql.general_log ORDER BY event_time DESC LIMIT 20;

-- 或记录到文件
SET GLOBAL general_log_file = '/var/log/mysql/general.log';
SET GLOBAL log_output = 'FILE';

# 常见错误码速查表
ERROR_CODES = {
    1045: "Access denied — 认证失败",
    1049: "Unknown database — 数据库不存在",
    1054: "Unknown column — 字段不存在",
    1062: "Duplicate entry — 唯一键冲突",
    1064: "SQL syntax error — SQL 语法错误",
    1146: "Table doesn't exist — 表不存在",
    1205: "Lock wait timeout — 锁等待超时",
    1213: "Deadlock — 死锁",
    1406: "Data too long — 数据过长",
    1452: "Foreign key constraint — 外键约束",
    2006: "Server gone away — 服务器断开",
    2013: "Lost connection — 连接丢失",
    2014: "Commands out of sync — 命令不同步",
}

# 问题: 连接字符集与表字符集不一致导致乱码
# 表: utf8mb4, 连接: latin1

# 解决: 连接时设置字符集
conn = mysql.connect(host='localhost', charset='utf8mb4')
# 或在连接后执行: SET NAMES utf8mb4

# 问题: MySQL BIGINT 无符号最大值 2^64 - 1
# Python int 无限制，但某些驱动可能有 bug

# 解决: 使用正确的类型映射
import struct
value = struct.unpack('<Q', data[offset:offset+8])[0]
# 使用 unsigned 解包

# 问题: 不关闭预处理语句导致服务器资源泄漏
stmt = conn.prepare("SELECT * FROM users WHERE id = %s")
# 忘记关闭!

# 解决: 使用上下文管理器
with conn.cursor() as cursor:
    cursor.execute("SELECT * FROM users WHERE id = %s", (42,))
    # 自动关闭

# 问题: 一次性读取百万行数据
cursor.execute("SELECT * FROM huge_table")
rows = cursor.fetchall()  # 内存爆炸!

# 解决: 使用流式读取或服务器端游标
cursor.execute("SELECT * FROM huge_table")
while True:
    row = cursor.fetchone()
    if not row:
        break
    process(row)

# 问题: 异常导致事务未提交/回滚，连接归还到池中
try:
    conn = pool.get()
    conn.begin()
    conn.execute("UPDATE ...")
    # 发生异常!
    conn.commit()  # 未执行
except:
    pass
pool.put(conn)  # 连接带有未完成的事务!

# 解决: 使用上下文管理器
with pool.get() as conn:
    with conn.transaction():
        conn.execute("UPDATE ...")
        # 异常时自动回滚