Automate MySQL Table Optimization and Partitioning Script

This comprehensive Bash script helps database administrators automatically optimize MySQL tables by defragmenting them, creating necessary indexes, and setting up partitions based on column types.

Features:

• Defragmentation: Optimizes tables by reducing fragmentation and reclaiming space.
• Index Creation: Automatically creates indexes for columns without existing indexes to enhance query performance.
• Dynamic Partitioning: Creates partitions based on Id (if it exists and is of type INT or BIGINT) or by datetime columns such as Create_at, Entry_dt, Ent_dt, and Create_dt to improve data management.

Bash Script for Database Optimization and Partitioning:

#!/bin/bash

# MySQL Configuration
DB_HOST="localhost"
DB_USER="root"
DB_PASSWORD="your_password"
DB_NAME="cms"

# Function to optimize data and create indexes for a table
optimize_table() {
    local table_name=$1

    echo "Optimizing and creating index for table: $table_name"

    # Defragmentation (OPTIMIZE)
    mysql -h "$DB_HOST" -u "$DB_USER" -p"$DB_PASSWORD" -D "$DB_NAME" -e "OPTIMIZE TABLE $table_name;"

    # Check and create indexes for columns without indexes
    columns_without_index=$(mysql -h "$DB_HOST" -u "$DB_USER" -p"$DB_PASSWORD" -D "$DB_NAME" -e "
        SELECT column_name 
        FROM information_schema.columns 
        WHERE table_schema = '$DB_NAME' 
        AND table_name = '$table_name' 
        AND column_key = '';" -B -N)

    # Create indexes for columns without indexes
    for column in $columns_without_index; do
        echo "Creating index for column: $column in table: $table_name"
        mysql -h "$DB_HOST" -u "$DB_USER" -p"$DB_PASSWORD" -D "$DB_NAME" -e "ALTER TABLE $table_name ADD INDEX (${column});"
    done
}

# Function to check and create partitions for a table
partition_table() {
    local table_name=$1

    echo "Checking table: $table_name for partitioning."

    # Check if the table has an Id column of type INT or BIGINT
    id_column=$(mysql -h "$DB_HOST" -u "$DB_USER" -p"$DB_PASSWORD" -D "$DB_NAME" -e "
        SELECT COLUMN_NAME 
        FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_SCHEMA = '$DB_NAME' 
        AND TABLE_NAME = '$table_name' 
        AND COLUMN_NAME = 'Id' 
        AND DATA_TYPE IN ('int', 'bigint');" -B -N)

    if [ ! -z "$id_column" ]; then
        echo "Partitioning table $table_name based on Id column."
        mysql -h "$DB_HOST" -u "$DB_USER" -p"$DB_PASSWORD" -D "$DB_NAME" -e "
        ALTER TABLE $table_name 
        PARTITION BY RANGE ($id_column) (
        PARTITION p0 VALUES LESS THAN (100000),
        PARTITION p1 VALUES LESS THAN (500000),
        PARTITION p2 VALUES LESS THAN (1000000),
        PARTITION p3 VALUES LESS THAN MAXVALUE
        );"
        return
    fi

    # Check for datetime columns
    datetime_column=$(mysql -h "$DB_HOST" -u "$DB_USER" -p"$DB_PASSWORD" -D "$DB_NAME" -e "
        SELECT COLUMN_NAME 
        FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_SCHEMA = '$DB_NAME' 
        AND TABLE_NAME = '$table_name' 
        AND COLUMN_NAME IN ('Create_at', 'Entry_dt', 'Ent_dt', 'Create_dt') 
        AND DATA_TYPE = 'datetime';" -B -N)

    if [ ! -z "$datetime_column" ]; then
        echo "Partitioning table $table_name based on $datetime_column column."
        mysql -h "$DB_HOST" -u "$DB_USER" -p"$DB_PASSWORD" -D "$DB_NAME" -e "
        ALTER TABLE $table_name 
        PARTITION BY RANGE (YEAR($datetime_column)) (
        PARTITION p0 VALUES LESS THAN (2022),
        PARTITION p1 VALUES LESS THAN (2023),
        PARTITION p2 VALUES LESS THAN (2024),
        PARTITION p3 VALUES LESS THAN MAXVALUE
        );"
        return
    fi

    echo "No suitable columns found for partitioning in table $table_name."
}

# Get list of all tables in the database
tables=$(mysql -h "$DB_HOST" -u "$DB_USER" -p"$DB_PASSWORD" -D "$DB_NAME" -e "SHOW TABLES;" -B -N)

# Optimize and partition all tables
for table in $tables; do
    optimize_table "$table"
    partition_table "$table"
done

echo "Optimization and partitioning process completed for all tables in database $DB_NAME."

Usage Instructions:

1. Create a new Bash script file:

   nano optimize_and_partition_db.sh

2. Paste the script into the file and save it.
3. Grant execution permissions to the script:

   chmod +x optimize_and_partition_db.sh

4. Run the script with sudo privileges:

   sudo ./optimize_and_partition_db.sh

Note:

• Ensure your MySQL supports partitioning and that the columns used for partitioning are valid.
• Adjust the number of partitions and value ranges according to your actual data needs.
• If a table already has partitions, MySQL will return an error. Ensure that tables do not already have partitions before running this script.