When using outfile or infile in mysql you are likely to encounter permissions problems. This page shows how to work around the problems with both of these commands.

With Mysql, you can use the load command to read from an input file in a SQL statement to read data into a table. However, you may run into permissions errors when you do this. For example:

mysql> load data infile 'somefile.csv' into table sometable ...
ERROR 1045 (28000): Access denied for user 'someuser'@'localhost' (using password: YES)

You must make sure that the file you are trying to load AND all paths leading up to that file are accessible by the mysql users that the service is running as. If the file is in your home directory you may find that this directory itself is not readable by any other users so the mysql user has no access.

Also, when using the Luddy School MySQL server (db.luddy.indiana.edu), the problem is that the SQL statement is executed on the database server and the file you are trying to read does not exist on the remote database server. To get around this you must tell the load to read a local file and not one on the database server. You can do that by simply adding the local option to the load as in this example:

mysql> load data local infile 'somefile.csv' into table sometable ...
Query OK, N rows affected (0.00 sec)
Records: N Deleted: 0 Skipped: 0 Warnings: 0

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. 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 several 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.
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.
3. Remote execution - You may be working with a remote database server and the output file that is written would be written on the remote server that you can't access.

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

echo 'select * from sometable' | 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.