Change MySQL 5.7 Error Log Location on Ubuntu 16.04 – How-To Guide

16.04MySQL

Whenever I change the log location

form: /var/log/mysql to: /var/www

in order to keep every log files in the same place (just under public folder) in mysqld.cnf mysql server cannot restart.

/etc/mysql/mysql.conf.d/mysqld.cnf

log_error = /var/log/mysql/mysql_errors.log

I checked for some solutions and added:

character-set-server = utf8

collation-server = utf8_general_ci

But still didn't work.
/var/www has 770 permissions and belonging to www-data group.

I added mysql to www-data group:

sudo usermod -aG www-data mysql

But still didn't work.

MySQL: 5.7.19

Ubuntu: 16.04.1

Here is the error log:

Job for mysql.service failed because the control process exited with error code. See "systemctl status mysql.service" and "journalctl -xe" for details.

mysql.service: Main process exited, code=exited, status=1/FAILURE

Whenever I undo the log location change, it starts to work.

EDIT 31.07.2017

I run this after log location change:

sudo touch /var/www/mysql_errors.log
sudo chown mysql:mysql /var/www/mysql_errors.log
sudo chmod 770 /var/www/mysql_errors.log

sudo systemctl restart mysql

sudo journalctl -xe > ~/journal.txt

Here are the relevant log entities:

-- Unit mysql.service has begun starting up.
Jul 31 00:33:55 mydomain.com audit[1738]: AVC apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/proc/1738/status" pid=1738 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=112 ouid=112

Jul 31 00:33:55 mydomain.com audit[1738]: AVC apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/var/www/mysql_errors.log" pid=1738 comm="mysqld" requested_mask="ac" denied_mask="ac" fsuid=112 ouid=112
Jul 31 00:33:55 mydomain.com audit[1738]: AVC apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" 

Jul 31 00:33:55 mydomain.com systemd[1]: mysql.service: Main process exited, code=exited, status=1/FAILURE

Best Answer

Now based on the link you provided and another resource about the relationship between AppArmor and Mysql I was able to get it to work. The policy that governs mysql is loaded from this location /usr/sbin/mysqld which you can see when you run: sudo aa-status. This can be altered from,:

/etc/apparmor.d/usr.sbin.mysqld

and 

/etc/apparmor.d/local/usr.sbin.mysqld

I will be altering the latter.

Steps:

  1. I have created a folder /var/www/html/mysql and with placed a file (error.log) in there. I believe mysql will look for such a file. Now this is my custom log location.

  2. Open the /etc/appparmor.d/local/usr.sbin.mysqld and add the following lines:

    /var/www/html/mysql/ r,
/var/www/html/mysql/** rwk,
    • Interpretation:
      • /var/www/html/mysql/ r,:--> give mysql read access to that folder.
      • /var/www/html/mysql/** rwk,:--> give mysql read, write and lock access to contents in there.

  3. Open /etc/mysql/mysql.conf.d/mysqld.cnf and change the log location to:

    #log_error = /var/log/mysql/error.log
log_error = /var/www/html/mysql/error.log

  4. Now restart apparmor: sudo systemctl restart apparmor

  5. Now restart mysql: sudo systemctl restart mysql

Note:

  1. I set the user:group of /var/www/html to $USER:www-data and the permissions to:
    • user: rwx,
    • group: rwx, and
    • others: r-x

  2. Added mysql user to www-data group:

    sudo usermod -a -G www-data mysql

Theory:

Apparmor is Ubuntu's way of making sure there is fine grained control over who accesses the file system and how it is done. It has two modes, enforce and complain modes. The enforce mode is where mysql falls under ( to see these run sudo aa-status).

You can either change mysql to complain mode where apparmor will only complain but not prevent mysql accessing other areas of the file system. In this case I choose the enforce mode and simply altered the apparmor policy for mysql To alter these apparmor modes you have to install apparmor-utils and then you could just do sudo aa-complain /usr/sbin/mysqld and apparmor will now only complain about filesystem violations by mysql.

Two locations are used to alter the policy, these are (1) /etc/apparmor.d/usr.sbin.mysqld, and (2)/etc/appamrmor.d/local/usr/sbin/mysqld. Which chosen will depend on the type of change desired (network wide or machine specific). So alter to suite your plans.

Resource(please look at these):

https://blogs.oracle.com/jsmyth/apparmor-and-mysql

https://blogs.oracle.com/jsmyth/selinux-and-mysql

Related Question