Migrating a MySQL database between servers doesn’t have to be complex—especially when you use command-line tools. Whether you’re shifting to a new server, optimizing infrastructure, or backing up production data, this guide from ServerAdminz walks you through a reliable and secure migration process using mysqldump, scp, and SSH.

Prerequisites

Before you begin, ensure the following:

  • SSH access to both source and destination servers
  • MySQL installed and configured on both servers
  • Root or database-level privileges
  • Knowledge of the SSH port (if custom)
  • Sufficient disk space on the target server

Step 1: Export the Database Using mysqldump

Start on your source server and generate a SQL dump of your database:

mysqldump -u root -p epcenergy_ESS_SanJose > epcenergy_ESS_SanJose.sql

  • Replace epcenergy_ESS_SanJose with your database name.
  • The -p flag prompts you to enter the MySQL root password.
  • This creates a .sql dump file in your current directory.

ServerAdminz Tip:
Use the –single-transaction flag with InnoDB for a more consistent dump without locking tables.

Step 2: Transfer the SQL File Using scp

Use scp to securely transfer the dump file to your destination server:

scp -P 5555 epcenergy_ESS_SanJose.sql root@162.216.5.214:/home/epcenergy/public_html

  • Replace 5555 with your actual SSH port.
  • Replace root@162.216.5.214 with your destination server credentials.
  • Ensure /home/epcenergy/public_html exists and is writable.

Security Best Practice:
 Always prefer SSH key authentication over passwords for production environments.

Step 3: Import the SQL File on the Destination Server

SSH into your destination server:

ssh -p 5555 root@162.216.5.214

Before importing, make sure the target database exists:

mysql -u root -p -e “CREATE DATABASE epcenergy_ESS_SanJose;”

Then import the SQL file:

mysql -u root -p epcenergy_ESS_SanJose < /home/epcenergy/public_html/epcenergy_ESS_SanJose.sql

This restores your database to the new server

ServerAdminz Checklist

  • Database exported with mysqldump
  • SQL file transferred via scp
    Target database created
  • SQL file successfully imported

Bonus: Use Compression for Faster Transfers

For large databases, compress your dump before transferring:

mysqldump -u root -p epcenergy_ESS_SanJose | gzip > epcenergy_ESS_SanJose.sql.gz

scp -P 5555 epcenergy_ESS_SanJose.sql.gz root@destination:/path/

gunzip epcenergy_ESS_SanJose.sql.gz

Then import the uncompressed file as usual.

Final Thoughts

At ServerAdminz, we understand how critical it is to perform database migrations with zero downtime and no data loss. This command-line method is reliable, efficient, and perfect for developers and sysadmins alike. For complex migrations or 24/7 support, our team is just a click away.

Need a deeper understanding of MySQL syntax and usage? Head over to our blog on MySQL – Basic Commands in MySQL for quick command-line reference tips.

Want us to handle your database migration? Contact ServerAdminz for expert assistance.