Mysqldump где находится файл
Бэкап баз данных mysql является обязательной и регулярной процедурой при сопровождении информационной системы. Я расскажу, как делаю бэкап mysql с помощью mysqldump, а так же покажу, как сделать проверку и настроить мониторинг бэкапов баз данных. В качестве систем мониторинга будет выступать Zabbix.
Если у вас есть желание научиться строить и поддерживать высокодоступные и надежные системы, рекомендую познакомиться с онлайн-курсом «DevOps практики и инструменты» в OTUS. Курс не для новичков, для поступления нужно пройти .Настройка mysqldump
Как я уже сказал, бэкап базы данных mysql я буду делать с помощью mysqldump. В общем случае выполнить его проще простого. Вот пример команды, которая сделает дамп базы данных:
Отдельно отмечу, что использовать выгрузку сырых данных из базы в виде текстового дампа имеет смысл для не очень больших баз. Думаю, что для баз размером до 10-15 Гб это можно делать. Сжатые дампы будут весить 500-1000 Мб. Если базы больше, лучше использовать другие способы. Например, бинарный бэкап с помощью Percona XtraBackup.
- --add-drop-table - в дампе перед каждым созданием таблицы добавляется строка с её удалением. То есть при заливке дампа сначала удаляется таблица, потом создается пустая и в неё заливаются данные из дампа. И так для всех таблиц.
- --add-locks - в дампе в строке перед созданием таблицы ставится команда на ее блокировку, а после окончания заливки данных блокировка снимается. Это позволяет гарантировать успешную запись данных в таблицу при загрузке дампа.
- --create-options - в дамп добавляются команды на создание таблиц.
- --disable-keys - в дамп добавляются параметры, отключающие создание индексов рядом с каждой командой insert. Это позволяет ускорить загрузку дампа, а индексы создаются, когда все строки будут добавлены.
- --extended-insert - используется особый синтаксис multiple-row для команд insert.
- --lock-tables - блокировка таблиц перед созданием дампа. Этот параметр частенько мешает в движке innodb и его лучше не использовать (параметр, а не движок).
- --quick - ускоренный механизм получения строк таблицы.
- --set-charset - добавляет в дамп информацию о кодировках.
В целом, все дефолтные параметры можно считать полезными и удобными, кроме блокировки таблиц. Для дампа innodb баз, а это самый популярный движок хранения данных в mysql, рекомендуется не использовать lock-tables, а вместо этого включать механизм single-transaction. С этим параметром для обеспечения целостности данных в дампе используется не механизм блокировок, а учёт транзакций.
Я не буду сейчас подробно останавливаться на теории, так как все эти параметры очень хорошо описаны в документации. Можете сами зайти и почитать. Показываю свой набор ключей для создания дампов баз данных mysql с помощью mysqldump.
Это мой универсальный набор параметров mysqldump, которые я обычно использую, когда делаю выгрузку базы данных mysql. В целом, тут почти дефолтные настройки, только убраны блокировки, добавлены транзакции и некоторые другие сущности mysql типа events, triggers и т.д.
Проверка дампа mysql
То, что вы сделали дамп базы совсем не значит, что он завершился успешно. Он может быть прерван по какой-то причине. Причем, он год может выполняться успешно, а в какой-то момент начнет давать сбой в самом конце выгрузки. Если никак не отслеживаете ситуацию, можете это не заметить, а потом получить неконсистентную (битую) выгрузку.
Я делаю следующую проверку дампа сразу после его создания, чтобы не тащить на сервер бэкапов битый архив. Если дамп прошел успешно, то в первой строке дампа будет примерно такая строка:
У вас может отличаться версия сервера, но начало будет одинаковое - двойное тире и слово Mysql. В конце дампа будет следующая строка:
Эти две строки я и буду проверять в скрипте, который будет делать дамп в автоматическом режиме. Результат проверки бэкапа я буду записывать в лог файл, а заодно и вывод утилиты mysqldump, чтобы в случае какой-то ошибки, я смог его проанализировать.
Я скрипт прокомментировал, так что добавить особо нечего. Делаем дамп, проверяем первую и последнюю строки. Результат проверки пишем в лог файл, за которым далее будем наблюдать с помощью Zabbix.
Мониторинг бэкапов mysql
Для того, чтобы нам не тащить на бэкап сервер битые дампы, будем проверять их сразу же на месте. Сразу поясню, что это не отменяет дальнейшие проверки этих дампов на возможность реального восстановления из них. Эту процедуру надо делать на отдельном сервере. В рамках данной заметки я не буду это рассматривать. Сейчас мы просто будем следить за тем, что дамп базы данных mysql выполнен корректно.
Мониторинг за валидацией бэкапов будем осуществлять с помощью Zabbix.
Если у вас еще нет своего сервера для мониторинга, то рекомендую материалы на эту тему. Для тех, кто предпочитает систему CentOS:
То же самое на Debian 10, если предпочитаете его:
Мы настроили вывод результатов проверки архивов в лог файл /var/log/mysql/backup.log. Теперь сделаем так, чтобы Zabbix анализировал содержимое файла и слал оповещение, если там появится слово corrupted, что будет означать проблему с созданием дампа.
Для этого создаем новый шаблон и добавляем туда элемент данных.
Тут всё очень просто и стандартно. Далее делаем триггер.
Прикрепляем шаблон к хосту, где делаем бэкап. Не забудьте убедиться, что у zabbix-agent на хосте есть доступ на чтение этого лог файла. Таким образом, если проверка дампа не будет завершена успешно, сработает триггер. Он будет висеть активным до тех пор, пока не будет создан корректный бэкап базы mysql.
Вот так достаточно просто и быстро я решаю вопрос создания, проверки и оповещения о проблемах при создании дампов и бэкапов mysql баз.
Заключение
Решение задачи по бэкапу mysql баз, что я описал, не претендует на уникальность и 100% правильность. Это просто мой личный опыт. Никаких особых изысканий и поиска наилучшего решения не проводил. Просто сделал, как сделал, чем с вами и поделился. В моих задачах такой подход достаточен.
Еще раз напоминаю, что этот способ актуален для относительно небольших баз. Дампить объемные базы плохая идея, так как будет сильно проседать i/o дисков. Плюс тут нет возможности делать инкрементные бэкпы. Только полные, что, очевидно, не всегда удобно.
Для мониторинга бэкапов в целом, можете воспользоваться моей объемной статьей по теме - Мониторинг бэкапов с помощью zabbix.
В статье рассмотрим общие принципы выполнения резервного копирования СУБД MySQL или MariaDB. Также рассмотрим некоторые примеры часто используемых ключей и параметров резервирования.
Синтаксис и базовая команда
Создание дампа выполняется из командной строки Linux или Microsoft. Общий синтаксис:
mysqldump [опции] -p <база> > <в какой файл сделать дамп>
Пример базовой команды для резервирования базы:
mysqldump -v -uroot -p base > /tmp/dump.sql
* в данном примере мы создадим резервную копию базы base и поместим его в папку /tmp, назвав сам файл dump.sql. Подключение к базе происходит от пользователя root. Это самый простой пример создания дампа MySQL.
Базовые параметры команды mysqldump:
| Параметр | Описание |
|---|---|
| -u | Учетная запись, от которой выполняется резервное копирование. Необходимо, чтобы у пользователя были соответствующие права. |
| -p | Пароль учетной записи. Его можно ввести в команде, например -p12345 (для скрипта) или оставить -p (безопаснее). |
* полный перечень параметров смотрите в официальном руководстве.
Примеры создания дампа MySQL
1. С последующим архивированием
mysqldump -uroot -p $ | gzip > /tmp/$.sql.gz
* в данном примере мы сначала создали переменную DBNAME, в которую внесли значение с именем базы, которую необходимо забэкапить. После выполняем команду mysqldump, результат выполнения которой по конвееру отдаем архиватору gzip. В результате мы получит дамп по пути /tmp/<имя базы>.sql.gz
Или с подробным выводом информации на экран (дольше по времени):
mysqldump -v -uroot -p $ | gzip > /tmp/$.sql.gz
2. Для одновременно нескольких баз
Просто перечисляем имена баз через пробел и добавляем параметр -B
mysqldump -v -uroot -p -B base1 base2 base3 > /tmp/dump_multiply_bases.sql
3. Для всех баз одной командой
Для этого ставим --all-databases, вместо имен баз
mysqldump -v -uroot -p --all-databases > /tmp/dump_all_bases.sql
4. Резервирование только структуры базы
Для уточнения, это создание копии только самих таблиц без данных. Делается добавлением параметра --no-data
mysqldump -v -uroot -p --no-data base1 > /tmp/dump_base1_nodata.sql
5. Создание копии определенной таблицы
Для этого после базы через пробел перечисляем названия таблиц
mysqldump -v -uroot -p base1 table1 table2 > /tmp/dump_base1_tables.sql
6. Резервирование прав доступа на СУБД
Позволяет выгрузить все учетные записи с паролями. Удобно для переноса СУБД на новый сервер без потери доступа к нему.
mysqldump -v -uroot -p mysql user > /tmp/mysql_user.sql
* после восстановления этого дампа, необходимо в sql shell выполнить команду flush privileges;
7. Проигнорировать определенную таблицу
Выполняется при помощи ключа ignore-table:
mysqldump -v -uroot -p base --ignore-table=base.logs > /tmp/base.sql
Скрипт для резервного копирования
Для повседневных операций по резервному копированию MySQL рекомендуется написать скрипт и запускать его через cron.
Резервное копирование в phpMyAdmin
В качестве графического инструмента для работы с MySQL/MariaDB используется phpMyAdmin. Разберем, как с его помощью сделать экспорт данных.
В верхней части меню кликаем по Экспорт:
В разделе «Способ экспорта» ставим переключатель в положение Обычный:
* обычный режим откроет дополнительные опции для резервного экспорта данных.
Утилита mysqldump позволяет получить дамп содержимого базы данных или совокупности баз для создания резервной копии или пересылки данных на другой SQL-сервер (не обязательно MySQL-сервер). Дамп будет содержать набор команд SQL для создания и/или заполнения таблиц.
Так же mysqldump имеет возможность развертывания баз данных из созданного sql-файла.
Создание дампа
Разберем пример простейшее использования, задампим базу данных "database" при помощи перенаправления потока в файл "database.sql":
- -u или -–user=. - имя пользователя
- -h или --host=. - удаленный хост (для локального хоста можно опустить этот параметр)
- -p или --password - запросить пароль
- 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).
--all-databases, -A
Сохраняет все таблицы из всех баз данных, которые находятся под управлением текущего сервера.
--allow-keywords
Разрешить создавать имена столбцов, которые совпадают с ключевыми словами. Отсутствие конфликтов обеспечивается прибавлением имени таблицы в качестве префикса к имени каждого столбца.
--comments, -i
Данный параметр позволяет добавить в дамп дополнительную информацию, такую, как версия mysqldump, версия MySQL, имя хоста, на котором расположен сервер MySQL.
--compact
Данный параметр требует от mysqldump создать дамп, используя как можно более компактный формат. Параметр является противоположным --comments.
--compatible=name
Параметр генерирует вывод, который совместим с другими СУБД или более старыми версиями MySQL. Вместо ключевого слова name можно использовать: "ansi", "mysql323", "mysql40", "postgresql", "oracle", "mssql", "db2", "maxdb", "no_key_options", "no_table_options", "no_field_options". Можно использовать несколько значений, разделив их запятыми.
--complete-insert, -c
Используется полная форма оператора INSERT (с именами столбцов).
--create-options
Добавляет дополнительную информацию в операторы CREATE TABLE. Это может быть тип таблицы, начальное значение AUTO_INCREMENT и другие параметры.
--databases, -B
Параметр позволяет указать имена нескольких баз данных, для которых необходимо создать дамп.
--delayed
Использовать команду INSERT DELAYED при вставке строк.
--delete-master-logs
На главном сервере репликации автоматически удаляются бинарные логи (logbin) после того, как дамп был успешно создан при помощи mysqldump. Этот параметр автоматически включает параметр "--master-data".
--disable-keys, -K
Для каждой таблицы, окружает оператор INSERT выражениями /*!40000 ALTER TABLE tbl_name DISABLE KEYS */; и /*!40000 ALTER TABLE tbl_name ENABLE KEYS */; в выводе результата дампа. Это ускорит загрузку данных на сервер для таблиц типа MyISAM, так как индексы создаются после внесения всех данных.
--extended-insert, -e
Использовать команду INSERT с новым многострочным синтаксисом (повышает компактность и быстродействие операторов ввода).
--flush-logs, -F
Записать на диск данные системного журнала из буфера MySQL-сервера перед началом выполнения дампа.
--force, -f
Продолжать даже если в процессе создания дампа произошла ошибка.
--hex-blob
Параметр позволяет представить бинарные данные в полях типа BINARY, VARBINARY, BLOB и BIT в шестнадцатеричном формате. Так последовательность "abc" будет заменена на 0x616263.
--ignore-table=db_name.tbl_name
Позволяет игнорировать таблицу tbl_name базы данных db_name при создании дампа. Если из дампа необходимо исключить несколько таблиц, необходимо использовать несколько параметров "--ignore-table", указывая по одной таблице в каждом из параметров.
--insert-ignore
Добавляет ключевое слово IGNORE в оператор INSERT.
--lock-all-tables, -x
Указание этого параметра приводит к блокировке всех таблиц во всех базах данных на время создания полного дампа всех баз данных.
--lock-tables, -l
Указание этого параметра приводит к блокировке таблиц базы данных, для которой создается дамп.
--no-autocommit
Включает все операторы INSERT, относящиеся к одной таблице, в одну транзакцию, что приводит к увеличению скорости загрузки данных.
--no-create-db, -n
Подавляет создание в дампе операторов CREATE DATABASE, которые автоматически добавляются при использовании параметров --databases и --all-databases.
--no-data, -d
Подавляет создание операторов INSERT в дампе, что может быть полезно при создании дампа структуры базы данных без самих данных.
--opt
Параметр предназначен для оптимизации скорости резервирования данных и является сокращением, включающим следующие опции: --quick --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --set-charset. Начиная с MySQL 4.1, параметр --opt используется по умолчанию, т.е. все вышеперечисленные параметры включаются по умолчанию, даже если они не указываются. Для того чтобы исключить такое поведение, необходимо воспользоваться параметров --skip-opt
--order-by-primary
Указание параметра приводит к тому. что каждая таблица сортируется по первичному ключу или первому уникальному индексу.
--port, -P
Номер TCP порта, используемого для подключения к хосту.
--protocol=
Параметр позволяет задать протокол подключения к серверу.
--quick, -q
Позволяет начать формирование дампа, не дожидаясь полной загрузки данных с сервера и экономя тем самым память.
--quote-names, -Q
Помещает имена баз данных, таблиц и столбцов в обратные апострофы `. Начиная с MySQL 4.1, данный параметр включен по умолчанию.
--replace
Добавляет ключевое слово REPLACE в оператор INSERT. Данный параметр впервые появился в MySQL 5.1.3.
--routines, -R
Данный параметр создает дамп хранимых процедур и функций. Доступен с MySQL 5.1.2.
--single-transaction
Параметр создает дамп в виде одной транзакции.
--skip-comments
Данный параметр позволяет подавить вывод в дамп дополнительной информации.
--socket=/path/to/socket, -S /path/to/socket
Файл сокета для подсоединения к localhost.
--tab=/path/, -T /path/
При использовании этого параметра в каталоге path для каждой таблицы создаются два отдельных файла: tbl_name.sql, содержащий оператор CREATE TABLE, и tbl_name.txt, который содержит данные таблиц, разделенные символом табуляции. Формат данных может быть переопределен явно с помощью параметров --fields-xxx и --lines-xxx.
--tables
Перекрывает действия параметра --databases (-B). Все аргументы, следующие за этим параметром, трактуются как имена таблиц.
--triggers
Создается дамп триггеров. Этот параметр включен по умолчанию. для его отключения следует использовать параметр --skip-triggers.
--tz-utc
при использовании данного параметра в дамп будет добавлен оператор вида SET TIME_ZONE='+00:00', который позволит обмениваться дампа в различных временных зонах.
--verbose, -v
Расширенный режим вывода. Вывод более детальной информации о работе программы.
--version, -V
Вывести информацию о версии программы.
--where='where-condition', -w 'where-condition'
Выполнить дамп только выбранных записей. Обратите внимание, что кавычки обязательны.
--xml, -X
Представляет дамп базы данных в виде XML.
--first-slave, -x
Блокирует все таблицы во всех базах данных.
--help
Вывести справочную информацию и выйти из программы.
Еще пару слов о бекапе в MySQL
mysqlhotcopy для MyISAM
Для быстрого резервирования БД с типом таблиц ISAM и MyISAM можно использовать "mysqlhotcopy", которая скопирует файлы *.frm, *.MYD и *.MYI:
Для InnoDB не подойдет данный способ, потому что при этом типе не обязательно все файлы будут храниться в директории базы данных.
xtrabackup для InnoDB
Для InnoDB есть xtrabackup, рекомендую посмотреть!
UPD: XtraBackup - резервное копирование для innoDB
Бин-лог и репликации
Для репликации "mysqldump" не предназначена, для этого есть бин-лог (--log-bin):
Для полной репликации необходимо вести бин-лог с самого начала работы БД, то есть еще до создания структур и данных.
Резервирование данныс в MySQL 6.x
С версии MySQL 6.x доступен online-backup, вот слайд объясняющий нововведения:
В этом руководстве объясняется, как создавать резервные копии и восстанавливать базы данных MySQL или MariaDB из командной строки с помощью утилиты mysqldump.
Файлы резервных копий, созданные утилитой mysqldump, в основном представляют собой набор операторов SQL, которые можно использовать для воссоздания исходной базы данных. Команда mysqldump также может создавать файлы в форматах CSV и XML.
Вы также можете использовать утилиту mysqldump для переноса вашей базы данных MySQL на другой сервер MySQL.
Если вы не сделаете резервную копию своих баз данных, программная ошибка или отказ жесткого диска могут иметь катастрофические последствия. Чтобы сэкономить время и нервы, настоятельно рекомендуется принять меры предосторожности и регулярно создавать резервные копии баз данных MySQL.
Синтаксис команды Mysqldump
Прежде чем перейти к использованию команды mysqldump, давайте начнем с обзора основного синтаксиса.
Выражения утилиты mysqldump имеют следующую форму:
Чтобы использовать команду mysqldump, сервер MySQL должен быть доступен и запущен.
Резервное копирование единой базы данных MySQL
Например, чтобы создать резервную копию базы database_name с именем database_name используя пользовательский root и сохранить ее в файл с именем database_name.sql вы должны выполнить следующую команду:
Вам будет предложено ввести пароль root. После успешной аутентификации начнется процесс дампа. В зависимости от размера базы данных процесс может занять некоторое время.
Если вы вошли в систему как тот же пользователь, которого используете для экспорта, и этот пользователь не требует пароля, вы можете опустить параметры -u и -p :
Резервное копирование нескольких баз данных MySQL
Для резервного копирования нескольких баз данных MySQL с помощью одной команды вам нужно использовать параметр --database за которым следует список баз данных, которые вы хотите --database . Каждое имя базы данных должно быть разделено пробелом.
Приведенная выше команда создаст файл дампа, содержащий обе базы данных.
Резервное копирование всех баз данных MySQL
Используйте параметр --all-databases для резервного копирования всех баз данных MySQL:
Как и в предыдущем примере, приведенная выше команда создаст один файл дампа, содержащий все базы данных.
Резервное копирование всех баз данных MySQL в отдельные файлы
Утилита mysqldump не предоставляет возможности резервного копирования всех баз данных в отдельные файлы, но мы легко достигаем этого с помощью простого цикла FOR bash :
Приведенная выше команда создаст отдельный файл дампа для каждой базы данных, используя имя базы данных в качестве имени файла.
Создание сжатой резервной копии базы данных MySQL
Если размер базы данных очень велик, рекомендуется сжать вывод. Для этого просто направьте вывод в утилиту gzip и перенаправьте его в файл, как показано ниже:
Создать резервную копию с отметкой времени
Если вы хотите хранить более одной резервной копии в одном месте, вы можете добавить текущую дату к имени файла резервной копии:
Приведенная выше команда создаст файл в следующем формате database_name-20180617.sql
Восстановление дампа MySQL
Вы можете восстановить дамп MySQL с помощью инструмента mysql . Общий синтаксис команды следующий:
В большинстве случаев вам потребуется создать базу данных для импорта. Если база данных уже существует, сначала ее нужно удалить.
В следующем примере первая команда создаст базу данных с именем database_name а затем импортирует в нее дамп database_name.sql :
Восстановление единой базы данных MySQL из полного дампа MySQL
Если вы создали резервную копию всех своих баз данных с помощью параметра -all-databases и хотите восстановить одну базу данных из файла резервной копии, содержащего несколько баз данных, используйте параметр --one-database как показано ниже:
Экспорт и импорт базы данных MySQL одной командой
Вместо создания файла дампа из одной базы данных и последующего импорта резервной копии в другую базу данных MySQL вы можете использовать следующий однострочный файл:
Приведенная выше команда передаст вывод клиенту mysql на удаленном хосте и импортирует его в базу данных с именем remote_database_name . Перед запуском команды убедитесь, что база данных уже существует на удаленном сервере.
Автоматизируйте резервное копирование с помощью Cron
Автоматизировать процесс резервного копирования баз данных так же просто, как создать задание cron, которое будет запускать команду mysqldump в указанное время.
Чтобы настроить автоматическое резервное копирование базы данных MySQL с помощью cronjob, выполните следующие действия:
Создайте файл с именем .my.cnf в домашнем каталоге пользователя:
Скопируйте и вставьте следующий текст в файл .my.cnf.
Не забудьте заменить dbuser и dbpasswd на пользователя базы данных и пароль пользователя.
Ограничьте права доступа к файлу учетных данных, чтобы только ваш пользователь имел к нему доступ:
Создайте каталог для хранения резервных копий:
Откройте ваш пользовательский файл crontab:
Добавьте следующее задание cron, которое будет создавать резервную копию базы данных с именем mydb каждый день в 3 часа ночи:
Не забудьте заменить username своим фактическим именем пользователя. Мы также избегаем знаков процента ( % ), потому что они имеют особое значение в crontab.
Вы также можете создать другое задание cron для удаления любых резервных копий старше 30 дней:
Конечно, вам нужно настроить команду в соответствии с местоположением резервной копии и именами файлов. Чтобы узнать больше о команде find, ознакомьтесь с нашим руководством по поиску файлов в Linux с помощью командной строки .
Выводы
Это руководство охватывает только основы, но оно должно стать хорошим началом для всех, кто хочет научиться создавать и восстанавливать базы данных MySQL из командной строки с помощью утилиты mysqldump.
Если вы хотите узнать больше о работе с MySQL из командной строки, ознакомьтесь с нашим руководством по управлению учетными записями пользователей и базами данных MySQL .
Вы также можете проверить руководство о том, как сбросить пароль root MySQL, если вы его забыли.
Если у вас есть какие-либо вопросы или отзывы, не стесняйтесь оставлять комментарии.
Читайте также: