How to Import and Export MySQL Databases in Linux

If you are a Linux user and you work with MySQL databases, you might need to import or export databases from time to time. Importing a database means copying data from an external source into a MySQL database, while exporting means copying data from a MySQL database to an external source. This article will show you how to import and export MySQL databases in Linux using several different methods.

Introduction to MySQL

MySQL is a popular open-source relational database management system that uses SQL (Structured Query Language) to manage databases. It is widely used in web applications and is often paired with PHP to create dynamic websites.

The Basic Commands

Before we dive into importing and exporting databases, let's review the basic commands you'll need to know to work with MySQL databases in Linux.

To access the MySQL prompt, open a terminal window and type the following command −

mysql -u username -p

Replace "username" with your MySQL username. You will be prompted to enter your password.

Once you are logged into MySQL, you can create a new database with the following command −

CREATE DATABASE dbname;

Replace "dbname" with the name you want to give your database.

You can then switch to the new database with the following command −

USE dbname;

To show a list of databases, use the following command −

SHOW DATABASES;

To exit MySQL, type the following command −

exit

Now that you know the basic commands, let's move on to importing and exporting databases.

Exporting MySQL Databases

Exporting a MySQL database involves creating a backup of the database that can be used to restore it later or to transfer it to another system. Here are three different methods you can use to export a MySQL database in Linux.

Using the mysqldump Command

The mysqldump command is a utility that is used to create backups of MySQL databases. To use it, open a terminal window and type the following command −

mysqldump -u username -p dbname > backupfile.sql

Replace "username" with your MySQL username, "dbname" with the name of the database you want to export, and "backupfile.sql" with the name you want to give the backup file.

This command will create a backup of the database in SQL format and save it to the specified file. You can then transfer this file to another system or use it to restore the database later.

Using the Export Function in phpMyAdmin

If you have phpMyAdmin installed on your system, you can use it to export MySQL databases. To do so, open phpMyAdmin in your web browser and follow these steps −

• Select the database you want to export from the list on the left-hand side of the screen.

• Click on the "Export" tab in the top menu.

• Select the tables you want to export or leave the default settings to export the entire database.

• Choose the format you want to export the database in (SQL, CSV, etc.).

• Click on the "Go" button to download the exported file.

Using the MySQL Enterprise Backup Tool

If you are using the MySQL Enterprise Edition, you can use the MySQL Enterprise Backup tool to create backups of MySQL databases. To do so, open a terminal window and type the following command −

mysqlbackup --user=username --password=password --backup-dir=/path/to/backup/dir backup

Replace "username" and "password" with your MySQL username and password, and "/path/to/backup/dir" with the directory where you want to save the backup file.

This command will create a backup of the database in a compressed format and save it to the specified directory. You can then transfer this file to another system or use it to restore the database later.

Importing MySQL Databases

Importing a MySQL database involves copying data from an external source into a MySQL database. Here are three different methods you can use to import a MySQL database in Linux.

Using the mysql Command

The mysql command is a utility that is used to import SQL files into MySQL databases. To use it, open a terminal window and type the following command −

mysql -u username -p dbname < backupfile.sql

Replace "username" with your MySQL username, "dbname" with the name of the database you want to import into, and "backupfile.sql" with the name of the SQL file you want to import.

This command will import the data from the SQL file into the specified database.

Using the Import Function in phpMyAdmin

If you have phpMyAdmin installed on your system, you can use it to import MySQL databases. To do so, open phpMyAdmin in your web browser and follow these steps −

• Select the database you want to import into from the list on the left-hand side of the screen.

• Click on the "Import" tab in the top menu.

• Choose the file you want to import.

• Choose the format of the file you want to import (SQL, CSV, etc.).

• Click on the "Go" button to import the file.

Using the MySQL Enterprise Backup Tool

If you are using the MySQL Enterprise Edition, you can use the MySQL Enterprise Backup tool to restore MySQL databases. To do so, open a terminal window and type the following command −

mysqlbackup --user=username --password=password --backup-dir=/path/to/backup/dir copy-back-and-apply-log

Replace "username" and "password" with your MySQL username and password, and "/path/to/backup/dir" with the directory where the backup file is saved.

This command will restore the database from the backup file.

Conclusion

Importing and exporting MySQL databases is an important task for anyone working with MySQL databases in Linux. In this article, we discussed three different methods for exporting MySQL databases and three different methods for importing MySQL databases. By using these methods, you can easily transfer data between systems or create backups of your databases for safekeeping.

Remember to always make backups of your databases before making any changes or transferring them to another system. This will ensure that you always have a copy of your data in case anything goes wrong.

If you run into any issues while importing or exporting MySQL databases, refer to the MySQL documentation or seek help from the MySQL community. With a little practice, you'll be able to import and export MySQL databases like a pro!