MySQL fails to start after editing my.cnf

Question

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

I see you're on Linux - could you post the output of this command: sdiff mysql.cnf my.cnf.fallback | grep -v "#". This makes a side-by-side comparison of the config files (and does a quick and dirty attempt to remove different line comments) — knb, Aug 21, 2017 at 12:47
@knb The fallback file has only one line: !includedir /etc/mysql/conf.d/ — Spero, Aug 21, 2017 at 12:53
@knb I found some error logs using journalctl. Pls check my updated post. — Spero, Aug 21, 2017 at 13:01

knb · Accepted Answer · 2017-08-21 13:16:30Z

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>
}

Thx for sharing your config files. I replaced my.cnf and apparmor config with yours and restarted apparmor. Sadly. all it does is now mysqld crashes faster: Active: failed (Result: exit-code) — Spero, Aug 21, 2017 at 13:32
Also, about the hyphen vs underscore confusion, it seems the convention has changed: bugs.launchpad.net/ubuntu/+source/mysql-5.7/+bug/1610765/… — Spero, Aug 21, 2017 at 13:44
have you created a subdirectory /var/log/mysql? The dir permissions are drwxr-s--- 2 mysql adm 4096 Aug 21 07:35 on my devserver - seems mysql needs this even if the logfile is usually empty (0 bytes) — knb, Aug 21, 2017 at 13:48
also, I've edited my mysql.conf file (the real IP address is different), I've just replaced it with the 127.0 value instead — knb, Aug 21, 2017 at 13:50
Thank you! Finally I have some log files going. Checking the logs now. — Spero, Aug 21, 2017 at 13:55

Stack Exchange Network

MySQL fails to start after editing my.cnf

1 Answer 1

Your Answer

Not the answer you're looking for? Browse other questions tagged
errors
my.cnf
mysqld
or ask your own question.

Hot Network Questions

MySQL fails to start after editing my.cnf

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged errorsmy.cnfmysqld or ask your own question.

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
errors
my.cnf
mysqld
or ask your own question.