Резервное копирование MySQL (backup)
Во множество задач относящихся к администрированию баз данных входит резервирование и восстановление базы данных, которую мы и постараемся немного обсудить в этом посте. Чтобы минимизировать риск необратимой потери данных, необходимо регулярно создавать резервные копии баз данных, в которых будут сохраняться производимые изменения данных. Хорошо продуманная стратегия резервного копирования и восстановления защищает базы от потери данных при повреждениях, происходящих из-за различных сбоев.
Резервное копирование – один из надежных способов сохранить и предохранить свои данные от потери или порчи. Процесс резервного копирования также делается в профилактических целях, для увеличения производительности базы данных – это достигается за счет того, что в момент копирования происходит считывание последних версий всех записей, старые же версии в копию никогда не попадают. Здесь важно заметить, что недостаточно одного лишь резервного копирования, нужно иногда проверять восстанавливаемость базы данных из резервной копии, потому что бывают случаи, что база данных работает в постоянно, backup базы данных может, происходит нормально, но в силу определенных причин база данных не восстанавливается, последствия могут быть плачевными для всех данных. По этому, необходимо проводить профилактическое восстановление базы данных из backup`ов.
Существует несколько вариантов резервирования баз данных, опишу несколько из них.
Копирование файлов базы
Базу данных MySQL можно скопировать, если временно выключить MySQL-сервер и просто скопировать файлы из папки /var/lib/mysql/(имя БД)/. Если сервер не выключить, по очевидным причинам вероятна потеря и порча данных. Для больших нагруженных баз эта вероятность близка к 100%. Кроме того, при первом запуске с «грязной» копией базы данных MySQL-сервер начнет процесс проверки всей базы, который может затянуться на часы.
Копирование через текстовые файлы
Для того, что бы считать в backup данные из базы, необязательно работать с файлами на прямую. Можно выбрать данные запросом и сохранить их в текстовый файл. Для этого используется SQL-команда:
- Код: выделить все
SELECT INTO OUTFILE
Выгрузка производится построчно (можно отобрать для сохранения только нужные строки, как в обычном SELECT). Структура таблиц нигде не указывается, сохраняются только сами данные. Также необходимо позаботиться о включении команд SELECT INTO OUTFILE в транзакцию, если это необходимо для обеспечения целостности данных. На практике SELECT INTO OUTFILE используется для частичного backup`а очень больших таблиц, которые нельзя скопировать никаким другим образом.
По умолчанию MySQL работает в режиме autocommit. Это означает, что результаты выполнения любого SQL-оператора, изменяющего данные, будут сразу сохраняться.
Режим autocommit можно отключить командой
- Код: выделить все
SET AUTOCOMMIT = 0;
При отключенном режиме autocommit каждую транзакцию надо явно завершать операторами COMMIT (подтвердить)/ ROLLBACK (отменить/откатиться).
Примеры использования:
- Код: выделить все
SELECT * FROM person p INTO OUTFILE '/tmp/result.txt';
Или с указанием дополнительных параметров для хранения в определенном виде с использованием ручным подтверждением транзакций:
- Код: выделить все
SET AUTOCOMMIT = 0;
START TRANSACTION;
SELECT * INTO OUTFILE '/tmp/result.txt'
FIELDS TERMINATED BY ',' /
OPTIONALLY ENCLOSED BY '”'
LINES TERMINATED BY '\n'
FROM Person p;
COMMIT;
Вместо * (звёздочки) можно указать необходимые поля для выгрузки
START TRANSACTION; – начать транзакцию
FIELDS TERMINATED BY ',' – границы данных определены по символу ','
OPTIONALLY ENCLOSED BY '”' – определяет, что данные могут содержать символ обрамления '”'
LINES TERMINATED BY '\n' – означает что строка заканчивается переходом на новую строку
COMMIT; – завершить транзакцию подтверждением её выполнения
ROLLBACK; – или отменить транзакцию
Репликация
Общая проблема с любыми backup’ом в том, что они всегда отстают. В случае фатального сбоя основного сервера восстановить систему можно будет только с некоторым «откатом» по времени, что в некоторых случаях бывает не приемлемо. Избежать откатов, призвана система репликации MySQL. Идея репликации основана на том, что кроме «главного» сервера («Мастера») постоянно работают ведомые сервера MySQL («Слейвы»), которые получают инкрементные backup с мастера в режиме реального времени. Таким образом, время отката уменьшается почти до сетевого сбоя. В случае краха «Мастера» можно оперативно назначить новым «Мастером» один из «Слейвов» и перенаправить клиентов на него. Кроме того, «Слейвы» могут обрабатывать запросы на чтение данных (SELECT-ы); это можно использовать для выполнения каких-то расчетов или снижения нагрузки на мастера.
Репликация — это очень здорово, только использовать её нужно по назначению. Реплика — это полная копия базы, но это не резервная копия! Очевидно же, что если на мастере выполнить DROP TABLE или UPDATE, изменения будут тут же скопированы на «Слейв», и откатить их назад станет невозможно.
Репликация — это очень здорово, только использовать её нужно по назначению. Реплика — это полная копия базы, но это не резервная копия! Очевидно же, что если на мастере выполнить DROP TABLE или UPDATE, изменения будут тут же скопированы на «Слейв», и откатить их назад станет невозможно.
Создание резервных копий
Утилита mysqldump позволяет получить дамп содержимого базы данных или совокупности баз для создания резервной копии или пересылки данных на другой SQL-сервер (не обязательно MySQL-сервер). Дамп будет содержать набор команд SQL для создания и/или заполнения таблиц.
Что бы почитать встроенную документацию утилиты необходимо в командной строке ввести команду:
- Код: выделить все
mysqldump --help
Будут отображены все возможные ключи и параметры данной утилиты.
Разберем пример простейшего использования утилиты mysqldump, создадим копию базы данных "s11" при помощи перенаправления потока в файл "temp.s11.sql":
если пароль содержит спецсимволы то его необходимо заключить в двойные кавычки:
либо указать параметр "-p" без значения, что повлечет за собой запрос пароля:
Если вы работаете в среде Linux или у вас установлен архиватор gzip, то backup можно сразу заархивировать, добавив небольшое изменение в нашу команду:
Если мы хотим сохранить только структуру БД без данных то используем команду ниже:
- Код: выделить все
mysqldump --user=USER --password=SeCreT s11 > temp.s11.sql
если пароль содержит спецсимволы то его необходимо заключить в двойные кавычки:
- Код: выделить все
mysqldump --user=USER --password="!<>SeCreT" s11 > temp.s11.sql
либо указать параметр "-p" без значения, что повлечет за собой запрос пароля:
- Код: выделить все
mysqldump --user=USER -p s11 > temp.s11.sql
Если вы работаете в среде Linux или у вас установлен архиватор gzip, то backup можно сразу заархивировать, добавив небольшое изменение в нашу команду:
- Код: выделить все
mysqldump --user=USER --password=SeCreT s11 | gzip > temp.s11.sql.gzip
Если мы хотим сохранить только структуру БД без данных то используем команду ниже:
- Код: выделить все
mysqldump --no-data --user=USER --password=SeCreT s11 > /адрес/куда/сохраняем/файл/dump.s11.sql
Чтобы в дамп попали хранимые процедуры и функции, нужно добавить ключ --routines
Иногда бывает необходимо создать только дамп хранимых процедур и триггеров, например, во время получения триггеров или процедур из другой БД. Для этого можно выполнить команду ниже:
Для дампа всех данных используется ключ --all-databases (в файл пишутся команды создания всех таблиц):
Иногда бывает необходимо создать только дамп хранимых процедур и триггеров, например, во время получения триггеров или процедур из другой БД. Для этого можно выполнить команду ниже:
- Код: выделить все
mysqldump --no-create-info --no-data --triggers --routines --user=USER --password=SeCreT s11 | gzip> ~/trigsnproc.s11.sql.gz
Для дампа всех данных используется ключ --all-databases (в файл пишутся команды создания всех таблиц):
- Код: выделить все
mysqldump --user=USER --password=SeCreT --all-databases > /адрес/куда/сохраняем/файл/dump.full.sql
Напоминаем о полной ответственности за любые действия, выполненные и осуществленные с вашей базой данных. Автор не несет ответственности за не корректное использование данного материала. Рекомендуем, с осторожность относится ко всем командам, описанным в данной статье.
Замечания и предложения приветствуются.
