Как сделать резервную копию баз данных MySQL (MariaDB) в Windows без phpMyAdmin

Как сделать резервную копию БД MySQL и MariaDB в Windows

Резервную копию можно создать в графическом веб-интерфейсе в phpMyAdmin. Если вы хотите сделать бэкап сразу всех баз данных, то перейдите на главную страницу phpMyAdmin, оттуда перейдите во вкладку Экспорт. Далее всё достаточно просто.

Аналогично при бэкапе отдельных баз данных: перейдите на страницу интересующей БД, а далее во вкладку Экспорт.

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

Если вы решили сделать бэкап базы данных MySQL из командной строки в Windows, то для этого понадобиться утилита mysqldump.exe, она поставляется вместе с MySQL и расположена в каталоге с установленной MySQL/MariaDB в папке bin. Например, если СУБД установлена в C:\Server\bin\mysql-8.0, то программа mysqldump.exe будет находиться в папке C:\Server\bin\mysql-8.0\bin\.

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

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

Кстати, файл нужно искать в той папке, которую вы видите в приглашении командной строки.

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

Чтобы сделать резервную копию нескольких баз данных используйте опцию —databases, а после него через пробел перечислите желаемые для бэкапа базы данных:

Чтобы сделать резервную копию только одной таблицы (wp_posts) из базы данных (wordpress):

Для того, чтобы сделать резервную копию нескольких таблиц, перечислите их через пробел после названия БД:

Восстановление баз данных из резервной копии без phpMyAdmin в Windows

Для экспорта баз данных, phpMyAdmin работает довольно неплохо. Но с импортом возникло ряд проблем. Намного проще и в разы быстрее сделать импорт без phpMyAdmin и других помощников на PHP скриптах.

Для этого откройте командную строку Windows. Нажмите комбинацию WIN+x, затем выберите Windows PowerShell. Чтобы выйти из PowerShell и перейти в командную строку, выполните:

Затем смените текущую папку (укажите путь до подпапки bin внутри папки, куда установлена MySQL или MariaDB — у вас это может быть другой путь):

Пример скрипта для создания резервной копии MySQL

Данный скрипт написан на Unix Shell под управлением операционной системы CentOS. Он будет работать на большинстве систем семейств Linux и BSD.

Пример скрипта

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

Создаем каталог для скриптов и сам скрипт:

1. #!/bin/bash
2. PATH=/etc:/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin
3. destination=»/backup/mysql»
4. userDB=»backup»
5. passwordDB=»backup»
6. fdate=`date +%Y-%m-%d`
7. find $destination -type d \( -name «*-1[^5]» -o -name «*-[023]?» \) -ctime +30 -exec rm -R <> \; 2>&1
8. find $destination -type d -name «*-*» -ctime +180 -exec rm -R <> \; 2>&1
9. mkdir $destination/$fdate 2>&1
10. for dbname in `echo show databases | mysql -u$userDB -p$passwordDB | grep -v Database`; do
11. case $dbname in
12. information_schema)
13. continue ;;
14. mysql)
15. continue ;;
16. performance_schema)
17. continue ;;
18. test)
19. continue ;;
20. *) mysqldump —databases —skip-comments -u$userDB -p$passwordDB $dbname | gzip > $destination/$fdate/$dbname.sql.gz ;;
21. esac
22. done;

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

chmod +x /scripts/mysql_backup.sh

Описание скрипта

1	Указываем на путь к интерпретатору.
2	Задаем системные переменные, чтобы не пришлось в скрипте прописывать полные пути до исполняемых файлов.
4 — 7	Задаем переменные.
4	Каталог, в котором будем сохранять резервные копии.
5	Учетная запись для подключения к базе данных.
6	Пароль для подключения к базе данных.
7	Дата, когда запускается скрипт.
9	Находим все резервные копии, которые старше 30 дней и удаляем их. Оставаляем для архива файлы на 15 число.
10	Удаляем все резервные копии старше 180 дней.
11	Создаем каталог, в который будем сохранять резервные копии. В качестве имени каталога используем дату запуска скрипта в формате ГГГГ-MM-ДД.
13 — 25	Подключаемся к базе данных и вытаскиваем список всех баз данных. Для каждой делаем резервную копию.
15 — 22	Пропускаем служебные базы information_schema, mysql, performance_schema, test.
23	Делаем резервную копию для баз.

