How to import a SQL file into MySQL or MariaDB

Check what kind of dump it is

A dump of one database loads into whatever database you name. A dump made with --databases or --all-databases has its own CREATE DATABASE and USE lines, which decide where the data goes. Check which you have (for a .gz file, pipe gunzip -c mydb.sql.gz into the same grep without the file name):

grep -n -m 5 -E '^(CREATE DATABASE|USE )' mydb.sql

No output means a single-database dump. head -n 5 mydb.sql shows which program wrote it: -- MySQL dump or -- MariaDB dump, followed by the version.

Create the database and import

Making the dump is covered in MySQL backups with mysqldump. On MariaDB you can type mariadb wherever this guide says mysql.

mysql -u root -p -e "CREATE DATABASE mydb CHARACTER SET utf8mb4"

mysql -u root -p mydb < mydb.sql

• -u root -p logs in as root and prompts for the password, keeping it out of shell history. Any account that can create tables, views, triggers and routines there will do.
• mydb is the database the statements run in. Leave it out for a dump with its own USE lines.
• If root logs in through the Unix socket (the Ubuntu and Debian default), run sudo mysql mydb < mydb.sql instead.

The client stops at the first error, reports the line of the failing statement, as in ERROR 1273 (HY000) at line 25: Unknown collation: 'utf8mb4_0900_ai_ci', and exits with status 1. Fix the cause and import again into an empty database. --force carries on past errors, but the result is missing whatever failed; use it only to list every problem in one pass.

Import with source inside the client

CREATE DATABASE IF NOT EXISTS mydb;
USE mydb;
source /home/deploy/mydb.sql

source (or \.) is a client command, not SQL, so the path is on the machine running mysql. Interactively, it prints each error and keeps going, so failures can scroll past unnoticed; for big files the shell form is easier to check.

Import a gzipped dump

gunzip -c mydb.sql.gz | mysql -u root -p mydb

gunzip -c (or zcat) writes the SQL to the pipe and leaves the file untouched. Redirecting the .gz file straight into mysql fails with an ASCII '\0' appeared in the statement error.

Show progress on a large import

The client prints nothing while it works. pv (Pipe Viewer) shows how much of the file has been read, the speed and the time left. It is a separate package: sudo apt install pv on Debian and Ubuntu. Its progress bar and the password prompt garble each other, so put the login in ~/.my.cnf, which the client reads on its own, and chmod 600 the file:

[client]
user=root
password="your-password-here"

pv mydb.sql.gz | gunzip | mysql mydb

The percentage is the share of the compressed file read so far; heavily indexed tables load slower than their share. Delete ~/.my.cnf afterwards if you do not want the password left on disk.

Make large imports faster

Check what the dump already does. Unless made with --compact, mysqldump and mariadb-dump files switch off unique and foreign key checks near the top, switch them back on at the end, and use multi-row INSERTs, which the MySQL manual says import quickly as they are:

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

For SQL from other tools, or a --compact dump, set the same session variables yourself. --init-command runs one statement as the client connects:

mysql --init-command="SET SESSION foreign_key_checks=0, unique_checks=0" -u root -p mydb < export.sql

• unique_checks=0 lets InnoDB batch its secondary index writes. The manual's condition: be certain the data contains no duplicate keys.
• foreign_key_checks=0 lets tables load in any order. Rows loaded this way are not re-checked when the checks come back on, so use it only for data that was consistent when dumped.
• Both last for this connection only and need no special privilege.

Run the import on the database server, not across a network. Beyond that, the format is the limit: every row is replayed and every index rebuilt. For very large databases, physical backups with XtraBackup restore far faster.

Character sets and --default-character-set

mysqldump and mariadb-dump write a SET NAMES line near the top, so client and server agree on the encoding. Files from other tools, --compact dumps and pieces cut from a dump may not have one. If grep -m 1 'SET NAMES' mydb.sql finds nothing, tell the client the encoding, or accented letters and emoji can be stored double-encoded (é where é should be):

mysql --default-character-set=utf8mb4 -u root -p mydb < export.sql

Import a single table

A dump of just that table (mysqldump mydb orders > orders.sql) imports like any other: it drops and recreates orders and leaves the rest alone. To take one table out of a full dump, restore the dump into a scratch database and copy the table across. With the login in ~/.my.cnf as above:

mysql -e "CREATE DATABASE mydb_scratch"

gunzip -c mydb.sql.gz | mysql mydb_scratch

