Home Insights Delete InfiniteWP Activity Log With Bash & SQL
20th July 2015 in Web Development

Delete InfiniteWP Activity Log With Bash & SQL

By Louise Towler

Recently we decided to move our InfiniteWP installation over to a new server, which provided a good opportunity to clean out some historical data and schedule some regular backups. We’ve been running our Infinite WP installation now since 2012. It’s never once had any of it’s old log data purged, and as you can imagine this has led to a rather large database. A few Google searches will show this is a common problem with InfiniteWP databases. Many are in excess of 250MB. Most of which is old historical data as there is currently no way to clear the InfiniteWP activity log tables within the application (current version v2.4.8).

In order to save server disk space, storage within our Dropbox account, bandwidth and to just improve performance we’ve created a bash script to clean historical data from the log tables. By default InfiniteWP removes data from iwp_history_raw_data that was created more than 30 days ago, but it doesn’t touch the iwp_history and iwp_history_additional_data tables.

The following SQL query does all the work. We are removing any records from both tables where the microtimeAdded field contains a timestamp older than 30 days. This can be run directly within phpMyAdmin, a PHP script or pretty much anything that can connect to a database, and run SQL queries.

DELETE h, h2
FROM iwp_history AS h
INNER JOIN iwp_history_additional_data AS h2
ON h2.historyID = h.historyID
WHERE h.microtimeAdded < (UNIX_TIMESTAMP()-(86400*30));

We put together the following script which is configured to run at weekly intervals via cron to clear historical data 30+ days old. You can simply copy the script below, update the dbuser, dbpass and dbname variables with your InfiniteWP database credentials and store it on your server somewhere as a .sh file.

IMPORTANT – DO NOT store this file in a public folder. This script is provided “as is”, at no point will we take responsibility for any loss of data, damages or other liability arising from this script. It’s highly recommend that you take a full database backup before using this script.

#!/bin/bash -e

# database user

# database password

# database name

# number of days to store

# backup database to backups directory
mysql -u$dbuser -p$dbpass $dbname -e "

DELETE h, h2
FROM iwp_history AS h
INNER JOIN iwp_history_additional_data AS h2
ON h2.historyID = h.historyID
WHERE h.microtimeAdded < (UNIX_TIMESTAMP()-(86400*${days}));"

Cron Schedule

Our script runs every 7 days at 3:30am (30 minutes after our backup is created). You can alter the time to suit your needs. For more information about cron schedules checkout these 15 Practical Cron Examples.

For a generic use case, the following example will run every 7 days at midnight.

0 0 */7 * * ~/.scripts/iwppurgehistory.sh >/dev/null 2>&1

Leave a Reply

Your email address will not be published.