MySQL 远程访问实战：从基础操作到真实踩坑记录-编程阁

MySQL 远程访问实战：从基础操作到真实踩坑记录

本文记录了一次完整的 MySQL 远程连接踩坑过程，涵盖基础命令行操作、认证插件报错、IP 被拉黑等问题及解决方案。

一、MySQL 基础命令行操作

1.1 登录与退出

# 本地登录（默认走 localhost）mysql-uroot-p# 指定 IP 登录（走网络连接）mysql-h192.168.1.100-uroot-p# 指定端口mysql-h192.168.1.100-P3306-uroot-p# 退出exit;-- 或 quit;

1.2 用户管理

-- 查看所有用户SELECTUser,Host,pluginFROMmysql.user;-- 创建本地用户CREATEUSER'alice'@'localhost'IDENTIFIEDBY'密码';-- 创建远程用户（允许任意 IP）CREATEUSER'bob'@'%'IDENTIFIEDBY'密码';-- 创建远程用户（只允许特定 IP）CREATEUSER'charlie'@'192.168.1.50'IDENTIFIEDBY'密码';-- 修改密码ALTERUSER'bob'@'%'IDENTIFIEDBY'新密码';-- 删除用户DROPUSER'bob'@'%';-- 刷新权限（修改后必须执行）FLUSHPRIVILEGES;

1.3 权限管理

-- 授予所有权限GRANTALLPRIVILEGESON*.*TO'bob'@'%';-- 授予特定数据库权限GRANTALLPRIVILEGESONmydb.*TO'bob'@'%';-- 授予只读权限GRANTSELECTONmydb.*TO'readonly'@'%';-- 撤销权限REVOKEALLPRIVILEGESON*.*FROM'bob'@'%';-- 查看用户权限SHOWGRANTSFOR'bob'@'%';

1.4 数据库与表操作

-- 创建数据库CREATEDATABASEmydbCHARACTERSETutf8mb4COLLATEutf8mb4_unicode_ci;-- 查看所有数据库SHOWDATABASES;-- 切换数据库USEmydb;-- 创建表CREATETABLEusers(idINTPRIMARYKEYAUTO_INCREMENT,nameVARCHAR(50)NOTNULL,emailVARCHAR(100)UNIQUE,created_atTIMESTAMPDEFAULTCURRENT_TIMESTAMP);-- 查看表结构DESCusers;-- 或SHOWCREATETABLEusers;-- 插入数据INSERTINTOusers(name,email)VALUES('张三','zhangsan@example.com');-- 查询SELECT*FROMusersWHEREnameLIKE'%张%';-- 更新UPDATEusersSETemail='new@example.com'WHEREid=1;-- 删除DELETEFROMusersWHEREid=1;

1.5 服务管理（Linux）

# 启动sudosystemctl start mysql# 停止sudosystemctl stop mysql# 重启sudosystemctl restart mysql# 查看状态sudosystemctl status mysql

二、远程访问配置

2.1 服务端配置

编辑 MySQL 配置文件：

Linux:/etc/mysql/mysql.conf.d/mysqld.cnf或/etc/my.cnf
Windows:my.ini

# 允许所有网卡监听（默认 127.0.0.1 只监听本地） bind-address = 0.0.0.0 # 或注释掉该行 # bind-address = 127.0.0.1

重启生效：

sudosystemctl restart mysql

2.2 防火墙放行

# UFW (Ubuntu/Debian)sudoufw allow3306/tcp# firewalld (CentOS/RHEL)sudofirewall-cmd--permanent--add-port=3306/tcpsudofirewall-cmd--reload# 云服务器还需在安全组中放行 3306 端口

2.3 用户授权

-- 创建远程专用用户（推荐）CREATEUSER'remote_user'@'%'IDENTIFIEDBY'强密码';GRANTALLPRIVILEGESONmydb.*TO'remote_user'@'%';FLUSHPRIVILEGES;

三、踩坑实录：认证插件与 IP 拉黑

3.1 环境背景

服务端: MySQL 8.0（Linux 服务器）
客户端: Windows C++ 程序，使用较老的 MySQL C API 库
连接方式:mysql_real_connect(conn, "10.136.11.246", "root", ...)

3.2 第一坑：IP 被拉黑

报错信息：

mysql_real_connect failed: Host '10.136.26.183' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'

原因分析：
MySQL 有安全机制，当某个 IP 连续多次连接失败（默认阈值 100 次），会自动将该 IP 加入黑名单，防止暴力破解。

解决方法：

# 服务端执行，清空黑名单mysqladmin flush-hosts

或登录 MySQL 后：

FLUSH HOSTS;

3.3 第二坑：认证插件不兼容

报错信息：

mysql_real_connect failed: Authentication plugin 'caching_sha2_password' cannot be loaded: 找不到指定的模块。

