Arch Linux

mikael

Member

From: Slovakia

Registered: 2011-05-22

Posts: 30

[SOLVED] Mysql query log is empty after boot

Hi,
I can't figure out why is my mysql general and slow query log empty after computer reboot. After restarting the server with systemctl the logs get populated.
After reboot the files are created just empty.

$ stat /var/log/mysql/query.log
  File: '/var/log/mysql/query.log'
  Size: 0               Blocks: 0          IO Block: 4096   regular empty file
Device: 801h/2049d      Inode: 6947352     Links: 1
Access: (0660/-rw-rw----)  Uid: (   89/   mysql)   Gid: (   89/   mysql)
Access: 2016-04-28 05:04:04.530835820 +0200
Modify: 2016-04-28 05:04:04.530835820 +0200
Change: 2016-04-28 05:04:04.530835820 +0200
 Birth: -
$ stat /var/log/mysql/slow.log
  File: '/var/log/mysql/slow.log'
  Size: 0               Blocks: 0          IO Block: 4096   regular empty file
Device: 801h/2049d      Inode: 6947484     Links: 1
Access: (0660/-rw-rw----)  Uid: (   89/   mysql)   Gid: (   89/   mysql)
Access: 2016-04-28 05:04:04.530835820 +0200
Modify: 2016-04-28 05:04:04.530835820 +0200
Change: 2016-04-28 05:04:04.530835820 +0200
 Birth: -

my.cnf settings:

# The MariaDB server
[mysqld]
port	= 3306
socket = /run/mysqld/mysqld.sock
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
character-set-server = utf8

general_log = 1 # log every query
general_log_file = /var/log/mysql/query.log

slow_query_log = 1
long_query_time = 1 # second
slow_query_log_file = /var/log/mysql/slow.log
log_queries_not_using_indexes = 0

# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin

# binary logging format - mixed recommended
binlog_format=mixed

Using the default systemd unit file:

$ cat /etc/systemd/system/multi-user.target.wants/mysqld.service
[Unit]
Description=MariaDB database server
After=syslog.target

[Service]
User=mysql
Group=mysql

ExecStart=/usr/bin/mysqld --pid-file=/run/mysqld/mysqld.pid 
ExecStartPost=/usr/bin/mysqld-post

Restart=always
PrivateTmp=true

[Install]
WantedBy=multi-user.target

After service restart the logs get populated:

$ sudo systemctl restart mysqld.service 
$ stat /var/log/mysql/query.log
  File: '/var/log/mysql/query.log'
  Size: 255             Blocks: 8          IO Block: 4096   regular file
Device: 801h/2049d      Inode: 6947352     Links: 1
Access: (0660/-rw-rw----)  Uid: (   89/   mysql)   Gid: (   89/   mysql)
Access: 2016-04-28 05:04:04.530835820 +0200
Modify: 2016-04-28 06:50:52.534122763 +0200
Change: 2016-04-28 06:50:52.534122763 +0200
 Birth: -
$ stat /var/log/mysql/slow.log
  File: '/var/log/mysql/slow.log'
  Size: 171             Blocks: 8          IO Block: 4096   regular file
Device: 801h/2049d      Inode: 6947484     Links: 1
Access: (0660/-rw-rw----)  Uid: (   89/   mysql)   Gid: (   89/   mysql)
Access: 2016-04-28 05:04:04.530835820 +0200
Modify: 2016-04-28 06:50:51.854122769 +0200
Change: 2016-04-28 06:50:51.854122769 +0200
 Birth: -

Last edited by mikael (2016-07-11 03:34:25)

mikael

Member

From: Slovakia

Registered: 2011-05-22

Posts: 30

Re: [SOLVED] Mysql query log is empty after boot

Maybe it's an issue with logrotate that runs after mysql

$ systemd-analyze blame
          4.150s logrotate.service
          3.875s updatedb.service
          3.552s netctl@ethernet\x2dstatic.service
          1.065s mysqld.service

mikael

Member

From: Slovakia

Registered: 2011-05-22

Posts: 30

Re: [SOLVED] Mysql query log is empty after boot

The solution was changing the postrotate command in /etc/logrotate.d/mysql from

/usr/bin/systemctl reload mysqld.service 2>/dev/null

to

/usr/bin/systemctl restart mysqld.service 2>/dev/null