Solved

MySQL auto purge

Posted on 2013-06-28
10
253 Views
Last Modified: 2014-02-03
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
Comment
Question by:cqr213
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 23

Assisted Solution

by:nemws1
nemws1 earned 100 total points
ID: 39284953
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
 

Author Comment

by:cqr213
ID: 39289865
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
 
LVL 35

Assisted Solution

by:gr8gonzo
gr8gonzo earned 300 total points
ID: 39315628
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 35

Accepted Solution

by:
gr8gonzo earned 300 total points
ID: 39315630
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
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 39315758
What's an HMI?
0
 

Author Comment

by:cqr213
ID: 39315806
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
 
LVL 35

Assisted Solution

by:gr8gonzo
gr8gonzo earned 300 total points
ID: 39315869
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
 
LVL 28

Assisted Solution

by:FishMonger
FishMonger earned 100 total points
ID: 39317624
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Today, the web development industry is booming, and many people consider it to be their vocation. The question you may be asking yourself is – how do I become a web developer?
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question