※当サイトの記事には、広告・プロモーションが含まれます。

トップ > MySQL > MySQLの"secure_file_priv"の値がNULLだと、SQLの結果をファイルに出力できない

MySQLの"secure_file_priv"の値がNULLだと、SQLの結果をファイルに出力できない

MySQL

f:id:ts0818:20210304182753j:plain

nazology.net

2月25日にオープンアクセスジャーナル『Frontiers in Molecular Neuroscience』で発表された新しい研究は、私たちの記憶がコンピュータと同じようなバイナリ形式で、シナプスを構成するタンパク質に書き込まれていると報告しています。

人間の脳は有機スーパーコンピュータだった 「0と1」で記憶を保存すると明らかに - ナゾロジー

⇧ Oh, my gosh...

ザワザワしちゃうよね～、何これ怖い～、それでも人間って素晴らしいと信じて生きていくできっと良いはず、どうもボクです。

というわけで、MySQLについて。

レッツトライ～。

 

"secure_file_priv"って何？

MySQL 5.6のドキュメントによりますと、

dev.mysql.com

サーバーシステム変数 

MySQL Server には MySQL Server の構成方法を指示する多くのシステム変数が保持されています。各システム変数にはデフォルト値があります。システム変数は、コマンド行のオプションを使用するか、オプションファイルでサーバー起動時に設定できます。これらのほとんどは、SET ステートメントを使用してサーバーの実行中に動的に変更でき、これによりサーバーを停止して再起動することなくサーバーの動作を変更できます。システム変数値を式によって参照できます。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 5.1.4 サーバーシステム変数

⇧「サーバーシステム変数」なるものがございまして、

  • オプションファイルで変更してMySQLサーバー再起動
    静的な設定
  • SETコマンドで変更
    動的な設定

 のどっちかで値を修正できるんだと。「サーバーシステム変数」のほとんどは「動的な設定」ができるんだと。

で、「secure_file_priv」 の値はというと、

  • secure_file_priv

コマンド行形式 --secure-file-priv=path
システム変数 secure_file_priv
スコープ グローバル
動的 いいえ
型 文字列

デフォルトでは、この変数は空です。ディレクトリの名前に設定すると、LOAD_FILE() 関数と、LOAD DATA および SELECT ... INTO OUTFILE ステートメントの効果を制限し、そのディレクトリ内のファイルにのみ機能します。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 5.1.4 サーバーシステム変数

⇧ ん？「動的」が「いいえ」になってるんだが...

www.ukkari-san.net

結果、一時的な書き換えは不可だったので、mysqlの設定ファイルを編集し、「secure-file-priv = “”」を追記します。

mysqlコマンド(import, export編) | うっかりさん！

⇧ 上記サイト様によりますと、SETコマンドによる設定（「動的な設定」）はできないらしい...

というか、MySQL 5.6のドキュメントだと、デフォルト値は「空」ってことらしいんだけど、

f:id:ts0818:20210304161357p:plain

⇧「NULL」なんですけど？

MySQL 8 で何か変わったのか？

dev.mysql.com

  • secure_file_priv

    Command-Line Format --secure-file-priv=dir_name
    System Variable secure_file_priv
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type String
    Default Value platform specific
    Valid Values

    empty string

    dirname

    NULL

    This variable is used to limit the effect of data import and export operations, such as those performed by the LOAD DATA and SELECT ... INTO OUTFILE statements and the LOAD_FILE() function. These operations are permitted only to users who have the FILE privilege.

    secure_file_priv may be set as follows:

    • If empty, the variable has no effect. This is not a secure setting.

    • If set to the name of a directory, the server limits import and export operations to work only with files in that directory. The directory must exist; the server does not create it.

    • If set to NULL, the server disables import and export operations.

    The default value is platform specific and depends on the value of the INSTALL_LAYOUT CMake option, as shown in the following table. To specify the default secure_file_priv value explicitly if you are building from source, use the INSTALL_SECURE_FILE_PRIVDIR CMake option.

https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html

⇧ 変わってました...

「platform specific」に依りけりということらしい...

しかも、『If set to NULL, the server disables import and export operations.』 ってことらしいので、「NULL」だと、ファイル出力はできないらしい...

 

