How to get something written to MySQL error log without restart
Bad queries and other invalid actions typically only write to the error log when there's not a client connection to write the error to... such as when a thread that isn't a client thread encounters an error... or from the event scheduler.
From within a scheduled event, SIGNAL
can be used to throw errors and warnings that will be written to MySQL's error log. And, of course, you can "schedule" an event to run only once, right now.
mysql> USE test;
Database changed
mysql> CREATE EVENT test_error_log ON SCHEDULE AT NOW() ON COMPLETION NOT PRESERVE
DO SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'hi from the error log';
Query OK, 0 rows affected (0.01 sec)
$ sudo tail /usr/local/mysql/data/hostname.err
--8<-- snip --8<--
150121 14:25:06 [ERROR] Event Scheduler: [sqlbot@%][test.test_error_log] hi from the error log
Alternately, there's a 3rd party user-defined function log_error()
that can write to the error log: https://github.com/mysqludf/lib_mysqludf_log
That's assuming you're not "really" wanting to trigger MySQL to politely close and reopen the log file... this is done with FLUSH ERROR LOGS;
.