0

I've created my first EVENT:

ALTER EVENT flush_logs_event 
ON SCHEDULE EVERY 6 HOUR 
COMMENT 'Rotate /var/log/mysql/master-bin*' 
DO FLUSH LOGS;

I changed it from "12" to "6" because of how fast the log is growing for replication. I want it rotating by time and not file size to make deploying additional slaves easier for me.

I thought "6 Hour" means 6H00 12H00 18H00 and 24H00. At the same time I altered the event I also made a tweak to /etc/mysql/my.cnf:

expire_logs_days=7

And then I did

systemctl restart mariadb.service

The master-bin rotated when MariaDB re-stared. It created master-bin.000006. When I restarted MariaDB it was ~5:21. Does this mean the master-bin will rotate at 11:21, 17:21, etc.?

For full disclosure I am also including:

MariaDB [mysql]> show events \G
*************************** 1. row ***************************
                  Db: mysql
                Name: flush_logs_event
             Definer: root@localhost
           Time zone: SYSTEM
                Type: RECURRING
          Execute at: NULL
      Interval value: 6
      Interval field: HOUR
              Starts: 2021-12-09 05:21:23
                Ends: NULL
              Status: ENABLED
          Originator: 1
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
  Database Collation: utf8mb4_general_ci

Thanks.

1 Answer 1

2

I thought "6 Hour" means 6H00 12H00 18H00 and 24H00.

No. This means "6 hours from previous start". You have no specidfied any timepoint as a base - and server uses previous event starting time for this purposes.

If you want the event to be fired at listed hours you must specify STARTS schedule option - in this case the specified value will be used as a base. Like

ALTER EVENT flush_logs_event 
ON SCHEDULE EVERY 6 HOUR 
            STARTS '2021-12-09 00:00:00'
COMMENT 'Rotate /var/log/mysql/master-bin*' 
DO FLUSH LOGS;
1
  • For future reference: It is also worth nothing I've had to edit /etc/mysql/my.cnf . I needed to add " event_scheduler = ON " to [mariadb] . It is also noteworthy that the command " SHOW PROCESSLIST; " will confirm last time events have run. Dec 9, 2021 at 15:43

Your Answer

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

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