Mendadak Goblok

Housekeeping your database today means fewer headaches tomorrow. Clean data, clear logs, and optimized storage keep your system healthy and future-proof.

Why we need housekeeping our database?

Think of your database like a closet — without regular cleaning, it gets stuffed and messy. Housekeeping helps avoid full storage and keeps everything running fast.

In my case i would do housekeeping my database mysql in centos server.

Steps

First, enter your server with ssh.

Then create folder script

mkdir script

And go inside script, create folder log

cd script/
mkdir log

We will create script housekeeping in /script/mysql_housekeeping.sh
So go to /script, then create file mysql_housekeeping.sh

cd ..
touch mysql_housekeeping.sh

Give access executable to sh file

chmod +x mysql_housekeeping.sh

Then write the script

vi mysql_housekeeping.sh

mysql_housekeeping.sh BASH

#!/bin/bash

# Variables
DB_USER="admin"
DB_PASS="yoursecretpassword"
DB_NAME="log"
TABLE_DATA="general_log"
LOG_FILE="/home/pandz3rd/script/log/mysql_housekeeping.log"

# Start logging
{
echo "============================================================"
echo "[$(date +'%Y-%m-%d %H:%M:%S')] Starting housekeeping..."

```
# Calculate date from 1 month ago
MONTH_DATE=$(date -d "1 month ago" +"%Y-%m-%d")

# Delete old records
echo "[$(date +'%Y-%m-%d %H:%M:%S')] Deleting old records from $TABLE_DATA..."
mysql -u $DB_USER -p"$DB_PASS" -e "DELETE FROM $DB_NAME.$TABLE_DATA WHERE $TABLE_DATA.sys_creation_date < '$MONTH_DATE';"

# Optimize table after deletion
mysql -u $DB_USER -p"$DB_PASS" -e "OPTIMIZE TABLE $TABLE_DATA;"

echo "[$(date +'%Y-%m-%d %H:%M:%S')] Housekeeping completed."
echo "============================================================"
```
} >> $LOG_FILE 2>&1

Save the script

:wq

Next step we need to config crontab to automate start task on schedule. This task i want to run in every 2 pm.

crontab -e

Add crontab job

0 2 * * * /bin/bash /home/pandz3rd/script/mysql_housekeeping.sh