"secure_file_priv"の値がNULL以外ならOKらしいので、空を設定する

 まぁ、MySQL  5.6 のドキュメントに「secure_file_priv」のデフォルト値は「空」ですってなってたので、「空」にしときますかね。

MySQL 8 のドキュメントでも『If empty, the variable has no effect. This is not a secure setting.』ってことで『「空」を設定することによる影響はない、ただしセキュアな設定ではない』って言ってるんで、本番環境では、何か適当なディレクトリを設定する感じになるんですかね？

というわけで、どのファイルを修正すれば？

MySQL 8 の場合は、

On Windows, MySQL programs read startup options from the files shown in the following table, in the specified order (files listed first are read first, files read later take precedence).

Table 4.1 Option Files Read on Windows Systems

File Name Purpose
%WINDIR%\my.ini, %WINDIR%\my.cnf Global options
C:\my.ini, C:\my.cnf Global options
BASEDIR\my.ini, BASEDIR\my.cnf Global options
defaults-extra-file The file specified with --defaults-extra-file, if any
%APPDATA%\MySQL\.mylogin.cnf Login path options (clients only)
DATADIR\mysqld-auto.cnf System variables persisted with SET PERSIST or SET PERSIST_ONLY (server only)

https://dev.mysql.com/doc/refman/8.0/en/option-files.html

⇧ ドキュメントを見た感じでは、上記のパスに配置された「my.ini」「my.cnf」のどっちかでいけるらしいですね。

まぁ、自分の場合は、MySQLをインストールした場所がおかしかったのか、上記のパスではなかったんですが...

f:id:ts0818:20210304163345p:plain

⇧ で、Windowsの場合、

jobtech.jp

MySQLをCドライブのProgram Fileフォルダ内にインストールした場合「my.ini」ファイルを直接編集できないことがあります。

また編集できても保存できないことがあります。

※設定ファイルを変更する場合は必ず変更前ファイルのバックアップを取っておく

MySQLの文字コード変更が反映されない時の確認 | JOBTECH.JP

⇧ 上記サイト様のように、「C:\Program Files」配下にMySQLをインストールしてる場合、MySQLの設定ファイルを開くと「上書き禁止」になってしまうので、「C:\Program Files」以外の場所にMySQLの設定ファイルを移動して、編集してから元の場所にファイルを配置する感じにする必要がありますと。

設定ファイルには、以下を追記します。

[mysqld]
secure-file-priv = ""

⇧ ってな感じで、サーバー側の設定の部分（「[mysqld]」）に追記します。

そしたら、設定ファイルを保存して再配置します。

f:id:ts0818:20210304165635p:plain

「ファイルを置き換える(R)」で。 

f:id:ts0818:20210304165659p:plain

「続行(C)」で。 

f:id:ts0818:20210304165733p:plain

で、WindowsにMySQLをインストールしてる場合は、Windowsの「サービス」でMySQLの「サービス」を再起動すれば良いらしい。

f:id:ts0818:20210304165430p:plain

f:id:ts0818:20210304165820p:plain

MySQLサーバー再起動した場合は、MySQLに接続し直さないと怒られるみたい...

f:id:ts0818:20210304165942p:plain

「secure-file-priv」に「空」が設定されました。

f:id:ts0818:20210304170021p:plain

無事に、SQLの結果をCSVファイルに出力できました。

■OKな例

f:id:ts0818:20210304180710p:plain

f:id:ts0818:20210304181216p:plain

ただし、自分の環境だけなのかしれませんが、ファイルのパスに日本語が入ってるとエラーになりました。

■NGな例

f:id:ts0818:20210304181056j:plain

⇧ 日本語が解釈できない...

何だろう、MySQLの文字コードの設定なのか、コマンドプロンプトを使ってるのが良くないのか、時間のある時に調査ですかね...

ちなみに、CSVファイルなどに出力する際に「ヘッダ」をつける必要がある場合は、「UNION句」を付けて上げる必要があるみたい。

database.guide

⇧ 上記サイト様が詳しいです。

SQLのインデントなどは、

qiita.com

⇧ 上記サイト様を参考にさせていただきました。

というわけで、いろいろ注意しないといけないことが多そうなMySQLですが、慣れていきたいところですね。

有識者の方のSQLを写経して勉強したいな...

今回はこのへんで。