Archiving Logs?

[SurgeProto]

I'm running into a problem on a server I work on for a client where the log files are getting very large very quick. We're working on what may be causing this, but in the mean time, is there any way to auto-archive the logs so they don't get ridiculously large, or archive them when they reach a certain size automatically?

[Krik]

You didn't really specify how the log was being created. And posting the question in the "Database" forums does serve to confuse us on how the logs are being stored.

If the file is being created by code in the website use [man]filesize[/man] to check the the files size each time a new log is added to the file and once it reaches the limit the code can archive the file and start a new log file.

Now if the error logging is configured by php.ini, as is often the case, you can turn it off and use the Error Handling functions to do custom logging of errors.

Another option is to use Cron Jobs, these are tasks that can be run at regular intervals. So use them to check the logs file size and then archive it as needed.

If you are logging errors in the database and need to archive logs that is as simple as copying the old log records to another table and then deleting the logs from the first table.

[SurgeProto]

I apologize if I was unclear. I'm referring to the logs for MySQL, specifically the slow queries log. I can disable this, but I'd prefer to keep a running list and just archive it at certain points, say a certain size. I was not sure if there was some type of automatic setting for this or if I would have to write a script to handle it.

[Krik]

I have logged various user activity in MySQL before but I usually set it up to delete the logs after a period of time. I have not actually had to go about archiving. In my initial post I was thinking if it were myself I would just move the records when they got to a certain number of days old.

But it sounds like you need something a bit more advanced.

You may want to read up on MySQL Storage Engines. Most people usually recognize the MyISAM storage engine (commonly the default for MySQL databases) but there are several other kinds of storage engines including an ARCHIVE storage engine.

The trick is the automatic part. As it sounds like there would be a large number of records that would have to be moved to an archived table I would think using a Cron Job would be best as it wouldn't interfere with the users experience. You would set the Cron Job to run a script that would at certain times check the database and if certain criteria were met that script would then move the older records.

There are many good turtorial on Cron Jobs a search should turn up quite a few.

[bradgrafelman]

See this page in the MySQL manual, where a "mysql-log-rotate" script is discussed.

Even if that doesn't apply, it shouldn't be too hard to write your own script (some shell-language or even a PHP script) to rotate the log files manually and setup that script to be automatically executed every day at midnight (for example).