Script that recovered the IDB files back into mySQL

rebuild_idb_files.sh

#!/bin/bash

# Variables
DB_NAME="my_db"       # Replace with your database name
IBD_FOLDER="/opt/homebrew/var/mysql_backup/my_db"  # Folder containing all the backed up .ibd files
MYSQL_USER="root"             # MySQL username
MYSQL_PASSWORD="password"     # MySQL password
MYSQL_CMD="mysql -u$MYSQL_USER -p$MYSQL_PASSWORD $DB_NAME"

# Variables for system paths and user information
MYSQL_DATA_DIR="/opt/homebrew/var/mysql"  # MySQL data directory
TARGET_USER="Mohsin"                      # Target user's username
TARGET_GROUP="admin"                      # Target user's group

# Loop through all .ibd files in the folder
for ibd_file in "$IBD_FOLDER"/*.ibd; do
  # Extract table name from the .ibd file name
  table_name=$(basename "$ibd_file" .ibd)

  echo "Processing table: $table_name"

  # Drop secondary indexes for the table (Modify to match your indexes)
  echo "Dropping secondary indexes for $table_name"
  $MYSQL_CMD -e "SHOW INDEX FROM $table_name WHERE Key_name != 'PRIMARY'" | awk '{if(NR>1) print $3}' | while read index_name; do
    echo "Dropping index $index_name from table $table_name"
    # Print the generated command for debugging
    $MYSQL_CMD -e "ALTER TABLE \`$table_name\` DROP INDEX \`$index_name\`;"
  done

  # Discard the existing tablespace
  echo "Discarding tablespace for $table_name"
  $MYSQL_CMD -e "ALTER TABLE $table_name DISCARD TABLESPACE;" 2>/dev/null

  # Check if the .ibd file exists before copying
  if [ -f "$ibd_file" ]; then
    # Copy the .ibd file to the MySQL data directory
    echo "Copying .ibd file ($ibd_file) for $table_name"
    cp "$ibd_file" "$MYSQL_DATA_DIR/$DB_NAME/$table_name.ibd"

    # Set proper ownership and permissions
    echo "Setting ownership and permissions for $table_name"
    chown "$TARGET_USER:$TARGET_GROUP" "$MYSQL_DATA_DIR/$DB_NAME/$table_name.ibd"
    chmod 660 "$MYSQL_DATA_DIR/$DB_NAME/$table_name.ibd"

    # Import the tablespace
    echo "Importing tablespace for $table_name"
    $MYSQL_CMD -e "ALTER TABLE $table_name IMPORT TABLESPACE;" 2>/dev/null
  else
    echo "Error: .ibd file for $table_name is missing, skipping."
  fi

  echo "Done processing $table_name"
  echo "-----------------------------"
done

echo "All tables processed."