Search code examples
phpmysqlfile-permissionstmp

Using mysql INTO OUTFILE in web application context

I want to export data from mysql quickly to an output file. As it turns out, INTO OUTFILE syntax seems miles ahead of any kind of processing I can do in PHP performance wise . However, this aproach seems to be ridden with problems:

  1. The output file can only be created in /tmp or /var/lib/mysql/ (mysqld user needs write permissions)
  2. The output file owner and group will be set as mysqld
  3. tmp dir is pretty much a dumpster fire because of settings like "private tmp".

How would I manage this in a way that isn't a nightmare in terms of managing the user accounts / file permissions? I need to access the output file from my php script and I would also like to output this file to the application directory if possible. Of course, if there is another way to export my query results in a performance effective way, I would like to know of it.

Currently I am thinking of the following aproaches:

  • Add mysqld user to a "www-data" group to give access to application files, write to application dir and other www-data users will hopefully be able to access the output files.
Solution

  • I could not get the access rights working for the mysql user. Having scripts add the user to www-data group or other such measures would also increase the application deployment overhead.

    I decided to go with using the program piping method with symfony Process component.

    mysql -u <username> -p<password> <database> -e "<query>" | sed 's/\t/","/g;s/^/"/;s/$/"/;' > /output/path/here.csv

    Note that the csv formatting might break in case you have values that contain reserved characters like \ , " \n etc. in your columns. You will also need escape these characters (" to \" for example) and possibly do something about mysql outputting null values as NULL (string).