MySQL远程访问故障排查与解决方法

用户报告远程无法连接
       │
       ▼
┌─────────────────────────────────────┐
│  step 1: 容器状态与端口映射检查     │
│  - docker ps → 容器运行正常         │
│  - docker port → 3306→3308 映射正确 │
│  - ss -tlnp → docker-proxy 监听正常 │
└─────────────────────────────────────┘
       │
       ▼
┌─────────────────────────────────────┐
│  step 2: mysql 用户权限检查         │
│  - docker exec 进入容器             │
│  - 查询 mysql.user 表               │
│  → root@% 存在，允许远程连接        │
│  → admin@% 存在，允许远程连接       │
└─────────────────────────────────────┘
       │
       ▼
┌─────────────────────────────────────┐
│  step 3: mysql 网络配置检查         │
│  - bind_address = * ✅              │
│  - skip_networking = off ✅         │
│  - require_secure_transport = off   │
└─────────────────────────────────────┘
       │
       ▼
┌─────────────────────────────────────┐
│  step 4: 宿主机防火墙检查           │
│  - iptables input 策略 accept      │
│  - 无 drop 3308 规则               │
│  - 未启用 ufw/firewalld            │
└─────────────────────────────────────┘
       │
       ▼
┌─────────────────────────────────────┐
│  step 5: 认证插件兼容性排查         │
│  - mysql 8.4 默认 caching_sha2     │
│  - 改为 mysql_native_password      │
│  → 本地连接成功，远程仍失败         │
└─────────────────────────────────────┘
       │
       ▼
┌─────────────────────────────────────┐
│  step 6: 多维度连接测试             │
│  - 127.0.0.1:3306 → ✅ 成功        │
│  - 172.16.1.234:3308 → ✅ 成功     │
│  - 容器内 → 公网ip:3308 → ❌ 失败  │
│  - 远程服务器 → 公网ip:3308 → ❌   │
│  → 所有用户均失败（root,admin等）  │
└─────────────────────────────────────┘
       │
       ▼
┌─────────────────────────────────────┐
│  step 7: 网络层深入排查 ★关键发现★ │
│  - 抓取 mysql 握手包对比           │
│  → 公网ip:3308 → mysql 8.4.10     │
│  → 内网ip:3308 → mysql 8.4.11     │
│  → 这是两个不同的服务器！          │
└─────────────────────────────────────┘
       │
       ▼
┌─────────────────────────────────────┐
│  step 8: 端口变更与最终验证         │
│  - 申请新端口 3309 转发            │
│  - 创建新容器，映射 3309:3306      │
│  - 配置 mysql_native_password      │
│  - 放行云防火墙                    │
│  → 远程连接成功 ✅                 │
└─────────────────────────────────────┘

docker ps
docker port daily_test_mysql
ss -tlnp | grep 3308

docker exec daily_test_mysql mysql -uroot -p123456 \
  -e "select user, host, plugin from mysql.user;"

user            host            plugin
root            %               caching_sha2_password
root            localhost       caching_sha2_password
admin           %               caching_sha2_password

show variables like 'bind_address';       -- → * (监听所有接口)
show variables like 'skip_networking';    -- → off (允许tcp/ip)
show variables like 'require_secure_transport'; -- → off (不强制ssl)

