平时做技术实践时，很多问题不是概念不会，而是细节没串起来。拿“MySQL远程访问配置与常见问题解决指南”来说，它看着像小点，放到项目里常会牵出环境、配置、兼容性和维护成本。下面按实际采用顺序，把思路、关键写法和容易踩坑的地方讲清楚，便于大家直接对照操作。

目录

• 一、MySQL 基础命令行操作
• 1.1 登录与退出
• 1.2 用户管理
• 1.3 权限管理
• 1.4 数据库与表操作
• 1.5 服务管理（Linux）
• 二、远程访问设置
• 2.1 服务端设置
• 2.2 防火墙放行
• 2.3 用户授权
• 三、踩坑实录：认证插件与 IP 拉黑
• 3.1 环境背景
• 3.2 第一坑：IP 被拉黑
• 3.3 第二坑：认证插件不兼容
• 3.4 第三坑：ALTER USER 报错
• 四、完整解决过程
• 步骤 1：确认现有用户
• 步骤 2：修改认证插件（服务端执行）
• 步骤 3：验证修改结果
• 步骤 4：清空 IP 黑名单
• 步骤 5：客户端连接测试
• 五、其他解决方案对比
• 方案 A：改服务端认证方式（本文采用）
• 方案 B：升级客户端库（建议长期方案）
• 方案 C：采用 SSH 隧道（最安全）
• 六、安全建议
• 七、常用排查命令速查
• 总结

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

一、MySQL 基础命令行操作

1.1 登录与退出

# 本地登录（默认走 localhost）
mysql -u root -p
# 指定 IP 登录（走网络连接）
mysql -h 192.168.1.100 -u root -p
# 指定端口
mysql -h 192.168.1.100 -P 3306 -u root -p
# 退出
exit;
-- 或
quit;

1.2 用户管理

-- 查看所有用户
SELECT User, Host, plugin FROM mysql.user;
-- 创建本地用户
CREATE USER 'alice'@'localhost' IDENTIFIED BY '密码';
-- 创建远程用户（允许任意 IP）
CREATE USER 'bob'@'%' IDENTIFIED BY '密码';
-- 创建远程用户（只允许特定 IP）
CREATE USER 'charlie'@'192.168.1.50' IDENTIFIED BY '密码';
-- 修改密码
ALTER USER 'bob'@'%' IDENTIFIED BY '新密码';
-- 删除用户
DROP USER 'bob'@'%';
-- 刷新权限（修改后必须执行）
FLUSH PRIVILEGES;

1.3 权限管理

-- 授予所有权限
GRANT ALL PRIVILEGES ON *.* TO 'bob'@'%';

-- 授予特定数据库权限
GRANT ALL PRIVILEGES ON mydb.* TO 'bob'@'%';

-- 授予只读权限
GRANT SELECT ON mydb.* TO 'readonly'@'%';

-- 撤销权限
REVOKE ALL PRIVILEGES ON *.* FROM 'bob'@'%';

-- 查看用户权限
SHOW GRANTS FOR 'bob'@'%';

1.4 数据库与表操作

-- 创建数据库
CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 查看所有数据库
SHOW DATABASES;

-- 切换数据库
USE mydb;

-- 创建表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 查看表结构
DESC users;
-- 或
SHOW CREATE TABLE users;

-- 插入数据
INSERT INTO users (name, email) VALUES ('张三', '[email protected]');

-- 查询
SELECT * FROM users WHERE name LIKE '%张%';

-- 更新
UPDATE users SET email = '[email protected]' WHERE id = 1;

-- 删除
DELETE FROM users WHERE id = 1;

1.5 服务管理（Linux）

# 启动
sudo systemctl start mysql
# 停止
sudo systemctl stop mysql
# 重启
sudo systemctl restart mysql
# 查看状态
sudo systemctl 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

重启生效：

sudo systemctl restart mysql

2.2 防火墙放行

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

2.3 用户授权

-- 创建远程专用用户（推荐）
CREATE USER 'remote_user'@'%' IDENTIFIED BY '强密码';
GRANT ALL PRIVILEGES ON mydb.* TO 'remote_user'@'%';
FLUSH PRIVILEGES;

三、踩坑实录：认证插件与 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 库）版本太老，不兼容这个插件。

根本区别：

3.4 第三坑：ALTER USER 报错

报错信息：

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

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

SELECT User, Host FROM mysql.user;

结果：

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

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

MySQL 用户匹配规则：

注意：localhost ≠ 10.136.11.246

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

四、完整解决过程

步骤 1：确认现有用户

SELECT User, Host, plugin FROM mysql.user WHERE User = 'root';

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

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

-- 修改已存在的 'root'@'%' 用户
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '你的密码';
FLUSH PRIVILEGES;

步骤 3：验证修改结果

SELECT User, Host, plugin FROM mysql.user WHERE User = '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("连接失败: %sn", mysql_error(conn));
} else {
    printf("连接成功！n");
}
mysql_close(conn);

五、其他解决方案对比

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

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

-- 改单个用户
ALTER USER 'user'@'%' IDENTIFIED WITH mysql_native_password BY '密码';

-- 或改全局默认（my.cnf）
[mysqld]
default_authentication_plugin = mysql_native_password

方案 B：升级客户端库（建议长期方案）

优点：兼容最新特性，更安全
缺点：需重新编译项目

• 下载 MySQL Connector/C++ 8.0+: （链接已移除）
• 或更新 C API 的 libmysql.dll 到 8.0 版本

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

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

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

六、安全建议

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

七、常用排查命令速查

-- 查看当前连接
SHOW PROCESSLIST;

-- 查看连接错误阈值
SHOW VARIABLES LIKE 'max_connect_errors';

-- 修改阈值（临时）
SET GLOBAL max_connect_errors = 1000;

-- 查看用户认证方式
SELECT User, Host, plugin, authentication_string FROM mysql.user;

-- 查看被拉黑的 IP（performance_schema 需开启）
SELECT * FROM performance_schema.host_cache WHERE SUM_CONNECT_ERRORS > 0;

总结

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

落到代码里，本文基于 MySQL 8.0 + Windows C++ 客户端的真实踩坑经历整理。

到此这篇关于MySQL远程访问设置与常用问题解决指南的文章就介绍到这了,更多相关MySQL远程访问设置与常用问题内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多兼容脚本之家！

您可能感兴趣的文章:

• MySQL8 Connection refused远程访问失败的解决
• 5分钟带你搞定MySQL数据库远程访问
• MySQL开启远程访问权限的完整步骤记录
• Docker Desktop中安装MySQL同时开启远程访问的详细教程
• 开启MySQL远程访问权限、允许远程连接方式