How to make a rotation log using MySQL?

Question

MySQL

Change has a fairly large application that works with the database. At the same time, I log some of the events in the database. But the log is growing so fast that there is an urgent need to somehow clean it. Of course, you can add cleaning on the application side, but I really want to transfer this task to the DBMS itself.
Is it possible to somehow implement one of the scenarios using MySQL?:
1) The log should not exceed, for example, 2 GB. Where log is a single table that does not contain binary data. When the limit of this value is reached, the oldest ones (by date or primary integer key) are removed and new ones are added.
2) The log (I repeat, 1 table) should not contain entries that are older than 2 months. In this case, the field containing the date and time of the creation of the record is present.
Of course, I want to do without “scheduled tasks” because. not all mysql servers have been updated to the required version (if memory serves, >=5.1.26). But if you can't do without it - well, okay.

Answer 1

If you want to use only the database, then you can make a trigger.
Will delete all records from the table older than 2 months.

delimiter //
CREATE TRIGGER ins_logtable AFTER INSERT ON logtable
FOR EACH ROW
BEGIN
    SELECT NOW() - interval 2 month INTO @rotate_date;
    DELETE FROM logtable WHERE added_date < @rotate_date;
END;//
delimiter ;

This trigger will execute on every INSERT into the table and delete everything from the table older than 2 months.
Although I would still make a script run by cron, because purge through a trigger can affect performance. :(

Answer 2

usually a script is hung up in cron that does such things. If it is not possible to hang a task in cron, then you can make a trigger as mentioned above, but additionally do a simple filtering using a global variable in which to store, for example, the time of the last garbage collection.
get a user in mysql who has a minimum of rights to this table and make a function that does what you need. call it from the script
mysql -u user -ppass -e 'select the_function();'
2. with the date is simple:
delete from log_table where created<date_sub(now(), interval 2 month);
1. with the size a little more complicated - in information_schema.tables there is data_length in bytes - you can take the difference between 2 * 1024 * 1024 and data_length and divide by data_length / table_rows - we get how many rows, approximately, need to be removed. something like
select if(data_length>2*1024*1024,floor((data_length-2*1024*1024)/(data_length/table_rows)),0) from information_schema.tables where table_schema='db_name' and table_name='log_table' into @row_cnt; delete from log_table order by created limit @row_cnt;
this, of course, you need to create an index on created - a field where the date of creation

Answer 3

The question, of course, is interesting, but why not use the classic logrotate?

Answer 4

MySQL does not have this functionality built in.
Theoretically, you can hang a trigger that, on insertion, will check the size of the database through the information_schema table and delete records from there. But there are several disadvantages
1) When using innodb, you will not be able to get the exact size of the table
2) The next insert can cause the deletion of old data, and if your log is not written asynchronously, it will cause a noticeable slowdown in other operations.
A more correct option is to make separate tables by year + month with engine=Archive and drop the old ones by crown once a day. Check the total size of the archive tables and if anything - bang the oldest of them

Answer 5

1) Don't need crowns. There are events. dev.mysql.com/doc/refman/5.1/en/events.html
2) Table partitioning (eg by days). And then cut partitions. It will be much faster than delete (because no transactional mechanism is used).

Answer 6

we did this: the function of saving a record in the database calculates the name of the table by today's date, for example, the_log_2012_09, then it checks the existence of the table in the database and in case of failure the table is created, then a record is added to the table. Old tables can be moved/deleted at your leisure and all that.