157 lines
4.7 KiB
Bash
Executable File
157 lines
4.7 KiB
Bash
Executable File
#!/bin/bash
|
||
|
||
# Database Migration Script for Tilbudgivern
|
||
# Migrates database from s.alw.dk to local instance
|
||
# Author: GitHub Copilot
|
||
# Date: $(date)
|
||
|
||
set -e
|
||
|
||
# Colors for output
|
||
RED='\033[0;31m'
|
||
GREEN='\033[0;32m'
|
||
YELLOW='\033[1;33m'
|
||
BLUE='\033[0;34m'
|
||
NC='\033[0m' # No Color
|
||
|
||
# Configuration
|
||
REMOTE_HOST="s.alw.dk"
|
||
REMOTE_PORT="55555"
|
||
REMOTE_USER="alex"
|
||
REMOTE_DB_NAME="tilbudgivern"
|
||
REMOTE_DB_USER="tilbuduser"
|
||
REMOTE_DB_PASS="tilbudpass123"
|
||
|
||
LOCAL_DB_NAME="tilbudgivern"
|
||
LOCAL_DB_USER="tilbuduser"
|
||
LOCAL_DB_PASS="tilbudpass123"
|
||
|
||
BACKUP_DIR="/tmp/tilbudgivern_migration"
|
||
BACKUP_FILE="tilbudgivern_backup_$(date +%Y%m%d_%H%M%S).sql"
|
||
|
||
error() {
|
||
echo -e "${RED}❌ ERROR: $1${NC}"
|
||
exit 1
|
||
}
|
||
|
||
success() {
|
||
echo -e "${GREEN}✅ $1${NC}"
|
||
}
|
||
|
||
warning() {
|
||
echo -e "${YELLOW}⚠️ $1${NC}"
|
||
}
|
||
|
||
info() {
|
||
echo -e "${BLUE}ℹ️ $1${NC}"
|
||
}
|
||
|
||
# Create backup directory
|
||
info "Step 1: Creating backup directory..."
|
||
mkdir -p "$BACKUP_DIR"
|
||
success "Backup directory created at $BACKUP_DIR"
|
||
|
||
# Test local database connection
|
||
info "Step 2: Testing local database connection..."
|
||
if mysql -u "$LOCAL_DB_USER" -p"$LOCAL_DB_PASS" -e "USE $LOCAL_DB_NAME; SELECT 'Local DB OK' as status;" > /dev/null 2>&1; then
|
||
success "Local database connection OK"
|
||
else
|
||
error "Local database connection failed. Please check credentials."
|
||
fi
|
||
|
||
# Test SSH connection
|
||
info "Step 3: Testing SSH connection to remote server..."
|
||
if ssh -p "$REMOTE_PORT" "$REMOTE_USER@$REMOTE_HOST" "echo 'SSH OK'" > /dev/null 2>&1; then
|
||
success "SSH connection to remote server OK"
|
||
else
|
||
error "SSH connection failed. Please check SSH credentials and connectivity."
|
||
fi
|
||
|
||
# Export database from remote server
|
||
info "Step 4: Exporting database from remote server..."
|
||
ssh -p "$REMOTE_PORT" "$REMOTE_USER@$REMOTE_HOST" "
|
||
sudo mysqldump '$REMOTE_DB_NAME' \
|
||
--single-transaction \
|
||
--routines \
|
||
--triggers \
|
||
--hex-blob \
|
||
--default-character-set=utf8mb4 \
|
||
--add-drop-table \
|
||
--complete-insert
|
||
" > "$BACKUP_DIR/$BACKUP_FILE" 2>/dev/null
|
||
|
||
if [ -f "$BACKUP_DIR/$BACKUP_FILE" ] && [ -s "$BACKUP_DIR/$BACKUP_FILE" ]; then
|
||
BACKUP_SIZE=$(du -h "$BACKUP_DIR/$BACKUP_FILE" | cut -f1)
|
||
success "Database exported successfully ($BACKUP_SIZE)"
|
||
else
|
||
error "Database export failed or file is empty"
|
||
fi
|
||
|
||
# Show backup file info
|
||
info "Backup file details:"
|
||
echo " Location: $BACKUP_DIR/$BACKUP_FILE"
|
||
echo " Size: $(du -h "$BACKUP_DIR/$BACKUP_FILE" | cut -f1)"
|
||
echo " Lines: $(wc -l < "$BACKUP_DIR/$BACKUP_FILE")"
|
||
|
||
# Ask for confirmation before importing
|
||
echo ""
|
||
warning "About to import database to local MySQL instance."
|
||
warning "This will REPLACE all existing data in the local '$LOCAL_DB_NAME' database!"
|
||
read -p "Are you sure you want to continue? (yes/no): " confirm
|
||
|
||
if [ "$confirm" != "yes" ]; then
|
||
info "Migration cancelled by user"
|
||
exit 0
|
||
fi
|
||
|
||
# Create local backup first
|
||
info "Step 5: Creating local database backup before migration..."
|
||
LOCAL_BACKUP_FILE="local_backup_before_migration_$(date +%Y%m%d_%H%M%S).sql"
|
||
mysqldump -u "$LOCAL_DB_USER" -p"$LOCAL_DB_PASS" "$LOCAL_DB_NAME" > "$BACKUP_DIR/$LOCAL_BACKUP_FILE" 2>/dev/null || warning "Local backup failed (database might be empty)"
|
||
|
||
# Import database to local server
|
||
info "Step 6: Importing database to local server..."
|
||
mysql -u "$LOCAL_DB_USER" -p"$LOCAL_DB_PASS" "$LOCAL_DB_NAME" < "$BACKUP_DIR/$BACKUP_FILE"
|
||
|
||
if [ $? -eq 0 ]; then
|
||
success "Database imported successfully!"
|
||
else
|
||
error "Database import failed"
|
||
fi
|
||
|
||
# Verify import
|
||
info "Step 7: Verifying database import..."
|
||
TABLE_COUNT=$(mysql -u "$LOCAL_DB_USER" -p"$LOCAL_DB_PASS" -e "USE $LOCAL_DB_NAME; SHOW TABLES;" 2>/dev/null | wc -l)
|
||
if [ "$TABLE_COUNT" -gt 1 ]; then
|
||
success "Database verification OK - Found $(($TABLE_COUNT - 1)) tables"
|
||
|
||
# Show some basic stats
|
||
echo ""
|
||
info "Database import summary:"
|
||
mysql -u "$LOCAL_DB_USER" -p"$LOCAL_DB_PASS" -e "
|
||
USE $LOCAL_DB_NAME;
|
||
SELECT 'customer_projects' as table_name, COUNT(*) as record_count FROM customer_projects
|
||
UNION ALL
|
||
SELECT 'project_quotes', COUNT(*) FROM project_quotes
|
||
UNION ALL
|
||
SELECT 'project_materials', COUNT(*) FROM project_materials
|
||
UNION ALL
|
||
SELECT 'bygma_prisbog', COUNT(*) FROM bygma_prisbog;
|
||
" 2>/dev/null || info "Some tables might not exist yet"
|
||
else
|
||
error "Database verification failed - No tables found"
|
||
fi
|
||
|
||
# Clean up (optional)
|
||
read -p "Do you want to keep the backup files? (yes/no): " keep_backup
|
||
if [ "$keep_backup" != "yes" ]; then
|
||
rm -f "$BACKUP_DIR/$BACKUP_FILE"
|
||
rm -f "$BACKUP_DIR/$LOCAL_BACKUP_FILE" 2>/dev/null
|
||
info "Backup files cleaned up"
|
||
else
|
||
info "Backup files kept in: $BACKUP_DIR"
|
||
fi
|
||
|
||
echo ""
|
||
success "Database migration completed successfully!"
|
||
info "Local database '$LOCAL_DB_NAME' now contains data from remote server" |