Bulk delete data from Aurora/MySQL

run_pt_archive.sh

#!/bin/bash

# Deletes data in the given table.
# This takes the strategy of deleting based on
# some indexed key, possibly the primary key.
# It will repeatedly delete the first 1000
# records found up to the maximim $MAXVAL.
# This seems to have OK performance when working
# against a small Aurora instance.
# It deletes around 100,000 records every minute.

# You'll need to have Percona tools installed.
# This was tested against percona-toolkit-3.3.1
# Note the version of percona installed by apt
# is old as of writing (11/22/22). I had to
# download from Percona's website. 

export HOST=
export USER=
export DB=
export TABLE=
export FIELD=
export MAXVAL=

pt-archiver \
    --user $USER \
    --source h=$HOST,D=$DB,t=$TABLE \
    --purge \
    --where '$FIELD < $MAXVAL' \
    --bulk-delete \
    --progress 10000 \
    --statistics \
    --commit-each \
    --limit 1000 \
    --skip-foreign-key-checks \
    --no-check-charset \
    --ask-pass \
    --optimize $TABLE