How to Import a SQL Dump into MySQL (CLI and GUI)

Sooner or later every MySQL or MariaDB project needs the same ritual: take a .sql dump and load it somewhere else. This guide covers how to import a SQL dump into MySQL with both the command line and a desktop GUI, plus the errors that burn an afternoon when character sets, DEFINER clauses, or giant inserts fight back.

What a dump usually contains

A typical dump is a text file of SQL statements:

• CREATE DATABASE / USE
• CREATE TABLE and indexes
• INSERT (or extended inserts)
• views, routines, triggers, events
• optional SET statements for time zone and SQL mode

Before you import, know whether the file is:

• schema only
• data only
• full (schema + data)
• single database vs multiple

Opening the first 50 lines in an editor saves more time than any fancy tool.

Option 1: import with the mysql CLI

Fast, scriptable, and available everywhere the client package is installed:

mysql -h 127.0.0.1 -P 3306 -u root -p target_database < dump.sql

Or create the database first:

mysql -h 127.0.0.1 -u root -p -e "CREATE DATABASE target_database CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
mysql -h 127.0.0.1 -u root -p target_database < dump.sql

For remote hosts, prefer SSH to the server (or a tunnel) instead of exposing port 3306.

Useful CLI flags

Option 2: import with a desktop GUI

A MySQL GUI is often clearer when you are:

• picking the target connection visually
• importing into a non-production scratch DB
• reading per-statement errors instead of a wall of CLI output
• mixing import with quick “does this table look right?” checks

In TabulaSQL, open the target connection/database and use the SQL import flow for a .sql file. Pure PHP import means you do not need mysql CLI binaries on every laptop, which helps on locked-down Windows machines and minimal Linux desktops.

Pre-flight checklist

1. Create or choose the target database deliberately (never guess production)
2. Check disk space on the server; dumps expand
3. Match major version expectations when possible (8.x → 8.x is calmer than 5.7 quirks into 8.4)
4. Disable busy traffic on the target if this is a restore
5. Prefer a throwaway database for the first attempt with an unfamiliar dump

Common import failures (and fixes)

Access denied / cannot create database

Your user can connect but lacks CREATE or privileges on the schema. Import as a migration user, or pre-create the empty database and grant rights.

Unknown collation / charset errors

Old dumps mention collations your server does not ship. Either upgrade the server packages or rewrite the dump’s charset/collation lines to utf8mb4 / utf8mb4_unicode_ci (test on a copy).

DEFINER errors on views/routines

Dumps often contain DEFINER=\someone`@`host``. That user may not exist on the destination. Strip or rewrite DEFINER clauses, or create matching users before import.

Packet too large

Increase max_allowed_packet on the server (and sometimes the client) for dumps with huge extended inserts or BLOB rows.

Partial import, then chaos

If a run dies halfway, drop the target database and start clean unless you know exactly which objects succeeded. Half-applied dumps are worse than no dump.

GUI workflow that stays safe

1. Connect to local Docker / Herd MySQL first
2. Create project_import_test
3. Import the dump there
4. Spot-check row counts and a few critical tables
5. Only then import into staging with a maintenance window

TabulaSQL supports SQL import with per-statement error reporting, which makes step 4 much less painful than scrolling a silent CLI failure.

Dump hygiene for next time

When you create dumps:

• prefer utf8mb4 end to end
• document whether routines/triggers are included
• avoid dumping accounts you do not intend to move
• keep a small anonymized fixture dump for developers

Related

• Free MySQL client for Windows and Linux
• How to connect to MySQL on Mac
• Download TabulaSQL