🗃 How to Transfer a Database Between VPS Servers

When moving a website or project to a new VPS, it’s crucial to properly transfer your database. Mistakes can cause data loss or service downtime. This guide explains safe ways to migrate MySQL and PostgreSQL databases.

📦 Method 1: MySQL/MariaDB Transfer via Dump

• 📤 On the old server, run:
  mysqldump -u root -p dbname > dump.sql
• 📁 Copy the dump to the new VPS:
  scp dump.sql user@new-server:/home/user/
• 📥 Import on the new server:
  mysql -u root -p dbname < dump.sql
• ✅ Verify that all data and structure are restored correctly

🐘 Method 2: PostgreSQL Database Transfer

• 📤 Create the dump:
  pg_dump -U postgres dbname > db.sql
• 📁 Copy the file to the new VPS:
  scp db.sql user@new-vps:/home/user/
• 📥 Import the dump:
  psql -U postgres dbname < db.sql
• 🛠 Make sure the database and user exist before importing

🔄 Alternative: Transfer Physical Files via rsync

• ⚠ Only use if both servers have the same DB version
• 🧊 Stop the database:
  systemctl stop mysql or systemctl stop postgresql
• 📦 Copy files:
  rsync -avz /var/lib/mysql/ user@new-vps:/var/lib/mysql/
• 🔓 Ensure correct permissions and ownership
• ▶ Start the database and check logs for issues

📌 Best Practices

• 🕒 Always make a backup before migration
• 🔐 Avoid using insecure protocols to transfer dump files
• 📤 Ensure character sets match between source and destination
• 🧩 Check CMS or application compatibility with DB version