Подготовка системы

Подключаемся к базе данных и создаем учетную запись с правом на создание резервных копий:

> GRANT SELECT, SHOW VIEW, RELOAD, REPLICATION CLIENT, EVENT, TRIGGER, LOCK TABLES, PROCESS ON *.* TO backup@localhost IDENTIFIED BY ‘backup’;

* в данном примере мы создаем учетную запись backup с паролем backup.

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

mkdir -p /backup/mysql

Сохранение данных на удаленном компьютере

Резервные копии необходимо создавать на удаленном компьютере или внешнем диске, чтобы они были доступны при выходе из строя сервера. В данном примере используется общая папка на удаленном сервере, в которой будут размещаться файлы с backup.

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

и добавим в него следующую строчку:

//192.168.0.1/backup /mnt cifs user,rw,noauto,credentials=/root/.smbclient 0 0

* в данном примере выполняется монтирование общей папки backup на сервере с IP-адресом 192.168.0.1 в каталог /mnt. В качестве сетевой файловой системы используется cifs (протокол SMB: сервер samba или общая папка Windows). Параметры для подключения — user: позволяет выполнить монтирование любому пользователю, rw: с правом на чтение и запись, noauto: не монтировать автоматически при старте системы, credentials: файл, в котором написаны логин и пароль для подключения к общей папке.

Теперь создадим файл с логином и паролем:

и приведем его к следующему виду:

* username: имя пользователя, password: пароль. Само собой, в вашем случае указываются свои данные.

Теперь введите следующую команду:

Должна примонтировать сетевая папка //192.168.0.1/backup. Это можно проверить следующей командой:

Автоматический запуск по расписанию

Создадим правило в cron для автоматического запуска нашего скрипта. Выполняем команду:

И добавляем строку:

0 3 * * * /scripts/mysql_backup.sh

* в данном примере мы будем запускать резервирование каждый день в 3 часа ночи.

ИТ База знаний

Курс по Asterisk

Полезно

— Узнать IP — адрес компьютера в интернете

— Онлайн генератор устойчивых паролей

— Онлайн калькулятор подсетей

— Калькулятор инсталляции IP — АТС Asterisk

— Руководство администратора FreePBX на русском языке

— Руководство администратора Cisco UCM/CME на русском языке

— Руководство администратора по Linux/Unix

Навигация

Серверные решения

Телефония

FreePBX и Asterisk

Настройка программных телефонов

Корпоративные сети

Протоколы и стандарты

Популярное и похожее

Курс по сетям

15 примеров команды PING для диагностики сети

Руководство по команде grep в Linux

Рекурсивно найти слово в файлах и папках Linux

Как найти файл в Linux? Руководство по команде find с примерами

Как использовать rsync для резервного копирования данных

Как показать скрытые файлы в Linux

Как заставить curl игнорировать ошибки сертификата

Еженедельный дайджест

Как сделать резервную копию и восстановить базу данных MySQL с помощью Mysqldump

Делай бекапы — не будет факапов

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

Обучайся в Merion Academy

Пройди курс по сетевым технологиям

Начать

Файлы резервных копий, созданные утилитой mysqldump, представляют собой набор операторов SQL, которые можно использовать для воссоздания исходной базы данных. Команда mysqldump также может генерировать файлы в формате CSV и XML. Вы также можете использовать утилиту mysqldump для переноса вашей базы данных MySQL на другой сервер MySQL.

Синтаксис команды Mysqldump

Прежде чем приступить к использованию команды mysqldump, начнем с обзора основного синтаксиса.

Выражения утилиты mysqldump имеют следующую форму:

• options — параметры mysqldump
• file.sql — дамп (резервная копия) файла

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

Резервное копирование одной базы данных MySQL

Наиболее распространенный вариант использования инструмента mysqldump — резервное копирование одной базы данных.

Например, чтобы создать резервную копию базы данных с именем database_name , используя пользователя root , и сохранить ее в файл с именем database_name.sql , вы должны выполнить следующую команду:

