MySQL远程访问配置与常见问题解决指南

# 本地登录（默认走 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;

-- 查看所有用户
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;

-- 授予所有权限
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'@'%';

-- 创建数据库
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 ('张三', 'zhangsan@example.com');

-- 查询
select * from users where name like '%张%';

-- 更新
update users set email = 'new@example.com' where id = 1;

-- 删除
delete from users where id = 1;

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

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

sudo systemctl restart mysql

# ufw (ubuntu/debian)
sudo ufw allow 3306/tcp
# firewalld (centos/rhel)
sudo firewall-cmd --permanent --add-port=3306/tcp
sudo firewall-cmd --reload
# 云服务器还需在安全组中放行 3306 端口

-- 创建远程专用用户（推荐）
create user 'remote_user'@'%' identified by '强密码';
grant all privileges on mydb.* to 'remote_user'@'%';
flush privileges;

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

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

flush hosts;

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

error 1396 (hy000): operation alter user failed for 'root'@'10.136.11.246'

select user, host from mysql.user;

+------+-----------+
| user | host      |
+------+-----------+
| root | %         |
| root | localhost |
+------+-----------+

select user, host, plugin from mysql.user where user = 'root';

-- 修改已存在的 'root'@'%' 用户
alter user 'root'@'%' identified with mysql_native_password by '你的密码';
flush privileges;

select user, host, plugin from mysql.user where user = 'root';

flush hosts;

#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);

-- 改单个用户
alter user 'user'@'%' identified with mysql_native_password by '密码';

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

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

-- 查看当前连接
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;