原因分析：
MySQL 8.0 默认使用caching_sha2_password认证插件，但客户端（C++ 的 MySQL 库）版本太老，不支持这个插件。

根本区别：

MySQL 版本	默认认证插件	兼容性
5.7 及以前	`mysql_native_password`	老客户端都支持
8.0+	`caching_sha2_password`	需要新版客户端库

3.4 第三坑：ALTER USER 报错

报错信息：

ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'10.136.11.246'

原因分析：
MySQL 的用户是用户名 + Host的组合。查询发现系统中只有：

SELECTUser,HostFROMmysql.user;

结果：

+------+-----------+ | User | Host | +------+-----------+ | root | % | | root | localhost | +------+-----------+

'root'@'10.136.11.246'这个用户根本不存在！

MySQL 用户匹配规则：

Host 值	含义
`localhost`	只允许本机 socket 连接
`127.0.0.1`	只允许本机 TCP 连接
`192.168.1.%`	允许该网段
`10.136.11.246`	只允许该特定 IP
`%`	允许任意 IP（最宽松）

注意：localhost≠10.136.11.246

localhost走127.0.0.1，不经过网卡
10.136.11.246是服务器的实际网卡 IP，走网络协议

四、完整解决过程

步骤 1：确认现有用户

SELECTUser,Host,pluginFROMmysql.userWHEREUser='root';

确认有'root'@'%'存在。

步骤 2：修改认证插件（服务端执行）

-- 修改已存在的 'root'@'%' 用户ALTERUSER'root'@'%'IDENTIFIEDWITHmysql_native_passwordBY'你的密码';FLUSHPRIVILEGES;

步骤 3：验证修改结果

SELECTUser,Host,pluginFROMmysql.userWHEREUser='root';

确认'root'@'%'的plugin变为mysql_native_password。

步骤 4：清空 IP 黑名单

FLUSH HOSTS;

步骤 5：客户端连接测试

C++ 代码：

#include<mysql.h>MYSQL*conn=mysql_init(nullptr);if(!mysql_real_connect(conn,"10.136.11.246",// 服务器 IP"root",// 用户名"你的密码",// 密码"数据库名",// 数据库3306,// 端口nullptr,0)){printf("连接失败: %s\n",mysql_error(conn));}else{printf("连接成功！\n");}mysql_close(conn);

五、其他解决方案对比

方案 A：改服务端认证方式（本文采用）

优点：客户端不用改，快速解决
缺点：安全性略降，新特性无法使用

-- 改单个用户ALTERUSER'user'@'%'IDENTIFIEDWITHmysql_native_passwordBY'密码';-- 或改全局默认（my.cnf）[mysqld]default_authentication_plugin=mysql_native_password

方案 B：升级客户端库（推荐长期方案）

优点：支持最新特性，更安全
缺点：需要重新编译项目

下载MySQL Connector/C++ 8.0+: https://dev.mysql.com/downloads/connector/cpp/
或更新 C API 的libmysql.dll到 8.0 版本

方案 C：使用 SSH 隧道（最安全）

# 本地建立隧道，把远程 3306 映射到本地 3307ssh-L3307:localhost:3306 user@服务器IP# 然后 C++ 连接本地 3307，实际走的是加密 SSHmysql_real_connect(conn,"127.0.0.1","root",...,3307,...);

优点：不暴露 3306 端口，全程加密
缺点：需要额外配置 SSH

六、安全建议

不要用 root 远程访问：创建专用账号，最小权限原则
限制 Host 范围：能用192.168.1.%就不要用%
强密码 + SSL：生产环境必须配置 SSL 连接
修改默认端口：将 3306 改为其他端口，减少扫描
fail2ban：自动封禁暴力破解 IP

七、常用排查命令速查

-- 查看当前连接SHOWPROCESSLIST;-- 查看连接错误阈值SHOWVARIABLESLIKE'max_connect_errors';-- 修改阈值（临时）SETGLOBALmax_connect_errors=1000;-- 查看用户认证方式SELECTUser,Host,plugin,authentication_stringFROMmysql.user;-- 查看被拉黑的 IP（performance_schema 需开启）SELECT*FROMperformance_schema.host_cacheWHERESUM_CONNECT_ERRORS>0;

总结

问题	现象	解决
IP 被拉黑	`Host is blocked`	`FLUSH HOSTS;`
认证插件不支持	`caching_sha2_password cannot be loaded`	改`mysql_native_password`或升级客户端
用户不存在	`Operation ALTER USER failed`	先`SELECT`查用户，确认 Host 正确
localhost vs IP	连接方式不同，匹配的用户不同	明确用`%`还是具体 IP

核心教训：MySQL 的用户是用户名@Host的组合，修改前务必先查清楚！

本文基于 MySQL 8.0 + Windows C++ 客户端的真实踩坑经历整理。

MySQL 远程访问实战：从基础操作到真实踩坑记录