daily_range_partition.sh (2311B)
#!/bin/bash
DD=$(date +%Y-%m-%d)
TD=$(date -d "$DD + 1 day" +%Y-%m-%d)
MARIADB="mysql -u root -A "
#create new partition for today
echo "ALTER TABLE some_table DROP PARTITION pFuture; ALTER TABLE some_table ADD PARTITION (PARTITION p$(date -d "$DD" +%Y%m%d) VALUES LESS THAN (TO_DAYS('$TD')), PARTITION pFuture VALUES LESS THAN MAXVALUE);" | $MARIADB
#drop old partitions
OLD_PART=$(date -d "$DD - 6 months" +%Y%m%d)
for PART in $(echo "SELECT partition_name FROM information_schema.partitions WHERE table_name = 'some_table' AND partition_method = 'RANGE';" | $MARIADB --skip-column-names | grep -v Future)
do
PART_DATE="${PART:1}" #drop prefix p to extract date portion
PART_DATE_TEMP="${PART_DATE:0:4}-${PART_DATE:4:2}-${PART_DATE:6:2}"
if [[ "$OLD_PART" > "$PART_DATE_TEMP" ]]; then
echo "ALTER TABLE some_table DROP PARTITION ${PART};" | $MARIADB
fi
done
#EXPLAINER
#The benefit of partitions in the case of large tables is that the
#queries hitting the table can be told to use only specific partitions
#which means that the query optimizer will not even need to look at
#the rest of the table at all.
#Effectively, partitioning splits the data in the big table down into separate tables,
#while still getting treated as a single table. When creating a partition, the partition key
#must be added into the PRIMARY KEY (e.g. id,parition_key). Queries with a WHERE clause should
#specify the partition(s) to be used e.g.
#SELECT <stuff> FROM some_table PARTITION(pYYYYMMDD)
#or if using a JOIN, the join should ideally happen on the partition_key
# Example of a RANGE partitionable table, e.g. some stuff gets written here daily
# and we need to keep this data around for a long time, maybe transaction logs, or so
#CREATE TABLE `some_table` (
# `id` varchar(36) NOT NULL,
# `user_id` varchar(36) DEFAULT NULL,
# `date` varchar(36) DEFAULT NULL,
# `interesting_value1` int(11) DEFAULT NULL,
# `interesting_value2` int(11) DEFAULT NULL,
# `partition_key` datetime NOT NULL DEFAULT current_timestamp(),
# PRIMARY KEY (`id`,`partition_key`)
#) ENGINE=InnoDB;
#ALTER TABLE some_table
#PARTITION BY RANGE (TO_DAYS(partition_key)) (
# PARTITION pYYYYMMDD VALUES LESS THAN (TO_DAYS('YYYY-MM-DD+1')),
# PARTITION pFuture VALUES LESS THAN MAXVALUE
#);