Restore a MySQL Database from a mysqldump File

To restore a MySQL database from a mysqldump file, create the target database and pipe the dump into the mysql client — for example mysql -u root -p appdb < appdb.sql. The whole job takes a few minutes on a small VPS, and this guide walks through preparing the file, importing it safely and confirming the data landed correctly.

You need a Linux VPS with MySQL or MariaDB running, root or sudo access, and the dump file itself (usually .sql or .sql.gz). The steps below work the same on Ubuntu 22.04/24.04, Debian 12 and AlmaLinux or Rocky 8–9.

Before you start

Gather these first:

• The dump file, for example appdb-2026-01-01.sql or appdb-2026-01-01.sql.gz.
• An account that can create databases and read the dump — root via sudo mysql, or a user with CREATE, INSERT, DROP and ALTER privileges on the target database.
• Free disk space at least twice the size of the dump. The import writes data plus binlogs and temporary files.
• A note of the source server's MySQL or MariaDB version, if you have it. Restoring a dump from a newer major version into an older server can fail on syntax the old server does not understand.

Check the server is up and you can log in:

sudo systemctl status mysql
mysql --version
sudo mysql -e "SHOW DATABASES;"

On MariaDB the service is often mariadb instead of mysql. Both commands work either way.

Put the dump file on the server

If the file is on your laptop, copy it up with SFTP or scp:

scp appdb-2026-01-01.sql.gz deploy@203.0.113.10:/home/deploy/

For key-based transfers and client setup, see secure file transfer with SFTP over SSH keys. On the server, move the file somewhere tidy and check it is not truncated:

sudo mkdir -p /var/backups/mysql
sudo mv /home/deploy/appdb-2026-01-01.sql.gz /var/backups/mysql/
ls -lh /var/backups/mysql/

If the dump is gzipped, look at the first lines without unpacking it:

zcat /var/backups/mysql/appdb-2026-01-01.sql.gz | head -n 30

You should see comments with the source host and server version, then CREATE TABLE statements. If the file starts with binary noise, it is not a text dump and mysql cannot import it.

Create the target database

A mysqldump file normally contains CREATE TABLE statements but no CREATE DATABASE, so create the database yourself. Match the charset to the source where you can:

sudo mysql -e "CREATE DATABASE appdb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

Then create a dedicated application user rather than importing as root — the exact grants are covered in How to create a database and user for a web app:

CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'StrongPasswordHere';
GRANT ALL PRIVILEGES ON appdb.* TO 'appuser'@'localhost';
FLUSH PRIVILEGES;

Restore a MySQL database from a mysqldump file

The basic import is a single redirect. Run it as root so the dump's DROP TABLE and CREATE TABLE statements are allowed:

sudo mysql appdb < /var/backups/mysql/appdb-2026-01-01.sql

For a gzipped dump, decompress on the fly so you never store a second copy on disk:

gunzip < /var/backups/mysql/appdb-2026-01-01.sql.gz | sudo mysql appdb

To follow progress on a large dump, use pv to show a throughput bar:

sudo apt install pv        # Debian or Ubuntu
sudo dnf install pv        # AlmaLinux or Rocky
pv /var/backups/mysql/appdb-2026-01-01.sql | sudo mysql appdb

Nothing prints when the import succeeds — the shell just returns to the prompt. Silence is the expected result, not a hang. For multi-gigabyte dumps, the process can run for several minutes, so check with top before assuming it has stalled.

If you prefer a password prompt instead of socket authentication:

mysql -u root -p appdb < /var/backups/mysql/appdb-2026-01-01.sql

Set the character set explicitly

If your data contains accented characters, emoji or CJK text and they look wrong after import, the client and the dump disagree about the charset. Pass the source encoding on the command line:

sudo mysql --default-character-set=utf8mb4 appdb < /var/backups/mysql/appdb-2026-01-01.sql

For dumps taken from older servers, utf8 (which is really a three-byte subset) may be the correct value. You can also set it in a small options file and pass it with --defaults-extra-file, which keeps credentials out of your shell history:

[client]
user=root
password=YourRootPassword
default-character-set=utf8mb4

Then:

sudo mysql --defaults-extra-file=/root/.my-restore.cnf appdb < /var/backups/mysql/appdb-2026-01-01.sql

Choose between full and partial restore

For a single table, extract just that table's block from the dump and import it into an existing database:

sed -n '/CREATE TABLE `orders`/,/^UNLOCK TABLES;/p' /var/backups/mysql/appdb-2026-01-01.sql > /tmp/orders.sql
sudo mysql appdb < /tmp/orders.sql

That works for small dumps. For anything large, restoring into a scratch database and copying the table across with INSERT... SELECT is more reliable.

Check it works

List the tables and compare the count with the source server:

sudo mysql -e "SHOW TABLES FROM appdb;" | wc -l
sudo mysql -e "SELECT table_name, table_rows FROM information_schema.tables WHERE table_schema='appdb';"

Then confirm a few known rows survived:

sudo mysql appdb -e "SELECT COUNT(*) FROM users; SELECT id, email FROM users ORDER BY id DESC LIMIT 5;"

Open the application in a browser and log in with a known account. If the site reads data but cannot write it, the grants for appuser are the usual cause. Table and index sizes are worth a look too — see how to check database and table sizes in MySQL and MariaDB.

Troubleshooting

• ERROR 1049 (42000): Unknown database 'appdb' — the target database does not exist. Run the CREATE DATABASE step again, and check spelling and case; Linux filesystems are case-sensitive for database names.
• ERROR 1045 (28000): Access denied for user — the account lacks privileges on that database, or the dump contains DEFINER clauses for a user that does not exist on this server. Grant the privileges, or strip definers with sed 's/DEFINER=[^]*@[^]*//g' dump.sql. The full checklist is in Fix MySQL error 1045: Access denied.
• ERROR 1064 or ERROR 1273: Unknown collation — the dump came from a newer MySQL version than the target. Upgrade the server, or edit the offending COLLATE lines to a collation the local server knows.
• ERROR 1114: The table is full or the import dies partway — the disk is full. Check with df -h and du -sh /var/lib/mysql, free space, then re-run the import. A partial import leaves half-loaded tables, so drop the database and start clean.
• Import finishes but pages show garbled text — charset mismatch. Re-import with --default-character-set set to the source encoding.
• mysqldump: Got error: 2002 while restoring — you are running the wrong binary. mysqldump exports, mysql imports.

Next steps

• Schedule unattended dumps with Automating MySQL backups with mysqldump and cron.
• Read How to back up and restore MySQL databases with mysqldump for the export side and selective restore options.
• Lock the server down after the migration with Install and secure MariaDB or MySQL.
• If the restored app feels slow on a small instance, work through Tune MySQL and MariaDB memory settings for a small VPS.