Whether you're backing up your website, migrating to a new server, or just need to move data between environments, knowing how to import and export MySQL or MariaDB databases is a crucial skill for any sysadmin or developer. Fortunately, this process can be simple and efficient when you understand the tools and syntax involved.
In this guide, we'll walk you through the commands and best practices for safely exporting (dumping) and importing databases using the command line.
Prerequisites
Before you begin, make sure you have the following:
Access to a Linux-based server via SSH
MySQL or MariaDB installed
The mysqldump tool (usually comes with MySQL/MariaDB)
Login credentials for the database (username, password)
Sudo/root access (or a user with DB permissions)
Optional but recommended:
A destination directory with write permissions (e.g., /home/username/backups)
Understanding of basic Linux shell commands
Export a MySQL or MariaDB Database
To export a database, you'll use the mysqldump command, which creates a .sql file that contains all the data and structure of your database.
Basic Export Command
mysqldump -u [username] -p [database_name] > [output_file].sql
Replace [username] with your DB user
Replace [database_name] with the name of your database
Replace [output_file].sql with the desired filename (e.g., backup.sql)
You'll be prompted to enter the database password after running the command.
Example:
mysqldump -u root -p myshop > myshop_backup.sql
Import a MySQL or MariaDB Database
To import a previously exported .sql file into a database, you'll use the mysql command.
mysql -u [username] -p [database_name] < [input_file].sql
Make sure the database you're importing into already exists.
Example:
mysql -u root -p myshop < myshop_backup.sql
Export or Import with Compression
To save space, you can compress your database dumps.
Export with gzip
mysqldump -u root -p myshop | gzip > myshop_backup.sql.gz
Import from gzip:
gunzip < myshop_backup.sql.gz | mysql -u root -p myshop
Conclusion
Now you know how to confidently move your databases in and out of MySQL or MariaDB environments! Whether you're doing a quick backup or planning a full server migration, these commands will be your go-to tools.