docker下的mysql远程连接

docker下的mysql8.0的一个奇怪的地方

• 早上给用docker给装mysql（lastest版本）的时候发现了一个奇怪的现象
• 如果有

还是先说说坑吧

• 刚装完的mysql是不给远程连接的，这个问题好解决网上搜一搜，答案就出来了，然而~
• 关于授权的命令有很多版本，如下…

#授权
GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY "rw";
flush privileges;

1)  grant all privileges on *.*  to 'root'@'%'    #  给用于授予权限
   　　　　 GRANT ALL PRIVILEGES ON *.*  ‘root’@’%’ identified by ‘123123’ WITH GRANT OPTION;  这是网上流传较多的写法。实际上会报错的。
　　2) flush privileges   # 刷新权限

• 等等等等，诸如此类。
• 我全是试过去了，还是不行，连不上去。
• 于是我就想着最粗暴的方法——直接改User 表

mysql> desc user ;
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field                    | Type                              | Null | Key | Default               | Extra |
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host                     | char(255)                         | NO   | PRI |                       |       |
| User                     | char(32)                          | NO   | PRI |                       |       |
| Select_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Insert_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Update_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Delete_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Create_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Drop_priv                | enum('N','Y')                     | NO   |     | N                     |       |
| Reload_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Shutdown_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Process_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| File_priv                | enum('N','Y')                     | NO   |     | N                     |       |
| Grant_priv               | enum('N','Y')                     | NO   |     | N                     |       |
| References_priv          | enum('N','Y')                     | NO   |     | N                     |       |
| Index_priv               | enum('N','Y')                     | NO   |     | N                     |       |
| Alter_priv               | enum('N','Y')                     | NO   |     | N                     |       |
| Show_db_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Super_priv               | enum('N','Y')                     | NO   |     | N                     |       |
| Create_tmp_table_priv    | enum('N','Y')                     | NO   |     | N                     |       |
| Lock_tables_priv         | enum('N','Y')                     | NO   |     | N                     |       |
| Execute_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Repl_slave_priv          | enum('N','Y')                     | NO   |     | N                     |       |
| Repl_client_priv         | enum('N','Y')                     | NO   |     | N                     |       |
| Create_view_priv         | enum('N','Y')                     | NO   |     | N                     |       |
| Show_view_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Create_routine_priv      | enum('N','Y')                     | NO   |     | N                     |       |
| Alter_routine_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Create_user_priv         | enum('N','Y')                     | NO   |     | N                     |       |
| Event_priv               | enum('N','Y')                     | NO   |     | N                     |       |
| Trigger_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Create_tablespace_priv   | enum('N','Y')                     | NO   |     | N                     |       |
| ssl_type                 | enum('','ANY','X509','SPECIFIED') | NO   |     |                       |       |
| ssl_cipher               | blob                              | NO   |     | NULL                  |       |
| x509_issuer              | blob                              | NO   |     | NULL                  |       |
| x509_subject             | blob                              | NO   |     | NULL                  |       |
| max_questions            | int(11) unsigned                  | NO   |     | 0                     |       |
| max_updates              | int(11) unsigned                  | NO   |     | 0                     |       |
| max_connections          | int(11) unsigned                  | NO   |     | 0                     |       |
| max_user_connections     | int(11) unsigned                  | NO   |     | 0                     |       |
| plugin                   | char(64)                          | NO   |     | caching_sha2_password |       |
| authentication_string    | text                              | YES  |     | NULL                  |       |
| password_expired         | enum('N','Y')                     | NO   |     | N                     |       |
| password_last_changed    | timestamp                         | YES  |     | NULL                  |       |
| password_lifetime        | smallint(5) unsigned              | YES  |     | NULL                  |       |
| account_locked           | enum('N','Y')                     | NO   |     | N                     |       |
| Create_role_priv         | enum('N','Y')                     | NO   |     | N                     |       |
| Drop_role_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Password_reuse_history   | smallint(5) unsigned              | YES  |     | NULL                  |       |
| Password_reuse_time      | smallint(5) unsigned              | YES  |     | NULL                  |       |
| Password_require_current | enum('N','Y')                     | YES  |     | NULL                  |       |
| User_attributes          | json                              | YES  |     | NULL                  |       |
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
51 rows in set (0.00 sec)

• 这里是表结构，可以看到主键是host+user
• 查了查表

mysql> select host ,user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | root             |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
5 rows in set (0.00 sec)

• 发现有两个root用户，我当时就纳闷了，既然有两个root用户，那么外网访问该听哪个，况且，按道理%应当包含了localhost这个。
• 由于我的印象里root应该只有一个的（以前用的5.7，这个应该是8+的版本）所以我就吧localhost+root这一行给删了~（反正是个docker容器）
• 结果还是连不上，报的1251错误
  

小插曲

• 据大佬说，从 5.7 升级 8.0 版本的不会改变现有用户的身份验证方法，但新用户会默认使用新的 caching_sha2_password 。客户端不支持新的加密方式。
• 于是得改，抄了下面这句

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '密码';

• 然而报错，我这才想起来我吧’root’@'localhost’删了~

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '1230';
ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'localhost

• 于是最后就这么改了：改成’root’@’%’

mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '1230';
Query OK, 0 rows affected (0.01 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

这次可以连接了：

总结

• 我也不知道我改的对不对，反正我以前没有碰到过两个root的情况，这就是我觉得很奇怪的地方
• 也许本来就是如此，也许是8.0版本的问题，也许是镜像制作者的问题，或者是docker本身启动的时候出了毛病。