0

I want to daily rotate MySQL log files on Windows. I want to keep the files, I don't want to use tables instead.

There are 3 log files: general.log slow-queries.log and error.log

I can do it easily with a scheduled task. I saw that it recommended stopping the logs before I copy them to the backup directory.

  • One problem is that 'error.log' cannot be stopped.
  • Another problem is that stopping the logs may cause some loss of events, and I want to avoid it.

Is there a safer way (or built-in way) for logs rotation? (on a daily base or according to the file size)?

3
  • You have no enough disk space for them? If yes, increase disk space. If no then copy current log files as common text files, store somewhere their sizes, and cut the part saved in previous backup (if you want, of course - but common compression ratio for them makes this process needless, I think) based on previous sizes info. Also save these files by the same way during server restart, and remove them saving prevous sizes as zeroes before daemon starting. Logging may be not paused at all.
    – Akina
    Jan 16, 2020 at 17:24
  • Check this out - a search reveals that it appears to work for PostgreSQL - YMMV.
    – Vérace
    Jan 16, 2020 at 18:56
  • Please SSH login as root and post results of A) SELECT @@version; and B) your OS including version. We will try to find you MySQL documentation for log rotation strategies. You must keep in mind, never purging logs will result in a full device someday. Jan 18, 2020 at 21:17

1 Answer 1

1

FLUSH LOGS gracefully does most of what you want. It stops writing to a log, renames the file and starts a new one. After that, you can deal with copying/moving/archiving/etc the files.

FLUSH LOGS can be run at any time without damaging anything or losing any log entries.

Caveat: There are many different log files; FLUSH may not work the same way on each, especially in older versions of MySQL.

For those three logs, in most systems, the general log is the biggest, followed by slow, then error. FLUSH also rotates the "binlog", but, again, that is "harmless".

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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