Solution Based On; Ubuntu 20.10 with Apparmor active and MySql 8
Solution Summary and Lesson Learned:
Unless you have a damn good reason not to
- Leave the default secure-file-priv settings alone,
- Give your user(s) FILE privileges in mysql,
- And tell apparmor to allow writing to the default directory set up by the mysql installer.
The default configuration set secure-files-priv to /var/lib/mysql-files. Left as it was, I was able to import files by placing them in that directory so long as I gave the user file privileges like so, GRANT FILE ON *.* TO myuser@host;
However, when I tried to write to that directory I got the dreaded Error Code: 1. Can't create/write to file '/var/lib/mysql-files/select_out.csv' (OS errno 13 - Permission denied). I could have solved this very easily by editing /etc/apparmor.d/usr.sbin.mysqld and changing
# Allow data files dir access
/var/lib/mysql-files/ r,
/var/lib/mysql-files/** rwk,
to
# Allow data files dir access
/var/lib/mysql-files/ rw,
/var/lib/mysql-files/** rwk,
But I didn't know about apparmor at the time, and so I fumbled around screwing up more stuff until I found @spcurtis81's post. If that's what happened to you, read on, maybe you can learn something from my journey.
Unfortunately at some point - and god only knows why - I decided to move the secure-file-priv directory to /mysql-files. This created a lot more work for me. If nothing else it taught me a lot, and gave me the opportunity to share.
I had to change the 'secure-files-priv' setting under [mysqld] section in one of .cnf files, but which, there are so damn many. I found it by doing this,
cd /
find . -name "*.cnf" -printf '%p\n' -exec grep "\[mysqld\]" {} \;
and getting
./conf.d/mysqldump.cnf
./conf.d/mysql.cnf
./my.cnf
./debian.cnf
./mysql.conf.d/mysqld.cnf
[mysqld]
./mysql.conf.d/mysql.cnf
./mysql.cnf
so the file above the [mysqld] was the one for me. I found several references to modifying my.cnf but as it didn't already have the section in it, I went with mysqld.cnf. I also reckoned that the mysqld.cnf was for the server daemon as opposed to the user program. It worked, so it aint stupid, but perhaps a more knowledgeable person could explain the hierarchy of .cnf files.
There was no existing secure-files-priv entry for this setting so I found what seemed to be a reasonable location for it and added the following.
# Added 12/13/2020 in an attempt to relocate the secure-file-priv directory
secure-file-priv = /mysql-files
No joy. I found another reference that said that you had to specify a temp directory and so I uncommented this line from the same file.
# If MySQL is running as a replication slave, this should be
# changed. Ref https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_tmpdir
tmpdir = /tmp
Still no joy. Then I found @spcurtis81's post here and learned about the apparmor settings. So in /etc/apparmor.d/usr.sbin.mysqld I changed
# Allow data files dir access
/var/lib/mysql-files/ r,
/var/lib/mysql-files/** rwk,
to
# Allow data files dir access
/mysql-files/ rw,
/mysql-files/** rwk,
AND still no f-ing joy!
The final step that resolved the issue: chmod -R 700 /mysql-files
When I had created the directory I set the owner to mysql and gave it 644 permissions. That seemed reasonable but added an error to my mysql error log: 'Insecure configuration for --secure-file-priv: Location is accessible to all OS users. Consider choosing a different directory.'
I don't like security warnings so I changed the permission to 600, never thinking mysql would need executable rights on a data directory. I was wrong. So, there you go.
