How to import/export a database in MySQL/MariaDB

Note#25

notes
Published

2022-11-28

Modified

2026-03-30

Use the dump tools to export data, and the client to import it back.

Export a database

MySQL:

mysqldump -u username -p db_name > backup.sql

MariaDB:

mariadb-dump -u username -p db_name > backup.sql

Export specific tables

MySQL:

mysqldump -u username -p db_name table1 table2 > backup.sql

MariaDB:

mariadb-dump -u username -p db_name table1 table2 > backup.sql

Export multiple databases

If you want the dump file to include CREATE DATABASE and USE, add --databases:

mysqldump -u username -p --databases db1 db2 > backup.sql
mariadb-dump -u username -p --databases db1 db2 > backup.sql

Use --databases when you want the dump file to recreate the original database names. Skip it if you want to restore into a differently named database.

Import a database

If the dump file does not contain CREATE DATABASE / USE, create the target database first.

MySQL:

mysql -u username -p db_name < backup.sql

MariaDB:

mariadb -u username -p db_name < backup.sql

If the dump file already contains USE db_name, you can omit the database name:

mysql -u username -p < backup.sql
mariadb -u username -p < backup.sql

If the dump is compressed:

gunzip < backup.sql.gz | mysql -u username -p db_name
gunzip < backup.sql.gz | mariadb -u username -p db_name

Notes

  • mysqldump is the standard logical backup tool for MySQL.
  • mariadb-dump is the current dump tool for MariaDB. mysqldump is an older name kept for compatibility in some environments.
  • mariadb-dump does not include stored procedures or events unless you ask for them with options such as --routines and --events.
  • mysqlpump exists in MySQL, but it is deprecated as of MySQL 8.0.34. Do not treat it as the default recommendation.
  • For large logical backups, tools such as mydumper / myloader can be faster because they support parallelism.

Documentation