Как импортировать и экспортировать базы данных в MySQL или MariaDB

Управление базами данных — важная часть в администрировании Linux-серверов и веб-приложений. Импорт и экспорт баз данных являются незыблемыми столпами в DevOps и системном администрировании.

Как минимум, любой разработчик должен уметь создавать резервные копии и переносить их между серверами. В этой статье будет рассмотрен процесс импортирования дампов баз данных и их экспортирование в MySQL или MariaDB.

Для того чтобы выполнить дальнейшие шаги, вам будет нужен Linux-сервер (в инструкции все действия выполняются на Ubuntu). Арендовать виртуальный сервер вы можете у нас по этой ссылке.

Работа с базой данных

MySQL и MariaDB — это популярные реляционные СУБД, которые используются для хранения данных больших приложений.

MariaDB — это форк MySQL, который был разработан первоначальной командой разработчиков из-за проблем с лицензированием, связанных с покупкой MySQL корпорацией Oracle. Но и MariaDB, и MySQL используют идентичные или похожие API и схемы работы.

Процесс создания базы данных:

1. Подключитесь к MySQL или MariaDB с root-правами (на чтение и запись):

• • Для MySQL:

mysql -u root -p

• • Для MariaDB: 

mariadb -u root -p

2. Используйте SQL-запрос для создания базы данных, если она не существует:

CREATE DATABASE IF NOT EXISTS <наименование_вашей_базы>;

Основные приемы управления SQL-данными остаются неизменными как в MySQL, так и MariaDB.

Если вы хотите посмотреть, какие базы созданы:

SHOW DATABASES;.

Для переключения на нужную базу данных выполните:

USE <наименование базы данных>;

После вы можете просмотреть существующие таблицы через:

SHOW TABLES;

Некоторые полезные команды вы можете увидеть ниже:

CREATE TABLE IF NOT EXISTS users (user_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, username VARCHAR(100) NOT NULL); 

— создает таблицу users с полями user_id и username.

INSERT INTO users (username) VALUES ('John Doe'); 

— вставляет в таблицу users новую колонку.

SELECT * FROM users; 

— выборка всех записей из таблицы users.

Статус и статистику работы MySQL или MariaDB возможно узнать через:

SHOW GLOBAL STATUS;

dbaas

Экспорт

Экспорт данных MySQL и MariaDB можно осуществить при помощи CLI-утилиты (command-line interface, утилита командной строки) mysqldump или через сторонние инструменты (например phpMyAdmin, который мы рассмотрим позднее). Данная утилита позволяет сохранять вашу базу данных в виде SQL-дампа. В нем находятся все нужные команды для создания столбцов и их заполнения. А также им можно легко управлять: импортировать или переместить.

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

Введите в ваш терминал следующий текст:

mysqldump -u <наименование пользователя> -p<пароль> <наименование базы данных> > db_dump.SQL

Где:

• -p<пароль> — пароль от пользователя (для ввода в ручном виде); либо можно использовать просто -p (автоматически запросит пароль);

• db_dump.SQL — файл дампа базы данных;

• <наименование пользователя> — наименование привилегированного пользователя (с правами на чтение);

• <наименование базы данных> — наименование базы данных.

Кроме создания дампов локально на самом сервере, вы можете создавать их удаленно, добавив в команду флаг -h:

mysqldump -h <ip-адрес> -u <наименование пользователя> -p<пароль> <наименование базы данных> > db_dump.SQL # если MySQL сервер на стандартном порту

# ИЛИ

mysqldump -h <ip-адрес> -P <порт> -u <наименование пользователя> -p<пароль> <наименование базы данных> > db_dump.SQL # если MySQL на нестандартном порту

Флаг -h используется для указания адреса удаленного сервера MySQL, с которого вы хотите создать дамп базы данных. Также можно использовать флаг -P для указания порта сервера.

Аналогично с импортом, выводятся только ошибки. Вы можете проверить корректность экспортируемых данных, чтобы убедиться в том, что процедура прошла успешно. В итоге вы успешно проведете экспорт таблиц MySQL.

В файле с форматом .sql можно видеть краткую характеристику дампа: СУБД, команды, информация о базе данных. Помимо SQL, поддерживаются CSV, XML и другие. Можно задать выбор конкретных таблиц.

В дампе будет указана ваша используемая СУБД (MySQL или MariaDB) и другая информация, а также самое главное — синтаксис для создания нужных колонок и их заполнения.

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

Для импорта дампа базы данных в MySQL уже не надо использовать mysqldump, можно обойтись прямым обращением к mysql.

Для этого введите команду ниже:

mysql -u <наименование пользователя> -p<пароль>  <наименование новой базы данных> < db_dump.SQL

Где:

• -p<пароль> — пароль пользователя; также можно использовать просто -p (будет запрошен пароль автоматически);

• db_dump.SQL — файл дампа базы данных;

• <наименование пользователя> — наименование привилегированного пользователя (права на запись);

• <наименование новой базы данных> — наименование новой базы данных, в которую мы импортируем дамп.

При правильном совершении действий вы ничего не увидите. В ином же случае СУБД сообщит вам об ошибках или предупреждениях. Проверить корректность можно, введя следующие MySQL-команды:

