How to import/export a database in MySQL/MariaDB

Zettel#25

zettelkasten
Published

2022-11-28

Databases are an essential part of modern applications, and there are times when you might need to move or backup your database. MySQL and MariaDB provide several ways to export and import databases.

CLI

Exporting a database

To export a database, you can use the mysql client.

mysql -u username -p db_name > file.sql

Caveat: You can only use mysql from the CLI, so you need to use a terminal or a command prompt to run this command.

Exporting specific tables

mysql -u username -p db_name table1 table2 > file.sql

Importing a database

Note that the DB has to exist before you import the sql file.

mysql -u username -p db_name < backup.sql

If the backup file is compressed with gzip, you can use the following command to import the data:

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

Scripting

If you want to script the backup process, you can use mysqlpump. It was introduced in MySQL 5.7.8, so you don’t have to install anything else, but note that mysqlpump is not included in MariaDB. Instead, you can use mysqldump, which is included in both MySQL and MariaDB; however, mysqldump is not multithreaded like mysqlpump.

mysqlpump -u username -p db_name > file.sql

mysqlpump offers several features, such as parallel compression, incremental backups, and the ability to exclude specific databases or tables.

For example, to exclude a database from the backup:

mysqlpump -u username -p --exclude-databases=db_name1,db_name2 > file.sql

To exclude specific tables:

mysqlpump -u username -p --exclude-tables=db_name.table_name > file.sql

Some features of mysqlpump

  • mysqlpump supports parallel compression, which allows it to compress backup data more efficiently.
  • mysqlpump can be used to create incremental backups by using the --incremental option. This feature enables you to backup only the changes that have been made to the database since the last backup.
  • mysqlpump provides better control over the backup process than mysqldump. For example, you can use the --exclude-databases and --exclude-tables options to exclude specific databases or tables from the backup.
  • mysqlpump generates backup files that are compatible with mysqldump, which means that you can use mysqldump to restore a backup created with mysqlpump.

Manage large databases

For large databases (greater than 1TB), consider using the mydumper and myloader tools, which offer parallelism and other benefits for more efficient database management.

Documentation