Export mysql database linux

Импорт и экспорт баз данных MySQL и MariaDB

При работе с данными очень важно уметь импортировать и экспортировать базы данных. Дампы данных можно использовать для резервного копирования и восстановления БД, что позволяет в случае необходимости получить доступ к одной из предыдущих версий данных или переместить их на новый сервер или среду разработки.

Дампы MySQL и MariaDB очень просты в работе. Это руководство научит вас экспортировать и импортировать базы данных с помощью дампа MySQL или MariaDB.

Требования

• Сервер Linux.
• Предварительно установленная система управления базами данных MySQL или MariaDB.
• База данных и пользователь СУБД.

Экспорт данных

Консольная утилита mysqldump позволяет экспортировать данные в текстовый файл SQL, который можно легко переместить. Для этого вам понадобится имя БД, а также учётные данные пользователя, у которого есть как минимум право на чтение БД.

Чтобы экспортировать БД, используйте такую команду:

mysqldump -u username -p database_name > data-dump.sql

• username – имя пользователя БД;
• database_name – имя БД, которую нужно экспортировать;
• data-dump.sql – файл в текущем каталоге, в который будут экспортированы данные.

Вывод этой команды не отображается на экране. Чтобы убедиться в том, что данные были импортированы успешно, проверьте содержимое дампа:

Файл дампа MySQL должен начинаться примерно так:

— MySQL dump 10.13 Distrib 5.7.16, for Linux (x86_64)
—
— Host: localhost Database: database_name
— ——————————————————
— Server version 5.7.16-0ubuntu0.16.04.1

Если во время экспорта данных произошла ошибка, утилита mysqldump сообщит вам о ней.

Импорт данных

Чтобы импортировать дамп, нужно создать новую БД MySQL или MariaDB, в которую будут перемещены данные из дампа.

Войдите как root или другой пользователь с расширенными привилегиями.

Эта команда откроет командную оболочку MySQL. Создайте новую БД, например, new_database.

CREATE DATABASE new_database;

На экране появится вывод, подтверждающий, что БД была создана:

Query OK, 1 row affected (0.00 sec)

Закройте оболочку MySQL (CTRL+D). С помощью обычной командной строки импортируйте дамп:

mysql -u username -p new_database < data-dump.sql

• username – имя пользователя, у которого есть доступ к БД.
• newdatabase – имя новой БД.
• data-dump.sql – имя дампа, который нужно импортировать.

В случае успешного выполнения команды вывод не отображается на экране. Если во время выполнения возникла какая-либо ошибка, mysql выведет их в терминал. Чтобы убедиться, что данные импортированы успешно, войдите в оболочку MySQL и проверьте БД. Для этого можно использовать:

USE new_database;
SHOW TABLES;

Заключение

Теперь вы умеете перемещать данные MySQL и MariaDB с помощью дампа. Утилита mysqldump имеет множество дополнительных параметров, при помощи которых можно настраивать дампы; подробнее об этом – в официальной документации mysqldump.

Источник

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 −

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 −

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

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

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

To exit MySQL, type the following command −

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!

Источник

How to Import and Export MySQL Databases in Linux

Importing and Exporting in MySQL databases aids in ensuring data security as importing databases is used for transferring data between servers, on the other hand, exporting databases helps in data backup and recovery.

This guide will provide the procedure for:

Prerequisite: Installation of MySQL Server in Linux

Updating your system before any installation is preferable so type:

Install the MySQL server by typing:

To check the installed version of MySQL, type:

For starting the services of MySQL use this command:

Exporting MySQL Databases in Linux

Lets export a database in Linux, but before that, you need to connect to a MySQL Server. Use this syntax to connect to a Local MySQL Server:

For this post the username is “mysql”:

As it is visible in the output above, the MySQL server is connected successfully.

Use this SHOW command to see all the available databases:

To export the “mysql” database from the available databases, first let’s create a directory using this command:

Navigate to this directory by typing:

To export the database, use this syntax:

Provide your username, database name that you want to export and the name for a file in which you want to export the database. Use this syntax and hit enter, and type the ls command to see if the database is exported successfully or not:

Here you can see that file is successfully created.

To see some data of this file, type:

Here the data of the exported database is visible.

Importing MySQL Databases in Linux

To import the database, let’s connect to the local server by using the syntax:

You have successfully logged in to your local database server.

To create a database, use this syntax:

Provide the name for the database:

To see the available databases, type this command:

Here it is visible that the database you created is available.

Let’s import the already existing file with “.sql” extension containing the backup data of the database, using this syntax:

Provide the required values in syntax:

Your database is imported successfully as it asked for the password and gave error-free output.

Conclusion

Importing and Exporting databases is an important task for data recovery and backup, data migration, collaboration, or testing and debugging. Exporting creates a backup file, to export the database use this syntax “sudo mysqldump -u -p > .sql” whereas importing allows you to make the data transfer easy between servers, you can import database from a file by using this syntax “mysql -u -p < .sql”.

About the author

Abdul Mannan

I am curious about technology and writing and exploring it is my passion. I am interested in learning new skills and improving my knowledge and I hold a bachelor's degree in computer science.

Источник