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

MySQL 远程访问实战从基础操作到真实踩坑记录本文记录了一次完整的 MySQL 远程连接踩坑过程涵盖基础命令行操作、认证插件报错、IP 被拉黑等问题及解决方案。一、MySQL 基础命令行操作1.1 登录与退出# 本地登录默认走 localhostmysql-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;-- 创建本地用户CREATEUSERalicelocalhostIDENTIFIEDBY密码;-- 创建远程用户允许任意 IPCREATEUSERbob%IDENTIFIEDBY密码;-- 创建远程用户只允许特定 IPCREATEUSERcharlie192.168.1.50IDENTIFIEDBY密码;-- 修改密码ALTERUSERbob%IDENTIFIEDBY新密码;-- 删除用户DROPUSERbob%;-- 刷新权限修改后必须执行FLUSHPRIVILEGES;1.3 权限管理-- 授予所有权限GRANTALLPRIVILEGESON*.*TObob%;-- 授予特定数据库权限GRANTALLPRIVILEGESONmydb.*TObob%;-- 授予只读权限GRANTSELECTONmydb.*TOreadonly%;-- 撤销权限REVOKEALLPRIVILEGESON*.*FROMbob%;-- 查看用户权限SHOWGRANTSFORbob%;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(张三,zhangsanexample.com);-- 查询SELECT*FROMusersWHEREnameLIKE%张%;-- 更新UPDATEusersSETemailnewexample.comWHEREid1;-- 删除DELETEFROMusersWHEREid1;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.cnfWindows:my.ini# 允许所有网卡监听默认 127.0.0.1 只监听本地 bind-address 0.0.0.0 # 或注释掉该行 # bind-address 127.0.0.1重启生效sudosystemctl restart mysql2.2 防火墙放行# UFW (Ubuntu/Debian)sudoufw allow3306/tcp# firewalld (CentOS/RHEL)sudofirewall-cmd--permanent--add-port3306/tcpsudofirewall-cmd--reload# 云服务器还需在安全组中放行 3306 端口2.3 用户授权-- 创建远程专用用户推荐CREATEUSERremote_user%IDENTIFIEDBY强密码;GRANTALLPRIVILEGESONmydb.*TOremote_user%;FLUSHPRIVILEGES;三、踩坑实录认证插件与 IP 拉黑3.1 环境背景服务端: MySQL 8.0Linux 服务器客户端: 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.0caching_sha2_password需要新版客户端库3.4 第三坑ALTER USER 报错报错信息ERROR 1396 (HY000): Operation ALTER USER failed for root10.136.11.246原因分析MySQL 的用户是用户名 Host的组合。查询发现系统中只有SELECTUser,HostFROMmysql.user;结果----------------- | User | Host | ----------------- | root | % | | root | localhost | -----------------root10.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.246localhost走127.0.0.1不经过网卡10.136.11.246是服务器的实际网卡 IP走网络协议四、完整解决过程步骤 1确认现有用户SELECTUser,Host,pluginFROMmysql.userWHEREUserroot;确认有root%存在。步骤 2修改认证插件服务端执行-- 修改已存在的 root% 用户ALTERUSERroot%IDENTIFIEDWITHmysql_native_passwordBY你的密码;FLUSHPRIVILEGES;步骤 3验证修改结果SELECTUser,Host,pluginFROMmysql.userWHEREUserroot;确认root%的plugin变为mysql_native_password。步骤 4清空 IP 黑名单FLUSH HOSTS;步骤 5客户端连接测试C 代码#includemysql.hMYSQL*connmysql_init(nullptr);if(!mysql_real_connect(conn,10.136.11.246,// 服务器 IProot,// 用户名你的密码,// 密码数据库名,// 数据库3306,// 端口nullptr,0)){printf(连接失败: %s\n,mysql_error(conn));}else{printf(连接成功\n);}mysql_close(conn);五、其他解决方案对比方案 A改服务端认证方式本文采用优点客户端不用改快速解决缺点安全性略降新特性无法使用-- 改单个用户ALTERUSERuser%IDENTIFIEDWITHmysql_native_passwordBY密码;-- 或改全局默认my.cnf[mysqld]default_authentication_pluginmysql_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;-- 查看连接错误阈值SHOWVARIABLESLIKEmax_connect_errors;-- 修改阈值临时SETGLOBALmax_connect_errors1000;-- 查看用户认证方式SELECTUser,Host,plugin,authentication_stringFROMmysql.user;-- 查看被拉黑的 IPperformance_schema 需开启SELECT*FROMperformance_schema.host_cacheWHERESUM_CONNECT_ERRORS0;总结问题现象解决IP 被拉黑Host is blockedFLUSH 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 客户端的真实踩坑经历整理。