Skip to main content

Enabling LOCAL INFILE in MySQL

Alejandro Millalen
  • Updated

LOAD DATA LOCAL INFILE is not enabled by default in MySQL, it should be enabled by placing local-infile=1 in your .cnf file in Linux and my.ini in Windows. But it does not work for all installations, in this article we explain how to debug and safely activate LOAD LOCAL INFILE.

Linux

Sometimes your LOCAL INFILE is set as Type: Boolean, that is the reason you can use a value "1". Check this note from MySQL to see more details. To resolve this, you have to use the value "ON", following these steps:

SHOW GLOBAL VARIABLES LIKE 'local_infile';
SET GLOBAL local_infile = 'ON';
SHOW GLOBAL VARIABLES LIKE 'local_infile';

 

It should look like this:

mysql> SHOW GLOBAL VARIABLES LIKE 'local_infile';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

mysql> SET GLOBAL local_infile = 'ON';
Query OK, 0 rows affected (0.06 sec)

mysql> SHOW GLOBAL VARIABLES LIKE 'local_infile';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql>

 

If it sets to 'ON', you are OK.

If it still says 'OFF', then look deep within the compiler settings to enable it.

 

If setting this in my.cnf

[mysqld]
local_infile=ON

and restarting mysql does not work either, you will have to start up mysql with something like this:

echo "SET GLOBAL local_infile = 'ON';" > /var/lib/mysql/init_file.sql
chown mysql:mysql /var/lib/mysql/init_file.sql
service mysql stop
service mysql start --init-file=/var/lib/mysql/init_file.sql
rm -f /var/lib/mysql/init_file.sql

or adding this to my.cnf:

[mysqld]
init-file=/var/lib/mysql/init_file.sql

then restart mysql.

 

Windows

 

For default installations of MySQL 8.x on Windows:

 

  • Look for the initialization file, commonly located at: C:\ProgramData\MySQL\MySQL Server 8.0 
  • Open my.ini file with a text editor
  • Look for the headers [Client], [mysql] under the CLIENT section and [mysql] under the SERVER section. And add the following statement under each of these headers:

          local_infile=ON

  • Save the file and restart MySQL service under the Windows local services.

Sometimes is not possible to save changes in the my.ini file, in this case you have to save the file edited in a new directory and then replace (copy and paste) the previous file using an Administrator account.

 

Related articles