Два основных метода резервного копирования базы данных — использование программы mysqldump или прямое копирование файла базы данных (например, с помощью cp, cpio или tar и т. д.). В этой статье подробно описан план резервного копирования базы данных платформы MySQL. В случае потери или повреждения таблицы базы данных важно создать резервную копию базы данных. Если произойдет сбой системы, вам определенно захочется восстановить ваши таблицы до состояния, в котором они находились на момент сбоя, с наименьшими возможными потерями данных. Иногда хаос вызывает администратор MySQL. Администратор уже знает, что таблицы повреждены, и попытка редактировать их напрямую с помощью редактора, такого как vi или Emacs, определенно не пойдет на пользу таблицам.
Два основных метода резервного копирования базы данных — использование программы mysqldump или прямое копирование файла базы данных (например, с помощью cp, cpio или tar и т. д.). Каждый метод имеет свои плюсы и минусы:
mysqldump работает с сервером MySQL. Метод прямого копирования выполняется вне сервера, и вы должны принять меры, чтобы гарантировать, что ни один клиент не изменяет копируемую таблицу. Если вы хотите использовать резервную копию файловой системы для резервного копирования базы данных, возникнет та же проблема: если таблица базы данных будет изменена во время процесса резервного копирования файловой системы, тема файла резервной копии таблицы будет противоречивой, и таблица будет бессмысленно для будущего выздоровления. Разница между резервной копией файловой системы и прямой копией файла заключается в том, что в последнем случае вы имеете полный контроль над процессом резервного копирования, поэтому вы можете предпринять шаги, чтобы гарантировать, что сервер покинет таблицу в целости и сохранности.
mysqldump медленнее, чем прямое копирование.
mysqldump генерирует текстовые файлы, которые можно переносить на другие машины, даже с другой аппаратной архитектурой. Непосредственно копируемые файлы нельзя переносить на другие машины, если копируемая таблица не использует формат хранения MyISAM. Таблицы ISAM можно копировать только на машинах с аналогичной аппаратной структурой. Формат хранения таблиц MyISAM, представленный в MySQL 3.23, решает эту проблему, поскольку этот формат не зависит от машины, поэтому прямое копирование файла можно перенести на машины с другой аппаратной структурой. При соблюдении двух условий: на другом компьютере также должен быть установлен MySQL 3.23 или новее, а файл должен быть представлен в формате MyISAM, а не в формате ISAM.
Независимо от того, какой метод резервного копирования вы используете, если вам необходимо восстановить базу данных, необходимо соблюдать несколько принципов, чтобы обеспечить наилучшие результаты:
Выполняйте регулярное резервное копирование. Разработайте план и придерживайтесь его.
Позвольте серверу вести журнал обновлений. Журнал изменений поможет вам, когда вам понадобится восстановить данные после сбоя. После использования файла резервной копии для восстановления данных до состояния, в котором они находились на момент резервного копирования, вы можете повторно применить изменения, внесенные после резервного копирования, выполнив запрос в журнале обновлений, который восстановит таблицы в базе данных до состояние, в котором они находились, когда произошла авария.
С точки зрения резервного копирования файловой системы файл резервной копии базы данных представляет собой полный дамп, а журнал обновлений — инкрементный дамп.
Используйте последовательную и понятную схему именования файлов резервных копий. Такие вещи, как резервное копирование1, резервное копирование2 и т. д. не имеют особого смысла. Выполняя восстановление, вы потратите время на выяснение того, что находится в файлах. Возможно, вам будет полезно использовать имя и дату базы данных для формирования имени файла резервной копии. Например:
%mysqldump samp_db >/usr/archives/mysql/samp_db.1999-10-02
%mysqldump зверинец >/usr/archives/mysql/menagerie.1999-10-02
Возможно, вы захотите сжать резервные копии после их создания. Резервные копии, как правило, имеют большой размер! Вам также необходимо установить срок действия файлов резервных копий, чтобы они не заполнили ваш диск, так же, как срок действия файлов журналов истекает.
Создайте резервную копию файлов резервной копии с помощью резервной копии файловой системы. Если у вас произойдет полный сбой, в результате которого будет очищен не только ваш каталог данных, но и диск, содержащий резервные копии вашей базы данных, у вас будут настоящие проблемы.
Также сделайте резервную копию журнала изменений.
Поместите файлы резервных копий в файловую систему, отличную от той, которая используется для вашей базы данных. Это уменьшит вероятность заполнения файловой системы, содержащей каталог данных, в результате создания резервной копии.
Методы создания резервных копий также полезны для копирования базы данных на другой компьютер. Чаще всего база данных перемещается на сервер, работающий на другом хосте, но вы также можете переместить данные на другой сервер на том же хосте.
1. Используйте mysqldump для резервного копирования и копирования базы данных.
Когда вы используете программу mysqldumo для создания файла резервной копии базы данных, по умолчанию содержимое файла содержит инструкцию CREATE, которая создает дамп таблицы, и инструкцию INSERT, содержащую данные строк в таблице. Другими словами, выходные данные, созданные mysqldump, позже можно использовать в качестве входных данных для MySQL для восстановления базы данных.
Вы можете сбросить всю базу данных в один текстовый файл следующим образом:
%mysqldump samp_db >/usr/archives/mysql/samp_db.1999-10-02
Начало выходного файла выглядит так:
# MySQL Dump 6.0# # Хост: localhost База данных: samp_db
#---------------------------------------#
Версия сервера 3.23.2-alpha-log## Структура таблицы при отсутствии таблицы
#CREATE TABLE отсутствие( Student_id int(10) без знака DEFAULT 0 NOT NULL, дата дата DEFAULT 0000-00-00 NOT NULL L,
PRIMARY KEY (student_id,date));## Дамп данных для отсутствия в таблице #INSERT INTO отсутствия VALUES (3,1999-09-03);INSERT INTO отсутствия VALUES (5,1999-09-03);INSERT INTO отсутствия VALUES (10.09.1999);......
Остальная часть файла состоит из операторов INSERT и CREATE TABLE. Если вы хотите сжать резервную копию, используйте команду, аналогичную следующей:
%mysqldump samp_db | gzip >/usr/archives/mysql/samp_db.1999-10-02.gz
Если у вас большая база данных, выходные файлы также будут большими, и ими может быть сложно управлять. При желании вы можете указать имена отдельных таблиц после имени базы данных в командной строке mysqldump, чтобы выгрузить их содержимое, что разобьет файл дампа на более мелкие и более управляемые файлы. В следующем примере показано, как выгрузить некоторые таблицы из базы данных samp_db в отдельные файлы:
%mysqldump samp_db отсутствие события оценки учащихся >grapbook.sql
%mysqldump президент участника samp_db >hist-league.sql
Если вы создаете файлы резервных копий, которые будут использоваться для периодического обновления содержимого другой базы данных, вы можете использовать опцию --add-drop-table. Это указывает серверу записать оператор DROP TABLE IF EXISTS в файл резервной копии, а затем, когда вы возьмете файл резервной копии и загрузите его во вторую базу данных, вы не получите ошибку, если таблица уже существует.
Если вы создадите дамп базы данных, чтобы переместить ее на другой сервер, вам даже не придется создавать файл резервной копии. Убедитесь, что база данных существует на другом хосте, а затем используйте канал для дампа базы данных, чтобы mysql мог напрямую читать выходные данные mysqldump. Например: вы хотите скопировать базу данных samp_db с хостаpit-viper.snake.net на boa.snake.net. Вы можете легко это сделать следующим образом:
%mysqladmin -h boa.snake.net создать samp_db
%mysqldump samp_db | mysql -h boa.snake.net samp_db
В будущем, если вы захотите снова обновить базу данных на boa.snake.net, пропустите команду mysqladmin, но добавьте --add-drop-table в mysqldump, чтобы избежать получения ошибки, что таблица уже существует: %mysqldump --add- отбросить таблицу samp_db | mysql -h boa.snake.net samp_db
Другие полезные параметры mysqldump включают: Комбинация --flush-logs и --lock-tables будет полезна для проверки вашей базы данных. --lock-tables блокирует все таблицы, которые вы сбрасываете, а --flush-logs закрывает и повторно открывает файл журнала обновлений. Новый журнал обновлений будет включать только запросы, которые изменили базу данных из точки резервного копирования. Это установит время резервного копирования контрольной точки журнала обновлений. (Однако если у вас есть клиенты, которым необходимо выполнить обновление, блокировка всех таблиц не является хорошей идеей для доступа клиентов во время резервного копирования.)
Если вы используете --flush-logs для проверки резервной копии, вероятно, лучше всего создать дамп всей базы данных.
Если вы создаете дамп отдельных файлов, сложнее синхронизировать контрольные точки журнала обновлений с файлами резервных копий. Во время восстановления вы обычно извлекаете содержимое журнала обновлений для каждой базы данных. Извлекать обновления для отдельных таблиц невозможно, поэтому вам придется извлекать их самостоятельно.
По умолчанию mysqldump считывает все содержимое таблицы в память перед записью. Обычно в этом нет необходимости, и на самом деле это практически ошибка, если у вас большая таблица. Вы можете использовать опцию --quick, чтобы указать mysqldump записывать каждую строку всякий раз, когда она ее получает. Для дальнейшей оптимизации процесса заливки используйте --opt вместо --quick. Опция --opt включает дополнительные параметры для ускорения сброса данных и их обратного чтения.
Реализация резервного копирования с помощью --opt, вероятно, является наиболее распространенным методом из-за преимущества резервного копирования в скорости. Однако имейте в виду, что опция --opt оптимизирует процесс резервного копирования, а не доступ других клиентов к базе данных. Опция --opt запрещает кому-либо обновлять какую-либо таблицу, которую вы выгружаете, блокируя все таблицы одновременно. Вы можете легко увидеть влияние на общий доступ к базе данных. Если ваша база данных обычно используется очень часто, просто корректируйте резервную копию один раз в день.
Опция, имеющая эффект, противоположный --opt, — --dedayed. Эта опция заставляет mysqldump записывать операторы INSERT DELAYED вместо операторов INSERT. --delayed полезен, если вы загружаете файл данных в другую базу данных и хотите, чтобы эта операция имела минимальное влияние на запросы, которые могут появиться в этой базе данных.
Опция --compress полезна при копировании базы данных на другой компьютер, поскольку она уменьшает количество байтов, передаваемых по сети. Вот пример. Обратите внимание, что --compress указывается для программ, которые взаимодействуют с сервером на удаленном хосте, а не для программ, которые подключаются к локальному хосту:
%mysqldump --opt samp_db | mysql --compress -h boa.snake.net samp_db
mysqldump имеет множество опций, подробности см. в «Справочном руководстве MySQL».
2 Метод резервного копирования и копирования с использованием базы данных прямого копирования
Другой способ резервного копирования базы данных и таблиц, не использующий mysqldump, — это прямое копирование файлов таблиц базы данных. Обычно это делается с помощью таких утилит, как cp, tar или cpio. В примерах в этой статье используется cp.
При использовании метода прямого резервного копирования необходимо убедиться, что таблица больше не используется. Если сервер изменяет таблицу во время ее копирования, копирование не имеет смысла.
Лучший способ обеспечить целостность вашей копии — выключить сервер, скопировать файлы, а затем перезапустить сервер. Если вы не хотите выключать сервер, заблокируйте его на время проверки таблицы. Если сервер работает, к копированию файлов применяются те же ограничения, и вам следует использовать тот же протокол блокировки, чтобы «заглушить» сервер.
Предполагая, что сервер не работает или вы заблокировали таблицу, которую хотите скопировать, ниже показано, как выполнить резервное копирование всей базы данных samp_db в резервный каталог (DATADIR представляет каталог данных сервера): %cd DATADIR%cp -r samp_db /usr /архив/mysql
Резервную копию одной таблицы можно создать следующим образом:
%cd DATADIR/samp_db%cp член.* /usr/archive/mysql/samp_db%cp Оценка.* /usr/archive/mysql/samp_db ....
После завершения резервного копирования вы можете перезапустить сервер (если вы его выключили) или снять блокировки, установленные на таблице (если вы оставили сервер включенным).
Чтобы скопировать базу данных с одного компьютера на другой с помощью файлов прямого копирования, просто скопируйте файлы в соответствующий каталог данных на другом хосте сервера. Убедитесь, что файл имеет формат MyIASM или оба компьютера имеют одинаковую аппаратную структуру, иначе ваша база данных будет иметь странное содержимое на другом компьютере. Вам также следует убедиться, что сервер на другом компьютере не обращается к таблицам базы данных во время их установки.
3 Репликация базы данных
Репликация аналогична копированию базы данных на другой сервер, но ее точный смысл заключается в обеспечении полной синхронизации двух баз данных в реальном времени. Эта функция появится в версии 3.23 и еще не очень развита, поэтому в этой статье она не будет представлена подробно.
4 Восстановить данные из резервной копии
Повреждение базы данных может произойти по многим причинам и в разной степени. Если вам повезет, вы можете повредить только одну или две таблицы (например, из-за отключения электроэнергии), если вам не повезет, вам, возможно, придется заменить весь каталог данных (например, при повреждении диска). Восстановление также требуется в определенных ситуациях, например, когда пользователь по ошибке удаляет базу данных или таблицу. Независимо от причины этих неудачных событий, вам необходимо будет реализовать какое-то восстановление.
Если таблицы повреждены, но не утеряны, попробуйте восстановить их с помощью myisamchk или isamchk. Если такие повреждения можно устранить с помощью программы восстановления, возможно, вам вообще не понадобится использовать файл резервной копии. Процесс восстановления таблицы см. в разделе «Обслуживание и восстановление базы данных».
Процесс восстановления включает два источника информации: файлы резервных копий и журналы изменений. Файл резервной копии восстанавливает таблицу в то состояние, в котором она находилась на момент выполнения резервного копирования. Однако обычно таблица была изменена за время между резервным копированием и возникновением проблемы, и журнал обновлений содержит запросы, использованные для внесения этих изменений. Вы можете использовать файлы журналов в качестве входных данных для MySQL для повторения запросов. Вот почему вам следует включить журнал изменений.
Процесс восстановления зависит от того, какой объем информации вам необходимо восстановить. На самом деле восстановить всю базу данных проще, чем одну таблицу, поскольку журнал обновлений проще применить к базе данных, чем к одной таблице.
4.1 Восстановить всю базу данных
Во-первых, если база данных, которую вы хотите восстановить, представляет собой базу данных MySQL, содержащую таблицу разрешений, вам необходимо запустить сервер с опцией --skip-grant-table. В противном случае он будет жаловаться, что таблица авторизации не найдена. После восстановления таблицы выполните mysqladmin flash-privileges, чтобы указать серверу загрузить токены авторизации и использовать их.
Скопируйте содержимое каталога базы данных в другое место, если оно понадобится вам позже.
Переустановите базу данных, используя последний файл резервной копии. Если вы используете файл, созданный mysqldump, используйте его в качестве входных данных для mysql. Если вы используете файлы, скопированные непосредственно из базы данных, скопируйте их обратно в каталог базы данных. Однако в этом случае вам придется закрыть базу данных, а затем перезапустить ее, прежде чем копировать файлы.
Используйте журнал обновлений для повторения запросов, изменяющих таблицы базы данных после резервного копирования. Все применимые журналы изменений передавайте их в качестве входных данных в MySQL. Указание опции --one-database заставляет MySQL выполнять запросы только для той базы данных, которую вы хотите восстановить. Если вы знаете, что вам необходимо применить все файлы журналов обновлений, вы можете использовать эту команду в каталоге, содержащем журналы:
% ls -t -r -1 обновление.[0-9]* xargs cat mysql --one-database имя_базы_данных |
Команда ls генерирует список файлов журнала обновлений в один столбец, отсортированный в соответствии с порядком, в котором они были созданы сервером (Идея: если вы измените какой-либо из файлов, вы измените порядок сортировки, в результате чего журнал обновлений будет используется в неправильном порядке.)
Скорее всего, вы будете использовать определенные журналы изменений. Например, если журналы обновлений, созданные после создания резервной копии, называются update.392, update.393 и т. д., вы можете перезапустить их следующим образом:
%mysql --one-database имя_базы_данных < update.392
%mysql --one-database имя_базы_данных < update.393
.....
Если вы выполняете восстановление и используете журнал обновлений для восстановления информации, потерянной из-за неправильно рекомендованных инструкций DROP DATABASE, DROP TABLE или DELETE, обязательно удалите эти инструкции из журнала обновлений перед его использованием.
4.2 Восстановление одной таблицы
Восстановление одной таблицы является более сложным. Если вы используете файл резервной копии, созданный mysqldump, и он не содержит данных для интересующих вас таблиц, вам нужно будет извлечь их из соответствующих строк и использовать в качестве входных данных для mysql. Это самая легкая часть. Сложная часть — извлечь из журнала обновлений фрагмент, который относится только к этой таблице. Для этого вам может пригодиться утилита mysql_find_rows, которая извлекает многострочные запросы из журнала изменений.
Другая возможность — использовать другой сервер для восстановления всей базы данных, а затем скопировать нужные файлы таблиц в исходную базу данных. Это может быть очень просто! Когда вы копируете файлы обратно в каталог базы данных, убедитесь, что исходный сервер базы данных выключен.