0

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.
Nonetallt
  • 352
  • 2
  • 11
  • Possible duplicate of [How to use SELECT INTO OUTFILE to write to a directory other than /tmp?](https://stackoverflow.com/questions/20479662/how-to-use-select-into-outfile-to-write-to-a-directory-other-than-tmp) – miken32 Mar 18 '19 at 21:37
  • ^ I know how to write to other directories but the problem is with the permissions. I could write to my application dir but obviously the mysqld user is not going to have permissions to access this dir. Also, the file is outputted with ownership set as mysqld:mysqld which means it cannot be accessed by the application. – Nonetallt Mar 18 '19 at 22:08
  • I don't have a MySQL box to test on right now, but on MariaDB the file is created with 666 permissions; ownership is irrelevant. As for permissions, that's the answer in the dupe, though another solution would be to write to a subdirectory of /tmp that's symlinked to the application directory. – miken32 Mar 18 '19 at 22:16

1 Answers1

0

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).

Nonetallt
  • 352
  • 2
  • 11