Overwriting MySQL database to only store 1 month of data We are logging data on hardware with only small memory (short on disk storage) only 4GB. 

We only require the data to be stored for 1 month and then be over written in a way that it overrides older data first. The memory on hardware is very small so cannot continue to record indefinitely.

We are using a MySQL data base, the hardware it is running on is not always powered on as it is in a vehicle. The data will be viewed in a graph to show historical data over time.

A few options I have thought of but not sure how to execute it:

Let’s assume I will record 1 million rows of data in a month

When the table (table1) gets to 1 million rows, move this table to another and start new table (table2). When table2 reaches 1 million rows. Delete table1, move table2 to new table and create table3 etc...

This way there will be minimum 1 month of entries.

Second option (not sure if possible):

When the table gets to 1 million rows it starts to override from row 1 again.
A short rotation script/event could look like:

drop table if exists table2;
rename table table1 to table2;
create table table1 like table2;