Connecting to MariaDB in Docker: Fixing "Access Denied" with Docker Compose

• MySQL/MariaDB: This error indicates that you're trying to connect to a MySQL or MariaDB database running in a Docker container using the root user from your local machine (localhost), but the connection is being rejected due to incorrect credentials or configuration.
• Docker: Docker is a containerization platform that allows you to package applications with their dependencies into standardized units called containers.
• Docker Compose: Docker Compose is a tool for defining and running multi-container applications with Docker. It helps you easily configure services, networks, and volumes for your application.
• External Volume: An external volume is a directory on your host machine that you map to a directory inside the Docker container. This allows you to persist data generated by the container, even if the container is recreated.

Potential Causes and Solutions:

1. Incorrect Root Password:

2. External Volume Interfering with Initialization:

3. Conflicting Credentials in External Volume:

4. Incorrect Connection Method:

Additional Tips:

• Consider creating a non-root user with appropriate permissions for your application instead of using root.
• Store passwords securely and avoid hardcoding them in configuration files.

version: "3"
services:
  mariadb:
    image: mariadb:10.4.8
    environment:
      MYSQL_ROOT_PASSWORD: your_strong_password
    volumes:
      # Add volumes for persistent data if needed (optional)
      - my-data-volume:/var/lib/mysql

Handling External Volume and Initialization:

Option A: Create a new, empty volume for initialization:

version: "3"
services:
  mariadb:
    image: mariadb:10.4.8
    volumes:
      - new-data-volume:/var/lib/mysql  # Empty volume for initialization

Option B: Temporarily remove volume mount during initialization:

version: "3"
services:
  mariadb:
    image: mariadb:10.4.8
    # Comment out volume mount during initialization
    # volumes:
    #   - my-data-volume:/var/lib/mysql

    # Run initialization command (optional)
    command: mysqld --initialize-insecure

    # Uncomment volume mount after initialization
    # volumes:
    #   - my-data-volume:/var/lib/mysql

Connecting to the Container:

Using docker exec:

docker exec -it mariadb_container mysql -u root -p your_strong_password

Using a separate client application:

mysql -h <container_ip_address> -u root -p your_strong_password

Replace placeholders like:

• your_strong_password: Your actual strong password for the root user.
• my-data-volume: The name of your external volume.
• new-data-volume: Name for the new volume used during initialization (if used).
• mariadb_container: The name of your MariaDB container.
• <container_ip_address>: The IP address of your MariaDB container (use docker inspect mariadb_container to find it).

• This is a more secure approach compared to using the root user.
• Define a dedicated user with specific permissions for your application in the container's initialization script or by connecting to the container and running MariaDB commands.

Example (using docker-compose.yml):

version: "3"
services:
  mariadb:
    image: mariadb:10.4.8
    environment:
      MYSQL_ROOT_PASSWORD: your_strong_password  # For initialization
    volumes:
      - my-data-volume:/var/lib/mysql
    command: mysqld --initialize-insecure  # Initialize with insecure mode
             mysql -u root -p your_strong_password <<-EOF
             CREATE USER my_user@localhost IDENTIFIED BY 'my_user_password';
             GRANT ALL PRIVILEGES ON my_database.* TO my_user@localhost;
             FLUSH PRIVILEGES;
             EOF

• Replace my_user, my_user_password, and my_database with your desired values.
• This example creates a user my_user with a password my_user_password and grants them all privileges on the my_database database.

Using a Secrets Management Tool:

• Consider using a secrets management tool like HashiCorp Vault or AWS Secrets Manager to store sensitive information like the root password securely and retrieve it dynamically when starting the container.
• This improves security by keeping passwords out of your code or configuration files.

Environment Variables for Non-Root User Credentials:

• Define environment variables in your docker-compose.yml to store the non-root user credentials:

version: "3"
services:
  mariadb:
    image: mariadb:10.4.8
    environment:
      MYSQL_ROOT_PASSWORD: your_strong_password  # For initialization
      MYSQL_USER: my_user
      MYSQL_PASSWORD: my_user_password
    volumes:
      - my-data-volume:/var/lib/mysql
    command: mysqld --initialize-insecure  # Initialize with insecure mode

• Connect to the container using these credentials:

mysql -h <container_ip_address> -u $MYSQL_USER -p $MYSQL_PASSWORD

Important Considerations:

• Choose the method that best suits your security requirements and application needs.
• For production environments, using a non-root user and a secrets management tool is highly recommended.
• Always prioritize strong password practices and avoid hardcoding them in configuration files.