According to MySQL documentation ( http://dev.mysql.com/doc/refman/5.5/en/server-signal-response.html http://dev.mysql.com/doc/refman/5.5/en/flush.html ), when MySQL receives the HUP signal, it performs a FLUSH TABLES; command. FLUSH LOGS; should be enough for logrotating, instead of the HUP signal. The problem is explained in detail at the bug URL. Reproducible: Always Steps to Reproduce: See bug URL. Actual Results: When logrotate sends HUP signal to MySQL, long queries can exit with "MySQL server has gone away" message. Expected Results: Logs being rotated with FLUSH LOGS, and long queries can continue without problems.
How would you suggest we go about this another way? The only way I can think of is to use 'mysqladmin flush-logs'. This would require giving the logrotate cron job access to your database. To do that, means teaching admins to create a /root/.my.cnf file for each install with a user and password that has the RELOAD permissions and potentially more. This could be an even bigger security risk.
Well, three things came to my mind: Suggestion A) Modify the e-build which warns the user about this problem, but does nothig else. At least a heads up, when she wants to do long queries when the logrotate happens. We spent two years (!) to debug this problem. Suggestion B) implement /etc/init.d/mysql reload in way, that it checks, if /root/.my.cnf (or something else) exists or not. If not, send HUP signal to the mysql process. If it does exist, check the permissions of the file, it must be owned by root, and have rights of 600. Then invoke mysqladmin flush-logs. Still notify the upgrading user about this option, and the risks with .my.cnf . This would be totally backwards compatible, but the ebuild would give the user the option to decide if she want's a stable SQL connection or opt-out from storing a root (or user with reload right) plantext password. Suggestion C) Patch MySQL with not-yet-in-use signal (say, USR1), which only does the FLUSH LOGS operation.
Suggestion D) Forget file based logging, and use syslog instead. This could be controlled with a syslog USE flag, and warn the user about the problem when she installs it without it.
I like option B, but request that it be implemented like debian's mysql, which has /etc/mysql/debian.cnf as root:root/600, and creates a user debian-sys-maint, that uses the socket and has full perms via localhost only. Option D is nice, but problematic for things like the slow query log.
I recently ran into this issue. The logrotate job sent mysqld a HUP at 3am while a long running export query was in progress. The effect was not that the long running query was killed, rather all insert queries were blocked until the long running query finished. Unless you have something in place to kill long running queries I think the server stall is the expected behavior when this happens. I also like Debian's solution using a dedicated localhost user for maintenance tasks.
the HUP signal may also cause table corruption and doesn't really reopen file descriptors to the new log file as lsof tells me mysqld is still writing to deleted files. my workaround was adding copytruncate to logrotate's mysql file and removing the postrotate section completely (yes i know i can lose some log entries, but i'm more concerned about database integrity).
I am currently testing another solution. Both mysql-5.6 and mariadb-10.0 include an unix socket authentication plugin. This allows someone as that user on the system to connect to mysql without a password and perform what ever actions they have access to (RELOAD permission only in this case). with MariaDB, the setup is: INSTALL SONAME 'auth_socket.so'; CREATE USER mysql IDENTIFIED VIA unix_socket; GRANT RELOAD on *.* TO 'mysql'; So dropping to the unprivileged mysql user and running 'mysqladmin flush-logs' should work without a password. That's the theory. Still need to work it out.
(Adding perfinion by irc request) Ok.. preliminary tests show this is possible.. On MariaDB: INSTALL SONAME 'auth_socket.so'; CREATE USER mysql@localhost IDENTIFIED VIA unix_socket; GRANT RELOAD on *.* TO 'mysql'@'localhost'; On MySQL: INSTALL PLUGIN auth_socket SONAME 'auth_socket.so'; CREATE USER mysql@localhost IDENTIFIED WITH auth_socket; GRANT RELOAD on *.* TO 'mysql'@'localhost'; Then the logrotate script becomes [ -f /var/run/mysqld/mysqld.pid ] && /bin/su -c '/usr/bin/mysqladmin flush-logs -u mysql' -s /bin/sh mysql comments?
