Guide to Troubleshooting MySQL Remote Connection Issues in a Docker Environment

Guide to Troubleshooting MySQL Remote Connection Issues in a Docker Environment

Problem Description

A MySQL database running within a Docker container appears to be functioning correctly, with port 3306 mapped to the host machine. However, it is not possible to connect to the MySQL service from either the local machine or external networks.

Problem Analysis

This is typically a Docker network access permission issue. The error logs indicate that access attempts from the Docker default bridge IP (172.17.0.1) are being denied, suggesting that there might be a problem with the MySQL user permissions configuration.

Resolution Steps

1. Reset MySQL User Permissions

First, enter the MySQL container and reset the user permissions:

# Enter the MySQL container
docker exec -it your_mysql_container bash

# Connect to MySQL
mysql -u root -p

# Check current user permissions
SELECT user, host FROM mysql.user;

# Remove existing root user settings
DROP USER 'root'@'%';
DROP USER 'root'@'localhost';
DROP USER 'root'@'172.17.0.1';

# Recreate the root user and grant privileges
CREATE USER 'root'@'%' IDENTIFIED BY 'your_password';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

2. Verify MySQL Configuration

Ensure that the MySQL configuration allows remote connections:

# Execute inside the container
cat /etc/mysql/my.cnf
# Or
cat /etc/mysql/mysql.conf.d/mysqld.cnf

If you see bind-address = 127.0.0.1, change it to:

bind-address = 0.0.0.0

3. Restart the Container

docker restart your_mysql_container

4. Validate Connection

# Test connection from the host
mysql -h localhost -u root -p

# Test connection from a remote location
mysql -h your_server_ip -u root -p

Troubleshooting Checklist

If you still cannot connect, check the following:

1. Verify Docker Port Mapping

docker inspect your_mysql_container | grep -A 20 PortBindings

Ensure you see similar output:

"PortBindings": {
    "3306/tcp": [
        {
            "HostIp": "0.0.0.0",
            "HostPort": "3306"
        }
    ]
}

2. Check Firewall Settings

sudo ufw status

Ensure port 3306 is open:

sudo ufw allow 3306

Precautions

  1. For security reasons, avoid using the root user for remote connections.
  2. It’s recommended to limit access to specific IP addresses instead of using ‘%’.
  3. In production environments, enforce a stronger password policy.
  4. Consider using SSL/TLS for encrypted connections.

Summary

Remote connection issues with MySQL usually involve multiple aspects: user permissions, network configuration, firewall settings, etc. By systematically troubleshooting and configuring, you can gradually identify and resolve the problem. While solving the problem, pay attention to maintaining system security.

Comments

Post a Comment

Popular posts from this blog

How to turn off Sass warning prompts in Nuxt.js projects

Background of the Issue When developing projects with Nuxt.js, it’s common to encounter a large number of Sass-related warning messages in the console. These warnings mainly include: Warnings about the order of mixin declarations. Warnings about the use of Legacy JS API. Although these warnings do not affect the operation of the program, they can degrade the development experience by cluttering the console. Today, we will explore how to elegantly address this issue. Solutions 1. Disable Warnings via Configuration In the nuxt.config.js file, we can modify the Sass configuration to suppress these warning messages: export default { build: { loaders: { scss: { sassOptions: { quietDeps: true , // Suppress warnings from dependencies logger: { warn: function ( message ) { return null ; // Silence warning messages }, debug: function ( message ) { return null ; // Silence ...
Read more

Guide to Modifying Docker Container Port Mappings

Guide to Modifying Docker Container Port Mappings If you need to modify the port mappings for an existing Docker container, such as one with the ID 123456 , follow these steps carefully. This guide assumes that you have administrative access to the Docker host and are familiar with using a text editor like vim on Linux systems. service docker stop Step 1: Modify hostconfig.json First, edit the hostconfig.json file located in the container’s directory: sudo vim /var/lib/docker/containers/ 123456 /hostconfig.json Look for the "PortBindings" configuration. If your container was started with port mappings, this object will not be empty. Add or update the port bindings as follows: { " PortBindings ": { " 5700/tcp ": [{ " HostIp ": "" , " HostPort ": "10086" }] , " 6700/tcp ": [{ " HostIp ": "" , " HostP...
Read more

Optimizing Class Name Management with CSS Attribute Selector

Optimizing Class Name Management with CSS Attribute Selectors In modern web development, managing class names in HTML and CSS is a common challenge. As projects grow in size, the stacking and repeated use of class names often lead to bloated and hard-to-maintain CSS code. This article introduces how to leverage CSS’s special attribute selectors to manage class names in a more elegant and efficient way. The Problem In HTML, we frequently use class names to define element styles. For example: < div class = "bestseller out-of-stock" > ... </ div > As the project evolves, the number of class names can grow significantly, making the CSS code difficult to maintain. For example: .bestseller { ... } .out-of-stock { ... } In such cases, the CSS file becomes lengthy and hard to manage. The Power of CSS Attribute Selectors CSS provides three special attribute selectors that are similar in concept to regular expressions, helping us filter class names more flexibly ...
Read more