How to Import and Export Databases via SSH

Managing databases is a critical part of running any web application or service. While many users rely on web-based tools like PHPMyAdmin for database management, these tools can struggle or fail when dealing with large databases due to upload limits, timeouts, or server resource restrictions.

A more robust and efficient approach is to use SSH (Secure Shell) to interact directly with your database server for importing and exporting SQL database files. This method gives you full control, better performance, and is especially useful for large databases.

This guide will cover how to securely export and import MySQL databases via SSH on your dedicated server, along with tips for downloading your backups safely.

Why Use SSH for Database Operations?

• No size limitations: Unlike PHPMyAdmin, SSH commands don’t have browser or upload size restrictions.
• Reliability: SSH commands run directly on the server, reducing failures and timeouts.
• Security: Data transfers happen over encrypted connections.
• Flexibility: Easily automate backup and restore processes with scripts and cron jobs.

Exporting a Database via SSH

Exporting a database means creating a SQL dump file that contains all your database tables, structures, and data. This dump file can be used as a backup or transferred to another server.

Step-by-step guide:

1. Connect to your dedicated server via SSH
   Use a terminal or SSH client (e.g., PuTTY for Windows, Terminal on macOS/Linux).
   • <username>: Your MySQL username.
   • <host>: The MySQL host, often localhost if the database runs on the same server.
   • <database_name>: The database you want to export.
   • backup.sql: The output file name for your exported database.
2. Enter your MySQL password when prompted. The dump will begin, and the terminal may show no output until completion.

Verify the dump file is created by listing files:

ls -lh backup.sql

Run the mysqldump command:

mysqldump -u <username> -h <host> -p <database_name> > backup.sql

Importing a Database via SSH

Importing a database involves taking an existing SQL dump file and restoring its contents to a MySQL database.

Step-by-step guide:

1. Connect to your server via SSH (link above if needed).
2. Enter your MySQL password when prompted.
3. The import will start and may take time depending on the database size. After completion, your database will be restored.

Run the mysql command:

mysql -u <username> -h <host> -p <database_name> < backup.sql

Replace placeholders with your actual details.

Handling Large Databases: Compression

For very large databases, transferring raw SQL files can be slow and resource-intensive. Compressing the dump file is a best practice.

Export with gzip compression:

mysqldump -u <username> -h <host> -p <database_name> | gzip > backup.sql.gz

Import compressed SQL dump:

gunzip < backup.sql.gz | mysql -u <username> -p <database_name>

Downloading Database Dumps via SFTP

Once you have your database dump file on the server, you may want to download it to your local machine for safekeeping.

How to download:

1. Use an SFTP client like FileZilla.
2. Connect using your server credentials:
   • Host: sftp://<server-ip>
   • Username: your SSH username (commonly root)
   • Password: your SSH password
3. Navigate to the directory containing the dump file (e.g., /root or where you ran mysqldump).
4. Drag and drop the .sql or .sql.gz file to your local computer.

Automating Backups with Cron Jobs

To avoid manual exports and ensure regular backups, you can automate database dumps using cron jobs.

Example cron job to dump a database daily at 2 AM:

0 2 * * * /usr/bin/mysqldump -u <username> -p<password> <database_name> > /path/to/backups/backup_$(date +\%F).sql

Be cautious with passwords in scripts; consider using .my.cnf files for safer authentication.

Troubleshooting Tips

• Permission Denied: Ensure your MySQL user has sufficient privileges for exporting/importing databases.
• Connection Errors: Verify your MySQL host and credentials.
• File Location: Remember where you saved your dump file to locate it later.
• Timeouts: Large imports/exports over SSH may take time; be patient or use screen/tmux sessions to keep your SSH session alive.

Summary

Using SSH to export and import databases is a powerful alternative to web-based tools, offering better reliability and control especially for large or critical databases. Combined with SFTP for secure file transfer and cron jobs for automation, it forms the backbone of professional database management.

For assistance or to host your databases on reliable servers with expert support, check out EUGameHost’s dedicated server and VPS solutions.