Материал подготовлен командой Simple-Server для администраторов VPS и выделенных серверов. Команды и пути проверяйте на тестовой машине перед production.

Кратко о задаче

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

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

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

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

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

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

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

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

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

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

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

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

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

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 новую колонку.

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

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

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

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

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

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

-p<пароль> — пароль от пользователя (для ввода в ручном виде); либо можно использовать просто -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 — файл дампа базы данных;
<наименование пользователя> — наименование привилегированного пользователя (права на запись);
<наименование новой базы данных> — наименование новой базы данных, в которую мы импортируем дамп.

-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:

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

Создайте файл службы (/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

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

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

[Unit]
Descripton=Run MySQL Backup Service Weekly
[Timer]
OnCalendar=weekly
Persistent=true
[Install]
WantedBy=timers.target

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

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

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

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

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

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

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

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

Выберите нужную базу из левой панели.
Перейдите к разделу «Экспорт» через меню PMA.
Инициируйте процесс сохранения дампа на локальный ПК с помощью кнопки «Экспорт».

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

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

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

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

Найдите нужную вам базу данных в левой области, открыв список ее таблиц.
Отметьте необходимые для выгрузки таблицы галочками.
В нижней части экрана из списка действий с отмеченными элементами выберите «Экспорт».
На следующем шаге убедитесь в правильном формате и нажмите «Вперед» для сохранения дампа.

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

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

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

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

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

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

Для выбора нужной базы в левом меню следует раскрыть список доступных таблиц.
В интерфейсе phpMyAdmin перейдите на вкладку «Импорт».
Загрузите файл дампа для импорта, выбрав его через кнопку «Выбрать файл» и загрузив с вашего устройства.
Установите параметры: определите кодировку, формат файла (можно использовать стандартные настройки).
Подтвердите процесс импортом путем нажатия соответствующей кнопки.

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

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

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

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

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

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

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

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

Нужен сервер для практики? Закажите VPS на Simple-Server — root-доступ, NVMe, DDoS-защита и поддержка 24/7.

Импорт и экспорт базы данных в MySQL или MariaDB: инструкция

Кратко о задаче

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

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

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

VPS для проекта

StarterVDS

PerformanceVDS

Импорт и экспорт базы данных в MySQL или MariaDB: инструкция

Кратко о задаче

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

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

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

VPS для проекта

StarterVDS

PerformanceVDS

Читайте также