MySQL fails to start after editing the.cnf

errorsmy.cnfmysqld

I am trying to optimize memory usage (eating up more than 500MB by default) of MySQL using my.cnf, but I am having some trouble. The service is not starting after placing my custom my.cnf. It goes back to working state if I fallback to default my.cnf.

MySQL version:

mysql Ver 14.14 Distrib 5.7.19, for Linux (x86_64) using EditLine
wrapper

OS Version:

DISTRIB_ID=Ubuntu DISTRIB_RELEASE=17.04 DISTRIB_CODENAME=zesty
DISTRIB_DESCRIPTION="Ubuntu 17.04"

Linux 4.10.0-32-generic #36-Ubuntu SMP Tue Aug 8 12:10:06
UTC 2017 x86_64 x86_64 x86_64 GNU/Linux

Here is my /etc/mysql/my.cnf ( generated using myconfgen with memory size parameter 0.25):

[mysql]
# CLIENT #
port                           = 3306
socket                         = /var/lib/mysql/mysql.sock

[mysqld]
# GENERAL #
user                           = mysql
default-storage-engine         = InnoDB
socket                         = /var/lib/mysql/mysql.sock
pid-file                       = /var/run/mysqld/mysqld.pid

# MyISAM #
# key-buffer-size                = 32M
# myisam-recover                 = FORCE,BACKUP

# SAFETY #
max-allowed-packet             = 16M
max-connect-errors             = 1000000
skip-name-resolve
sql-mode                       = NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER
sysdate-is-now                 = 1
innodb-strict-mode             = 1

# DATA STORAGE #
datadir                        = /var/lib/mysql

# SERVER ID #
server-id                      = 968898

# BINARY LOGGING #
log-bin

# CACHES AND LIMITS #
max-connections                = 500
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 1024
table-open-cache               = 2048

# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 768M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 192M

# LOGGING #
log-error                      = /var/log/mysqld.log
slow-query-log                 = 1
slow-query-log-file            = /var/log/mysqld-slow.log
log-queries-not-using-indexes  = OFF
long_query_time                = 30

[mysqldump]
max-allowed-packet             = 16M

When I try to start MySQL, this is what I get:

● mysql.service - MySQL Community Server
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
   Active: activating (start-post) (Result: exit-code) since Mon 2017-08-21 12:09:51 UTC; 1s ago
  Process: 14659 ExecStart=/usr/sbin/mysqld (code=exited, status=1/FAILURE)
  Process: 14643 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
 Main PID: 14659 (code=exited, status=1/FAILURE); Control PID: 14660 (mysql-systemd-s)
    Tasks: 2 (limit: 4915)
   Memory: 760.0K
      CPU: 223ms
   CGroup: /system.slice/mysql.service
           └─control
             ├─14660 /bin/bash /usr/share/mysql/mysql-systemd-start post
             └─14669 sleep 1

There is nothing in /var/log/mysql.log. I don't know where else to look for error log. Can you please help?

Edit: I ran journalcl and found more error logs: 

    -- Unit mysql.service has begun starting up.
Aug 21 13:04:46 www.####.com kernel: audit: type=1400 audit(1503320686.962:1051): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/proc/18169/status" pid=18169 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=112 ouid=112
Aug 21 13:04:46 www.####.com audit[18169]: AVC apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/proc/18169/status" pid=18169 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=112 ouid=112
Aug 21 13:04:46 www.####.com audit[18169]: AVC apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/sys/devices/system/node/" pid=18169 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=112 ouid=0
Aug 21 13:04:46 www.####.com kernel: audit: type=1400 audit(1503320686.974:1052): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/sys/devices/system/node/" pid=18169 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=112 ouid=0
Aug 21 13:04:46 www.####.com audit[18169]: AVC apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/proc/18169/status" pid=18169 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=112 ouid=112
Aug 21 13:04:46 www.####.com kernel: audit: type=1400 audit(1503320686.978:1053): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/proc/18169/status" pid=18169 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=112 ouid=112
Aug 21 13:04:47 www.####.com audit[18169]: AVC apparmor="DENIED" operation="mknod" profile="/usr/sbin/mysqld" name="/var/log/mysqld.log" pid=18169 comm="mysqld" requested_mask="c" denied_mask="c" fsuid=112 ouid=112
Aug 21 13:04:47 www.####.com audit[18169]: AVC apparmor="DENIED" operation="mknod" profile="/usr/sbin/mysqld" name="/var/log/mysqld.log" pid=18169 comm="mysqld" requested_mask="c" denied_mask="c" fsuid=112 ouid=112
Aug 21 13:04:47 www.####.com audit[18169]: AVC apparmor="DENIED" operation="mknod" profile="/usr/sbin/mysqld" name="/var/log/mysqld.log" pid=18169 comm="mysqld" requested_mask="c" denied_mask="c" fsuid=112 ouid=112
Aug 21 13:04:47 www.####.com audit[18169]: AVC apparmor="DENIED" operation="mknod" profile="/usr/sbin/mysqld" name="/var/log/mysqld.log" pid=18169 comm="mysqld" requested_mask="c" denied_mask="c" fsuid=112 ouid=112
Aug 21 13:04:47 www.####.com audit[18169]: AVC apparmor="DENIED" operation="mknod" profile="/usr/sbin/mysqld" name="/var/log/mysqld.log" pid=18169 comm="mysqld" requested_mask="c" denied_mask="c" fsuid=112 ouid=112
Aug 21 13:04:47 www.####.com audit[18169]: AVC apparmor="DENIED" operation="mknod" profile="/usr/sbin/mysqld" name="/var/log/mysqld.log" pid=18169 comm="mysqld" requested_mask="c" denied_mask="c" fsuid=112 ouid=112
Aug 21 13:04:47 www.####.com kernel: audit: type=1400 audit(1503320687.182:1054): apparmor="DENIED" operation="mknod" profile="/usr/sbin/mysqld" name="/var/log/mysqld.log" pid=18169 comm="mysqld" requested_mask="c" denied_mask="c" fsuid=112 ouid=112
Aug 21 13:04:47 www.####.com kernel: audit: type=1400 audit(1503320687.182:1055): apparmor="DENIED" operation="mknod" profile="/usr/sbin/mysqld" name="/var/log/mysqld.log" pid=18169 comm="mysqld" requested_mask="c" denied_mask="c" fsuid=112 ouid=112
Aug 21 13:04:47 www.####.com kernel: audit: type=1400 audit(1503320687.182:1056): apparmor="DENIED" operation="mknod" profile="/usr/sbin/mysqld" name="/var/log/mysqld.log" pid=18169 comm="mysqld" requested_mask="c" denied_mask="c" fsuid=112 ouid=112
Aug 21 13:04:47 www.####.com kernel: audit: type=1400 audit(1503320687.182:1057): apparmor="DENIED" operation="mknod" profile="/usr/sbin/mysqld" name="/var/log/mysqld.log" pid=18169 comm="mysqld" requested_mask="c" denied_mask="c" fsuid=112 ouid=112
Aug 21 13:04:47 www.####.com kernel: audit: type=1400 audit(1503320687.182:1058): apparmor="DENIED" operation="mknod" profile="/usr/sbin/mysqld" name="/var/log/mysqld.log" pid=18169 comm="mysqld" requested_mask="c" denied_mask="c" fsuid=112 ouid=112
Aug 21 13:04:47 www.####.com kernel: audit: type=1400 audit(1503320687.182:1059): apparmor="DENIED" operation="mknod" profile="/usr/sbin/mysqld" name="/var/log/mysqld.log" pid=18169 comm="mysqld" requested_mask="c" denied_mask="c" fsuid=112 ouid=112
Aug 21 13:04:47 www.####.com systemd[1]: mysql.service: Main process exited, code=exited, status=1/FAILUREl.service: Main process exited, code=exited, status=1/FAILURE

