Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Docker MySQL 8 how to set --secure-file-priv

I would like to solve this problem:

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

with the official MySQL 8 image on docker.

the command:

SHOW VARIABLES LIKE "secure_file_priv";

gives:

+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv | NULL  |
+------------------+-------+
1 row in set (0.01 sec)

Is it possible to overwrite the default value NULL for the --secure-file-priv by using the official MySQL 8 Docker image?

the default value in this image is set to NULL config/my.cnf

Perfectly I would like to set just an environmental variable or parameter when using docker run or create instead of bringing my own config file.

but if this is not possible then how to use custom config file? is it possible that custom file just overwrites this one parameter and leaves others as they are in the official image config?

like image 433
Jimmix Avatar asked Mar 15 '19 22:03

Jimmix


People also ask

How do I fix priv secure file?

To overcome this error, you either need to remote the --secure-file-priv setting from your my. cnf file, or load your data from a directory specified in the value of the variable. Once you do so, your data should be loaded in without any issues!

How do you fix error code 1290 the MySQL server is running with the -- secure file priv option so it Cannot execute this statement?

Fixing the MySQL error 1290 by reconfiguring and restarting Go to start menu and type services. msc then press Ctrl+Shift+Enter to run it as an administrator. Locate the MySQL service and double-click to open its properties dialog. Check the Path to Executable for the –defaults-file option to determine where my.


1 Answers

According to this documentation, you can configure secure-file-priv through command-line by passing --secure-file-priv=dir_name

secure-file-priv possible values are: empty string, dirname or NULL as explained in the privous url.

From mysql-docker page:

Configuration without a cnf file: Many configuration options can be passed as flags to mysqld. This will give you the flexibility to customize the container without needing a cnf file. For example, if you want to change the default encoding and collation for all tables to use UTF-8 (utf8mb4) just run the following:

$ docker run --name some-mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:tag --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci

So in our case would be like this:

dir_name should be a directory inside your container otherwise you will get the following error: mysqld: Error on realpath() on 'dir_name' (Error 2 - No such file or directory)

$ docker run --name some-mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:tag --secure-file-priv=dir_name

And now our change is committed in MySQL

mysql> SHOW VARIABLES LIKE "secure_file_priv";
+------------------+----------+
| Variable_name    | Value    |
+------------------+----------+
| secure_file_priv | dir_name |
+------------------+----------+

Alternatively, you can use a custom configuration file as explained in here:

Using a custom MySQL configuration file: The default configuration for MySQL can be found in /etc/mysql/my.cnf, which may !includedir additional directories such as /etc/mysql/conf.d or /etc/mysql/mysql.conf.d. Please inspect the relevant files and directories within the mysql image itself for more details.

If /my/custom/config-file.cnf is the path and name of your custom configuration file, you can start your mysql container like this (note that only the directory path of the custom config file is used in this command):

$ docker run --name some-mysql -v /my/custom:/etc/mysql/conf.d -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:tag

This will start a new container some-mysql where the MySQL instance uses the combined startup settings from /etc/mysql/my.cnf and /etc/mysql/conf.d/config-file.cnf, with settings from the latter taking precedence.

like image 173
Mostafa Hussein Avatar answered Sep 20 '22 15:09

Mostafa Hussein