A strange place of mysql8.0 under docker

• I found a strange phenomenon when I installed mysql (last version) with docker in the morning
• If there is

Let's talk about the pit first

• The newly installed mysql does not allow remote connection. This problem is easy to solve. Once you search online, the answer will come out~
• There are many versions of the authorization commands, as follows

#To grant authorization
GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY "rw";
flush privileges;

1) Grant all privileges on *. * to 'root' @ '%' ාgrant permission to
   GRANT ALL PRIVILEGES ON *. * 'root' @ '%' identified by '123123' WITH GRANT OPTION; this is a popular writing method on the Internet. In fact, it will be wrong.
2) flush privileges

• Wait, wait, etc.
• I've tried it all, but I can't connect.
• So I thought about the rudest way -- directly changing the User table

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)

• Here is the table structure. You can see that the primary key is host+user
• Look-up table

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)

• I found that there are two root users, and I was wondering. Since there are two root users, which one should I listen to when I visit the Internet? Besides, according to the principle%, localhost should be included.
• In my opinion, there should be only one root (the previous 5.7, which should be the 8 + version). So I deleted the line of localhost+root ~ (it's a docker container anyway)
• It still doesn't connect. 1251 error reported
  

Episode

• According to the boss, upgrading version 8.0 from 5.7 will not change the existing user's authentication method, but the new user will use the new caching_sha2_password by default. The client does not support the new encryption method.
• So I had to change. I copied the following sentence

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Password';

• However, if the error is reported, I will remember that I deleted 'root' @ 'localhost'~

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

• So in the end, it's changed to '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)

This time you can connect:

summary

• I don't know if I've changed it. Anyway, I haven't met two root cases before. This is what I think is strange
• Maybe it is, maybe it's the problem of version 8.0, maybe it's the problem of the image maker, or there's something wrong with docker when it's started.