Best Answer

This is too big for a comment.

For comparison I'll paste the contents of my custom "my.cnf file". The command to generate this was grep -v "#" mysqld.cnf executed in /etc/mysql/mysql.conf.d

My file permissions are -rw-r--r-- 1 root root 3432 Sep 21 2016 mysqld.cnf

Please note that your posting shows

log-error = /var/log/mysqld.log

with a dash -

whereas mine is 

 log_error = /var/log/mysql/error.log

with an underscore. Don't know if this matters. The file is also empty.

I think your Apparmor configuration may prevent mysql to create files in /var/log. Try a subdirectory in /var/log, e.g. /var/log/mysql. Please check /etc/apparmor.d/usr.sbin.mysqld

Quick solution: switch off Apparmor temporarily with systemctl stop apparmor. Then start mysql. Stop mysql. Fix apparmor config. See below for mine.

(Also, my real value bind-address is actually different than displayed)

[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /data2/mysql/data

tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
bind-address        = 127.0.0.1  ### FAKE
key_buffer_size     = 16M
max_allowed_packet  = 16M
thread_stack        = 192K
thread_cache_size       = 8
myisam-recover-options  = BACKUP
query_cache_type = ON
query_cache_limit   = 1M
query_cache_size        = 16M
log_error = /var/log/mysql/error.log
expire_logs_days    = 10
max_binlog_size   = 100M

 # typo3 CMS  works better this way
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

My apparmor config file for my mysql Devserver:

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

# vim:syntax=apparmor
# Last Modified: Tue Feb 09 15:28:30 2016
#include <tunables/global>

/usr/sbin/mysqld {
  #include <abstractions/base>
  #include <abstractions/nameservice>
  #include <abstractions/user-tmp>
  #include <abstractions/mysql>
  #include <abstractions/winbind>

# Allow system resource access
  /sys/devices/system/cpu/ r,
  capability sys_resource,
  capability dac_override,
  capability setuid,
  capability setgid,

# Allow network access
  network tcp,

  /etc/hosts.allow r,
  /etc/hosts.deny r,

# Allow config access
  /etc/mysql/** r,

# Allow pid, socket, socket lock file access
  /var/run/mysqld/mysqld.pid rw,
  /var/run/mysqld/mysqld.sock rw,
  /var/run/mysqld/mysqld.sock.lock rw,
  /run/mysqld/mysqld.pid rw,
  /run/mysqld/mysqld.sock rw,
  /run/mysqld/mysqld.sock.lock rw,

# Allow execution of server binary
  /usr/sbin/mysqld mr,
  /usr/sbin/mysqld-debug mr,

# Allow plugin access
  /usr/lib/mysql/plugin/ r,
  /usr/lib/mysql/plugin/*.so* mr,

# Allow error msg and charset access
  /usr/share/mysql/ r,
  /usr/share/mysql/** r,

# Allow data dir access
  /var/lib/mysql/ r,
  /var/lib/mysql/** rwk,

# Allow data files dir access
  /var/lib/mysql-files/ r,
  /var/lib/mysql-files/** rwk,

# Allow keyring dir access
  /var/lib/mysql-keyring/ r,
  /var/lib/mysql-keyring/** rwk,

# Allow log file access
  /var/log/mysql.err rw,
  /var/log/mysql.log rw,
  /var/log/mysql/ r,
  /var/log/mysql/** rw,

  # Site-specific additions and overrides. See local/README for details.
  #include <local/usr.sbin.mysqld>
}
Related Question