在使用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]>
到此完成全部设置
网友评论