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.