How to Restore a SQL Database in cPanel Using Terminal

Restoring a SQL database via the cPanel terminal (SSH) is faster, more reliable, and handles large .sql backup files that phpMyAdmin often rejects due to upload limits. This step-by-step guide walks you through the entire process — from creating the database in cPanel to importing your backup in seconds.

Why Use the Terminal Instead of phpMyAdmin?

• No file size limits — phpMyAdmin blocks uploads above ~50 MB by default.
• Speed — A direct MySQL import is significantly faster than a browser upload.
• Reliability — No browser timeouts on large databases.
• Full control — You can monitor progress, pipe compressed files, and handle errors inline.

Prerequisites

Before you begin, make sure you have:

• cPanel login credentials
• SSH access enabled on your hosting plan
• A .sql backup file (locally or already on the server)
• An SSH client (Terminal on Mac/Linux, PuTTY or Windows Terminal on Windows)

Step 1 — Create a Database and User in cPanel

You must create the target database and assign a user with full privileges before importing any data.

1.1 Create the Database

1. Log in to cPanel.
2. Go to Databases → MySQL Databases.
3. Under Create New Database, type a name (e.g., mysite_db) and click Create Database.

Note: cPanel automatically prefixes the name with your cPanel username.
Example: username_mysite_db

1.2 Create a Database User

1. Scroll to MySQL Users → Add New User.
2. Enter a username (e.g., mysite_user) and a strong password.
3. Click Create User.

1.3 Assign the User to the Database

1. Under Add User to Database, select your new user and database.
2. Click Add, then tick ALL PRIVILEGES.
3. Click Make Changes.

Step 2 — Connect via SSH (Terminal)

Open your terminal and connect to your server using SSH:

ssh username@yourdomain.com -p 22

Replace username with your cPanel username and yourdomain.com with your server's hostname or IP. If your host uses a non-standard port, replace 22 accordingly.

Enter your SSH password when prompted. You are now inside your server's shell.

Step 3 — Upload Your SQL Backup File

If your .sql file is on your local machine, upload it first. Open a new terminal window on your local computer and run:

scp /path/to/backup.sql username@yourdomain.com:~/backup.sql

This copies the file to your home directory (~/) on the server. Alternatively, use the cPanel File Manager to upload the file.

Uploading a Compressed (.gz) Backup

If your backup is gzip-compressed, upload it as-is:

scp /path/to/backup.sql.gz username@yourdomain.com:~/backup.sql.gz

Step 4 — Restore the Database via Terminal

Switch back to your SSH session. Navigate to where the file was uploaded:

cd ~
ls -lh

You should see backup.sql (or backup.sql.gz) listed.

4.1 Import a Plain .sql File

Run the following command to import the database:

mysql -u username_mysite_user -p username_mysite_db < ~/backup.sql

• username_mysite_user — your full cPanel database username
• username_mysite_db — your full cPanel database name
• ~/backup.sql — path to your SQL backup file

When prompted, enter the database user password. The import will run silently — no output means success.

4.2 Import a Compressed .sql.gz File (Without Extracting)

You can import a gzipped file directly without decompressing it first:

gunzip < ~/backup.sql.gz | mysql -u username_mysite_user -p username_mysite_db

This saves disk space and time, especially for large backups.

4.3 Restore with mysqldump Compatibility

If the backup was created with mysqldump, the standard import command works perfectly:

mysql -h localhost -u username_mysite_user -p username_mysite_db < ~/backup.sql

Step 5 — Verify the Restore

Log in to MySQL to confirm the data was imported correctly:

mysql -u username_mysite_user -p username_mysite_db

Then run:

SHOW TABLES;
SELECT COUNT(*) FROM your_table_name;
EXIT;

You should see all your tables listed and the correct row counts.

Alternatively, log in to cPanel → phpMyAdmin, select your database, and browse the tables visually.

Common Errors & Fixes

Error: Access Denied

ERROR 1045 (28000): Access denied for user

Fix: Double-check the database username, password, and that the user is assigned to the database with ALL PRIVILEGES in cPanel.

Error: Unknown Database

ERROR 1049 (42000): Unknown database 'username_mysite_db'

Fix: The database name is incorrect or the database was not created yet. Go back to Step 1 and create it in cPanel.

Error: Got a packet bigger than 'max_allowed_packet'

Fix: Run the import with an increased packet size:

mysql --max_allowed_packet=512M -u username_mysite_user -p username_mysite_db < ~/backup.sql

Permission Denied on the .sql File

Fix: Make sure the file is readable:

chmod 644 ~/backup.sql

Quick Reference — Full Command Cheat Sheet

Task	Command
Connect via SSH	ssh user@domain.com
Upload .sql via SCP	scp backup.sql user@domain.com:~/
Import .sql file	mysql -u db_user -p db_name < backup.sql
Import .sql.gz file	gunzip < backup.sql.gz | mysql -u db_user -p db_name
Verify tables	mysql -u db_user -p db_name -e "SHOW TABLES;"

Tips for a Smooth Restore

• Always back up the current database before overwriting it with a restore.
• Use screen or nohup for very large imports so the session doesn't time out:
  nohup mysql -u db_user -p db_name < backup.sql &
• Keep your backup files in your home directory (~/), not inside public_html, to prevent public access.
• Delete backup files from the server once the restore is confirmed.

That's it! Restoring a SQL database via cPanel's terminal is the most efficient and reliable method for any database size. If you're on a shared hosting plan without SSH access, contact our support team — we'll help you get it set up.