SHOW DATABASES; # покажет базы данных
USE <БАЗА ДАННЫХ>; # переходим в базу данных
SHOW TABLES; # покажет таблицы в базе данных

systemd-служба для бэкапа

Допустим, вы хотите автоматизировать процесс бэкапа (экспорта) базы данных. В этом руководстве мы создадим службу-таймер, которая будет запускать скрипт для бэкапа данных.

Timer — это механизм для планирования выполнения конкретной службы в заданное время или через определенные интервалы.

Для этого подключитесь к серверу и выполните следующие команды:

mkdir -p /usr/bin/backup_scripts # создаем директорию, где будут храниться скрипты для бекапа

После создайте и откройте файл /usr/bin/backup_scripts/mysql_backup.sh через любой редактор (например, nano) и запишите в нем следующее:

TIMESTAMP=$(date +'%F') # получаем текущую дату
BACKUP_DIR='<путь до директории, где будут сохраняться дампы>'
MYSQL_USER='<наименование пользователя>'
MYSQL_PASSWORD='<пароль пользователя>'
DATABASE_NAME='<название базы данных>'

mkdir -p '$BACKUP_DIR/$TIMESTAMP' # создаем директорию
mysqldump -u $MYSQL_USER -p$MYSQL_PASSWORD $DATABASE_NAME > '$BACKUP_DIR/$TIMESTAMP/$DATABASE_NAME-$TIMESTAMP.sql' # создаем дамп
find '$BACKUP_DIR;' -type d -mtime +28 -exec rm -rf {} \; # удаление бекапов старше 4 недель

Укажите в нем директорию для бэкапа, наименование пользователя и пароль, и также название базы данных.

Далее выдадим этому скрипту права на исполнение:

chmod +x /usr/bin/backup_scripts/mysql_backup.sh

Следующим шагом будет создание службы и таймера systemd:

1. Создайте файл службы (/etc/systemd/system/mysql-backup.service):

[Unit]
Description=MySQL Database Backup Service

[Service]
Type=oneshot
ExecStart=/usr/bin/backup_scripts/mysql_backup.sh

[Install]
WantedBy=multi-user.target

2. Создайте файл таймера (/etc/systemd/system/mysql-backup.timer):

[Unit]
Descripton=Run MySQL Backup Service Weekly

[Timer]
OnCalendar=weekly
Persistent=true

[Install]
WantedBy=timers.target

3. Активируйте службу и таймер:

systemctl daemon-reload # перезагрузка конфигурации systemD
systemctl enable mysql-backup.timer # включаем таймер в автозагрузку
systemctl start mysql-backup.timer # запуск таймера

4. Проверьте статус службы:

systemctl status mysql-backup.timer
systemctl list-timers

Готово! Теперь каждую неделю будет создаваться дамп нужной вам базы данных.

Экспорт и импорт через phpMyAdmin

Совершать импорт и экспорт можно не только через консольные утилиты, но и через веб-интерфейс самого phpMyAdmin. Чаще всего этот способ удобнее, если размер дампа небольшой (не более 1 гигабайта).

В этой секции будет рассматриваться базовый импорт и экспорт базы данных.

Экспорт

Для процесса экспорта перейдите в нужную базу данных и нажмите на вкладку «Экспорт», а после выберите нужный вам метод экспорта: быстрый или расширенный (он подойдет, если в вашем случае процесс экспорта специфичен и требует настройки).

При экспортировании данных в быстром режиме:

1. Выберите нужную базу из левой панели.

2. Перейдите к разделу «Экспорт» через меню PMA.

3. Инициируйте процесс сохранения дампа на локальный ПК с помощью кнопки «Экспорт».

Для экспорта конкретных таблиц:

1. Найдите нужную вам базу данных в левой области, открыв список ее таблиц.

2. Отметьте необходимые для выгрузки таблицы галочками.

3. В нижней части экрана из списка действий с отмеченными элементами выберите «Экспорт».

4. На следующем шаге убедитесь в правильном формате и нажмите «Вперед» для сохранения дампа.

Импорт

Импорт базы данных аналогичен экспорту. Войдите в phpMyAdmin и перейдите в интересующую вас базу данных, а после перейдите на вкладку «Импорт»:

Чтобы осуществить импорт базы данных, необходимо выполнить следующие действия:

1. Для выбора нужной базы в левом меню следует раскрыть список доступных таблиц.

2. В интерфейсе phpMyAdmin перейдите на вкладку «Импорт».

3. Загрузите файл дампа для импорта, выбрав его через кнопку «Выбрать файл» и загрузив с вашего устройства.

4. Установите параметры: определите кодировку, формат файла (можно использовать стандартные настройки).

5. Подтвердите процесс импортом путем нажатия соответствующей кнопки.

В левой колонке вы увидите находящийся файлы и их содержимое, а также имеете возможность изменить их.

Разверните базу данных в облаке за пару минут

Заключение

Выбор более удобного метода остается за вами. Если вы хотите вручную делать бэкапы или быстро получить доступ к базе данных — стоит использовать phpMyAdmin. В случае, если вам нужно автоматизировать рутинные задачи, или вы переносите инфраструктуру, более удобным будет ручной вариант через утилиты командной строки.