Enable LOAD DATA LOCAL INFILE on Ubuntu 12.04

Posted on 2013-01-30 14:41:48
by Geert Vandeweyer

Loading Content

Enable LOAD DATA LOCAL INFILE on Ubuntu 12.04

Posted on 2013-01-30 14:41:48
by Geert Vandeweyer

On my ubuntu 12.04 LTS server, I noticed that I could not run the following mysql command anymore: 

LOAD DATA LOCAL INFILE '10.data.txt' INTO TABLE `MYDB`.`My_Table` (name, value, cash, foo,bar);

I got errors saying that the used command is not allowed with my version of MySQL. It turned out that due to security issues, the command was disabled. 

To re-enable I added the 'local-infile = 1 ' lines to mysql and mysqld parts of "/etc/mysql/my.cnf

[mysqld]
local-infile    = 1

[mysql]
local-infile    = 1

After restarting mysql, you can use the command when logging in from the commandline : 

mysql --local-infile -uroot -pyourpwd yourdbname

In perl, you need to add the "mysql_local_infile=1" statement to the connect line. 

use DBI;

my $dsn = "DBI:mysql:mydb;mysql_local_infile=1"; <----

my $user = "UserName";
my $password = "Password";
my $dbh = DBI->connect($dsn,$user,$password);

$dbh->do("LOAD DATA LOCAL INFILE '10.data.txt' INTO TABLE `MYDB`.`My_Table` (name,value,cash,foo,bar)");

... take note that is was disabled for security reasons, etc etc :-)

mysql, Perl, Ubuntu