convert_to_innodb.sh
· 2.4 KiB · Bash
原始檔案
#!/bin/bash
#===============================================================================
# convert_to_innodb.sh
#===============================================================================
# Description:
# Converts all non-InnoDB tables in a MySQL database to the InnoDB engine.
# This script requires a properly configured ~/.my.cnf file for authentication.
# It automatically detects all tables with engines other than InnoDB and
# converts them one by one, providing progress updates.
#
# Usage:
# ./convert_to_innodb.sh <database_name>
#
# Example:
# ./convert_to_innodb.sh my_wordpress_db
#
# Author: Generated on $(date +%Y-%m-%d)
#===============================================================================
if [ "$#" -ne 1 ]; then
echo "Usage: $0 <database_name>"
echo "Example: $0 my_database"
exit 1
fi
DB_NAME="$1"
# Welcome message
echo "Converting all non-InnoDB tables to InnoDB engine..."
echo "Database: $DB_NAME"
# Get a list of all non-InnoDB tables
TABLES=$(mysql -e "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='$DB_NAME' AND ENGINE != 'InnoDB';" -s)
# Check if there are any non-InnoDB tables
if [ -z "$TABLES" ]; then
echo "No non-InnoDB tables found in $DB_NAME. Nothing to convert."
exit 0
fi
# Count how many tables need conversion
TABLE_COUNT=$(echo "$TABLES" | wc -l)
echo "Found $TABLE_COUNT tables to convert..."
# Convert each table
COUNTER=0
for TABLE in $TABLES; do
COUNTER=$((COUNTER+1))
echo "[$COUNTER/$TABLE_COUNT] Converting table: $TABLE"
# Get current engine for logging
CURRENT_ENGINE=$(mysql -e "SELECT ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='$DB_NAME' AND TABLE_NAME='$TABLE';" -s)
echo " Current engine: $CURRENT_ENGINE"
# Execute the ALTER TABLE statement
mysql $DB_NAME -e "ALTER TABLE \`$TABLE\` ENGINE=InnoDB;"
if [ $? -eq 0 ]; then
echo " Conversion successful!"
else
echo " Conversion failed! Please check the table structure."
fi
done
# Verify all tables have been converted
REMAINING=$(mysql -e "SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='$DB_NAME' AND ENGINE != 'InnoDB';" -s)
echo ""
echo "Conversion completed!"
echo "Non-InnoDB tables remaining: $REMAINING"
if [ "$REMAINING" -eq "0" ]; then
echo "All tables successfully converted to InnoDB!"
else
echo "Some tables could not be converted. Manual intervention may be required."
fi
| 1 | #!/bin/bash |
| 2 | #=============================================================================== |
| 3 | # convert_to_innodb.sh |
| 4 | #=============================================================================== |
| 5 | # Description: |
| 6 | # Converts all non-InnoDB tables in a MySQL database to the InnoDB engine. |
| 7 | # This script requires a properly configured ~/.my.cnf file for authentication. |
| 8 | # It automatically detects all tables with engines other than InnoDB and |
| 9 | # converts them one by one, providing progress updates. |
| 10 | # |
| 11 | # Usage: |
| 12 | # ./convert_to_innodb.sh <database_name> |
| 13 | # |
| 14 | # Example: |
| 15 | # ./convert_to_innodb.sh my_wordpress_db |
| 16 | # |
| 17 | # Author: Generated on $(date +%Y-%m-%d) |
| 18 | #=============================================================================== |
| 19 | |
| 20 | if [ "$#" -ne 1 ]; then |
| 21 | echo "Usage: $0 <database_name>" |
| 22 | echo "Example: $0 my_database" |
| 23 | exit 1 |
| 24 | fi |
| 25 | |
| 26 | DB_NAME="$1" |
| 27 | |
| 28 | # Welcome message |
| 29 | echo "Converting all non-InnoDB tables to InnoDB engine..." |
| 30 | echo "Database: $DB_NAME" |
| 31 | |
| 32 | # Get a list of all non-InnoDB tables |
| 33 | TABLES=$(mysql -e "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='$DB_NAME' AND ENGINE != 'InnoDB';" -s) |
| 34 | |
| 35 | # Check if there are any non-InnoDB tables |
| 36 | if [ -z "$TABLES" ]; then |
| 37 | echo "No non-InnoDB tables found in $DB_NAME. Nothing to convert." |
| 38 | exit 0 |
| 39 | fi |
| 40 | |
| 41 | # Count how many tables need conversion |
| 42 | TABLE_COUNT=$(echo "$TABLES" | wc -l) |
| 43 | echo "Found $TABLE_COUNT tables to convert..." |
| 44 | |
| 45 | # Convert each table |
| 46 | COUNTER=0 |
| 47 | for TABLE in $TABLES; do |
| 48 | COUNTER=$((COUNTER+1)) |
| 49 | echo "[$COUNTER/$TABLE_COUNT] Converting table: $TABLE" |
| 50 | |
| 51 | # Get current engine for logging |
| 52 | CURRENT_ENGINE=$(mysql -e "SELECT ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='$DB_NAME' AND TABLE_NAME='$TABLE';" -s) |
| 53 | echo " Current engine: $CURRENT_ENGINE" |
| 54 | |
| 55 | # Execute the ALTER TABLE statement |
| 56 | mysql $DB_NAME -e "ALTER TABLE \`$TABLE\` ENGINE=InnoDB;" |
| 57 | |
| 58 | if [ $? -eq 0 ]; then |
| 59 | echo " Conversion successful!" |
| 60 | else |
| 61 | echo " Conversion failed! Please check the table structure." |
| 62 | fi |
| 63 | done |
| 64 | |
| 65 | # Verify all tables have been converted |
| 66 | REMAINING=$(mysql -e "SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='$DB_NAME' AND ENGINE != 'InnoDB';" -s) |
| 67 | |
| 68 | echo "" |
| 69 | echo "Conversion completed!" |
| 70 | echo "Non-InnoDB tables remaining: $REMAINING" |
| 71 | |
| 72 | if [ "$REMAINING" -eq "0" ]; then |
| 73 | echo "All tables successfully converted to InnoDB!" |
| 74 | else |
| 75 | echo "Some tables could not be converted. Manual intervention may be required." |
| 76 | fi |