How to Export MySQL DataBase and Restore Using SSH?

If you are looking to export a MySQL database and restore it via SSH, the easiest and most efficient way to do so is by using the mysqldump command to create a backup file and then using the mysql command to import the file into a new database. This process is secure, efficient, and perfect for large databases, especially when working with live production servers.

In this comprehensive guide, you’ll learn how to export a MySQL database using SSH, transfer it if needed, and restore it safely on another server.

What Is MySQL and Why Use SSH?

One of the most widely used relational database management systems worldwide is MySQL. It powers platforms like WordPress, Magento, and Shopify (in certain integrations).

Using SSH (Secure Shell) provides a secure, encrypted connection to your server. Unlike web-based tools like phpMyAdmin, SSH is:

• Faster for large databases
• More reliable for production servers
• More secure
• Ideal for automated backups

If you manage VPS, dedicated servers, or cloud hosting, SSH is the professional method.

Step 1: Connect to Your Server via SSH

First, log in to your server using SSH.

Bash
ssh username@your-server-ip

Example:

Bash
ssh [email protected]

If you are using a custom port:

Bash
ssh -p 2222 username@your-server-ip

After entering your password (or using SSH keys), you will gain access to your server terminal.

Step 2: Export (Backup) MySQL Database Using mysqldump

The mysqldump command is used to export databases into a .sql file.

Basic Syntax

Bash
mysqldump -u db_user -p db_name > backup.sql

After pressing Enter, it will ask for your database password.

Example

Bash
mysqldump -u root -p mydatabase > mydatabase_backup.sql

This creates a file called:

Code
mydatabase_backup.sql

in your current directory.

Export a Database with Specific Host

If your database runs on a different host:

Bash
mysqldump -h localhost -u db_user -p db_name > backup.sql

Export All Databases

Bash
mysqldump -u root -p --all-databases > all_databases_backup.sql

Export with Compression (Recommended for Large Databases)

For large databases, compress the file instantly:

Bash
mysqldump -u root -p mydatabase | gzip > mydatabase.sql.gz

This saves space and speeds up transfers.

Step 3: Download the Backup File (If Needed)

If you’re migrating to another server, download the file to your local computer using SCP:

Bash
scp username@server-ip:/path/to/backup.sql .

Example:

Bash
scp [email protected]:/root/mydatabase_backup.sql .

Step 4: Upload Backup to New Server

To restore on another server, upload the backup file:

Bash
scp backup.sql username@new-server-ip:/home/username/

Step 5: Restore MySQL Database Using SSH

Before restoring, make sure the new database exists.

Create a New Database

Login to MySQL:

Bash
mysql -u root -p

Then run:

SQL
CREATE DATABASE newdatabase;
EXIT;

Restore Database from .sql File

Basic restore command:

Bash
mysql -u db_user -p newdatabase < backup.sql

Example:

Bash
mysql -u root -p mydatabase < mydatabase_backup.sql

Restore Compressed Backup

If your file is compressed:

Bash
gunzip < mydatabase.sql.gz | mysql -u root -p mydatabase

Step 6: Verify the Restoration

Login to MySQL:

Bash
mysql -u root -p

Select database:

SQL
USE mydatabase;
SHOW TABLES;

If tables appear, the restoration was successful.

Advanced mysqldump Options (For Professionals)

Export Without Locking Tables (For Live Sites)

Bash
mysqldump -u root -p --single-transaction --quick mydatabase > backup.sql

This prevents downtime for InnoDB tables.

Export Only Specific Tables

Bash
mysqldump -u root -p mydatabase table1 table2 > tables_backup.sql

Export-Structure Only (No Data)

Bash
mysqldump -u root -p --no-data mydatabase > structure.sql

Export Data Only (No Structure)

Bash
mysqldump -u root -p --no-create-info mydatabase > data.sql

Common Errors and Fixes

1. Access Denied Error

If you see:

Code
Access denied for user

Make sure:

• Username is correct
• Password is correct
• User has proper privileges

Grant privileges if needed:

SQL
GRANT ALL PRIVILEGES ON mydatabase.* TO 'db_user'@'localhost';
FLUSH PRIVILEGES;

2. Max Allowed Packet Error

If restoring fails due to packet size:

Edit MySQL configuration file:

Code
/etc/my.cnf

Add:

Code
max_allowed_packet=512M

Restart MySQL.

Best Practices for Database Backup and Restore

✔ Always test restore on staging before production
✔ Keep multiple backup copies
✔ Use cron jobs for automatic backups
✔ Encrypt backup files if sensitive data exists
✔ Store backups offsite (cloud storage recommended)

Automating Daily Backups with Cron

Example cron job:

Bash
0 2 * * * mysqldump -u root -p'yourpassword' mydatabase > /backup/mydatabase_$(date +\%F).sql

This runs daily at 2 AM.

Note: Avoid writing passwords directly in scripts. Use .my.cnf file for better security.

Why Use SSH Instead of phpMyAdmin?

For professional server management, SSH is the industry standard.

Final Thoughts

Exporting and restoring a MySQL database using SSH is the most efficient and secure method for server administrators, developers, and website owners. By using mysqldump for export and mysql for restoration, you gain full control over backups, migrations, and disaster recovery processes.

Whether you’re migrating a website, moving to a new VPS, or simply creating regular backups, mastering SSH-based database management will save time, prevent data loss, and ensure smooth server operations.

If you’re running ecommerce stores, WordPress websites, or SaaS platforms, this method is not just recommended — it’s essential.