95 lines
3.1 KiB
Bash
Executable File
95 lines
3.1 KiB
Bash
Executable File
#!/bin/bash
|
|
|
|
# Complete Table-by-Table Database Migration
|
|
set -e
|
|
|
|
# Database credentials
|
|
DB_USER="tilbudgivern_service"
|
|
DB_PASS="REDACTED_PASSWORD"
|
|
DB_NAME="tilbudgivern"
|
|
|
|
echo "🔄 Starting COMPLETE table-by-table database migration..."
|
|
|
|
# Test connections
|
|
echo "Testing connections..."
|
|
ssh -p 55555 [email protected] "echo 'SSH OK'"
|
|
mysql -u "$DB_USER" -p"$DB_PASS" -e "SELECT 'Local DB OK' as status;"
|
|
|
|
# Create backup directory
|
|
mkdir -p /tmp/complete_migration
|
|
cd /tmp/complete_migration
|
|
|
|
# Drop and recreate database for clean start
|
|
echo "🗄️ Recreating database..."
|
|
mysql -u "$DB_USER" -p"$DB_PASS" -e "DROP DATABASE IF EXISTS $DB_NAME;"
|
|
mysql -u "$DB_USER" -p"$DB_PASS" -e "CREATE DATABASE $DB_NAME CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
|
|
|
|
# Get complete table list from remote
|
|
echo "📋 Getting table list from remote server..."
|
|
ssh -p 55555 [email protected] "sudo mysql tilbudgivern -e 'SHOW TABLES;'" | grep -v "Tables_in" > table_list.txt
|
|
|
|
echo "Found $(wc -l < table_list.txt) tables to migrate"
|
|
|
|
# Export each table structure and data separately
|
|
echo "📤 Exporting table structures..."
|
|
ssh -p 55555 [email protected] "sudo mysqldump tilbudgivern --no-data --single-transaction --skip-triggers --skip-routines --skip-events" > structure.sql 2>/dev/null
|
|
|
|
# Clean structure file of problematic elements
|
|
sed -i 's/DEFINER=[^ ]* //g' structure.sql
|
|
|
|
echo "📦 Importing table structures..."
|
|
mysql -u "$DB_USER" -p"$DB_PASS" "$DB_NAME" < structure.sql
|
|
|
|
# Export and import data for each table
|
|
echo "📊 Migrating table data..."
|
|
failed_tables=0
|
|
success_tables=0
|
|
|
|
while read -r table; do
|
|
echo " -> Migrating $table..."
|
|
|
|
# Export table data
|
|
ssh -p 55555 [email protected] "sudo mysqldump tilbudgivern --no-create-info --skip-triggers --skip-routines --complete-insert $table" > "${table}_data.sql" 2>/dev/null
|
|
|
|
# Import table data
|
|
if mysql -u "$DB_USER" -p"$DB_PASS" "$DB_NAME" < "${table}_data.sql" 2>/dev/null; then
|
|
echo " ✅ $table migrated successfully"
|
|
((success_tables++))
|
|
else
|
|
echo " ❌ $table failed"
|
|
((failed_tables++))
|
|
fi
|
|
|
|
done < table_list.txt
|
|
|
|
echo ""
|
|
echo "📊 Migration Summary:"
|
|
echo " ✅ Successfully migrated: $success_tables tables"
|
|
echo " ❌ Failed migrations: $failed_tables tables"
|
|
|
|
# Verify final table count
|
|
local_tables=$(mysql -u "$DB_USER" -p"$DB_PASS" -e "USE $DB_NAME; SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = '$DB_NAME';" --skip-column-names)
|
|
echo " 📋 Local tables now: $local_tables"
|
|
|
|
# Show key table data counts
|
|
echo ""
|
|
echo "📈 Key Table Data Verification:"
|
|
mysql -u "$DB_USER" -p"$DB_PASS" -e "
|
|
USE $DB_NAME;
|
|
SELECT 'customer_projects' as table_name, COUNT(*) as records FROM customer_projects
|
|
UNION ALL
|
|
SELECT 'project_quotes', COUNT(*) FROM project_quotes
|
|
UNION ALL
|
|
SELECT 'bygma_products', COUNT(*) FROM bygma_products
|
|
UNION ALL
|
|
SELECT 'materials', COUNT(*) FROM materials;
|
|
" 2>/dev/null || echo "Some tables might be empty"
|
|
|
|
echo ""
|
|
if [ $failed_tables -eq 0 ]; then
|
|
echo "🎉 COMPLETE database migration successful!"
|
|
else
|
|
echo "⚠️ Migration completed with $failed_tables failed tables"
|
|
fi
|
|
|
|
echo "Database is now ready for production use!" |