mysqldump mydb_scratch orders | mysql mydb

mysql -e "DROP DATABASE mydb_scratch"

If the dump is too big to restore twice, cut the table out. mysqldump starts each table with a -- Table structure for table comment, so this keeps everything from the orders header to the next one:

gunzip -c mydb.sql.gz | sed -n '/^-- Table structure for table `orders`/,/^-- Table structure for table/p' > orders.sql

The piece lacks the dump header, so load it with the settings the header would have set:

mysql --init-command="SET SESSION foreign_key_checks=0" --default-character-set=utf8mb4 -u root -p mydb < orders.sql

This needs the comments, which --compact and --skip-comments dumps lack; if orders is the last table, the piece also picks up the views and routines after it. For one database out of an --all-databases dump, create it and run mysql --one-database shop -u root -p < all-databases.sql; the manual calls the option rudimentary, since it filters only on USE lines.

Check the import worked

An import without errors is a good sign, not proof. Count the objects on the source and the copy; the numbers should match:

SELECT
  (SELECT COUNT(*) FROM information_schema.TABLES
     WHERE TABLE_SCHEMA = 'mydb' AND TABLE_TYPE = 'BASE TABLE') AS tables_count,
  (SELECT COUNT(*) FROM information_schema.TABLES
     WHERE TABLE_SCHEMA = 'mydb' AND TABLE_TYPE = 'VIEW') AS views_count,
  (SELECT COUNT(*) FROM information_schema.ROUTINES
     WHERE ROUTINE_SCHEMA = 'mydb') AS routines_count,
  (SELECT COUNT(*) FROM information_schema.TRIGGERS
     WHERE TRIGGER_SCHEMA = 'mydb') AS triggers_count;

Count rows in the tables that matter with SELECT COUNT(*), not with TABLE_ROWS from information_schema, which for InnoDB can be 40 to 50 percent off. To compare contents, CHECKSUM TABLE reads every row and returns one number:

CHECKSUM TABLE mydb.orders;

Different values mean the tables differ; equal values mean they almost certainly match. The value depends on row format, so compare the same server version, and the table is read-locked while it runs. Whether your application starts against the copy is the real test: see how to test a backup restore.

Errors and how to fix them

Raise the packet limit on the server (admin account; new connections pick it up) and on the client:

SET GLOBAL max_allowed_packet = 1073741824;

mysql --max-allowed-packet=1G -u root -p mydb < mydb.sql

1G is the maximum. To keep it after a restart, add max_allowed_packet = 1G under [mysqld] in the server's config. To strip the GTID lines from a dump you cannot remake (the GTID set can span several lines, so the script skips to the closing semicolon):

gunzip -c mydb.sql.gz | awk '/^SET @@GLOBAL.GTID_PURGED/ {skip=1} skip {if (/;$/) skip=0; next} /^SET @@SESSION.SQL_LOG_BIN/ {next} {print}' | mysql -u root -p mydb

To make every view, trigger and routine belong to the account running the import:

sed -E 's/DEFINER=`[^`]+`@`[^`]+`/DEFINER=CURRENT_USER/g' mydb.sql > mydb-fixed.sql

To replace MySQL 8's default collation with one every version knows (grep -o 'utf8mb4_0900_[a-z_]*' mydb.sql | sort -u lists any other 0900 collations; on MariaDB 10.10 or later, utf8mb4_uca1400_ai_ci follows newer Unicode sorting rules, as MySQL's does):

sed -i 's/utf8mb4_0900_ai_ci/utf8mb4_unicode_ci/g' mydb.sql

To skip the sandbox line of an affected MariaDB dump:

tail -n +2 mydb.sql | mysql -u root -p mydb

Frequently asked questions

How do I import a large SQL file into MySQL?

Use the command-line client on the database server itself, watch it with pv, and raise max_allowed_packet if it fails with 'server has gone away'. For hundreds of gigabytes, a physical backup restores far faster.

How do I import a .sql.gz file into MySQL?

Decompress it in the pipe: gunzip -c file.sql.gz | mysql -u root -p mydb. The .gz file is left as it is.

Can I import a MySQL dump into MariaDB?

Usually. The common snag is MySQL 8's utf8mb4_0900_ai_ci collation, which MariaDB accepts only from 11.4.5; on older versions replace it with utf8mb4_unicode_ci.

How do I skip errors when importing a SQL file?

mysql --force continues past errors, but the database ends up missing whatever failed. Use it to list the problems, fix them, then import again into an empty database.