Data Purge for Free Version of Mattermost

mm_data_purge.sh

#!/bin/bash

##############
## Mattermost Data Retention Purge Script
## Written by: Jeff Lehman, N8ACL
## Date Created: 05/30/2021
## Date Last Updated: 05/30/2021
##
## Since in the free version of Mattermost, there is no data retension policy that can be set like in the Enterprise Version, we have to do it on our own.
## This script is used to delete any posts and files over 6 Months old. This is to help keep the server clean and not fill up.
## This was cobbled together from a couple of different places on the internet and I wanted to post a fulle solution to share.
##
## Things to change below:
## Note on line 29 where it makes the connection to the MySQL Server.
## Replace <username> with the user name to log in as.
## Replace <password> with the password. Note that there is no space between the -p and the <password>. This is intentional. This is how MySQL logs in via the command line.
## You can also change the interval in the delete from posts query if you want a different time frame.
##############

# Declare the File Delete list for any file uploads.
dellistfile="/tmp/filedellist.txt"

# Remove the old list from the last run
rm $dellistfile

# Delete posts and reactions older then a rolling 6 months
# Then get a list of file uploads from those posts to delete and push them to a text file.
# Then delete the file inforamtion from the database.
mysql -u <username> -p<password> -sN <<END_QUERY
use mattermost;
delete from Posts where from_unixtime(CreateAt/1000) < date_add(now(), interval -6 month);
delete from Reactions where postid not in (select id from Posts);
select Path from FileInfo
where postid not in (select id from Posts)
into outfile '/tmp/dellistfile.txt'
FIELDS TERMINATED BY ','
ENCLOSED BY '' 
LINES TERMINATED BY '\n';
delete from FileInfo where postid not in (select id from Posts);
END_QUERY

# Now delete the files uploaded from the list generated from above SQL Queries.
while IFS= read -r line
do
  rm "/opt/mattermost/data/$line"
done < "$dellistfile"