How to import and export MySQL database in Linux

If you are a Linux user and use a MySQL database, you may need to import or export the database from time to time. Importing a database means copying data from an external source to 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 a MySQL database in Linux using a few different methods.

MySQL Introduction

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.

Basic commands

Before we dive into importing and exporting a database, let’s review the basic commands you need to know to use a MySQL database 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 for your password.

After logging in to MySQL, you can use the following command to create a new database -

CREATE DATABASE dbname;

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

You can then switch to the new database using the following command -

USE dbname;

To display the database list, use the following command -

SHOW DATABASES;

To exit MySQL, enter the following command -

exit

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

Export MySQL database

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

Use mysqldump command

The mysqldump command is a utility for creating 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 database backup 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.

Use the export function in phpMyAdmin

If phpMyAdmin is installed on your system, you can use it to export a MySQL database. To do this, open phpMyAdmin in your web browser and follow these steps -

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

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

• Select the tables to export or keep the default settings to export the entire database.

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

• Click the "Start" button to download the exported file.

Using MySQL Enterprise Backup Tool

If you are using MySQL Enterprise Edition, you can use the MySQL Enterprise Backup tool to create a backup of your MySQL database. To do this, 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 replace "/path/to/backup/dir" with the directory where you want to save the backup files.

This command will create a database backup in 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.

Import MySQL database

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.

Use mysql command

The mysql command is a utility used to import SQL files into a MySQL database. 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, replace "dbname" with the name of the database you want to import, and replace "backupfile.sql" with the name of the SQL file you want to import.

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

Use the import function in phpMyAdmin

If phpMyAdmin is installed on your system, you can use it to import a MySQL database. To do this, open phpMyAdmin in your web browser and follow these steps -

• Select the database to import from the list on the left side of the screen.

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

• Select the file you want to import.

• Select the file format to import (SQL, CSV, etc.).

• Click the "Start" button to import the file.

Using MySQL Enterprise Backup Tool

If you are using MySQL Enterprise Edition, you can use the MySQL Enterprise Backup tool to restore the MySQL database. To do this, 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

将“用户名”和“密码”替换为您的 MySQL 用户名和密码，将“/path/to/backup/dir”替换为保存备份文件的目录。

此命令将从备份文件恢复数据库。

结论

对于在 Linux 中使用 MySQL 数据库的任何人来说，导入和导出 MySQL 数据库是一项重要任务。在本文中，我们讨论了导出 MySQL 数据库的三种不同方法和导入 MySQL 数据库的三种不同方法。通过使用这些方法，您可以轻松地在系统之间传输数据或创建数据库备份以进行安全保存。

请记住，在进行任何更改或将数据库传输到另一个系统之前，请始终对数据库进行备份。这将确保您始终拥有数据副本，以防出现任何问题。

如果您在导入或导出 MySQL 数据库时遇到任何问题，请参阅 MySQL 文档或向 MySQL 社区寻求帮助。通过一些练习，您将能够像专业人士一样导入和导出 MySQL 数据库！

The above is the detailed content of How to import and export MySQL database in Linux. For more information, please follow other related articles on the PHP Chinese website!