Solved

Empty a table across several MySql databases

Posted on 2013-05-18
6
450 Views
Last Modified: 2013-05-20
Hi,
I got a little challenge here and hope that maybe someone has a good answer.

I have several domains, all of them run a CMS that utilizes its own MYSQL DB. In each database there is a table that accumulates lots of temporary data. This data is used weekly and could be deleted thereafter. However, it does not remove the data so that this particular table grows huge in each of the databases.
So, I look for a way to empty the database automatically on a set schedule. Or even better delete all data that are older then 7 or 10 days on a daily basis.

Is there some command that can do this across many databases. Or at least on one database?

Best wishes,
Tom
0
Comment
Question by:Thomanji
6 Comments
 
LVL 15

Expert Comment

by:Jagadishwor Dulal
Comment Utility
Or at least on one database

You can set a cron job to perform empty query :

http://upstartblogger.com/how-to-create-a-cron-job-in-cpanel/
0
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 500 total points
Comment Utility
If you're using MySQL or any of the related extensions, you can write a query like this:

$old = date('c', strtotime('Now - 1 week'));
$sql = "DELETE FROM dataBaseName.tableName WHERE insertDate < '$old' ";

You could put this query into a CRON job, but you might find that the DELETE runs very, very fast (especially if the insertDate column is an indexed DATETIME column).  If, in fact, the DELETE runs fast (and I think it will) you can put this "pruning" query into the script that creates the page footers.  Each instance of the CMS would be able to prune its own historical table.

If you decide to go with the CRON job, you will find that the paths to the data bases may not be the same as the paths inside the CMS.  You will want to set up the CRON job in such a way that it can produce output for you.  I find that this scheme works well.
<?php
ob_start();
phpinfo();
$out = ob_get_clean();
mail('you@your.org', 'CRON PHPINFO', $out);

Open in new window

If you decide to go with the CRON job, you will need to give it a list of data bases that require pruning.  This becomes an additional maintenance point, should the list of data bases ever change.

If you need weekly reporting, you might want to delete the rows that are older than two weeks.  That way you would always have a full week of data, and no matter what day it was, you could do Sunday-to-Sunday historical reporting.
0
 
LVL 20

Expert Comment

by:Mark Brady
Comment Utility
I suggest you go with Ray's idea on deleting the records that are older than some time period (two weeks - 1 week or whatever you decide). I regularly do this practice. I make a cleanup() function that is called randomly. Quick example

1: create a random number between 200 and 600.
2: if that random number is between 350 and 450 call the cleanup function which deletes old records.

That way you are not trying to cleanup every time the script is executed.
Either way you won't have to worry about maintaining cron jobs across multiple servers. (like Ray said 'less maintenance points to worry about"
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
If the WHERE clause in the DELETE query references an indexed column, I don't think you will even be able to detect the timing differences when the query is run.  In case you're skeptical about it (and I am always in favor of healthy skepticism) this little class can be used to attach an event timer.  You can try the page with and without the DELETE to see the times.  Demonstration code starts around like 160.

<?php // RAY_class_Stopwatch.php
error_reporting(E_ALL);


// DEMONSTRATE A SCRIPT TIMER FOR ALL OR PART OF A SCRIPT PHP 5+
// MAN PAGE http://php.net/manual/en/function.microtime.php


class StopWatch
{
    protected $a; // START TIME
    protected $s; // STATUS - IF RUNNING
    protected $z; // STOP TIME

    public function __construct()
    {
        $this->a = array();
        $this->s = array();
        $this->z = array();
    }

    // A METHOD TO PROVIDE A FINAL READOUT, IF NEEDED
    public function __destruct()
    {
        $ret = $this->readout();
        if (!$ret) return FALSE;
        echo
          __CLASS__
        . '::'
        . __FUNCTION__
        . '() '
        ;
        echo "<b>$ret</b>";
        echo PHP_EOL;
    }

    // A METHOD TO REMOVE A TIMER
    public function reset($name='TIMER')
    {
        // RESET ALL TIMERS
        if ($name == 'TIMER')
        {
            $this->__construct();
        }
        else
        {
            unset($this->a[$name]);
            unset($this->s[$name]);
            unset($this->z[$name]);
        }
    }

    // A METHOD TO CAPTURE THE START TIME
    public function start($name='TIMER')
    {
        $this->a[$name] = microtime(TRUE);
        $this->z[$name] = $this->a[$name];
        $this->s[$name] = 'RUNNING';
    }

    // A METHOD TO CAPTURE THE END TIME
    public function stop($name='TIMER')
    {
        $ret = NULL;

        // STOP ALL THE TIMERS
        if ($name == 'TIMER')
        {
            foreach ($this->a as $name => $start_time)
            {
                // IF THIS TIMER IS STILL RUNNING, STOP IT
                if ($this->s[$name])
                {
                    $this->s[$name] = FALSE;
                    $this->z[$name] = microtime(TRUE);
                }
            }
        }

        // STOP ONLY ONE OF THE TIMERS
        else
        {
            if ($this->s[$name])
            {
                $this->s[$name] = FALSE;
                $this->z[$name] = microtime(TRUE);
            }
            else
            {
                $ret .= "ERROR: CALL TO STOP() METHOD FOR '$name' IS NOT RUNNING";
            }
        }

        // RETURN AN ERROR MESSAGE, IF ANY
        return $ret;
    }

