Detailed configuration of mysql8.x docker remote access

Environmental conditions

MySQL 8.x is deployed via docker, and the startup docker-compose.yml is as follows:

version: "3.2"
services:
    mysql:
        container_name: mysql
        image: "mysql:8.0"
        ports:
            - "3306: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

When you start the docker-compose command for the first time, the mysql 8.x image will be automatically downloaded.

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

At this point, mysql is installed and started normally.​

Errors encountered

Next, when connecting to the MySQL server through a database client such as Navicat, it was found that it could not connect at all. The types of errors encountered were:

1. ERROR 1045 (28000): Access denied for user 'myuser'
2. 10060 Error
3. 10061 Error

Workaround

There are many methods on the Internet to solve the problem of remote access by setting database user permissions, but they only have the core steps and lack the process.

1. Log in to mysql docker

As can be seen from the above docker-compose.yml, the password of the mysql root user is not configured.
In the volume mapping section, you can see that we 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-cache
skip-name-resolve

Add a line as follows, so that no password is required to log in to MySQL.

[mysqld]
skip-host-cache
skip-name-resolve
skip-grant-tables

After adding, restart the container.

docker-compose down
docker-compose up -d

2. Set the root password

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

docker exec -it mysql /bin/bash
mysql -uroot # Press Enter here to log in to the server without entering a password. 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 you must flush privileges first; otherwise, the password change will not succeed.​

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

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

Delete the newly added line and restart the container.

docker-compose down
docker-compose up -d

3. Set up root remote access permissions

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

docker exec -it mysql /bin/bash
mysql -uroot -p # You need to enter the password mysqlroot configured in the previous step

mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'mysqlroot';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

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

After setting, you can connect to it with navicat without restarting the mysql docker container.

4. Set up remote access for ordinary user myuser

Follow the above steps to configure the remote connection for the ordinary 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 setting is successful, the myuser account can also connect remotely through navicat.

This is the end of this article about the detailed configuration of mysql8.x docker remote access. For more relevant mysql8.x docker remote access content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!