MySQL auto purge

I have a HMI that will be installed in remote field location, collecting data in MySQL tables over time.  What tools does MySQL offer where I can set up for certain tables to be purged periodically weather based on a time period or on space.
Since this machine will be stand-alone I'm just looking for a way for this MySQL to manage itself to an extent just so the hard drive doesn't fill up.  we shoulnd't need more than 3 months history on data at the most.
cqr213Asked:
Who is Participating?
 
gr8gonzoConnect With a Mentor ConsultantCommented:
I would also try running a SELECT on that query first to make sure it's grabbing only the data you want to delete. Deletes are permanent!
0
 
nemws1Connect With a Mentor Database AdministratorCommented:
Usually, I would do this with 'cron' and not with MySQL inself, although it does have an event scheduler, you have a lot more control with cron (or a scheduled task if you're using windows).

More info on MySQL's event scheduler:   http://dev.mysql.com/doc/refman/5.1/en/events.html
0
 
cqr213Author Commented:
Can you please elaborate?
So by default will tables in a MySQL schema just append to new data or does it just add until it fill up all space?
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
gr8gonzoConnect With a Mentor ConsultantCommented:
I would use a scripting language to create queries to analyze and drop data. For example, in PHP:

<?php
mysql_connect("localhost","username","password");
mysql_select_db("database_name");

mysql_query("DELETE FROM yourtable WHERE someDateTimeField <= NOW() - INTERVAL 3 MONTH");
?>
0
 
Ray PaseurCommented:
What's an HMI?
0
 
cqr213Author Commented:
Ok however I'm not familiar with PHP.  Is there a way for me to do this from inside Workbench?  I was hoping it was as easy as maybe doing a Scheduled task in windows or a event schedule in Workbench somehow..

HMI - Human Machine Interface
It's basically just a computer running software that controls mechanical devices via PLC's (I/O).
0
 
gr8gonzoConnect With a Mentor ConsultantCommented:
Man, I was certain it stood for a Hibernating Mammoth Inebriator. Twenty bucks down the drain.

You can also just create a SQL file with the commands you want to run and use Windows task scheduler to run a command like:

C:\path\to\mysql.exe --user=YourUsernameHere --password=PasswordHere --database=YourDatabase < C:\path\to\the\file.sql
0
 
FishMongerConnect With a Mentor Commented:
I'd partition the tables by date range and then have a cron job that kicks off a script that drops the older partition(s) and repartitions as needed.

Dropping partitions is more efficient than using select/delete queries.

RANGE Partitioning
0
All Courses

From novice to tech pro — start learning today.