108 lines
3.4 KiB
Bash
Executable File
108 lines
3.4 KiB
Bash
Executable File
#!/bin/bash
|
|
|
|
# Final Database Migration Script
|
|
set -e
|
|
|
|
# Database credentials from .env
|
|
DB_USER="tilbudgivern_service"
|
|
DB_PASS="REDACTED_PASSWORD"
|
|
DB_NAME="tilbudgivern"
|
|
|
|
echo "🔄 Starting database migration from s.alw.dk..."
|
|
|
|
# Test SSH and database 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/db_backup
|
|
cd /tmp/db_backup
|
|
|
|
echo "📥 Exporting database from remote server..."
|
|
|
|
# Get just the data (no structure, no triggers, no views)
|
|
ssh -p 55555 [email protected] "
|
|
sudo mysqldump tilbudgivern \
|
|
--no-create-info \
|
|
--single-transaction \
|
|
--skip-triggers \
|
|
--skip-routines \
|
|
--skip-add-locks \
|
|
--complete-insert \
|
|
--ignore-table=tilbudgivern.bygma_current_prices \
|
|
--ignore-table=tilbudgivern.v_bygma_current_prices \
|
|
--ignore-table=tilbudgivern.v_bygma_import_stats \
|
|
--ignore-table=tilbudgivern.v_bygma_price_changes \
|
|
--ignore-table=tilbudgivern.v_enhanced_quotes \
|
|
--ignore-table=tilbudgivern.package_overview
|
|
" > tilbudgivern_data_only.sql
|
|
|
|
# Get structure separately (without problematic views)
|
|
ssh -p 55555 [email protected] "
|
|
sudo mysql tilbudgivern -e \"
|
|
SHOW CREATE TABLE customer_projects;
|
|
SHOW CREATE TABLE project_quotes;
|
|
SHOW CREATE TABLE project_materials;
|
|
SHOW CREATE TABLE bygma_products;
|
|
SHOW CREATE TABLE bygma_import_log;
|
|
\" | sed 's/\`//g'
|
|
" > basic_structure.txt 2>/dev/null || echo "Structure export completed"
|
|
|
|
# Check export
|
|
if [ -s tilbudgivern_data_only.sql ]; then
|
|
echo "✅ Database exported successfully ($(du -h tilbudgivern_data_only.sql | cut -f1))"
|
|
else
|
|
echo "❌ Database export failed"
|
|
exit 1
|
|
fi
|
|
|
|
echo "📤 Importing to local database..."
|
|
|
|
# Drop and recreate database
|
|
mysql -u "$DB_USER" -p"$DB_PASS" -e "DROP DATABASE IF EXISTS $DB_NAME; CREATE DATABASE $DB_NAME CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
|
|
|
|
# Create basic schema
|
|
echo "Creating basic schema..."
|
|
mysql -u "$DB_USER" -p"$DB_PASS" "$DB_NAME" < /mnt/HC_Volume_103713257/tilbudgivern/setup_enhanced_features.sql 2>/dev/null || echo "Some schema items failed (expected)"
|
|
|
|
# Import data
|
|
echo "Importing data..."
|
|
mysql -u "$DB_USER" -p"$DB_PASS" "$DB_NAME" < tilbudgivern_data_only.sql
|
|
|
|
if [ $? -eq 0 ]; then
|
|
echo "✅ Database migration completed successfully!"
|
|
|
|
# Show statistics
|
|
echo "📊 Database statistics:"
|
|
mysql -u "$DB_USER" -p"$DB_PASS" -e "
|
|
USE $DB_NAME;
|
|
SELECT COUNT(*) as 'Total Tables' FROM information_schema.tables WHERE table_schema = '$DB_NAME';
|
|
"
|
|
|
|
# Show data counts for key tables
|
|
mysql -u "$DB_USER" -p"$DB_PASS" -e "
|
|
USE $DB_NAME;
|
|
SELECT
|
|
'customer_projects' as table_name,
|
|
COALESCE(COUNT(*), 0) as records
|
|
FROM information_schema.tables t
|
|
LEFT JOIN customer_projects cp ON t.table_name = 'customer_projects'
|
|
WHERE t.table_schema = '$DB_NAME' AND t.table_name = 'customer_projects'
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
'bygma_products' as table_name,
|
|
COALESCE(COUNT(*), 0) as records
|
|
FROM information_schema.tables t
|
|
LEFT JOIN bygma_products bp ON t.table_name = 'bygma_products'
|
|
WHERE t.table_schema = '$DB_NAME' AND t.table_name = 'bygma_products';
|
|
" 2>/dev/null || echo "Data count completed"
|
|
|
|
else
|
|
echo "❌ Database import failed"
|
|
exit 1
|
|
fi
|
|
|
|
echo "🎉 Migration complete!" |