Perfect solution to the problem that Navicat cannot connect after installing mysql in docker

1. Docker pulls the image

docker pull mysql (pull the latest version by default)

2. Run mysql

docker run -p 3308:3306 --name mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql

Container name: mysql Password: 123456

3. Check whether it is running

docker ps -a

4. View the startup log

docker logs mysql

mysql is the name of the container that was just started. Confirm that mysql is started normally.

5. Errors

When I used Navicat to link, I found the following error

We need the following processing

1. Enter the mysql client

docker exec -it c6c8e8e7940f /bin/bash

Where c6c8e8e7940f is the name of my mysql container

----Equivalent command docker exec -it mysql /bin/bash

mysql -u root -p123456

123456 is the login password of mysql, which is set when docker run

2. View user information

select host,user,plugin,authentication_string from mysql.user;

3. Reset your password

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'newpassword'

newpassword is the new password

I set the password to 'mysql'

4. Reconnect using Navicat

Appendix: It may also be a firewall problem

sudo firewall-cmd --add-port=3306/tcp (open port 3306)

or

sudo systemctl stop firewalld (turn off the firewall)

Supplement: Install MySQL 8 in Docker and configure remote connection

Step 1: Download the mysql image

docker pull mysql

The default is to download the latest stable version

Step 2: Start the mysql image

docker run --name dockermysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=mysqlpassword -d mysql

1. --name is the alias of the image

2. -p maps 3306 to 3306 (docker is a virtual machine with its own port)

3. -e MYSQL_ROOT_PASSWORD=mysqlpassword Set the MySQL server password (needed later, be sure to remember)

4. -d backend startup

5. The image name to be started (can be replaced by id)

Step 3: Query the boot image

docker ps

as follows:

Step 4: Enter the container

docker exec -it dockermysql bash

dockermysql is the name of the image, which can be replaced by id

Step 5: Log in to MySQL

mysql -u root -p

Then enter the password set above

Step 6: Set up remote access

Switch database (this should be the default, you don't need to switch, just switch it for safety)

use mysql;

Change remote link authorization

grant all privileges on *.* to 'root'@'%';

Step 7: Navicat reports error 2059 when connecting

The error is caused by encryption method problem

Check:

select Host,User,plugin from user;

The results before modification are as follows:

Execute the modification command:

alter user 'root'@'%' identified with mysql_native_password by 'yourPassword';

Change to your mysql password

The result after successful modification is as follows:

The above is my personal experience. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM. If there are any mistakes or incomplete considerations, please feel free to correct me.