0

For example, I have a table similar to this:

deviceid | servertime
1          2019-05-13 12:00:00
1          2019-05-14 12:00:00
2          2019-05-13 13:00:00
2          2019-05-12 13:00:00
2          2019-05-11 13:00:00
2          2019-05-10 13:00:00
3          2019-05-13 10:00:00
4          2019-04-16 15:00:00
...

I need a script that would run every day and delete all records belonging to deviceid "2" that is older than a year (from current date). In addition, it needs to delete records with other deviceids that are older than a month.

I would prefer to avoid cron jobs, because I'm concerned about security (I don't think storing MySQL password in a cron file is safe)

I'm a complete noob at SQL, I myself wouldn't know where to begin. I have phpMyAdmin if it's of relevance.

0

2 Answers 2

1

To delete records which have deviceid = 2 and are older than a year from the current date use

DELETE 
FROM table 
WHERE deviceid = 2 
  AND servertime < CURRENT_DATE - INTERVAL 1 YEAR; -- or, maybe, <=.

To delete records which are older than a year from the current date except those which have deviceid = 2 use

DELETE 
FROM table 
WHERE deviceid != 2 -- or deviceid <> 2
  AND servertime < CURRENT_DATE - INTERVAL 1 YEAR;

To perform this action daily use MySQL's Event Scheduler. Create a schedule which is executed every day

CREATE EVENT delete_old_records
ON SCHEDULE EVERY 1 DAY          -- execute daily
            STARTS CURRENT_DATE  -- at 00:00
DO
    DELETE 
    FROM table 
    WHERE deviceid != 2
      AND servertime < CURRENT_DATE - INTERVAL 1 YEAR;

And do not forget to enable event scheduler!!! Once (until disable or server restart) - by the query

SET GLOBAL event_scheduler = ON;

Permanently - by adding --event-scheduler=ENABLED to the service command-line or event-scheduler=ENABLED to the server configuration file (preferred).

0

... I don't think storing MySQL password in a cron file is safe ...

Absolutely right; it's not.

But you don't have to - you can (and should) store the password is a suitably protected file: https://dev.mysql.com/doc/refman/8.0/en/password-security-user.html

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.