Import and Export a MySQL or MariaDB Database (Command Line)

Updated: 12 October 2026 · Applies to: MariaDB 11.8 and 10.11, MySQL 8.4 and 8.0; tested on Debian 13, Ubuntu 26.04 and 24.04 and AlmaLinux 10

To import a .sql file into MySQL or MariaDB, create the database and feed the file to the mysql client. To export, use mysqldump:

mysql -u DBUSER -p DBNAME < dbname.sql                                       # import
mysqldump -u DBUSER -p --single-transaction --routines DBNAME > dbname.sql   # export

Both ask for the password. On MariaDB the same programs are also called mariadb and mariadb-dump; on Debian 13, mysql and mysqldump are links to them.

Import a database from a .sql file

1. Create the empty database. A dump of a single database has no CREATE DATABASE line, so the database must exist first:

   mysql -u root -p -e "CREATE DATABASE newshop CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci"

   Give your database user rights on it, as in How to create a MySQL user and grant permissions.
2. Import the file, naming the target database:

   mysql -u shopuser -p newshop < shopdb.sql

   No output means success. To import into a server on another machine, add -h db.example.com -P 3306; see connect to a MySQL database from the command line.
3. Check the result: mysql -u shopuser -p newshop -e "SHOW TABLES; SELECT COUNT(*) FROM orders"

Leave out the database name and the import stops at the first table. Lab output from MySQL 8.4.11:

$ mysql -u shopuser -p < shopdb.sql
ERROR 1046 (3D000) at line 22: No database selected

A database that does not exist gives ERROR 1049 (42000): Unknown database 'newshop' for root, and ERROR 1044 (42000): Access denied for user 'shopuser'@'localhost' to database 'newshop' for a normal user. Create it first (step 1).

Before you import into a database that already has tables, note that each table in a dump starts with DROP TABLE IF EXISTS: tables with the same name are replaced, not merged.

Import from inside the mysql client (SOURCE)

If you are already logged in, select the database and run the file with SOURCE (or its short form \.):

MariaDB [(none)]> USE srcshop;
Database changed
MariaDB [srcshop]> SOURCE /root/shopdb.sql;
Query OK, 0 rows affected (0.000 sec)
...
Query OK, 3 rows affected (0.007 sec)
Records: 3  Duplicates: 0  Warnings: 0

(Shortened lab output.) It prints one result per statement, so you see where an error happens. The same works in one line: mysql -u shopuser -p newshop -e "source /root/shopdb.sql".

Import a compressed dump (.gz, .zip, .zst)

Decompress on the fly instead of unpacking to disk first:

gunzip < shopdb.sql.gz | mysql -u shopuser -p newshop
unzip -p shopdb.zip | mysql -u shopuser -p newshop
zstd -dc shopdb.sql.zst | mysql -u shopuser -p newshop

Feeding the compressed file straight to mysql fails on both MySQL 8.4 and MariaDB 11.8 with ERROR: ASCII '\0' appeared in the statement, but this is not allowed unless option --binary-mode is enabled. Do not add --binary-mode: the file is compressed, not SQL. Decompress it as above.

Import on Windows

In Command Prompt (cmd.exe), the same mysql -u root -p newshop < shopdb.sql works. PowerShell does not accept < for input. Tested with PowerShell 7.4:

ParserError:
Line |
   1 |  mysql -u root -p shopdb < shopdb.sql
     |                          ~
     | The '<' operator is reserved for future use.

In PowerShell, let the client read the file itself, with forward slashes in the path: mysql -u root -p newshop -e "source C:/backups/shopdb.sql".

Import into MySQL or MariaDB running in Docker

docker exec -i mydb mariadb -u root -p'YOUR_PASSWORD' newshop < shopdb.sql

The -i is essential. In our test with podman (same options as docker), the command without -i ended with exit code 0 and no error, but nothing was imported, because the file never reached the client. Use mysql instead of mariadb in a MySQL container.

Import into a database with a different name

Dumps made with --databases or --all-databases contain CREATE DATABASE and USE `shopdb`. The USE line wins over the name you type: in our test, mysql copyshop < shopdb-db.sql left copyshop empty and wrote the tables back into shopdb, overwriting it. Check with grep -n '^USE\|^CREATE DATABASE' shopdb-db.sql and remove those lines first:

