[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 258
  • Last Modified:

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.
0
cqr213
Asked:
cqr213
5 Solutions
 
nemws1Commented:
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
 
gr8gonzoConsultantCommented:
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
gr8gonzoConsultantCommented:
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
 
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
 
gr8gonzoConsultantCommented:
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
 
FishMongerCommented:
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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now