0

First off, I'm not sure if this should go to Server Fault or Database Administrators. It could be a Windows Server 2008R2 problem. But I am asking here first in the chance that someone else running MySQL on Windows might have had the same problem.

We are using MySQL Community Version 5.5.34 with the general log turned on. On Windows, there was no built-in log rotation script, so I wrote one, following the documentation on the MySQL website. The information there says to rename the log file, then flush. This is supposedly because it's quicker (almost instant) to rename the file than it is to make a copy of it in Windows. Then presumably the log can be flushed right afterward (instead of waiting some arbitrary length of time for the log file to be copied). The script renames the log to (date-time-stamp).log, then flushes the log using mysqladmin. It then compresses the log as a zip file and moves it to an archive folder.

Anyway, I created a Windows Scheduled Task to run the script. Everything works when I run the script either directly or through the Scheduled Task manager. But when I leave it to run on its schedule, the log is not rotated properly. It looks like the log is flushed, because I see the new log. But the renamed file is not there. Our log does get fairly large, the first day it got to about 400 MB. When I ran tests on the script, the log was smaller (10 MB, etc). I'm wondering if having a large quickly updating log might be too much for Windows/MySQL to handle. Is there any other recommended way to rotate logs in Windows?

I will try running the rotation script without the zip compression and moving the file to the archive folder to see if that affects anything. But like I said, everything works when I test the script both from the command line or through the Scheduled Task Manager (so presumably permissions and working directory and stuff like that should be ok). The only difference I can think of is the size of the log file when the script is run.

Any ideas?

  • I found out the zip compression script I was using doesn't seem to work in certain cases. It errors out, destroying the file in the process. I'll have to look for something better. – zako42 Apr 28 '14 at 23:25
2

First of all, I'd like to commend you for using the general log. If this were Linux, log rotation could all be done with shell scripting and without manipulating mysql in any way. In your case, it is Windows.

Weel, I have some good news and bad news for you, in that order.

GOOD NEWS

You can enable the general log to record events in a table rather than a text file.

On Feb 11, 2012, I wrote a post on how to make the general log a MyISAM table, how to index it, how to enable it, and how to rotate it : MySQL general log. Don't worry, this works for Linux and Windows.

The beauty of having the general log as a table is the fact that you can query it for specific text and specific datetime.

BAD NEWS

You cannot take the text file version of the general log and convert. you will have to stop mysql with net stop mysql. You can copy the text file or delete it.

GIVE IT A TRY !!!

CAVEAT : It can be a headache to work with the general log as a text file in Windows since Windows places a a strict lock of the table so that you cannot truncate it with MySQL running as you could in Linux.

  • thank you for the good information, good to know! I will look into this in the future. – zako42 Apr 28 '14 at 23:23
  • GIVE IT A TRY!!!! :) – Philᵀᴹ Apr 29 '14 at 0:04
  • Windows does not place strict locks on anything by itself. Whether you can open a file for reading, writing and move or delete, is determined by file sharing mode of a process(es) that hold that file open. There is a flag in CreateFile for that. MySQL opens logs so they can be shared with any other process IIRC. – Vladislav Vaintroub Nov 6 '16 at 21:24
0

From OS point of view, renaming a file is cheap, and does not depend at all on the size of the file. Your zip script seems to have a problem though, as you wrote in a comment.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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