sed -e '/^CREATE DATABASE/d' -e '/^USE `shopdb`/d' shopdb-db.sql > shopdb-nodb.sql

When the import stops halfway

mysql stops at the first error, for example ERROR 1064 (42000) at line 3: You have an error in your SQL syntax, and everything before that line stays in the database. Fix the line and import again: with a normal dump a rerun is safe, because each table is dropped and created again. --force skips failing statements and carries on, but in our test it still ended with exit code 0, so read the output before you trust the result.

Export a database

1. Dump it as a user with access to the database:

   mysqldump -u shopuser -p --single-transaction --routines --triggers shopdb > shopdb.sql

   • --single-transaction takes a consistent copy of InnoDB tables without locking them, so the site keeps running.
   • --routines adds stored procedures and functions, which are not included by default. Triggers are; add --events for scheduled events.
   • Compressed: mysqldump -u shopuser -p --single-transaction shopdb | gzip > shopdb.sql.gz. Only some tables: list them after the database name. Structure only: --no-data.
   • MySQL 8.4 stops a normal user with mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces. Add --no-tablespaces.
2. Check the file. A complete dump ends with -- Dump completed on and the date; a dump that stopped early has no such line: tail -1 shopdb.sql

Scripts, cron and copying to another server

Put the login in ~/.my.cnf and run chmod 600 ~/.my.cnf, so the tools stop asking for it and you avoid the warning Using a password on the command line interface can be insecure:

[client]
user=shopuser
password=YOUR_PASSWORD

With an option file on both servers you can copy a database without a temporary file:

mysqldump --single-transaction --routines shopdb | gzip | ssh user@new-server 'gunzip | mysql newshop'

For dumps of several gigabytes (progress bars with pv, per-table dumps, resuming), see dump and import large MySQL or MariaDB databases. A row larger than max_allowed_packet stops the import with ERROR 2006 (HY000): Server has gone away: see how to change max_allowed_packet.

Character sets and moving between MySQL and MariaDB

• Dumps begin with SET NAMES utf8mb4, so accents, Indian scripts and emoji survive (our test rows "Café ☕", "नमस्ते" and "👍 thanks" came back intact). In a shell without a UTF-8 locale, the client may show them as question marks; add --default-character-set=utf8mb4 when you check data or import a hand-written file without SET NAMES.
• MySQL 8 dump into MariaDB 10.11: ERROR 1273 (HY000): Unknown collation: 'utf8mb4_0900_ai_ci' (MariaDB 11.8 accepted it). Fix: sed 's/utf8mb4_0900_ai_ci/utf8mb4_unicode_ci/g' shopdb.sql > shopdb-fixed.sql. See also unknown collation errors.
• MariaDB 11 dump into MySQL 8: Unknown collation: 'utf8mb4_uca1400_ai_ci', then Variable 'sql_mode' can't be set to the value of 'NO_AUTO_CREATE_USER'. Fix both: sed -e 's/utf8mb4_uca1400_ai_ci/utf8mb4_unicode_ci/g' -e 's/,NO_AUTO_CREATE_USER//g' shopdb.sql > shopdb-fixed.sql

Common errors

• ERROR 1045 (28000): Access denied for user 'shopuser'@'localhost' (using password: YES): wrong password, or the user exists only for another host.
• ERROR 1227 (42000): Access denied; you need (at least one of) the SET USER privilege(s) (MariaDB) or ERROR 1370: execute command denied: the dump has triggers or routines with DEFINER= another user. Remove the clauses before importing:

  sed -E 's/DEFINER=`[^`]+`@`[^`]+`//g' shopdb.sql > shopdb-nodefiner.sql

• mysqldump: command not found: install the client. Debian: sudo apt install mariadb-client. Ubuntu: mariadb-client or mysql-client, to match your server. AlmaLinux and Rocky Linux: sudo dnf install mariadb.

No SSH access on shared hosting? Use phpMyAdmin: import a database using phpMyAdmin in cPanel. To load a spreadsheet rather than a dump, see import a CSV file into a database. For scheduled backups, see back up a database using mysqldump. If you would rather have a migration done for you, Ucartz engineers can help through Hire an expert.

Official documentation: executing SQL statements from a text file (MySQL 8.4), mysqldump, mariadb-dump (MariaDB).