关于Mysql8的远程连接

发表于   |   分类于

在某人的怂恿下，准备升级体验下最新版的Mysql，然后就在Docker里做了个试验 直接 pull 了最新版 8.0.13 然后连接的时候一直报错 净整幺蛾子

运行 Mysql 容器

直接下载官方镜像 然后执行

docker run --name mysql8 -d -p 3306:3306 -v /data/mysql:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=root123456 mysql

简单讲下这个命令

--name mysql8 给容器指定了一个名称

-d 作为后台运行

-p 3306:3306 指定了本机的端口号3306 映射到容器的端口3306 当然你也可以指定本机的别的端口 比如你想指定本机的 43306端口到容器里3306 就可以写 -p 43306:3306 冒号前面是本机端口号 后面是容器端口号

-v /data/mysql:/var/lib/mysql 将主机目录/data/mysql 挂载到容器的 /var/lib/mysql 用来保存数据文件

-e MYSQL_ROOT_PASSWORD=root123456 是执行 初始化了root用户密码 我这里密码比较简单 设置了 root123456

最后就是镜像的名字了 没有带 tag

Tip: 运行容器的时候一定要指定端口号，我第一次创建的时候忘记指定端口号了，所以默认生成的容器是开放了mysql的3306端口但是那是容器啊 没有本机的端口去映射怎么访问了 所以我测试连接的时候就看到了熟悉的界面

can`t connect mysql server

1
2
3
4
5
[root@localhost ~]# docker run --name mysql8 -d -p 3306:3306 -v /data/mysql:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=root123456 mysql
27272feb56cd9f8b95b85e53f6bde525f2f5f324c329c3ec8a8450563c2d635d
[root@localhost ~]# docker ps
CONTAINER ID  IMAGE  COMMAND     CREATED   STATUS         PORTS                              NAMES
27272feb56cd  mysql  "docker.."  2s ago    Up 2 seconds   0.0.0.0:3306->3306/tcp, 33060/tcp  mysql8

这样就创建好了Mysql的容器

设置远程连接

下面要设置远程连接访问权限 需要进入容器设置 docker exec -it mysql8 bash

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
root@491fc8c3bb16:/# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
...
Server version: 8.0.13 ...
...
mysql> use mysql;
...
mysql> select host, user, authentication_string, plugin from user;
|-----------------------------------------------------------------------------------|
| host      | user             | authentication_string      | plugin                |
|-----------------------------------------------------------------------------------|
| %         | root             | $A$005$Zvl9/;vkZ)u...      | caching_sha2_password |
| localhost | mysql.infoschema | $A$005$THISISACOMB...      | caching_sha2_password |
| localhost | mysql.session    | $A$005$THISISACOMB...      | caching_sha2_password |
| localhost | mysql.sys        | $A$005$THISISACOMB...      | caching_sha2_password |
| localhost | root             | $A$005$W")OV8C]-@...       | caching_sha2_password |
|-----------------------------------------------------------------------------------|
5 rows in set (0.00 sec)

其实主要是设置那个 host=% && user=root 的列 如果有这个就是开放了 root用户 的所有 host 访问权限 如果没有的话就不能使用root远程登录了 也可以干掉新建用户权限

创建用户

在 Mysql8 下创建用户和之前 Mysql5.6（ps:没有试别的版本…） 有些不一样

在 Mysql8 下 这样直接创建用户并指定密码会报错

1
2
mysql> grant all privileges on *.* to 'reggie'@'%' identified by 'reggie123456';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'identified by 'reggie123456'' at line 1

我在另外一个 Mysql5.6 下就可以

1
2
mysql> grant all privileges on *.* to 'reggie'@'%' identified by 'reggie123456';
Query OK, 0 rows affected (0.04 sec)

找了一下说 需要先创建用户才能指定权限， 在 Mysql8 下可以这样操作 先创建用户 然后指定授权

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> create user 'reggie'@'%' identified by 'reggie123456';
Query OK, 0 rows affected (0.04 sec)
mysql> grant all privileges on *.* to 'reggie'@'%' with grant option;
Query OK, 0 rows affected (0.02 sec)
mysql> select host, user, plugin from user;
+-----------+------------------+-----------------------+
| host      | user             | plugin                |
+-----------+------------------+-----------------------+
| %         | reggie           | caching_sha2_password |
| %         | root             | caching_sha2_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session    | caching_sha2_password |
| localhost | mysql.sys        | caching_sha2_password |
| localhost | root             | caching_sha2_password |
+-----------+------------------+-----------------------+
6 rows in set (0.00 sec)

最后记得强制刷新下 flush privileges;

Grant 命令的用法

grant 是授权命令 允许你把你自己拥有的那些权限授给其他的用户

all privileges 参数表示授权所有的操作 这个操作就是 CURD alter 等 如果你想授权部分权限也可以直接写 可以使用逗号隔开 比如 grant select,insert,update,delete 这样

on *.* 参数表示 所有数据库 所有表 比如你想指定某个数据库 可以写 on test.* 表示 test数据库下的所有表 还可以 test.user 表示 test数据库的user表

to 'reggie'@'%' 表示指定用户 reggie 艾特符号后面的 % 表示统配所有的host地址，这些地址可以远程访问。你也可以指定具体IP地址 限制只有它才能访问 比如 to 'reggie'@'192.168.*.*'

远程连接Mysql

做好了准备工作后具备了远程连接的授权 就测试一下吧 果不其然又挂了

使用 Mysql Workbench 连接直接崩溃 没见过的骚操作！

workbench_bug

然后换了个客户端连接工具 SQLyog 提示如下

mysql_password_error

看到这个大概原因清楚了 一通搜索发现 报 2059 错误，这是因为 MySql8 和 5.* 的加密规则不一样，而现在的可视化工具只支持旧的加密方式

我的 Workbench 是 6.3 版本 至于他为什么会报上图的错不得而知

出现这个原因是 Mysql8 之前的版本中加密规则是 mysql_native_password, 而在 Mysql8 之后,加密规则是 caching_sha2_password.

下面是我在Mysql5.6中查询的结果

1
2
3
4
5
6
7
8
mysql> select host, user, authentication_string, plugin from user;
+------+------+-----------------------+-----------------------+
| host | user | authentication_string | plugin                |
+------+------+-----------------------+-----------------------+
| %    | root |                       | mysql_native_password |
| %    | mila |                       | mysql_native_password |
+------+------+-----------------------+-----------------------+
2 rows in set (0.00 sec)

解决问题方法有两种, 一种是升级客户端工具版本, 一种是把Mysql用户登录密码加密规则还原成 mysql_native_password.

第一种很简单啦 直接下载喜欢用的客户端工具最新版就好了 比如升级 MysqlWorkbench 最新版在来连接

workbench_success

第二种方法修改加密规则

执行命令 alter user 'reggie'@'%' identified with mysql_native_password by 'reggie123456'; 最后的 by 后面是你要设置的密码,然后执行刷新 flush privileges; 再次打开客户端连接即可

sqlyog_success

-------------The End-------------