optimize_innodb_tables.sh
· 2.5 KiB · Bash
Bruto
#!/bin/bash
# Script to optimize all InnoDB tables in a specified MySQL database
# Usage: ./optimize_innodb_tables.sh <database_name> [options]
# Display help information
function show_help {
echo "Usage: $0 <database_name> [options]"
echo ""
echo "Options:"
echo " -h, --help Display this help message"
echo " -u, --user USERNAME MySQL username (optional, will use .my.cnf if not provided)"
echo " -p, --password PASS MySQL password (optional, will use .my.cnf if not provided)"
echo ""
echo "Example: $0 mydatabase -u root -p mypassword"
echo "Example: $0 mydatabase (uses credentials from .my.cnf)"
exit 1
}
# Check if we have at least one argument
if [ $# -lt 1 ]; then
show_help
fi
# Initialize variables
DB_NAME=""
MYSQL_USER=""
MYSQL_PASS=""
MYSQL_OPTS=""
# Parse command line arguments
while [ $# -gt 0 ]; do
case "$1" in
-h|--help)
show_help
;;
-u|--user)
MYSQL_USER="$2"
shift 2
;;
-p|--password)
MYSQL_PASS="$2"
shift 2
;;
*)
# First non-option argument is the database name
if [ -z "$DB_NAME" ]; then
DB_NAME="$1"
shift
else
echo "Error: Unknown option $1"
show_help
fi
;;
esac
done
# Verify database name was provided
if [ -z "$DB_NAME" ]; then
echo "Error: Database name is required."
show_help
fi
# Set MySQL command options
if [ -n "$MYSQL_USER" ]; then
MYSQL_OPTS="$MYSQL_OPTS -u $MYSQL_USER"
fi
if [ -n "$MYSQL_PASS" ]; then
MYSQL_OPTS="$MYSQL_OPTS -p$MYSQL_PASS"
fi
# Get all InnoDB tables
echo "Fetching InnoDB tables from database $DB_NAME..."
INNODB_TABLES=$(mysql $MYSQL_OPTS -B -N -e "
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA='$DB_NAME'
AND ENGINE='InnoDB'")
# Check if any InnoDB tables were found
if [ -z "$INNODB_TABLES" ]; then
echo "No InnoDB tables found in the database $DB_NAME."
exit 0
fi
# Count the tables
TABLE_COUNT=$(echo "$INNODB_TABLES" | wc -l)
echo "Found $TABLE_COUNT InnoDB tables in database $DB_NAME."
# Initialize counter for tracking progress
COUNTER=0
# Optimize each table
for TABLE in $INNODB_TABLES; do
COUNTER=$((COUNTER + 1))
echo "[$COUNTER/$TABLE_COUNT] Optimizing table: $TABLE"
# Run OPTIMIZE TABLE command
RESULT=$(mysql $MYSQL_OPTS -B -N -e "
OPTIMIZE TABLE $DB_NAME.$TABLE" | tr '\t' ' ')
echo " Result: $RESULT"
done
echo "Optimization completed for all InnoDB tables in database $DB_NAME."
| 1 | #!/bin/bash |
| 2 | |
| 3 | # Script to optimize all InnoDB tables in a specified MySQL database |
| 4 | # Usage: ./optimize_innodb_tables.sh <database_name> [options] |
| 5 | |
| 6 | # Display help information |
| 7 | function show_help { |
| 8 | echo "Usage: $0 <database_name> [options]" |
| 9 | echo "" |
| 10 | echo "Options:" |
| 11 | echo " -h, --help Display this help message" |
| 12 | echo " -u, --user USERNAME MySQL username (optional, will use .my.cnf if not provided)" |
| 13 | echo " -p, --password PASS MySQL password (optional, will use .my.cnf if not provided)" |
| 14 | echo "" |
| 15 | echo "Example: $0 mydatabase -u root -p mypassword" |
| 16 | echo "Example: $0 mydatabase (uses credentials from .my.cnf)" |
| 17 | exit 1 |
| 18 | } |
| 19 | |
| 20 | # Check if we have at least one argument |
| 21 | if [ $# -lt 1 ]; then |
| 22 | show_help |
| 23 | fi |
| 24 | |
| 25 | # Initialize variables |
| 26 | DB_NAME="" |
| 27 | MYSQL_USER="" |
| 28 | MYSQL_PASS="" |
| 29 | MYSQL_OPTS="" |
| 30 | |
| 31 | # Parse command line arguments |
| 32 | while [ $# -gt 0 ]; do |
| 33 | case "$1" in |
| 34 | -h|--help) |
| 35 | show_help |
| 36 | ;; |
| 37 | -u|--user) |
| 38 | MYSQL_USER="$2" |
| 39 | shift 2 |
| 40 | ;; |
| 41 | -p|--password) |
| 42 | MYSQL_PASS="$2" |
| 43 | shift 2 |
| 44 | ;; |
| 45 | *) |
| 46 | # First non-option argument is the database name |
| 47 | if [ -z "$DB_NAME" ]; then |
| 48 | DB_NAME="$1" |
| 49 | shift |
| 50 | else |
| 51 | echo "Error: Unknown option $1" |
| 52 | show_help |
| 53 | fi |
| 54 | ;; |
| 55 | esac |
| 56 | done |
| 57 | |
| 58 | # Verify database name was provided |
| 59 | if [ -z "$DB_NAME" ]; then |
| 60 | echo "Error: Database name is required." |
| 61 | show_help |
| 62 | fi |
| 63 | |
| 64 | # Set MySQL command options |
| 65 | if [ -n "$MYSQL_USER" ]; then |
| 66 | MYSQL_OPTS="$MYSQL_OPTS -u $MYSQL_USER" |
| 67 | fi |
| 68 | |
| 69 | if [ -n "$MYSQL_PASS" ]; then |
| 70 | MYSQL_OPTS="$MYSQL_OPTS -p$MYSQL_PASS" |
| 71 | fi |
| 72 | |
| 73 | # Get all InnoDB tables |
| 74 | echo "Fetching InnoDB tables from database $DB_NAME..." |
| 75 | INNODB_TABLES=$(mysql $MYSQL_OPTS -B -N -e " |
| 76 | SELECT TABLE_NAME |
| 77 | FROM INFORMATION_SCHEMA.TABLES |
| 78 | WHERE TABLE_SCHEMA='$DB_NAME' |
| 79 | AND ENGINE='InnoDB'") |
| 80 | |
| 81 | # Check if any InnoDB tables were found |
| 82 | if [ -z "$INNODB_TABLES" ]; then |
| 83 | echo "No InnoDB tables found in the database $DB_NAME." |
| 84 | exit 0 |
| 85 | fi |
| 86 | |
| 87 | # Count the tables |
| 88 | TABLE_COUNT=$(echo "$INNODB_TABLES" | wc -l) |
| 89 | echo "Found $TABLE_COUNT InnoDB tables in database $DB_NAME." |
| 90 | |
| 91 | # Initialize counter for tracking progress |
| 92 | COUNTER=0 |
| 93 | |
| 94 | # Optimize each table |
| 95 | for TABLE in $INNODB_TABLES; do |
| 96 | COUNTER=$((COUNTER + 1)) |
| 97 | echo "[$COUNTER/$TABLE_COUNT] Optimizing table: $TABLE" |
| 98 | |
| 99 | # Run OPTIMIZE TABLE command |
| 100 | RESULT=$(mysql $MYSQL_OPTS -B -N -e " |
| 101 | OPTIMIZE TABLE $DB_NAME.$TABLE" | tr '\t' ' ') |
| 102 | |
| 103 | echo " Result: $RESULT" |
| 104 | done |
| 105 | |
| 106 | echo "Optimization completed for all InnoDB tables in database $DB_NAME." |