Вам будет предложено ввести пароль root. После успешной аутентификации начнется процесс дампа. В зависимости от размера базы данных процесс может занять некоторое время.

Если вы вошли в систему как тот же пользователь, которого вы используете для выполнения экспорта, и этот пользователь не требует пароля, вы можете пропустить опции -u и -p :

Резервное копирование нескольких баз данных MySQL

ля резервного копирования нескольких баз данных MySQL одной командой вам нужно использовать параметр —database , за которым следует список баз данных, которые вы хотите сделать резервную копию. Каждое имя базы данных должно быть разделено пробелом.

Команда выше создаст файл дампа, содержащий обе базы данных.

Резервное копирование всех баз данных MySQL

Используйте опцию —all-database для резервного копирования всех баз данных MySQL:

Как и в предыдущем примере, команда выше создаст один файл дампа, содержащий все базы данных.

Резервное копирование всех баз данных MySQL в отдельные файлы

Утилита mysqldump не предоставляет возможность резервного копирования всех баз данных в отдельные файлы, но мы легко достигаем этого с помощью простого цикла bash FOR:

Команда выше создаст отдельный файл дампа для каждой базы данных, используя имя базы данных в качестве имени файла.

Создание сжатой резервной копии базы данных MySQL

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

Создать резервную копию с отметкой времени

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

Команда выше создаст файл в следующем формате database_name-20200223.sql

Восстановление дампа MySQL

Вы можете восстановить дамп MySQL с помощью инструмента mysql. Общий синтаксис команды выглядит следующим образом:

В большинстве случаев вам необходимо создать базу данных куда вы будете производить импорт. Если база данных уже существует, сначала вам нужно удалить ее.

В следующем примере первая команда создаст базу данных с именем database_name , а затем импортирует в нее дамп database_name.sql :

Восстановление одной базы данных MySQL из полного дампа MySQL

Если вы создали резервную копию всех своих баз данных с помощью параметра -all-database и хотите восстановить одну базу данных из файла резервной копии, который содержит несколько баз данных, используйте параметр —one-database , как показано ниже:

Экспорт и импорт базы данных MySQL одной командой

Вместо того, чтобы создавать файл дампа из одной базы данных и затем импортировать резервную копию в другую базу данных MySQL, вы можете использовать следующую однострочную команду:

Команда выше передаст вывод клиенту mysql на удаленном хосте и импортирует его в базу данных с именем remote_database_name . Перед выполнением команды убедитесь, что база данных уже существует на удаленном сервере.

Автоматизация резервного копирования с помощью Cron

Автоматизация процесса резервного копирования баз данных так же проста, как создание задания cron, которое будет запускать команду mysqldump в указанное время.

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

1. Создайте файл с именем .my.cnf в вашем домашнем каталоге пользователя: Скопируйте и вставьте следующий текст в файл .my.cnf . Не забудьте заменить dbuser и dbpasswd на пользователя базы данных и пароль пользователя.
2. Ограничьте права доступа к файлу учетных данных, чтобы только ваш пользователь имел к нему доступ, используя команду cmod (подробнее про которую можно прочесть тут):
3. Создайте каталог для хранения резервных копий при помощи комадны mkdir (про нее тоже есть статья):
4. Откройте ваш пользовательский файл crontab: Добавьте следующее задание cron, которое будет создавать резервную копию имени базы данных mydb каждый день в 3 часа ночи: Не забудьте заменить username вашим реальным именем пользователя. Вы также можете создать еще один cron job, чтобы удалить любые резервные копии старше 30 дней: Конечно, вам нужно настроить команду в соответствии с вашим местоположением резервной копии и именами файлов. Чтобы узнать больше о команде find, ознакомьтесь с нашим Руководством по поиску файлов в Linux с помощью командной строки.

Заключение

Это руководство охватывает только основы, но оно должно быть хорошим началом для тех, кто хочет научиться создавать и восстанавливать базы данных MySQL из командной строки с помощью утилиты mysqldump. Если вы хотите найти больше материалов про базы данных, то просто наберите sql в нашем поиске!