How To Import and Export MySQL Databases in Linux

Importing and exporting MySQL or MariaDB databases is a regular task in system administration. You can use data dumps to back up and restore your databases or migrate them to a new server.

[ You might also like: How to Backup All MySQL Databases from Command Line ]

In this article, you will learn how to export a MySQL or MariaDB database and then import that database from the dump file in Linux.

Exporting a MySQL or MariaDB Database

To export a database, you need to use a mysqldump client utility that creates the logical backup of the databases to SQL text files, which makes it easier to transfer files from one server to another.

Run the mysqldump command to export your database:

$ mysqldump -u username -p database_name > linuxshelltips.sql

• username – The username used to log in to the database server.
• database_name – The name of the database to export.
• linuxshelltips.sql – The filename used to stores the database output in the current directory.

The above command doesn’t print any visual output, but you can investigate the contents of the SQL dump file using the following command.

$ head -n 5 linuxshelltips.sql

The beginning of the file should look similar to this, showing a MySQL dump for a database named linuxshelltips.

If any errors fall during the export procedure, mysqldump will show them to the screen.

Importing a MySQL or MariaDB Database

To import an existing database, you first need to create a new database in your MySQL or MariaDB server using a root user or another user with adequate privileges.

$ mysql -u root -p

Once you connected to the MySQL shell, you need to create a new database with the following command.

MariaDB [(none)]> CREATE DATABASE linuxshelltips;

Next exit the MySQL shell by typing quit or hitting CTRL+D. From the normal command line, you can import the dump file with the following command:

$ mysql -u username -p linuxshelltips < linuxshelltips.sql

Once the database has been imported, you can check the database by log in to the MySQL shell and run the following commands.

$ mysql -u root -p
MariaDB [(none)]> USE linuxshelltips;
MariaDB [(none)]> SHOW TABLES;

In this article, we have seen how to export and import MySQL or MariaDB databases in Linux.