Importing an SQL dump to a remote server

Below is the standard, reliable command-line approach used by system administrators to import a MariaDB (or MySQL-compatible) SQL dump from a local Linux machine into a remote MariaDB server.

---

Method 1: Import directly over the network (no manual upload)

This is the simplest and most common approach.

Basic syntax

mysql -h remote_host -u db_user -p db_name < dump.sql

Example

mysql -h db.example.com -u myuser -p mydatabase < backup.sql

• -h remote_host — hostname or IP of the remote server
• -u db_user — database username
• -p — prompts for password
• db_name — target database (must already exist)
• dump.sql — SQL file on your local machine

The SQL file is streamed over the network directly into MariaDB.

---

Method 2: Specify port or socket (if non-standard)

If the remote server uses a non-default port:

mysql -h db.example.com -P 3307 -u myuser -p mydatabase < backup.sql

Default port is 3306.

---

Method 3: Using compression (recommended for large dumps)

For large SQL files, compressing during transfer is significantly faster.

gzip -c dump.sql | mysql -h db.example.com -u myuser -p mydatabase

Or if the file is already compressed:

gunzip < dump.sql.gz | mysql -h db.example.com -u myuser -p mydatabase

---

Method 4: Import via SSH on the remote server

If the database does not accept remote connections, you can pipe the file through SSH:

cat dump.sql | ssh user@remote_server \
  "mysql -u db_user -p db_name"

You will be prompted for:

• SSH password (or key)
• MariaDB password (on the remote server)

Compressed version:

gzip -c dump.sql | ssh user@remote_server \
  "gunzip | mysql -u db_user -p db_name"

This is common on shared hosting or locked-down servers.

---

Method 5: Upload first, then import (classic approach)

1. Upload the file:

scp dump.sql user@remote_server:/home/user/

2. SSH into the server:

ssh user@remote_server

3. Import:

mysql -u db_user -p db_name < dump.sql

---

Common issues and solutions

1. “Access denied for user …”

• Ensure the user has permission from your IP:

GRANT ALL PRIVILEGES ON db_name.* TO 'user'@'your_ip';
FLUSH PRIVILEGES;

2. “Unknown database”

Create it first:

mysql -h remote_host -u user -p -e "CREATE DATABASE db_name;"

3. “MySQL server has gone away”

Use larger packet size:

mysql --max_allowed_packet=1G -h remote_host -u user -p db_name < dump.sql

---

Best practice checklist

• Confirm the database exists
• Confirm user has remote access
• Use compression for large dumps
• Avoid --force unless you want to ignore errors
• Test with a small dump first