Mysql SSH Migrator

readme.md

## Prerequisites

Before running the script, you must ensure the two servers can talk to each other and the destination database user exists.

### 1. Setup SSH Key Authentication
The "Sender" (Source) server needs to SSH into the "Receiver" (Destination) server without a password prompt.

* **On the Sender Server:**
  Generate an SSH key if you haven't already:
  ```bash
  ssh-keygen -t rsa -b 4096

(Press Enter through all prompts)

Copy the public key to the Receiver server (replace placeholders with actual values):

# Syntax: ssh-copy-id <remote_user>@<remote_ip>
ssh-copy-id [email protected]

(You will be asked for the remote user's password one last time)

• Test the connection:

ssh [email protected]

(If you log in without a password, it's working)

2. Setup Destination Database User

The script attempts to create the database structure, but the Database User must typically already exist on the Receiver server for authentication to work initially.

• On the Receiver Server (or via Ploi/Forge Panel): Create a new database user.
• Username: (e.g., my_app)
• Password: (Keep this handy; you will paste it into the script prompt)
• Privileges: Ensure this user has ALL PRIVILEGES on their own database (or specifically CREATE, INSERT, SELECT, UPDATE, DELETE).

Usage

1. Edit the Configuration: Open mysql-ssh-migrator.sh and update the following variables at the top:

• SOURCE_USER / SOURCE_PASS: Your local database credentials.
• DEST_SSH_USER: The remote system user (e.g., ploi, root, or ubuntu).
• DEST_SSH_HOST: The remote server IP address.

2. Make Executable:

chmod +x mysql-ssh-migrator.sh

3. Run:

./mysql-ssh-migrator.sh

4. Follow Prompts:

• Source DB Name: The name of the DB on the server you are running the script on.
• Destination User: The username you created in step 2 (the script assumes DB Name = Username).

mysql-ssh-migrator.sh

#!/bin/bash

# -----------------------------
# CONFIGURATION
# -----------------------------
# Source Database Credentials (Local/Sender)
SOURCE_HOST="localhost"
SOURCE_USER="your_source_db_user"       # CHANGE THIS
SOURCE_PASS="your_source_db_password"   # CHANGE THIS

# Destination SSH Configuration
DEST_SSH_USER="host"                    # CHANGE THIS
DEST_SSH_HOST="192.168.1.20"          # CHANGE THIS if needed
DEST_SSH="${DEST_SSH_USER}@${DEST_SSH_HOST}"

# -----------------------------
# INPUTS
# -----------------------------
echo "--- Database Migration Tool ---"
read -p "Enter source database name to migrate: " SRC_DB
read -p "Enter destination MariaDB username (also used as DB name): " DEST_USER
read -s -p "Enter destination MariaDB password: " DEST_PASS
echo ""

DEST_DB="$DEST_USER"

# -----------------------------
# TEMP FILE
# -----------------------------
TMP_DUMP="/tmp/${SRC_DB}_dump_$(date +%s).sql"

# -----------------------------
# MIGRATION PROCESS
# -----------------------------
echo "[1/4] Dumping database '$SRC_DB' from source..."
# Added --routines --triggers to ensure full DB capture
mysqldump -h "$SOURCE_HOST" -u "$SOURCE_USER" -p"$SOURCE_PASS" \
  "$SRC_DB" --single-transaction --quick --lock-tables=false --routines --triggers > "$TMP_DUMP"

if [ $? -ne 0 ]; then
    echo "❌ Error: Dump failed. Check source credentials or database name."
    rm -f "$TMP_DUMP"
    exit 1
fi

echo "[2/4] Transferring dump to destination via SSH..."
scp "$TMP_DUMP" "$DEST_SSH:/tmp/"

if [ $? -ne 0 ]; then
    echo "❌ Error: File transfer failed. Check SSH connection/keys."
    rm -f "$TMP_DUMP"
    exit 1
fi

REMOTE_DUMP="/tmp/$(basename "$TMP_DUMP")"

echo "[3/4] Creating destination database '$DEST_DB' (if missing)..."
# Using 'mariadb' or 'mysql' depending on availability. 
# The remote user needs CREATE privileges for this step to work automatically.
ssh "$DEST_SSH" "mariadb -h 127.0.0.1 -u$DEST_USER -p'$DEST_PASS' -e 'CREATE DATABASE IF NOT EXISTS \`$DEST_DB\`;'" 

if [ $? -ne 0 ]; then
    echo "⚠️ Warning: Database creation step might have failed (or DB exists)."
fi

echo "[4/4] Importing dump into destination..."
ssh "$DEST_SSH" "mariadb -h 127.0.0.1 -u$DEST_USER -p'$DEST_PASS' $DEST_DB < $REMOTE_DUMP && rm -f $REMOTE_DUMP"

if [ $? -eq 0 ]; then
    echo "✅ Migration completed successfully!"
else
    echo "❌ Error: Import failed on destination."
fi

# -----------------------------
# CLEANUP
# -----------------------------
rm -f "$TMP_DUMP"