Search code examples
mysqlpermissionsmariadbuser-permissions

exporting sql results having trouble with permissions

I am having permission issues with mariadb when exporting sql result.

select * from referalList
INTO OUTFILE '/home/joe/testOutFileReferalList.csv';


SQL Error [1] [HY000]: (conn=63) Can't create/write to file '/home/joe/testOutFileReferalList.csv' (Errcode: 13 "Permission denied")

I am using DBeaver as front-end. But, I get the same result with cli however I sign on:

sudo mysql -u root -p sudo mysql -u joe -p mariadb -u joe -p etc.

with or without sudo...

Then, without a path, the sql run puts the file into /var/lib/mysql/referals/ (referals is the name of my db).

select * from referalList
INTO OUTFILE 'testOutFileReferalList.csv';

It is created with permissions o mysql:mysql I have to change the permissions on every SQL result I export. How can I get permissions to be me, i.e. joe:joe? And how can I export files to anywhere I like, such as my home directory ~ ?

Also, any way to get mariadb to overwrite a file if it already exists?

mariadb version is Server version: 10.6.5-MariaDB-1:10.6.5+maria~focal mariadb.org binary distribution I am on Linux Ubuntu 20.04.4

Thank you,

Solution

  • SELECT INTO outfile is usually used to output a table on the server to a text file. This is done directly on the server, which is why the user under whom the server is running must have appropriate access rights to the file.

    The easiest way to output a table on the client side in a text file is to use the command line client:

    $> mysql -ujoe -pjoe yourdb -e "SELECT * FROM referalList" > /home/joe/testOutFileReferalList.csv