how to remove 6 months old data automatically from MYSQL innodb table

I have a very big table in which I am fetching data everyday and it's size is getting bigger everyday but for my software, only 6 months data is useful. So, I am planning to remove data which is older than 6 months. I know I can do it using CRON jobs but I want to use MYSQL way to delete older data automatically. I read about PARTITION also and My question is Can I do it using PARTITION or I will have to find some alternative way ?

The Event Scheduler allows to execute regular events according to a schedule. There is detailed example in my post on the Stack Overflow, you just need to change time interval value from 24 hours to 6 months.

Firstly, make sure the Event Scheduler is enabled. To enable it use

SET GLOBAL event_scheduler = ON;

After that you could crate event that will check rows creation time and delete old records. For example

CREATE EVENT cleaning ON SCHEDULE EVERY 1 MONTH ENABLE
  DO 
  DELETE FROM MyTable
  WHERE `timestamp_column` < CURRENT_TIMESTAMP - INTERVAL 6 MONTH;

If there is no column with timestamp of a row creation in your table, then you can create trigger that will insert current timestamp and inserted row identificator to auxiliary table.

CREATE TRIGGER logCreator AFTER INSERT ON MainTable
  FOR EACH ROW 
  INSERT INTO LogTable (MainID, Created) 
  VALUES(NEW.id, CURRENT_TIMESTAMP);

Then you can use this log to get keys of main table rows that was created before specific time and delete corresponding records.

delimiter |
CREATE EVENT cleaning ON SCHEDULE EVERY 1 MONTH ENABLE
  DO
  BEGIN
    DECLARE MaxTime TIMESTAMP;
    SET MaxTime = CURRENT_TIMESTAMP - INTERVAL 6 MONTH;
    DELETE FROM MainTable
    WHERE id IN (SELECT MainID FROM LogTable WHERE Created < MaxTime);
    DELETE FROM LogTable
    WHERE LogTable.Created < MaxTime;
  END |
delimiter ;

StackExchange Database Administrators Q#148308, answer score: 9