Mysql8远程授权问题(ERROR 1064 (42000))

mysql是常用关系型数据库，之前在centos上安装5.6都没有问题，8居然有授权问题。

Centos mysql8安装

移除 mariadb

rpm ‐qa | grep mariadb
yum remove mariadb*

安装 mySql

wget https://repo.mysql.com/mysql80-community-release-el7-3.noarch.rpm
yum localinstall mysql80-community-release-el7-3.noarch.rpm
yum -y install mysql-community-server  #社区版
or
yum -y install mysql-server

启动 mysql 服务

systemctl enable mysqld
systemctl start mysqld

重置密码

# 查找 root 初始密码
cat /var/log/mysqld.log

# 设置新的 root 密码
mysql -u root -p
mysql>  ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'P@ssw0rd';

开启远程访问
常用开启命令如下

grant all privileges on *.* to 'root'@'%' identified by '你的密码' with grant option

这种方法并不适用于Mysql 8.0以后的版本

image.png

需要用如下命令开启远程服务

CREATE USER 'root'@'%' IDENTIFIED BY '你的密码'; 
GRANT ALL ON *.* TO 'root'@'%'; 
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '你的密码';
FLUSH PRIVILEGES;

Centos mysql5.7安装

安装 mysql-server

# 下载并安装 mysql yum 
wget -i -c http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm
yum -y install mysql57-community-release-el7-10.noarch.rpm

# 安装 mysql-server
yum -y install mysql-community-server

mysql 初始化安装的一些配置

# 启动 mysql-serer 
systemctl start mysqld.service
# 查看是否启动成功，即是否存在 3306 端口
netstat -tnlp | grep 3306
# 查询 root 密码，登录到 mysql
grep "password" /var/log/mysqld.log
mysql -uroot -p 

# 首次操作要求重置密码,必须大小写特殊字符组成
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'new password';

# 授权远程访问 % 表示所有主机都可以访问
mysql> grant all privileges on *.* to 'root'@'%' identified by 'password' with grant option;
# 刷新权限信息
mysql> flush privileges; 

修改字符集

vi /etc/my.cnf

[client]
default-character-set=utf8

[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci

# 重启 mysql 
systemctl restart mysqld.service

# 查看是否配置成功
mysql> status; 

Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8