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