MySQL. Стратегия BackUP и примеры

В данной статье поговорим о стратегии backup в mysql. Статья основана на официальной документации. В каких случаях нам нужен backup?

-Аварийное завершение операционной системы

-Отключение питания

-Проблемы с файловой системой

-Проблемы с железом

Примеры в данной статье не содержат инструкций –user и –password для утилиты mysqldump и других клиентов mysql. Их необходимо прописывать отдельно к приведенным ниже примерам.

Представим, что данные хранятся в InnoDB, который поддерживает транзакции и автоматическое восстановление после аварийного завершения. Предположим также, что MySQL сервер находится под нагрузкой во время аварийного завершения.

Файлы данных InnoDB могут не содержать данные об аварийном завершении, но InnoDB читает логи и находит в них список подтвержденных и неподтвержденных транзакций, которые не были сохранены в файлы данных. InnoDB автоматически откатывает транзакции, которые не были подтверждены. Информация об этом сохраняется в error log. Вот пример error log

В случае проблем с файловой системой или проблем с железом, мы можем предположить, что данные не доступны после перезапуска. Это значит, что MySQL не может прочитать данные. В этом случае нужно переформатировать диск, решить проблему с железом, и после этого воспользоваться BackUP, который должен быть сделан заранее. Об этом мы и поговорим далее.

Стратегия BackUP

Backup файлы должны делаться регулярно. Полный backup (снимок данных на определенное время) может быть сделан несколькими инструментами MySQL. В данной статье мы рассмотрим утилиту mysqldump.

Представьте, что мы делаем полный BackUP всех наших таблиц во всех базах данных в воскресенье в 13.00, используя следующую команду


Примечание для Windows, чтобы корректно работали инструкции mysqldump, необходимо проделать следующее (пример для MySQL 5.5)

Win+X –> Командная строка (администратор), далее нужно изменить директорию таким образом


Далее можем прописать следующее

Полученный .sql файл, созданный mysqldump содержит набор SQL INSERT инструкций, которые используются, чтобы перезагрузить архивные таблицы позже.

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

Как только эта блокировка запрашивается, получаются координаты binary log . Если в это время обновляются инструкции, процесс Backup будет ждать, пока они не закончатся. После этого таблицы освобождаются от блокировки.  Инструкция –single-transaction необходима для движка InnoDB.

Полное архивирование необходимо, но она занимает много времени и места на диске, иногда также удобно обойтись частичным архивированием отдельных таблиц. Более эффективно делать полный изначальный архив и в дальнейшем архивировать только изменения. Это занимает меньше времени. Цена этого способа в том, что невозможно воспользоваться полным восстановлением из архива.

Изменения в БД в MySQL фиксируются в binary log, таким образом MySQL сервер должен быть запущен с параметром –log-bin. В этом режиме сервер записывает каждое изменение в БД в этот лог. Выглядит он примерно следующим образом

Каждый раз, когда он сервер перезапускается, он создает новый binary log, используя следующий номер в последовательности. Когда сервер запущен, можно попросить его закрыть текущий binary log и запустить следующий с помощью инструкции

Файл .index в директории содержит список всех binary log.

Если вы командуете “flush logs” при выполнении full backup, binary log создаются после этого и содержат все данные после BackUP. То есть, пример можно изменить таким образом

(В командной строке под администратором)

После выполнения этой команды, директория данных будет содержать новый binary log file, gbichot2-bin.000007

Параметр –masterdata просит прописать данные binary log во внешнем файле, таким образом файл .sql будет содержать следующие строки

Эти 2 строки означают следующее

-Дамп содержит все изменения, сделанные до того как изменения были записаны в gbichot2-
bin.000007

-Все изменения данных залогированные после backup отсутствуют в дамп файле, но присутствуют в gbichot2-
bin.000007

В понедельник в 13 00 мы можем создать частичное восстановление Backup, сбросив логи и начав новый binary log file. Например, выполнение инструкции –flush-logs создаст gbichot2-
bin.000008. Все изменения между Воскресеньем 13.00 и понедельником 13.00 будут в файле gbichot2-bin.000007.

Во вторник в 13.00 можно еще раз исполнить команду –flush-logs и все изменения между понедельником 13.00 и вторником 13.00 будут в файле gbichot2-bin.000008.

Все файлы дампов лучше хранить в отдельном месте – на отдельном компьютере, двд и так далее.

Чтобы освободить место на диске, можно удалять ненужные binary logs и делать full backup, например таким образом


Восстановление из Backup

Теперь предположим, что у нас случилась катастрофа в среду в 8 утра. Нам нужно срочно восстановить данные. Чтобы сделать это мы должны восстановить полный backup, который у нас есть (на воскресенье 13.00). Это можно сделать следующим образом

Все данные будут восстановлены к моменту – “Воскресенье 13.00”. Далее, чтобы восстановить все последующие изменения, мы должны воспользоваться частичными архивами – gbichot2-bin.000007 и gbichot2-
bin.000008. Достанем их из безопасного места и проделаем следующее

Файлы желательно хранить в другом месте, не там где работает mysql, для этого можно воспользоваться командой log-bin которая позволяет настроить место хранения. Данные у нас восстановлены на вторник 13.00, последним шагом будет

Теперь дата актуальности данных среда 13.00

Утилита mysqldump

Архивирование баз

mysqldump на выходе может давать 2 типа файлов .txt и .sql, если воспользоваться параметром –tab, то мы получим оба варианта, если без этого параметра, то только .sql

Архивировать все базы

Архивировать только конкретные базы

Если нужно удалить базы перед восстановлением, можно использовать параметр  –add-drop-database

Архивировать одну базу

или

 

Архивировать отдельные таблицы

Восстановление баз

Если дамп был создан с опциями  –all-databases or –databases, то он содержит в себе инструкции CREATE, а значит мы можем не указывать базу, в которую будем загружать архив. Можем написать просто

Можно тоже самое сделать в клиенте MySQL

Если в дампе нет инструкций CREATE, тогад нам нужно создать сначала БД

потом собственно произвести загрузку

Либо альтернативно в консоли MySQL

Как сделать копию БД?

Копирование БД с одного сервера на другой

На 1 сервере

Далее копируем файл dump.sql на второй сервер

На втором сервере

Использование инструкции –databases создает команды CREATE, поэтому на 2 сервере мы не создавали БД и не ставили её по умолчанию.

Альтернативный путь

На 1 сервере

На 2 сервере


Дамп хранимых процедур, триггеров, событий

–events  Этот параметр добавляет в архив события
–routines: Этот параметр добавляет в архив хранимые процедуры и функции
–triggers: Этот параметр добавляет в архив триггеры (они включены по умолчанию)

Если при загрузке нам что-то не нужно можем использовать инструкции

–skipevents, –skip-routines, or –skip-triggers.

by Stanislav_Panteleev