deepin 15.11 远程登录docker mysql-server 8

在使用deepin 15.11 的mysql 8 docker 容器中，遇到无法远程登录的问题，查看了很多资料，尝试了多次才最终得以解

1. 创建mysql server 8 容器

 docker run --name mysql -d -p 3306:3306 mysql/mysql-server:latest

2.检查容器状态

 ~$ docker ps
CONTAINER ID        IMAGE                       COMMAND                  CREATED             STATUS                    PORTS                               NAMES
ccfe216d6179        mysql/mysql-server:latest   "/entrypoint.sh mysq…"   14 minutes ago      Up 14 minutes (healthy)   0.0.0.0:3306->3306/tcp, 33060/tcp   mysql

3.查看并复制容器创建的root用户的一次性密码

 ~$ docker logs mysql |grep -i generated
[Entrypoint] No password option specified for new database.
[Entrypoint]   A random onetime password will be generated.
2020-02-29T05:12:51.981602Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.19) initializing of server in progress as process 21
2020-02-29T05:12:56.881082Z 5 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
2020-02-29T05:13:02.304538Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.19) starting as process 69
2020-02-29T05:13:03.138287Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-02-29T05:13:03.179590Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.19'  socket: '/var/lib/mysql/mysql.sock'  port: 0  MySQL Community Server - GPL.
2020-02-29T05:13:03.388766Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/var/run/mysqld/mysqlx.sock'
Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/leapseconds' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/tzdata.zi' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/zone1970.tab' as time zone. Skipping it.
[Entrypoint] GENERATED ROOT PASSWORD: eG@P^@c-4sSIkEGaKOnozQodUdOm
2020-02-29T05:13:07.610265Z 10 [System] [MY-013172] [Server] Received SHUTDOWN from user root. Shutting down mysqld (Version: 8.0.19).
2020-02-29T05:13:09.607684Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.19)  MySQL Community Server - GPL.
2020-02-29T05:13:09.924956Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.19) starting as process 1
2020-02-29T05:13:10.616704Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-02-29T05:13:10.673320Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.19'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server - GPL.
2020-02-29T05:13:10.766730Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/var/run/mysqld/mysqlx.sock' bind-address: '::' port: 33060

复制密码，用于第一次登录

4.登录mysql 容器

登录时的密码，直接粘贴上面复制的密码

~$ docker exec -it mysql mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 65
Server version: 8.0.19

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

第一次root用户登录后，必须重置密码，否则无法使用数据库

mysql> use mysql
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql>

5.修改root用户密码（将’your-passwd'替换为你想要设置的密码）

mysql> alter user 'root'@'localhost' identified by 'your-passwd';
Query OK, 0 rows affected (0.03 sec)

mysql>

6.使用mysql数据库

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>

7.目前root用户只能从localhost登录，需要修改为任意主机

mysql> select host,user,plugin,authentication_string from user;
+-----------+------------------+-----------------------+------------------------------------------------------------------------+
| host      | user             | plugin                | authentication_string                                                  |
+-----------+------------------+-----------------------+------------------------------------------------------------------------+
| localhost | healthchecker    | caching_sha2_password | $A$005$<t9La@)h?c2f\|Hs,KQ]drO8nuTS/TvOY8jrVFq/dX5Y5UTux3PwxU3Yi5l9DJA |
| localhost | mysql.infoschema | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.session    | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.sys        | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | root             | caching_sha2_password | $A$005$cy[
}CHwJj]<nzboAJ5rALeni.gkLH2LMD9brONBXERXmoOOKeUkgoQx0 |
+-----------+------------------+-----------------------+------------------------------------------------------------------------+
5 rows in set (0.00 sec)

接下来修改为任意主机（修改host字段为‘%’）

mysql> update user set host='%' where user='root';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

查看修改结果，用户root的host字段已经设置为‘%’，即任意主机。

mysql> select host,user,plugin,authentication_string from user;
+-----------+------------------+-----------------------+------------------------------------------------------------------------+
| host      | user             | plugin                | authentication_string                                                  |
+-----------+------------------+-----------------------+------------------------------------------------------------------------+
| %         | root             | caching_sha2_password | $A$005$cy[
}CHwJj]<nzboAJ5rALeni.gkLH2LMD9brONBXERXmoOOKeUkgoQx0 |
| localhost | healthchecker    | caching_sha2_password | $A$005$<t9La@)h?c2f\|Hs,KQ]drO8nuTS/TvOY8jrVFq/dX5Y5UTux3PwxU3Yi5l9DJA |
| localhost | mysql.infoschema | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.session    | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.sys        | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
+-----------+------------------+-----------------------+------------------------------------------------------------------------+
5 rows in set (0.00 sec)

8.到目前为止，仍然不能远程登录

$ mysql -h 172.17.0.2 -u root -p 
Enter password: 
ERROR 1130 (HY000): Host '172.17.0.1' is not allowed to connect to this MySQL server

授权并更改鉴权方式（修改plugin字段为‘mysql_native_password'）

mysql> grant all on *.* to 'root'@'%';
Query OK, 0 rows affected (0.02 sec)
mysql> alter user 'root'@'%' identified with 'mysql_native_password' by 'your-passwd';
Query OK, 0 rows affected (0.01 sec)

9.现在可以登录了

$ mysql -h 172.17.0.2 -u root -p 
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 126
Server version: 8.0.19 MySQL Community Server - GPL

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MySQL [mysql]>

到此完成全部设置