Shulou(Shulou.com)06/02 Report--

This article mainly introduces the example analysis of mysql8.x docker remote access configuration, which is very detailed and has certain reference value. Friends who are interested must finish it!

Environmental condition

Mysql 8.x is deployed through docker, and the docker-compose.yml is launched as follows:

Version: "3.2" services: mysql: container_name: mysql image: "mysql:8.0" ports:-"3306 mysql 3306" command: ["--character-set-server=utf8mb4", "--collation-server=utf8mb4_unicode_ci", "--sql_mode=STRICT_TRANS_TABLES,NO_ZERO_DATE" ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION " ] volumes:-type: bind source:. / mysql target: / var/lib/mysql-type: bind source:. / mysql-docker.cnf target: / etc/mysql/conf.d/docker.cnf environment:-MYSQL_RANDOM_ROOT_PASSWORD=yes -MYSQL_USER=myuser-MYSQL_PASSWORD=mypass-MYSQL_DATABASE=mydb restart: always

The first time you start with the docker-compose command, the image of mysql 8.x is automatically downloaded.

After the boot is successful, you can see that port 3306 is also mapped.

At this point, the mysql installation starts normally.

Errors encountered

Next, when you connect to the mysql server through a database client such as navicat, you find that you can't connect at all, and the types of errors encountered are:

ERROR 1045 (28000): Access denied for user' myuser'

10060 error

10061 error

Solution method

On the Internet, there are many ways to solve the problem of remote access by setting the permissions of database users, but there are only core steps and lack of process.

1. Log in to the mysql docker

As you can see from the docker-compose.yml above, the password for the mysql root user is not configured.

In the volumn mapping section, you can see that we have mapped the / etc/mysql/conf.d/docker.cnf file in the container to the outside. The contents of this file are as follows:

[mysqld] skip-host-cacheskip-name-resolve

Add the following line so that you don't need a password to log in to mysql.

[mysqld] skip-host-cacheskip-name-resolveskip-grant-tables

After adding, restart the container.

Docker-compose downdocker-compose up-D2. Set root password

Enter the container and log in to the mysql server with your root account.

Docker exec-it mysql / bin/bashmysql-uroot # enter directly here, and you can log in to the server mysql > flush privileges;Query OK, 0 rows affected (0.00 sec) mysql > ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY' mysqlroot';Query OK, 0 rows affected (0.01 sec) mysql > flush privileges;Query OK, 0 rows affected (0.00 sec)

Note that the flush privileges; must be executed first or the password change will not succeed.

Then, exit the container and restore the mapped / etc/mysql/conf.d/docker.cnf file.

[mysqld] skip-host-cacheskip-name-resolve

Delete the new line and restart the container.

Docker-compose downdocker-compose up-d3. Set root remote access permissions

After restarting the container, enter the container again and set the remote access permissions for the root user.

Docker exec-it mysql / bin/bashmysql-uroot-p # needs to enter the password mysqlrootmysql > ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY' mysqlroot';Query OK, 0 rows affected (0 sec) mysql > flush privileges;Query OK, 0 rows affected (0 sec) configured in the previous step

Configure remote access permissions with 'root'@'%'' instead of 'root'@'localhost'' in the previous step

Once set up, you don't have to restart the mysql docker container, you can connect with navicat.

4. Set up remote access for ordinary user myuser

The above operation then configures the remote connection of the normal user myuser.

Mysql > ALTER USER 'myuser'@'%' IDENTIFIED WITH mysql_native_password BY' mypass';Query OK, 0 rows affected (0.00 sec) mysql > flush privileges;Query OK, 0 rows affected (0.00 sec) mysql > grant all privileges on *. * to 'myuser'@'%' with grant option;Query OK, 0 rows affected (0.00 sec) mysql > flush privileges;Query OK, 0 rows affected (0.01 sec)

If the setup is successful, the myuser account can also be connected remotely through navicat.

The above is all the contents of the article "sample Analysis of mysql8.x docker remote access configuration". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.