Child pages
  • Why do I get Access Denied errors when trying to use outfile or infile in mysql?
Skip to end of metadata
Go to start of metadata

With Mysql, you can use 'into outfile' in a sql statement to write the output into a file but are likely to get errors when you do this. You may also get similar errors when trying to use 'infile'. For example:

mysql> select * from sometable into outfile '/tmp/output';
ERROR 1045 (28000): Access denied for user 'someuser'@'localhost' (using password: YES)

The problem with doing this is related to the way writing files works in mysql. The sql statement is run as the user who is running the mysql server (typically the mysql user) and not your own username. As a result, allowing normal database accounts to write files using this mechanism has a couple problems:

  1. File ownership - Files written using this mechanism will be owned by the mysql user. So, there are resulting ownership problems including the fact that you will not be able to delete the files that get created as the mysql user. Likewise, with infile the files are read as the mysql user.
  2. Security issues - Allowing a user to write arbitrary files as the mysql user opens up the potential for accidental overwriting of system files or even malicious abuse.

It is possible to grant FILE privilege to a mysql account to allow the use of this feature but the above issues make this less than ideal. An alternate approach that gives similar functionality is to do something like the following:

echo 'select * from sometable into outfile' | mysql -p -u someuser somedatabase > /tmp/output

The primary advantage of this approach is that the generated files are owned by you so there are no associated ownership issues. Of course, this workaround will not work in all cases and, if that is the case, granting FILE privilege may be the only option.

If your goal is to get a complete dump of your database, you can do this using the mysqldump command from the shell prompt (not the mysql prompt) as follows:

mysqldump -p -u mysql_account mysql_database > dumpfile.sql

In this example, you will want to replace mysql_account and mysql_database with the appropriate values for your database.