/etc/my.cnf                  # 全局配置（mysql 服务级别）
/etc/mysql/my.cnf            # 全局配置（mysql 发行版级别）
/etc/mysql/conf.d/*.cnf      # 附加配置目录（推荐放置自定义配置）
~/.my.cnf                    # 用户级别配置（仅影响该用户客户端）

[client]           # 客户端工具（mysql, mysqldump 等）的配置
port=3306
[mysql]            # mysql 命令行客户端的配置
prompt="\\u@\\h [\\d]> "
[mysqld]           # mysqld 服务器进程的配置 ← 这是最关键的节
bind-address=*
port=3306
max_connections=200

[mysqld]
bind-address = 127.0.0.1    # 只监听本机，拒绝远程连接！
skip-networking = on         # 完全禁用 tcp/ip！

[mysqld]
bind-address = *             # 监听所有网络接口
skip-networking = off        # 允许 tcp/ip 连接（默认值，可不写）
port = 3306                  # mysql 监听端口
mysql_native_password = on   # 启用传统认证插件（兼容旧客户端）
skip_name_resolve = on       # 跳过 dns 反向解析，避免连接卡顿

# 1. 在宿主机创建配置文件
mkdir -p /root/mysql-conf
cat > /root/mysql-conf/my-custom.cnf << 'eof'
[mysqld]
mysql_native_password = on
skip_name_resolve = on
max_connect_errors = 1000
eof

# 2. 启动容器时挂载配置目录
docker run -d --name daily_test_mysql \
  -p 3309:3306 \
  -v /root/mysql-conf:/etc/mysql/conf.d \
  -e mysql_root_password=123456 \
  mysql:8.4

# 1. 创建配置文件
cat > /tmp/mysql_custom.cnf << 'eof'
[mysqld]
mysql_native_password = on
skip_name_resolve = on
eof

# 2. 复制到容器内
docker cp /tmp/mysql_custom.cnf daily_test_mysql:/etc/mysql/conf.d/

# 3. 重启容器
docker restart daily_test_mysql

version: '3.8'
services:
  mysql:
    image: mysql:8.4
    container_name: daily_test_mysql
    ports:
      - "3309:3306"
    environment:
      mysql_root_password: 123456
    volumes:
      - mysql_data:/var/lib/mysql
      - ./mysql-conf:/etc/mysql/conf.d
    restart: unless-stopped
volumes:
  mysql_data:

# 查看配置文件是否被正确加载
docker exec daily_test_mysql mysql -uroot -p123456 \
  -e "show variables like 'mysql_native_password';"

# 输出应为 active
# variable_name          value
# mysql_native_password  on

authentication_policy = *,   ← 表示仅允许 caching_sha2_password

# 在容器内通过 127.0.0.1 连接 mysql（不经过 docker 端口映射）
docker exec daily_test_mysql mysql -u root -p123456 \
  -h 127.0.0.1 -p 3306 \
  -e "select current_user(), '连接成功' as status;"

current_user()  status
root@%          连接成功

# 在容器内通过宿主机内网 ip 连接（经过 docker 端口映射 3308→3306）
docker exec daily_test_mysql mysql -u root -p123456 \
  -h 172.16.1.234 -p 3308 \
  -e "select '内网ip连接测试' as status;"

status
内网ip连接测试

# 在容器内通过公网 ip 连接（经过云 nat → docker 端口映射）
docker exec daily_test_mysql mysql -u root -p123456 \
  -h 103.236.97.252 -p 3308 \
  -e "select '公网ip连接测试' as status;"

error 1045 (28000): access denied for user 'root'@'103.236.97.252' (using password: yes)

# 使用 admin 用户测试
docker exec daily_test_mysql mysql -u admin -p123456 \
  -h 103.236.97.252 -p 3308 \
  -e "select 'admin公网测试' as status;"

# 使用 testuser 用户测试
docker exec daily_test_mysql mysql -u testuser -ptest123 \
  -h 103.236.97.252 -p 3308 \
  -e "select 'testuser公网测试' as status;"

error 1045 (28000): access denied for user 'admin'@'103.236.97.252' (using password: yes)
error 1045 (28000): access denied for user 'testuser'@'103.236.97.252' (using password: yes)

# 查看容器内网 ip
docker inspect daily_test_mysql | grep "ipaddress"
# 输出: "ipaddress": "192.168.0.2"

# 从宿主机直接连接容器内部 ip（不经过 docker-proxy）
docker run --rm --network host mysql:8.4 mysql -u root -p123456 \
  -h 192.168.0.2 -p 3306 \
  -e "select '直连容器' as status;"

status
直连容器

┌─────────────────────────────────────────────────┐
│ 包长度 (3 bytes)  |  序列号 (1 byte)              │
│ protocol version (1 byte)                        │
│ server version (null-terminated string)          │
│ connection id (4 bytes)                          │
│ auth plugin data part 1 (8 bytes)                │
│ filler (1 byte)                                  │
│ capability flags (2 bytes)                       │
│ character set (1 byte)                           │
│ status flags (2 bytes)                            │
│ capability flags (upper 2 bytes)                 │
│ ...                                              │
│ auth plugin name (null-terminated string)        │
└─────────────────────────────────────────────────┘

# 测试 1：从公网 ip 103.236.97.252:3308 抓取握手包
python3 -c "
import socket
s = socket.socket()
s.settimeout(5)
s.connect(('103.236.97.252', 3308))
data = s.recv(1024)
s.close()
version = data[1:data.index(0, 1)].decode()
print(f'公网ip连接的mysql版本: {version}')
print(f'握手包前50字节(hex): {data[:50].hex()}')
"
# 测试 2：从内网 ip 172.16.1.234:3308 抓取握手包
python3 -c "
import socket
s = socket.socket()
s.settimeout(5)
s.connect(('172.16.1.234', 3308))
data = s.recv(1024)
s.close()
version = data[1:data.index(0, 1)].decode()
print(f'内网ip连接的mysql版本: {version}')
print(f'握手包前50字节(hex): {data[:50].hex()}')
"

公网ip连接的mysql版本: 8.4.10
握手包前50字节(hex): 4a0000000a382e342e31300069da00001c7f7c05524d5a7500ffffe00200ffdf15

内网ip连接的mysql版本: 8.4.11
握手包前50字节(hex): 4a0000000a382e342e313100280000000c52665a6d550c3000ffffff0200ffdf15

远程服务器                    云平台 nat 网关               内部网络
117.72.76.108                     │                      172.16.0.0/12
       │                          │                           │
       │  ┌──────────────────────────────────────────┐        │
       ├──→ 103.236.97.252:3308                     │        │
       │  │  dnat 规则匹配                          │        │
       │  │  3308/tcp → 172.16.x.x:3308 (其他机器)  ├──→ ❌  │
       │  │  3309/tcp → 172.16.1.234:3309 (本机)    ├──→ ✅  │
       │  └──────────────────────────────────────────┘        │
       │                                                       │
       └──→ 103.236.97.252:3309 → 本机 → mysql 8.4.11 ✅

dnat tcp -- !docker0 * 0.0.0.0/0 0.0.0.0/0 tcp dpt:3309 to:192.168.0.2:3306

问题类别：配置错误（configuration error）
具体类型：云网络端口转发配置错误（cloud nat port forwarding misconfiguration）
严重程度：中（medium）
影响范围：仅影响公网远程访问，本地和内网访问不受影响

docker run -d --name daily_test_mysql \
  -p 3309:3306 \
  -e mysql_root_password=123456 \
  mysql:8.4

□ 1. docker ps / docker port → 确认容器和端口映射
□ 2. docker exec → 查询 mysql.user 表
□ 3. show variables → 检查 bind_address, skip_networking
□ 4. ss -tlnp / iptables → 检查防火墙
□ 5. 对比测试：内网 ip vs 公网 ip
□ 6. 抓取握手包：对比 mysql 版本号
□ 7. 检查云平台 nat 端口转发规则
□ 8. 检查云平台安全组/防火墙规则

# 查看容器状态和端口映射
docker ps
docker port daily_test_mysql

# 查看 mysql 用户权限
docker exec daily_test_mysql mysql -uroot -p123456 \
  -e "select user, host, plugin from mysql.user;"

# 查看 mysql 配置
docker exec daily_test_mysql mysql -uroot -p123456 \
  -e "show variables like 'bind_address';"

# 查看端口监听状态
ss -tlnp | grep 3309

# 抓取 mysql 握手包
python3 -c "
import socket
s = socket.socket()
s.settimeout(5)
s.connect(('目标ip', 端口))
data = s.recv(1024)
s.close()
version = data[1:data.index(0, 1)].decode()
print(f'mysql版本: {version}')
"

# 测试端口连通性
curl -v telnet://103.236.97.252:3309 --connect-timeout 5