lsemenenko revidoval tento gist 11 months ago. Přejít na revizi
Žádné změny
lsemenenko revidoval tento gist 11 months ago. Přejít na revizi
1 file changed, 76 insertions
convert_to_innodb.sh(vytvořil soubor)
| @@ -0,0 +1,76 @@ | |||
| 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 | |
Novější
Starší