MySQL 连接问题及解决方案

Mysql 连接的一些报错及解决方案

MySql 连接其他远程服务器

mysql -u root -p
mysql -h 127.0.0.1 -u root -p -P 3306

Host ‘xxx.xx.xxx.xxx’ is not allowed to connect to this MySQL server

username 是用户名 password 是你的密码 localhost 是服务器

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' WITH GRANT OPTION;

CREATE USER 'username'@'%' IDENTIFIED BY 'password';

GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' WITH GRANT OPTION;

FLUSH PRIVILEGES;

MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
flush privileges;

Mysql 开启远程连接权限

# 登录 mysql
mysql -u root -p
# 创建用户
CREATE USER 'username'@'%' IDENTIFIED BY 'password';
# 授权
GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' WITH GRANT OPTION;
# 授权给 root 用户
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
# 刷新权限
FLUSH PRIVILEGES;

另外，Ubuntu 系统可能要防火墙放行一下

# 查看防火墙状态
sudo ufw status
# 开启防火墙
sudo ufw enable
# 开启端口
sudo ufw allow 3306

参考文章

• https://stackoverflow.com/questions/1559955/host-xxx-xx-xxx-xxx-is-not-allowed-to-connect-to-this-mysql-server
• https://stackoverflow.com/questions/50093144/mysql-8-0-client-does-not-support-authentication-protocol-requested-by-server