Sql dump что это
Как сделать дамп базы MySQL / MariaDB
В статье рассмотрим общие принципы выполнения резервного копирования СУБД MySQL или MariaDB. Также рассмотрим некоторые примеры часто используемых ключей и параметров резервирования.
Синтаксис и базовая команда
Создание дампа выполняется из командной строки Linux или Microsoft. Общий синтаксис:
Пример базовой команды для резервирования базы:
* в данном примере мы создадим резервную копию базы base и поместим его в папку /tmp, назвав сам файл dump.sql. Подключение к базе происходит от пользователя root. Это самый простой пример создания дампа MySQL.
Базовые параметры команды mysqldump:
* полный перечень параметров смотрите в официальном руководстве.
Примеры создания дампа MySQL
1. С последующим архивированием
Или с подробным выводом информации на экран (дольше по времени):
2. Для одновременно нескольких баз
3. Для всех баз одной командой
4. Резервирование только структуры базы
5. Создание копии определенной таблицы
Для этого после базы через пробел перечисляем названия таблиц
6. Резервирование прав доступа на СУБД
Позволяет выгрузить все учетные записи с паролями. Удобно для переноса СУБД на новый сервер без потери доступа к нему.
* после восстановления этого дампа, необходимо в sql shell выполнить команду flush privileges;
7. Проигнорировать определенную таблицу
Выполняется при помощи ключа ignore-table:
Скрипт для резервного копирования
Для повседневных операций по резервному копированию MySQL рекомендуется написать скрипт и запускать его через cron.
Резервное копирование в phpMyAdmin
В качестве графического инструмента для работы с MySQL/MariaDB используется phpMyAdmin. Разберем, как с его помощью сделать экспорт данных.
В верхней части меню кликаем по Экспорт:
В разделе «Способ экспорта» ставим переключатель в положение Обычный:
* обычный режим откроет дополнительные опции для резервного экспорта данных.
Выбираем компрессию, например, zip:
И в нижней части окна нажимаем OK.
Начнется загрузка файла с резервной копией на компьютер.
Для восстановления базы читайте инструкцию Как восстановить базу MySQL
Что такое дамп базы данных MySQL и как его сделать?
Следовательно, умение делать dump базы данных немаловажно для любого веб-разработчика. Что это такое? Это файл, содержащий инструкции на языке SQL, за счет которых создается точная копия вашей БД как по содержанию, так и по структуре. Для каких случаев вам стоит сделать дамп базы MySQL:
Dump может понадобиться, если вы запланировали миграцию БД на другой хостинг. С инструмента помощью вы сможете без каких-либо трудностей воссоздать точную копию старой базы данных.
Виртуальный хостинг сайтов, на котором включено ежедневное резервное копирование. Регулярное резервное копирование данных сэкономит вам немало сил и нервов в случае непредвиденных ситуаций. Неудачный эксперимент с сайтом способен привести к потере всей базы, и лишь вовремя сделанный дамп поможет вам в решении этой проблемы. Естественно, при этом его нельзя хранить на одном сервере с основной базой, ведь в противном случае вы потеряете всю информацию без возможности ее восстановления.
Как создать дамп базы без ошибок?
Существуют различные способы создания дампа, и далее мы рассмотрим основные варианты:
В этом случае для создания SQL дампа БД используется командная строка или консоль MySQL. Выполните подключение по SSH и введите команду «mysqldump», запустите утилиту и укажите специальные параметры:
Файл можно открыть в текстовом редакторе, как пример, для поиска записей и строк по части слова или темы. Обратите внимание, что не все редакторы поддерживают файлы больших размеров.
В том же случае, если вы не знаете консольных команд, вам потребуется дополнительное программное обеспечение.
Если вы предпочитаете использовать дополнительное программное обеспечение, то можно создать dump базы данных средствами PHP при помощи, например, phpMyAdmin. Для этого выполните следующие действия:
— Добавить SQL строчку «DROP TABLE/VIEW/PROCEDURE/FUNCTION/EVENT»
Нажать соответствующую кнопку.
Данная опция позволяет вам в дальнейшем без ошибок восстановить базу MySQL из дампа. Все таблицы, имеющие одинаковые имена, сначала будут удалены, то есть исполнится команда DROP, а затем заменятся на аналогичные из вашего дампа. В итоге вы обезопасите себя от создания дублей таблиц и как следствие, неправильной работы базы данных.
Теперь вы знаете, как сделать полный дамп, если вам вдруг потребуется перенос базы на другой сервер или возникнет любая другая ситуация, когда может пригодиться резервная копия БД. Конечно, существует еще достаточно много способов создания дампа, помимо описанных выше. Но даже владение самыми простыми вариантами и регулярное применение их на практике позволит вам более уверенно чувствовать себя в любой непредвиденной ситуации, ведь достаточно будет просто развернуть дамп MySQL и сразу восстановить все базы данных.
Если вы решились купить dedicated server в нашей компании, то Вам выдается 100 ГБ на удаленном хранилище бекапов, куда можно настроить автоматическое резервное копирование информации. В таком случае, у вас всегда будет свежий дамп.
Важная информация: Не храните и не публикуйте файлы с доступом по ссылке. Вся информацию (data) необходимо хранить в надежном месте.
Что такое дамп базы данных и как его создать
Перенос сайта с одного сервера на другой может оказаться непростой задачей для многих пользователей. Это связано с тем, что, помимо обычного перемещения файлов, необходимо также выполнить экспорт и импорт базы данных. В таких случаях используется специальный файл под названием дамп. Поговорим в сегодняшней статье, что это такое и как его сделать.
Что такое дамп базы данных
Копирование базы данных может быть полезно, когда нужно выполнить:
Помимо этого, дамп может заполнить не только пустую базу, но и заменить содержимое ранее созданной. Давайте перейдем к практической части и посмотрим на наглядном примере, как все это можно организовать.
Создаем дамп базы данных MySQL
Существует несколько способов создания дампов: через консольное окно или с помощью phpMyAdmin. Рассмотрим последовательно каждый из методов, а также попробуем восстановить БД из дампа.
Способ 1: Консольное окно MySQL
Удаленное подключение к хостингу по SSH разрешает работать с информационными хранилищами. Выбор данного протокола обусловлен его высокой безопасностью, так как вся информация передается в зашифрованном виде без возможности перехвата трафика.
Для подключения вы можете воспользоваться такими программами, как PuTTY и WinSCP – они распространяются в бесплатном доступе. Остановимся на первой утилите и посмотрим, как с ее помощью можно сделать дамп базы данных MySQL.
Обратите внимание, что если на компьютере функционирует сервер с БД, то соединение через порт 3306 будет некорректно. В таких случаях рекомендуется использовать другие значения, например, 3307, 3308 и так далее.
Теперь мы можем переходить к удаленному администрированию БД: создадим дамп базы данных MySQL. Для этого введем в консоль следующий запрос:
В целях безопасности рекомендуется вообще не использовать логин и пароль. В таком случае команда примет следующий вид:
Для понимания можете взглянуть на пример с использованием пользователя и пароля:
Таким образом будет создан файл WordPressDump.sql, содержащий в себе все нужные данные для точного копирования. Посмотрим, как этот файл импортировать в проект через консоль:
Аналогично подставляем свои данные в команду и в итоге получаем:
Также при импорте мы можем указать кодировку — для этого достаточно добавить ключ default-character-set. В итоге код преобразуется:
Вот такими несложными действиями можно сделать копирование через консольное окно. Теперь давайте «покопаемся» в phpMyAdmin и выполним в нем копирование БД.
Способ 2: Инструмент phpMyAdmin
PhpMyAdmin по умолчанию предустановлен на каждой CMS. Доступ к нему осуществляется через личный кабинет пользователя на хостинге либо через локальный веб-сервер на домашнем ПК.
Подключаемся к phpMyAdmin и экспортируем БД:
После этого нам будет предложен выбор места сохранения файла. В последующем мы сможем его использовать через вкладку «Импорт». Для этого достаточно загрузить файл и указать подходящую для него кодировку:
В заключение стоит сказать, что дамп базы данных – это незаменимый файл, без которого не обходится ни один серверный переезд. Используйте его для переноса базы на хостинге или с локальной машины, а также для создания резервных копий. Удачи!
Настройка mysqldump, проверка и мониторинг бэкапов mysql
Бэкап баз данных mysql является обязательной и регулярной процедурой при сопровождении информационной системы. Я расскажу, как делаю бэкап mysql с помощью mysqldump, а так же покажу, как сделать проверку и настроить мониторинг бэкапов баз данных. В качестве систем мониторинга будет выступать Zabbix.
Настройка mysqldump
Как я уже сказал, бэкап базы данных mysql я буду делать с помощью mysqldump. В общем случае выполнить его проще простого. Вот пример команды, которая сделает дамп базы данных:
Отдельно отмечу, что использовать выгрузку сырых данных из базы в виде текстового дампа имеет смысл для не очень больших баз. Думаю, что для баз размером до 10-15 Гб это можно делать. Сжатые дампы будут весить 500-1000 Мб. Если базы больше, лучше использовать другие способы. Например, бинарный бэкап с помощью Percona XtraBackup.
В целом, все дефолтные параметры можно считать полезными и удобными, кроме блокировки таблиц. Для дампа innodb баз, а это самый популярный движок хранения данных в mysql, рекомендуется не использовать lock-tables, а вместо этого включать механизм single-transaction. С этим параметром для обеспечения целостности данных в дампе используется не механизм блокировок, а учёт транзакций.
Я не буду сейчас подробно останавливаться на теории, так как все эти параметры очень хорошо описаны в документации. Можете сами зайти и почитать. Показываю свой набор ключей для создания дампов баз данных mysql с помощью mysqldump.
Это мой универсальный набор параметров mysqldump, которые я обычно использую, когда делаю выгрузку базы данных mysql. В целом, тут почти дефолтные настройки, только убраны блокировки, добавлены транзакции и некоторые другие сущности mysql типа events, triggers и т.д.
Проверка дампа mysql
То, что вы сделали дамп базы совсем не значит, что он завершился успешно. Он может быть прерван по какой-то причине. Причем, он год может выполняться успешно, а в какой-то момент начнет давать сбой в самом конце выгрузки. Если никак не отслеживаете ситуацию, можете это не заметить, а потом получить неконсистентную (битую) выгрузку.
Я делаю следующую проверку дампа сразу после его создания, чтобы не тащить на сервер бэкапов битый архив. Если дамп прошел успешно, то в первой строке дампа будет примерно такая строка:
Эти две строки я и буду проверять в скрипте, который будет делать дамп в автоматическом режиме. Результат проверки бэкапа я буду записывать в лог файл, а заодно и вывод утилиты mysqldump, чтобы в случае какой-то ошибки, я смог его проанализировать.
Я скрипт прокомментировал, так что добавить особо нечего. Делаем дамп, проверяем первую и последнюю строки. Результат проверки пишем в лог файл, за которым далее будем наблюдать с помощью Zabbix.
Мониторинг бэкапов mysql
Для того, чтобы нам не тащить на бэкап сервер битые дампы, будем проверять их сразу же на месте. Сразу поясню, что это не отменяет дальнейшие проверки этих дампов на возможность реального восстановления из них. Эту процедуру надо делать на отдельном сервере. В рамках данной заметки я не буду это рассматривать. Сейчас мы просто будем следить за тем, что дамп базы данных mysql выполнен корректно.
Мониторинг за валидацией бэкапов будем осуществлять с помощью Zabbix.
Если у вас еще нет своего сервера для мониторинга, то рекомендую материалы на эту тему. Для тех, кто предпочитает систему CentOS:
То же самое на Debian 10, если предпочитаете его:
Мы настроили вывод результатов проверки архивов в лог файл /var/log/mysql/backup.log. Теперь сделаем так, чтобы Zabbix анализировал содержимое файла и слал оповещение, если там появится слово corrupted, что будет означать проблему с созданием дампа.
Для этого создаем новый шаблон и добавляем туда элемент данных.
Тут всё очень просто и стандартно. Далее делаем триггер.
Прикрепляем шаблон к хосту, где делаем бэкап. Не забудьте убедиться, что у zabbix-agent на хосте есть доступ на чтение этого лог файла. Таким образом, если проверка дампа не будет завершена успешно, сработает триггер. Он будет висеть активным до тех пор, пока не будет создан корректный бэкап базы mysql.
Вот так достаточно просто и быстро я решаю вопрос создания, проверки и оповещения о проблемах при создании дампов и бэкапов mysql баз.
Заключение
Решение задачи по бэкапу mysql баз, что я описал, не претендует на уникальность и 100% правильность. Это просто мой личный опыт. Никаких особых изысканий и поиска наилучшего решения не проводил. Просто сделал, как сделал, чем с вами и поделился. В моих задачах такой подход достаточен.
Еще раз напоминаю, что этот способ актуален для относительно небольших баз. Дампить объемные базы плохая идея, так как будет сильно проседать i/o дисков. Плюс тут нет возможности делать инкрементные бэкпы. Только полные, что, очевидно, не всегда удобно.
Утилита mysqldump и шпаргалка по параметрам
Утилита mysqldump позволяет получить дамп содержимого базы данных или совокупности баз для создания резервной копии или пересылки данных на другой SQL-сервер (не обязательно MySQL-сервер). Дамп будет содержать набор команд SQL для создания и/или заполнения таблиц.
Так же mysqldump имеет возможность развертывания баз данных из созданного sql-файла.
Создание дампа
Разберем пример простейшее использования, задампим базу данных «database» при помощи перенаправления потока в файл «database.sql»:
Для того чтобы сделать дамп несколько баз данных, необходимо использовать параметр —databases (или сокращенно -B), пример:
А для того чтобы сделать дамп всех баз данных, необходимо использовать параметр —all-databases (или сокращенно -A), пример:
Развертывание дампа
Перенаправляем поток в обратную сторону и развертываем базу данных:
Или через mysql-console:
Ну, а если у нас gz-архив к примеру, то:
Пример использование некоторых параметров
Например, нам нужны данные с «продакшен версии базы» для «версии разработчика», то есть нам нужна «песочница». Выбираем не более 100 записей:
Или нам нужна только структура, без данных:
Делаем дамп только триггеров, процедур и событий:
Шпаргалка по параметрам
Приведу некоторые параметры, которые могут понадобится при работе с утилитой mysqldump.
—add-drop-database
Добавляет оператор DROP DATABASE перед каждым оператором CREATE DATABASE.
—add-drop-table
Добавляет оператор DROP TABLE перед каждым оператором CREATE TABLE.
—add-locks
Добавляет оператор LOCK TABLES перед выполнением и UNLOCK TABLE после выполнения каждого дампа таблицы (для ускорения доступа к MySQL).
—allow-keywords
Разрешить создавать имена столбцов, которые совпадают с ключевыми словами. Отсутствие конфликтов обеспечивается прибавлением имени таблицы в качестве префикса к имени каждого столбца.
—compact
Данный параметр требует от mysqldump создать дамп, используя как можно более компактный формат. Параметр является противоположным —comments.
—compatible=name
Параметр генерирует вывод, который совместим с другими СУБД или более старыми версиями MySQL. Вместо ключевого слова name можно использовать: «ansi», «mysql323», «mysql40», «postgresql», «oracle», «mssql», «db2», «maxdb», «no_key_options», «no_table_options», «no_field_options». Можно использовать несколько значений, разделив их запятыми.
—create-options
Добавляет дополнительную информацию в операторы CREATE TABLE. Это может быть тип таблицы, начальное значение AUTO_INCREMENT и другие параметры.
—delayed
Использовать команду INSERT DELAYED при вставке строк.
—delete-master-logs
На главном сервере репликации автоматически удаляются бинарные логи (logbin) после того, как дамп был успешно создан при помощи mysqldump. Этот параметр автоматически включает параметр «—master-data».
—hex-blob
Параметр позволяет представить бинарные данные в полях типа BINARY, VARBINARY, BLOB и BIT в шестнадцатеричном формате. Так последовательность «abc» будет заменена на 0x616263.
—ignore-table=db_name.tbl_name
Позволяет игнорировать таблицу tbl_name базы данных db_name при создании дампа. Если из дампа необходимо исключить несколько таблиц, необходимо использовать несколько параметров «—ignore-table», указывая по одной таблице в каждом из параметров.
—insert-ignore
Добавляет ключевое слово IGNORE в оператор INSERT.
—no-autocommit
Включает все операторы INSERT, относящиеся к одной таблице, в одну транзакцию, что приводит к увеличению скорости загрузки данных.
—order-by-primary
Указание параметра приводит к тому. что каждая таблица сортируется по первичному ключу или первому уникальному индексу.
—protocol=
Параметр позволяет задать протокол подключения к серверу.
—replace
Добавляет ключевое слово REPLACE в оператор INSERT. Данный параметр впервые появился в MySQL 5.1.3.
—single-transaction
Параметр создает дамп в виде одной транзакции.
—skip-comments
Данный параметр позволяет подавить вывод в дамп дополнительной информации.
—tables
Перекрывает действия параметра —databases (-B). Все аргументы, следующие за этим параметром, трактуются как имена таблиц.
—triggers
Создается дамп триггеров. Этот параметр включен по умолчанию. для его отключения следует использовать параметр —skip-triggers.
—tz-utc
при использовании данного параметра в дамп будет добавлен оператор вида SET TIME_ZONE=’+00:00′, который позволит обмениваться дампа в различных временных зонах.
—help
Вывести справочную информацию и выйти из программы.
Еще пару слов о бекапе в MySQL
mysqlhotcopy для MyISAM
Для быстрого резервирования БД с типом таблиц ISAM и MyISAM можно использовать «mysqlhotcopy», которая скопирует файлы *.frm, *.MYD и *.MYI:
Для InnoDB не подойдет данный способ, потому что при этом типе не обязательно все файлы будут храниться в директории базы данных.
xtrabackup для InnoDB
Бин-лог и репликации
Для репликации «mysqldump» не предназначена, для этого есть бин-лог (—log-bin):
Для полной репликации необходимо вести бин-лог с самого начала работы БД, то есть еще до создания структур и данных.
Резервирование данныс в MySQL 6.x
С версии MySQL 6.x доступен online-backup, вот слайд объясняющий нововведения: