The ultimate purge logs bash script for Laravel

Let's assume that you have a very large log table that can grow up to millions of records.

 In this example the table is named activity_logs and VERY IMPORTANT it should have an index in created_at to allow us to remove records by date.

If you put this shell script in the root folder of your Laravel project it will take the database credentials from your .env file and purgue the log table day by day, from an starting date to an end date (in the example from 6 months ago to 2 months ago)

#### Contents of purge_logs.sh

dbhost=$(grep -oP '^DB_HOST=\K.*' .env)
dbpass=$(grep -oP '^DB_PASSWORD=\K.*' .env)
dbname=$(grep -oP '^DB_DATABASE=\K.*' .env)
dbusername=$(grep -oP '^DB_USERNAME=\K.*' .env)

i=$(date --date="6 months ago" +"%Y-%m-%d")
limit=$(date --date="2 months ago" +"%Y-%m-%d")
while [ "$i" != "$limit" ]; do
    echo $i limit $limit
    tomorrow=$(date -I -d "$i + 1 day")

    mysql -u $dbusername -p$dbpass -h $dbhost --default-character-set=utf8 $dbname << EOF
delete from activity_logs
where
created_at>='$i'
and created_at<'$tomorrow'
EOF

    sleep 5
    i=$tomorrow
done

#### end of purge_logs.sh

Explanation

Those lines take the db parameters from the .env file and make them available to the script.

dbhost=$(grep -oP '^DB_HOST=\K.*' .env)
dbpass=$(grep -oP '^DB_PASSWORD=\K.*' .env)
dbname=$(grep -oP '^DB_DATABASE=\K.*' .env)
dbusername=$(grep -oP '^DB_USERNAME=\K.*' .env)

Then configure the starting date and the limit

i=$(date --date="6 months ago" +"%Y-%m-%d")
limit=$(date --date="2 months ago" +"%Y-%m-%d")

You can change the parameters as long as they are valid date params, for example you can use "today", "tomorrow", "2 weeks ago", ...

In the next two lines we'll start the loop and echo the dates for debug purposes, and guess the next day in the variable "tomorrow", beware, the limit should be greater than the start date, if you don't want an infinite loop :)

while [ "$i" != "$limit" ]; do
    echo $i limit $limit
    tomorrow=$(date -I -d "$i + 1 day")

 Then launch mysql with the script to delete the records between $i and $tomorrow

     mysql -u $dbusername -p$dbpass -h $dbhost --default-character-set=utf8 $dbname << EOF
delete from activity_logs
where
created_at>='$i'
and created_at<'$tomorrow'
EOF

 In the last lines we'll sleep for a while to avoid stressing our server and recalculate the $i date

    sleep 5
    i=$tomorrow
done

Finally you can add a line in your crontab to run the script once a day for example:

10 0 * * * cd root_folder; ./purge_logs.sh

Hope this help