MySQL Innodb Size Reduction by Partitioning

I've faced space limitation on hard dis, so finally found partitioning as a good option to manage old data for removing and freeing space (innodb doesn't free deleted data space back to OS). By partitioning you can discard a partition for example 1 month data and it release the used space by that partition file.

You can delete unused data from table before execute it, to free some space on just partitioning without truncating a partition.

This is the batch query for slicing a table, Using a temp table to prevent lock of update query while partitioning.

ALTER TABLE EventLog RENAME TO  EventLogTmp;

CREATE TABLE  EventLog (
    DateTime datetime NOT NULL,
    Node varchar(16) NOT NULL,
    Event varchar(512) NOT NULL,
    Count int(10) unsigned NOT NULL,
    PRIMARY KEY (DateTime , Node , Event) USING BTREE
)  ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE EventLogTmp
Partition by Range(Year(DateTime))
(
Partition OldYearsP Values Less Than(2010),
Partition Y2010P Values Less Than(2011),
Partition Y2011P Values Less Than(2012),
Partition Y2012P Values Less Than(2013),
Partition Y2013P Values Less Than(2014),
Partition Y2014P Values Less Than(2015),
Partition FutYearsP Values Less Than(2050)
);

ALTER TABLE EventLog RENAME TO EventLogTmp3;

ALTER TABLE EventLogTmp RENAME TO  EventLog;

INSERT INTO EventLog Select * From EventLogTmp3;

DROP TABLE EventLogTmp3;

If you made a backup of one partition data and this partition can't be used more like this sample that partition table base on year, you can drop the whole partition.

ALTER TABLE EventLog DROP PARTITION OldYearsP;

If you want to rotate a partition like monthly partition, you can make backup of data and then truncate data inside.

ALTER TABLE EventLog TRUNCATE PARTITION Y2012P;