Mysql: MySQL 8 docker-compose :Failed to access directory for --secure-file-priv

[sharonsahadevan]

Hi,

when I started the container using docker-compose up, I am getting below error.

_**_Attaching to mysql
mysql    | 
mysql    | ERROR: mysqld failed while attempting to check config
mysql    | command was: "mysqld --verbose --help"
mysql    | 
mysql    | mysqld: Error on realpath() on '/var/lib/mysql-files' (Error 2 - No such file or directory)
mysql    | 2019-02-13T17:30:55.780793Z 0 [ERROR] [MY-010095] [Server] Failed to access directory for --secure-file-priv. Please make sure that directory exists and is accessible by MySQL Server. Supplied value : /var/lib/mysql-files
mysql    | 2019-02-13T17:30:55.786696Z 0 [ERROR] [MY-010119] [Server] Aborting_**_

my object is mapping my.cnf file to host directory so I can able to do the changes easily and changes will be persisted. I used official mysql image.

please guide me on how to solve this issue using docker-compose method.

docker compose file:

version: '3.3'
services:
  mysql:
    container_name: mysql
    image: 'mysql:8'
    restart: always
    ports:
      - "3306:3306"
    volumes:
      - /var/data/mysql:/var/lib/mysql
      - /etc/mysql:/etc/mysql

    environment:
      MYSQL_ROOT_PASSWORD: secret
      MYSQL_DATABASE: test
      MYSQL_USER: cdr
      MYSQL_PASSWORD: secret123

[wglambert]

Looks like a configuration error with the files you're mounting to /etc/mysql/

ERROR: mysqld failed while attempting to check config
. . .
mysqld: Error on realpath() on '/var/lib/mysql-files' (Error 2 - No such file or directory)

[sharonsahadevan]

what is the configuration error?
This is something related to --secure-file-priv

[ltangvald]

For most Linux, systems, the default value of secure-file-priv is /var/lib/mysql-files.
However, for this image we set it to NULL as default: https://github.com/docker-library/mysql/blob/master/8.0/config/my.cnf#L26
Unless you have need of the secure-file-priv system, I suggest adding secure-file-priv=NULL to your mounted config files.

[wglambert]

Closing as this issue seems resolved

If you have further questions you should ask the Docker Community Forums, the Docker Community Slack, or Stack Overflow. Since these repositories are for issues with the image and not necessarily for questions of usability

[fredericrous]

I can reproduce the issue. Folder /etc/mysql is not mounted as expected. The folder is empty. Therefore my.cnf file is not present

[boay24]

3/11/2020 MySQL 8.0 confirm is not working
I'm facing a problem, I'v tried everything with no luck.
(wsl2)

[yosifkit]

@boay24, if you are mounting a config from windows with secure-file-priv set (-v C:/some/my.cnf:/etc/mysql/my.cnf), then that might the problem. See the warning a couple lines up: [Warning] World-writable config file '/etc/mysql/my.cnf' is ignored. Since the only way to share things from Windows to Linux containers is for them to be world-writable permissions, you'll need to instead build an image with your config inside (and ensure chmod to the proper permissions). Or basically every config value from my.cnf is available as a flag, so just pass in the few that need changed (use docker run -it --rm mysqld:8.0 --help --verbose to see all the options and their current default values).

docker run:

$ docker run -d -e MYSQL_ROOT_PASSWORD=secret mysql:8.0 --character-set-server=utf8mb4 [more-mysqld-flags]

docker-compose file:

services:
  sql:
    image: mysql:8.0
    command:
      - '--character-set-server=utf8mb4'
      - --another-flag

Also note to not override the base /etc/mysql/my.cnf file (since it has some useful defaults) and instead put new config files into an included conf.d directory: https://github.com/docker-library/docs/tree/47056d4d6b95b70cd58d1d61671f1b7d338bfee5/mysql#using-a-custom-mysql-configuration-file

[boay24]

@yosifkit thx ,I really appreciate it! save myday,
but When I tried to use this image,
docker-compose.yml
version 3.7

it ran into the error:

mysqld: [Warning] World-writable config file '/etc/mysql/my.cnf' is ignored.
[ERROR] [MY-010095] [Server] Failed to access directory for --secure-file-priv. Please make sure that directory exists and is accessible by MySQL Server. Supplied value : /var/lib/mysql-files

Have I missed something?

env-info:
wsl2 (os build 19577,1000)
ubuntu 18.04

[felipemeddeiros]

For most Linux, systems, the default value of secure-file-priv is /var/lib/mysql-files.
However, for this image we set it to NULL as default: https://github.com/docker-library/mysql/blob/master/8.0/config/my.cnf#L26
Unless you have need of the secure-file-priv system, I suggest adding secure-file-priv=NULL to your mounted config files.

It worked for me but after that I got an new issue to access my database by database managers.
link of the discussion here

[handhikadj]

@yosifkit your workaround doesn't work on me on mysql:latest and Desktop for mac v2.4. attaching the volume to /etc/mysql/conf.d is still resultsing mysql: [Warning] World-writable config file '/etc/mysql/conf.d/my.cnf' is ignored..

docker-compose:

  db:
    image: mysql:latest
    container_name: db
    restart: unless-stopped
    tty: true
    ports:
      - "3306:3306"
    environment:
      MYSQL_DATABASE: ${DB_DATABASE}
      MYSQL_ROOT_PASSWORD: ${DB_PASSWORD}
      MYSQL_USER: ${DB_USERNAME}
      MYSQL_PASSWORD: ${DB_PASSWORD}
      SERVICE_TAGS: dev
      SERVICE_NAME: mysql
    volumes:
      - dbdata:/var/lib/mysql
      - ./docker/mysql:/etc/mysql/conf.d
    networks:
      - app-network

[yosifkit]

@handhikadj, I said to not mount a config file; it will not work on Windows or OSX (due to the host<->VM file sharing permissions). You have to either build a new image with the config built in (taking extra care of file permissions), or pass all your config as flags. (https://github.com/docker-library/mysql/issues/541#issuecomment-597866001)

[handhikadj]

okay. that enlighten out things. thank you

[Hassanzadeh-sd]

simple way add this volume into your compose file:
- ./mysql-files:/var/lib/mysql-files
and up again

version: '3.3'
services:
  mysql:
    container_name: mysql
    image: 'mysql:8'
    restart: always
    ports:
      - "3306:3306"
    volumes:
      - /var/data/mysql:/var/lib/mysql
      - /etc/mysql:/etc/mysql
      - ./mysql-files:/var/lib/mysql-files

    environment:
      MYSQL_ROOT_PASSWORD: secret
      MYSQL_DATABASE: test
      MYSQL_USER: cdr
      MYSQL_PASSWORD: secret123