How Do I Resolve the MySQL '--secure-file-priv' Error When Using LOAD DATA INFILE?

Addressing the Error: "--secure-file-priv" in MySQL

When executing a LOAD DATA clause, MySQL users may encounter the error "--secure-file-priv". This error indicates that the MySQL server has been configured with the --secure-file-priv option, which restricts file loading for security reasons.

Cause of the Error

The --secure-file-priv option limits the directories from which files can be loaded using LOAD DATA INFILE. This restriction is implemented to prevent SQL injection attacks. When secure-file-priv is enabled, LOAD DATA INFILE is only allowed to read files from the directory specified by the secure_file_priv variable.

Resolving the Error

To resolve this error, you have two options:

Option 1: Move File to Allowed Directory

• Determine the allowed directory using the command: SHOW VARIABLES LIKE "secure_file_priv";.
• Move your text file ("text.txt") to the specified directory.
• Execute the LOAD DATA INFILE statement again with the correct file path.

Option 2: Disable --secure-file-priv

• Caution: Disabling secure-file-priv may increase security risks.
• Edit the MySQL configuration file (my.ini or similar) and remove the --secure-file-priv option from the startup parameters.
• Restart the MySQL server.
• This will disable the file loading restrictions.

Additional Note

In MySQL 5.6, the secure-file-priv option cannot be modified dynamically. Therefore, if you choose to disable it, you must edit the MySQL configuration file and restart the server.

The above is the detailed content of How Do I Resolve the MySQL '--secure-file-priv' Error When Using LOAD DATA INFILE?. For more information, please follow other related articles on the PHP Chinese website!