    // A METHOD TO READ OUT THE TIMER(S)
    public function readout($name='TIMER', $dec=3, $m=1000, $eol=PHP_EOL)
    {
        $str = NULL;

        // GET READOUTS FOR ALL THE TIMERS
        if ($name == 'TIMER')
        {
            foreach ($this->a as $name => $start_time)
            {
                $str .= $name;

                // IF THIS TIMER IS STILL RUNNING UPDATE THE END TIME
                if ($this->s[$name])
                {
                    $this->z[$name] = microtime(TRUE);
                    $str .= " RUNNING ";
                }
                else
                {
                    $str .= " STOPPED ";
                }

                // RETURN A DISPLAY STRING
                $lapse_time = $this->z[$name] - $start_time;
                $lapse_msec = $lapse_time * $m;
                $lapse_echo = number_format($lapse_msec, $dec);
                $str .= " $lapse_echo";
                $str .= $eol;
            }
            return $str;
        }

        // GET A READOUT FOR ONLY ONE TIMER
        else
        {
            $str .= $name;

            // IF THIS TIME IS STILL RUNNING, UPDATE THE END TIME
            if ($this->s[$name])
            {
                $this->z[$name] = microtime(TRUE);
                $str .= " RUNNING ";
            }
            else
            {
                $str .= " STOPPED ";
            }


            // RETURN A DISPLAY STRING
            $lapse_time = $this->z[$name] - $this->a[$name];
            $lapse_msec = $lapse_time * $m;
            $lapse_echo = number_format($lapse_msec, $dec);
            $str .= " $lapse_echo";
            $str .= $eol;
            return $str;
        }
    }
}



// DEMONSTRATE THE USE -- INSTANTIATE THE STOPWATCH OBJECT
$sw  = new Stopwatch;

// SET A STOPWATCH NAME THAT REFLECTS THE PARTS OF THE SCRIPT WE WANT TO TIME
$g_timer = 'GOOGLE TIMER';

// START A TIMER TO GET ELAPSED TIME FOR A CALL TO GOOGLE
$sw->start($g_timer);

// PERFORM SOME ACTIVITY THAT YOU WANT TO TIME (READS GOOGLE WEB PAGE)
$page = 'http://google.com';
$html = file_get_contents($page);

// GET A READOUT OF THE TIMER WHILE IT IS STILL RUNNING
echo nl2br($sw->readout($g_timer));
echo "<br/>" . PHP_EOL;

// PERFORM SOME OTHER ACTIVITY (READS GOOGLE WEB PAGE AGAIN)
$page = 'http://google.com';
$html = file_get_contents($page);

// STOP THE TIMER AND GET A READOUT WITH SHORT DECIMALS
$x = $sw->stop($g_timer);
echo nl2br($sw->readout($g_timer, 1));
echo "<br/>" . PHP_EOL;




// START A SECOND TIMER
$y_timer = 'YAHOO TIMER';
$sw->start($y_timer);

// PERFORM SOME OTHER ACTIVITY THAT YOU WANT TO TIME
$page = 'http://yahoo.com/';
$html = file_get_contents($page);

// REPORT THE STOPWATCHES CONTENT (ONE IS STOPPED AND ONE IS STILL RUNNING)
echo nl2br($sw->readout());
echo "<br/>" . PHP_EOL;

// SHOW THE OBJECT
echo "<pre>";
var_dump($sw);
echo "</pre>";

// STOP ALL OF THE STOPWATCHES
$sw->stop();

// REPORT THE STOPWATCHES CONTENT AGAIN
echo nl2br($sw->readout());
echo "<br/>" . PHP_EOL;

// SHOW THE OBJECT
echo "<pre>";
var_dump($sw);
echo "</pre>";



// TRY TO STOP A TIMER THAT IS NOT RUNNING
$x = $sw->stop($g_timer);
var_dump($x);
echo "<br/>" . PHP_EOL;
echo "<br/>" . PHP_EOL;



// START THIS TIMER OVER AGAIN
$sw->start($y_timer);

// PERFORM SOME OTHER ACTIVITY THAT YOU WANT TO TIME
$page = 'http://weather.yahoo.com/';
$html = file_get_contents($page);

// REPORT THE STOPWATCHES CONTENT
echo nl2br($sw->readout());
echo "<br/>" . PHP_EOL;

// SHOW THE OBJECT
echo "<pre>";
var_dump($sw);
echo "</pre>";



// REMOVE ONE OF THE STOPWATCHES
$sw->reset($g_timer);

// REPORT THE STOPWATCHES CONTENT
echo nl2br($sw->readout());
echo "<br/>" . PHP_EOL;

// SHOW THE OBJECT
echo "<pre>";
var_dump($sw);
echo "</pre>";



// REMOVE ALL OF THE STOPWATCHES
$sw->reset();
echo "ALL STOPWATCHES HAVE BEEN REMOVED";

// REPORT THE STOPWATCHES CONTENT (SHOWS NOTHING)
echo nl2br($sw->readout());

// SHOW THE OBJECT
echo "<pre>";
var_dump($sw);
echo "</pre>";

// SHOW THE DESTRUCTOR IN ACTION
$sw->start('Foo');
echo "<pre>";
unset($sw);
echo "</pre>";

Open in new window

Best to all, ~Ray
0
 

Author Closing Comment

by:Thomanji
Comment Utility
Hi Ray,
Thank you very much, all works fine.
Very nice of you to help me out.

Best wishes,
Tom
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
Thanks for the points and best of luck with it, ~Ray
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Creating and Managing Databases with phpMyAdmin in cPanel.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

744 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now