RussianLDP Рейтинг@Mail.ru
WebMoney: 
WMZ Z294115950220 
WMR R409981405661 
WME E134003968233 		 Visa 
4274 3200 2453 6495 

Глава 8. Резервирование и восстановление

Важно поддерживать Ваши базы данных так, чтобы можно было восстановить свои данные в случае, если встречаются проблемы, такие как отказ системы, оборудования или потребители, стирающие данные по ошибке. Резервные копии являются также гарантией спокойствия при обновлении MySQL, и они могут использоваться, чтобы переместить установку MySQL в другую систему.

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

  • Типы резервных копий: логические против физических, полные или частичные и так далее.

  • Методы создания резервных копий.
  • Методы восстановления, включая восстановление момента времени.
  • Планирование, сжатие и шифрование.
  • Табличная поддержка, чтобы восстановить поврежденную таблицу.

Дополнительные ресурсы

Ресурсы, связанные с резервным копированием или с поддержанием доступности данных, включают следующее:

  • Заказчики MySQL Enterprise Edition могут использовать продукт MySQL Enterprise Backup для резервных копий. Для общего обзора продукта MySQL Enterprise Backup см. раздел 27.2. Обзор MySQL Enterprise Backup Overview.

  • Форум, выделенный, чтобы сделать копию проблем, доступен на http://forums.mysql.com/list.php?28.
  • Подробные данные для mysqldump могут быть найдены в главе 5.
  • Синтаксис SQL-операторов, описанных здесь, дается в главе 14.
  • Для дополнительной информации о процедуре резервного копирования InnoDB см. раздел 16.17.
  • Репликация позволяет Вам поддержать идентичные данные на нескольких серверах. Это обладает несколькими преимуществами, такими как распределение нагрузки между машинами, доступность данных при отказе части серверов и возможность сделать резервные копии без воздействия на ведущее устройство при использовании ведомого сервера. См. раздел 19.
  • MySQL Cluster обеспечивает высокую доступность, версию MySQL адаптированную к распределенной вычислительной среде. См. материал MySQL Cluster NDB 7.3 and MySQL Cluster NDB 7.4, который предоставляет информацию о MySQL Cluster NDB 7.3 (основанном на MySQL 5.6, но содержащем самые последние улучшения и исправления для механизма хранения данных NDBCLUSTER).

    Механизм хранения данных NDBCLUSTER в настоящий момент не поддерживается в MySQL 8.0.

  • Distributed Replicated Block Device (DRBD) является другим высоконадежным решением. Это работает, тиражируя блочное устройство с основного сервера на вторичный сервер на блочном уровне. См. главу 18.

8.1. Типы резервирования и восстановления

Этот раздел описывает характеристики различных типов резервных копий.

Физические (сырые данные) против логических резервных копий

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

Логические резервные копии сохраняют информацию, представленную как логическая структура базы данных (операторы CREATE DATABASE, CREATE TABLE) и контент (операторы INSERT или разграниченные текстовые файлы). Этот тип резервного копирования является подходящим для более малых объемов данных, где Вы могли бы отредактировать значения данных или структуру таблицы, или воссоздать данные на иной архитектуре машины.

Физические копии имеют такие характеристики:

  • Они включают точные копии каталогов базы данных и файлов. Обычно это копия всего или части каталога базы данных MySQL.

  • Они более быстры, чем логические, потому что они включают только копирование файла без преобразования.
  • Вывод более компактен, чем для логического резервного копирования.
  • Поскольку скорость и размер копии важны для оживленных, важных баз данных, продукт MySQL Enterprise Backup выполняет физические резервные копии. Для общего обзора продукта MySQL Enterprise Backup, см. раздел 27.2.
  • Гранулярность резервного копирования и восстановления колеблется от уровня всего каталога базы данных вниз к уровню отдельных файлов. Это, возможно, не предусматривает гранулярность на уровне таблицы, в зависимости от механизма хранения. Например, таблицы InnoDB могут быть каждая в отдельном файле или совместно использовать хранилище файлов с другими таблицами InnoDB. Каждая таблица MyISAM соответствует уникальному набору файлов.
  • В дополнение к базам данных резервное копирование может включать любые связанные файлы, такие как журнал или конфигурационные файлы.
  • Данные из таблиц MEMORY таким способом сохранить сложно, потому что их содержание не сохранено на диске. У продукта MySQL Enterprise Backup есть функция, которой можно получить данные из таблицы MEMORY во время резервного копирования.
  • Резервные копии переносимы только на другие машины, у которых есть идентичные или подобные аппаратные характеристики.
  • Резервные копии могут быть выполнены, в то время как сервер MySQL не работает. Если он работает, необходимо выполнить адекватную блокировку так, чтобы сервер не изменил содержание базы данных во время резервного копирования. MySQL Enterprise Backup делает эту блокировку автоматически для таблиц, которые требуют этого.
  • Вспомогательные инструменты включают mysqlbackup MySQL Enterprise Backup для InnoDB или любых других таблиц, или системные команды уровня файла (такие как cp, scp, tar, rsync) для таблиц MyISAM.
  • Для восстановления

    • MySQL Enterprise Backup восстанавливает InnoDB и другие таблицы, которые поддерживает.

    • ndb_restore восстанавливает таблицы NDB.
    • Файлы, скопированные на уровне файловой системы, могут быть скопированы назад в их исходные расположения командами файловой системы.

У логических резервных методов есть эти характеристики:

  • Резервное копирование делается, запрашивая сервер MySQL, чтобы получить информацию о контенте и структуре базы данных.

  • Резервное копирование медленнее, чем физические методы, потому что сервер должен получить доступ к информации о базе данных и преобразовать ее в логический формат. Если вывод пишется на стороне клиента, сервер должен также отправить это программе резервирования.
  • Вывод больше, чем для физического резервного копирования, особенно когда используется текстовый формат.
  • Гранулярность резервного копирования и восстановления доступна на уровне сервера (все базы данных), уровне базы данных (все таблицы в определенной базе данных) или таблицы. Это верно, независимо от механизма хранения.
  • Резервное копирование не включает журнал или конфигурационные файлы или другие связанные с базой данных файлы, которые не являются частью баз данных.
  • Резервные копии, сохраненные в логическом формате, независимы от машины и очень переносимы.
  • Логические резервные копии выполняются только с работающего сервера MySQL.
  • Вспомогательные инструменты включают программу mysqldump и оператор SELECT ... INTO OUTFILE . Они работают на любом механизме хранения, включая MEMORY.
  • Чтобы восстановить логические резервные копии, файлы дампа формата SQL могут быть обработаны, используя клиент mysql. Чтобы загрузить разграниченные текстовые файлы, используйте оператор LOAD DATA INFILE или клиент mysqlimport .

Online и Offline резервирование

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

У онлайновых резервных методов есть такие характеристики:

  • Резервное копирование является менее интрузивным другим клиентам, которые могут соединиться с сервером MySQL во время резервного копирования и могут быть в состоянии обратиться к данным в зависимости от того, какие операции они должны выполнить.

  • Надо думать, как правильно поставить блокировку, чтобы резервируемые данные были непротиворечивы. Продукт MySQL Enterprise Backup делает такую блокировку автоматически.

У офлайновых резервных методов есть такие характеристики:

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

  • Процедура резервного копирования более проста, потому что нет никакой возможности интерференции от клиентского действия.

Похоже дело обстоит при восстановлении, но поскольку восстановление меняет данные в базах, то клиенты не должны их читать. Здесь нужна более сильная блокировка, чем при резервировании.

Локальное и удаленное резервирование

Локальное резервное копирование выполняется на том же самом хосте, где работает сервер MySQL, тогда как удаленное резервное копирование делается с другой машины. Для некоторых типов резервных копий резервное копирование может инициироваться удаленно, даже если вывод пишется на сервер локально.

  • mysqldump может соединиться с локальными или удаленными серверами. Для вывода SQL (CREATE и INSERT) локальные или удаленные дампы могут быть сделаны, они могут генерировать вывод на клиенте. Для вывода разграниченного текста (опция --tab) файлы данных создаются на сервере.

  • SELECT ... INTO OUTFILE может инициироваться с локального или удаленного хоста клиента, но выходной файл создается на сервере.
  • Физические резервные методы обычно инициируются локально на сервере MySQL, хотя место назначения для скопированных файлов могло бы быть и удаленным.

Резервирование образом

Некоторые реализации файловой системы позволяют образы (snapshot) . Они предоставляют логические копии файловой системы в данный момент времени, не требуя материальной копии всей файловой системы. Например, реализация может использовать методы копии на записи так, чтобы только части файловой системы, модифицируемые после времени создания образа должны были быть скопированы. Сам MySQL не обеспечивает возможность взятия образов файловой системы. Это доступно через сторонние решения, такие как Veritas, LVM или ZFS.

Полное и инкрементальное резервирование

Полное резервное копирование включает все данные, которыми управляет сервер MySQL в данный момент времени. Инкрементное резервное копирование сохраняет только состав изменений, произведенных в данных во время заданного промежутка времени (от одного момента времени до другого). У MySQL есть различные способы выполнить полные резервные копии, такие как описанные ранее в этом разделе. Инкрементные резервные копии возможны благодаря двоичному журналу сервера, который используется, чтобы записать изменения в данных.

Полное или до заданной точки восстановление

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

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

Поддержка таблиц

Целостность данных может оказаться под угрозу, если таблицы повреждаются. Для таблиц InnoDB это не типичная проблема. Для информации о программах, чтобы проверить таблицы MyISAM и восстановить их, если есть проблемы, см. раздел 8.6.

Планирование, сжатие и шифрование

Резервное планирование ценно для того, чтобы автоматизировать процедуры резервного копирования. Сжатие резервного вывода уменьшает требования по размещению, а шифрование вывода обеспечивает лучшую безопасность от несанкционированного доступа к данным. Сам MySQL не обеспечивает эти возможности. Продукт MySQL Enterprise Backup может сжать резервные копии InnoDB, и сжатие или шифрование резервного вывода могут быть достигнуты, используя утилиты файловой системы. Другие сторонние решения могут быть доступны.

8.2. Методы резервирования баз данных

Этот раздел суммирует некоторые общие методы для того, чтобы сделать резервные копии.

Горячее резервное копирование с MySQL Enterprise Backup

Заказчики MySQL Enterprise Edition могут использовать продукт MySQL Enterprise Backup, чтобы сделать физические резервные копии всех или выбранных баз данных и таблиц. Этот продукт включает функции инкрементального и сжатого резервирования. Поддержка физических файлов базы данных делает восстановление намного быстрее, чем логические методы такие, как команда mysqldump. Таблицы InnoDB копируются, используя механизм горячего резервирования. Идеально InnoDB должны представить значительное большинство данных. Таблицы других механизмов хранения копируются, используя механизм теплого резервирования. Для общего обзора продукта MySQL Enterprise Backup, см. раздел 27.2.

Резервирование с помощью mysqldump

Программа mysqldump может сделать резервные копии. Это может поддержать все виды таблиц. (См. раздел 8.4.

Для таблиц InnoDB возможно выполнить онлайновое резервное копирование, которое не блокирует таблицы, используя опцию --single-transaction в mysqldump .

Создание резервных копий в виде разграниченного текстового файла

Чтобы создать текстовый файл, содержащий данные таблицы, можно использовать SELECT * INTO OUTFILE 'file_name' FROM tbl_name. Файл создается на сервере. Для этого оператора не может уже существовать выходной файл, потому что доступ к файлам, которые будут перезаписаны, составляет угрозу безопасности. См. раздел 14.2.9. Этот метод работает на любом виде файла данных, но сохраняет только табличные данные, не структуру таблицы.

Другой способ создать текстовые файлы данных (наряду с файлами, содержащими операторы CREATE TABLE для структуры таблиц), должен использовать mysqldump с опцией --tab. См. раздел 8.4.3.

Чтобы загрузить файл данных разграниченного текста, надо использовать LOAD DATA INFILE или mysqlimport .

Создание инкрементных резервных копий с помощью двоичного журнала

MySQL поддерживает инкрементные резервные копии: следует запустить сервер с опцией --log-bin , чтобы включить двоичное журналирование, см. раздел 6.4.4. Двоичные файлы журнала предоставляют Вам информацию, Вы должны тиражировать изменения в базу данных, что делается после точки, в которой Вы выполняли резервное копирование. Когда Вы хотите сделать инкрементное резервное копирование (содержащее все изменения, которые произошли, начиная с последнего полного или инкрементного резервного копирования), следует ротировать двоичный журнал, используя FLUSH LOGS. Вы должны скопировать в резервную копию все двоичные журналы, которые находятся в диапазоне от момента последнего полного или инкрементного резервного копирования до предпоследнего. Эти двоичные журналы и есть инкрементное резервное копирование, во время восстановления Вы применяете их как объяснено в разделе 8.5. В следующий раз, когда Вы делаете полное резервное копирование, следует также ротировать журнал с помощью FLUSH LOGS или mysqldump --flush-logs .

Создание резервных копий, используя ведомый сервер репликации

Если у Вас есть проблемы производительности с Вашим главным сервером при резервировании, может помочь репликация. См. раздел 19.3.1.

Если Вы поддерживаете ведомый сервер репликации, следует поддержать его основную информацию и релейные репозитарии информации журнала (см. раздел 19.2.4), когда Вы резервируете базы данных ведомого, независимо от того резервного метода, который Вы выбираете. Эти информационные файлы всегда необходимы, чтобы возобновить репликацию после того, как Вы восстанавливаете данные ведомого. Если Ваш ведомый сервер тиражируется оператором LOAD DATA INFILE, следует также резервировать любые файлы SQL_LOAD-* в каталоге, который ведомый использует с этой целью. Ведомый сервер нуждается в этих файлах, чтобы возобновить репликацию любой прерванной операции LOAD DATA INFILE . Расположение этого каталога: значение опции --slave-load-tmpdir. Если сервер не был запущен с этой опцией, расположение каталога надо смотреть в системной переменной tmpdir.

Восстановление поврежденных таблиц

Если необходимо восстановить таблицы MyISAM, попробуйте применить к ним REPAIR TABLE или myisamchk -r. Это должно помочь в 99.9% всех ситуаций. Если myisamchk провалился, обратитесь к разделу 8.6.

Создание резервных копий, используя образ файловой системы

Если Вы используете файловую систему Veritas, можно сделать резервное копирование примерно так:

  1. Из клиентской программы, выполнить FLUSH TABLES WITH READ LOCK.

  2. Из другой оболочки выполнить mount vxfs snapshot.
  3. Из первого клиента выполнить UNLOCK TABLES.
  4. Скопировать файлы из образа.
  5. Отмонтировать обработанный образ.

Подобные возможности могут быть доступны в других файловых системах, таких как LVM или ZFS.

8.3. Стратегия резервного копирования и восстановления в качестве примера

Этот раздел обсуждает процедуру для того, чтобы выполнить резервные копии, который позволяет Вам восстановить данные после нескольких типов катастрофических отказов:

  • Катастрофический отказ операционной системы.

  • Сбой питания.
  • Катастрофический отказ файловой системы.
  • Аппаратная проблема (жесткий диск, системная плата, и т.д.).

Команды в качестве примера не включают такие опции, как --user и --password для клиентских программ mysqldump и mysql. Следует включать такие опции по мере необходимости, чтобы позволить клиентским программам соединиться с сервером MySQL.

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

Для случаев катастрофических отказов операционной системы или сбоев питания, мы можем предположить, что данные диска MySQL доступны после рестарта. Файлы данных InnoDB не могут содержать противоречивые данные вследствие катастрофического отказа, но InnoDB читает журналы и ищет в них список фиксированных и нефиксированных транзакций на ожидании, которые не были сброшены к файлам данных. InnoDB автоматически откатывает те транзакции, которые не фиксировались, и сбрасывает к файлам данных те, которые фиксировались. Информация об этом процессе восстановления выдается через журнал ошибок MySQL. Следующее выборка журнала в качестве примера: 

InnoDB: Database was not shut down normally.
InnoDB: Starting recovery from log files...
InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 13674004
InnoDB: Doing recovery: scanned up to log sequence number 0 13739520
InnoDB: Doing recovery: scanned up to log sequence number 0 13805056
InnoDB: Doing recovery: scanned up to log sequence number 0 13870592
InnoDB: Doing recovery: scanned up to log sequence number 0 13936128
...
InnoDB: Doing recovery: scanned up to log sequence number 0 20555264
InnoDB: Doing recovery: scanned up to log sequence number 0 20620800
InnoDB: Doing recovery: scanned up to log sequence number 0 20664692
InnoDB: 1 uncommitted transaction(s) which must be rolled back
InnoDB: Starting rollback of uncommitted transactions
InnoDB: Rolling back trx no 16745
InnoDB: Rolling back of trx no 16745 completed
InnoDB: Rollback of uncommitted transactions completed
InnoDB: Starting an apply batch of log records to the database...
InnoDB: Apply batch completed
InnoDB: Started
mysqld: ready for connections

Для случаев катастрофических отказов файловой системы или аппаратных проблем, мы можем предположить, что данные диска MySQL недоступны после рестарта. Это означает, что MySQL не в состоянии запуститься успешно, потому что некоторые блоки данных диска больше не читаемы. В этом случае необходимо переформатировать диск, установить новый или иначе исправить базовую проблему. Затем необходимо восстановить наши данные MySQL с резервных копий, что означает, что резервные копии уже были сделаны. Чтобы удостовериться, что это имеет место, разработайте и реализуйте политику резервирования.

8.3.1. Установление политики резервирования

Чтобы быть полезными, резервные копии должны регулярно планироваться. Полное резервное копирование данных в некий момент времени может быть сделано в MySQL несколькими инструментами. Например, MySQL Enterprise Backup может выполнить физическое резервирование с оптимизацией, чтобы минимизировать издержки и избежать сбоя файлов данных InnoDB, mysqldump обеспечивает онлайновое логическое резервирование. Это обсуждение использует mysqldump.

Предположите, что мы делаем полное резервное копирование всех таблиц InnoDB во всех базах данных, используя следующую команду в воскресенье в 13:00, когда загрузка низка: 

shell> mysqldump --single-transaction --all-databases > backup_sunday_1_PM.sql

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

Эта операция резервного копирования получает глобальную блокировку чтения на всех таблицах в начале дампа (используя FLUSH TABLES WITH READ LOCK ). Как только эта блокировка была получена, координаты двоичного журнала читаются, и блокировка снимается. Если долго обновляющие операторы работают, когда делается FLUSH, операция резервного копирования может остановиться до их окончания. После этого дамп делается без блокировок и не меняет чтения и записи таблиц.

Предполагалось ранее, что таблицы для резервирования InnoDB, таким образом, --single-transaction использует непротиворечивое чтение и гарантирует, что данные, увиденные mysqldump , не изменяются (изменения, произведенные другими клиентами в InnoDB не видны процессу mysqldump ). Если операция резервного копирования включает нетранзакционные таблицы, непротиворечивость требует, чтобы они не изменились во время резервного копирования. Например, для таблиц MyISAM базы данных mysql, не должно быть никаких административных изменений учетных записей MySQL во время резервного копирования.

Полные резервные копии необходимы, но не всегда удобно создавать их. Они производят большие файлы и занимают время. Они не оптимальны в том смысле, что каждое последующее полное резервное копирование включает все данные, включая часть, которая не изменилась с предыдущего полного резервного копирования. Более эффективно сделать начальное полное резервное копирование, а затем делать инкрементные резервные копии. Инкрементные резервные копии более компактны и занимают меньше времени. Зато во время восстановления невозможно восстановить свои данные только перезагружая полную резервную копию. Следует также обработать инкрементные резервные копии, чтобы восстановить инкрементные изменения.

Чтобы сделать инкрементные резервные копии, Вы должны сохранить инкрементные изменения. В MySQL эти изменения представлены в двоичном журнале, таким образом, MySQL должен всегда запускаться с опцией --log-bin, чтобы включить этот журнал. С включенным двоичным журналированием сервер пишет каждое изменение данных в файл, в то время как обновляет данные. При просмотре каталога базы данных MySQL, который был запущен с опцией --log-bin и работал в течение нескольких дней, обнаружим эти файлы журнала MySQL: 

-rw-rw---- 1 guilhem  guilhem   1277324 Nov 10 23:59 gbichot2-bin.000001
-rw-rw---- 1 guilhem  guilhem 4 Nov 10 23:59 gbichot2-bin.000002
-rw-rw---- 1 guilhem  guilhem79 Nov 11 11:06 gbichot2-bin.000003
-rw-rw---- 1 guilhem  guilhem 508 Nov 11 11:08 gbichot2-bin.000004
-rw-rw---- 1 guilhem  guilhem 220047446 Nov 12 16:47 gbichot2-bin.000005
-rw-rw---- 1 guilhem  guilhem    998412 Nov 14 10:08 gbichot2-bin.000006
-rw-rw---- 1 guilhem  guilhem 361 Nov 14 10:07 gbichot2-bin.index

Каждый раз, когда сервер перезапускается, он создает новый файл двоичного журнала, используя следующее число в последовательности. В то время как сервер работает, можно также явно сказать ему закрыть текущий файл журнала и начать новый SQL-командой FLUSH LOGS или через команду mysqladmin flush-logs. mysqldump также имеет опцию для сброса логов. Файл .index в каталоге данных содержит список всех файлов двоичных журналов MySQL в каталоге.

Двоичные журналы MySQL важны для восстановления, потому что они формируют набор инкрементных резервных копий. Если Вы удостоверяетесь, что сбросили журналы, когда Вы делаете свое полное резервное копирование, файлы двоичного журнала, создаваемые позже, содержат все изменения данных, произведенные начиная с резервного копирования. Модифицируйте предыдущую команду mysqldump так, чтобы она сбросила двоичные журналы MySQL в момент полного резервного копирования, и чтобы файл дампа содержал имя нового текущего двоичного журнала: 

shell> mysqldump --single-transaction --flush-logs --master-data=2 \
 --all-databases > backup_sunday_1_PM.sql

После выполнения этой команды каталог базы данных содержит новый файл двоичного журнала gbichot2-bin.000007, потому что опция --flush-logs предписывает серверу сбрасывать журналы. Опция --master-data предписывает mysqldump писать двоичные данные в вывод, таким образом, окончательный файл дампа .sql включает эти строки: 

-- Position to start replication or point-in-time recovery from
-- CHANGE MASTER TO MASTER_LOG_FILE='gbichot2-bin.000007',MASTER_LOG_POS=4;

Поскольку команда mysqldump сделала полное резервное копирование, те строки означают две вещи:

  • Файл дампа содержит все изменения, произведенные перед любыми изменениями, записанными в файл двоичного журнала gbichot2-bin.000007 или выше.

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

В понедельник в 13:00, мы можем создать инкрементную резервную копию, сбрасывая журналы, чтобы начать новый файл двоичного журнала. Например, выполнение mysqladmin flush-logs создает gbichot2-bin.000008. Все изменения между воскресным полным резервным копированием в 13:00 и резервированием в понедельник в 13:00 попадут в файл gbichot2-bin.000007. Это инкрементное резервное копирование важно, таким образом, хорошая идея скопировать его в безопасное место. Во вторник в 13:00, выполните другую команду mysqladmin flush-logs . Все изменения между 13:00 в понедельник и 13:00 во вторник попадут в файл gbichot2-bin.000008 (который также должен быть скопирован куда-нибудь подальше).

Двоичные журналы MySQL занимают место на диске. Чтобы освободить пространство, производите чистку их время от времени. Стоит удалить файлы, которые больше не нужны, в частности те, которые были сделаны до очередного полного резервного копирования: 

shell> mysqldump --single-transaction --flush-logs --master-data=2 \
 --all-databases --delete-master-logs > backup_sunday_1_PM.sql

Удаление двоичных журналов MySQL с помощью mysqldump --delete-master-logs может быть опасным, если Ваш сервер является главным сервером репликации, потому что ведомые серверы еще, возможно, не полностью обработали содержание двоичного журнала. Описание для команды PURGE BINARY LOGS объясняет, что должно быть проверено прежде, чем стереть двоичные журналы MySQL. См. раздел 14.4.1.1.

8.3.2. Использование резервных копий для восстановления

Теперь, предположите, что у нас есть катастрофический отказ в среду в 8:00, который требует восстановления из резервных копий. Чтобы восстановиться, сначала мы восстанавливаем последнюю полную резервную копию, которую имеем (от воскресенья 13:00). Полная резервная копия файла это только ряд SQL-операторов, так что восстановление выглядит очень легко: 

shell> mysql < backup_sunday_1_PM.sql

В этой точке данные восстанавливаются по состоянию на воскресенье 13:00. Чтобы восстановить изменения, произведенные с тех пор, мы должны использовать инкрементные резервные копии, то есть файлы gbichot2-bin.000007 и gbichot2-bin.000008 двоичного журнала: 

shell> mysqlbinlog gbichot2-bin.000007 gbichot2-bin.000008 | mysql

Мы теперь восстановили данные к состоянию 13:00 во вторник, но все еще пропускаем изменения с этого момента до момента сбоя. Чтобы не потерять их, мы должны были бы сделать так, чтобы сервер MySQL сохранил двоичные журналы в безопасное расположение (диски RAID, SAN...) так, чтобы эти журналы не были на уничтоженном диске. Таким образом, мы можем запустить сервер с опцией --log-bin, которая указывает расположение на ином устройстве. Если бы мы сделали это, мы имели бы файл gbichot2-bin.000009 (и любые последующие файлы) под рукой и могли быприменить их, используя mysqlbinlog и mysql , чтобы восстановить новые изменения данных до момента отказа: 

shell> mysqlbinlog gbichot2-bin.000009 ... | mysql

8.3.3. Обзор стратегий резервного копирования

В случае катастрофического отказа операционной системы или сбоя питания InnoDB непосредственно делает все восстановление данных. Но чтобы в этом удостовериться, проверьте следующее:

  • Всегда выполняйте сервер MySQL с опцией --log-bin или --log-bin= log_name, где файл журнала располагается на некотором безопасном носителе, отличающемся от диска, на котором располагается каталог базы данных. Если у Вас есть такие безопасные носители, этот метод может также быть хорошим для выравнивания нагрузки диска (что имеет результатом повышение производительности).

  • Сделайте периодические полные резервные копии, используя команду mysqldump , которая делает онлайновое неблокирующее резервное копирование.
  • Сделайте периодические инкрементные резервные копии с помощью FLUSH LOGS или mysqladmin flush-logs .

8.4. Использование mysqldump для резервирования

Этот раздел описывает, как использовать mysqldump , чтобы произвести файлы дампа, и как перезагрузить файлы дампа. Файл дампа может использоваться несколькими способами:

  • Как резервная копия, чтобы обеспечить восстановление данных в случае потери информации.

  • Как источник данных для ведомых устройств репликации.
  • Как источник данных для экспериментирования:

    • Сделать копию базы данных, которую можно использовать, не изменяя исходные данные.

    • Чтобы протестировать потенциальные несовместимости обновлений.

mysqldump производит два типа вывода, в зависимости от наличия опции --tab:

  • Без опции --tab mysqldump пишет SQL-операторы в стандартный вывод. Этот вывод включает команды CREATE, чтобы создать выведенные объекты (базы данных, таблицы, хранимые процедуры и т.д.) и операторы INSERT, чтобы зарядить данными таблицы. Вывод может быть сохранен в файле и загружен позднее, используя mysql , чтобы воссоздать выведенные объекты. Доступны опции, чтобы модифицировать формат SQL-операторов и управлять тем, какие объекты выводятся.

  • С опцией --tab mysqldump производит два выходных файла для каждой выведенной таблицы. Сервер пишет один файл как разграниченный табуляциями текст, одну строку на строку таблицы. Этот файл называют tbl_name.txt в выходном каталоге. Сервер также отправляет в mysqldump команды CREATE TABLE для таблиц, которые пишутся в файл tbl_name.sql в выходном каталоге.

8.4.1. Дамп данных в формат SQL с помощью mysqldump

Этот раздел описывает, как использовать mysqldump, чтобы создать файлы дампа в формате SQL. Для получения информации о загрузке таких файлов дампа см. раздел 8.4.2.

По умолчанию mysqldump пишет информацию как SQL-операторы на стандартный вывод. Можно сохранить вывод в файле: 

shell> mysqldump [arguments] > file_name

Чтобы вывести все базы данных, вызовите mysqldump с опцией --all-databases: 

shell> mysqldump --all-databases > dump.sql

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

shell> mysqldump --databases db1 db2 db3 > dump.sql

Опция --databases предписывает все имена в командной строке обработать как имена баз данных. Без этой опции mysqldump обрабатывает первое имя как имя базы данных, а остальные как имена таблиц.

С опцией --all-databases или --databases mysqldump пишет CREATE DATABASE и USE до дампа для каждой базы данных. Это гарантирует, что когда файл дампа загружается, он создает каждую базу данных, если он не существует, и делает его базой данных по умолчанию, таким образом, содержанием базы данных заряжают ту же самую базу данных, из которой они прибыли. Если Вы хотите полностью пересоздать базу данных перед тем, как вызвать файл дампа, используйте опцию --add-drop-database. В этом случае mysqldump пишет команду DROP DATABASE перед каждым вызовом CREATE DATABASE.

Чтобы вывести единственную базу данных, назовите ее в командной строке: 

shell> mysqldump --databases test > dump.sql

В случае единственной базы данных допустимо пропустить опцию --databases : 

shell> mysqldump test > dump.sql

Различие между двумя предыдущими командами в том, что без --databases вывод не содержит CREATE DATABASE или USE. У этого есть несколько последствий:

  • Когда Вы загружаете файл дампа, следует указать имя базы данных по умолчанию так, чтобы сервер знал, которую база данных загрузить.

  • Для того, чтобы загрузить, можно указать имя базы данных, отличающееся от настоящего имени, что позволяет Вам загрузить данные в иную базу данных.
  • Если база данных не существует, следует создать ее сначала.
  • Поскольку вывод не будет содержать CREATE DATABASE, опция --add-drop-database не имеет никакого эффекта. Если Вы используете ее, она не произведет оператор DROP DATABASE.

Чтобы вывести только определенные таблицы из базы данных, назовите их в командной строке после имени базы данных: 

shell> mysqldump test t1 t3 t7 > dump.sql

8.4.2. Загрузка резервных копий формата SQL

Чтобы загрузить файл дампа, записанный mysqldump, состоящий из SQL-операторов, используйте его как ввод для клиента mysql. Если файл дампа создавался mysqldump с опцией --all-databases или --databases , это содержит операторы CREATE DATABASE и USE , а значит не надо указывать базу данных по умолчанию, в которую можно загрузить данные: data: 

shell> mysql < dump.sql

Альтернативно можно использовать в mysql команду source: 

mysql> source dump.sql

Если файл дамп единственной базы данных, не содержащий операторов CREATE DATABASE и USE, создайте базу данных сначала (в случае необходимости): 

shell> mysqladmin create db1

Затем укажите имя базы данных, когда Вы загружаете файл дампа: 

shell> mysql db1 < dump.sql

Альтернативно в mysql создайте базу данных, выберите ее как базу данных по умолчанию и загрузите файл дампа: 

mysql> CREATE DATABASE IF NOT EXISTS db1;
mysql> USE db1;
mysql> source dump.sql

Для любителей Windows PowerShell: поскольку символ "<" резервируется для перспективного использования в PowerShell, требуется использование кавычек: cmd.exe /c "mysql < dump.sql".

8.4.3. Дамп данных в формат разграниченного текста с mysqldump

Этот раздел описывает, как использовать mysqldump, чтобы создать файл дампа разграниченного текста.

Если Вы вызываете mysqldump с опцией --tab= dir_name, он использует dir_name как выходной каталог и пишет таблицы в этом каталоге, используя два файла для каждой таблицы. Имя таблицы это базовое имя для этих файлов. Для таблицы t1 будет два файла: t1.sql и t1.txt. Файл .sql содержит оператор CREATE TABLE для таблицы. Файл .txt содержит табличные данные, одну строку на строку таблицы.

Следующая команда выводит содержание базы данных db1 в файлы в каталоге /tmp: 

shell> mysqldump --tab=/tmp db1

Файлы .txt, содержащие табличные данные, пишутся сервером, таким образом, они принадлежат системной учетной записи, используемой для того, чтобы выполнить сервер. Он использует SELECT ... INTO OUTFILE , чтобы записать файлы, таким образом, Вы должны иметь привилегию FILE, чтобы выполнить эту работу. Если файл .txt уже есть, происходит ошибка.

Сервер посылает определение CREATE для выведенных таблиц в mysqldump , который пишет его в файлы .sql. Эти файлы поэтому принадлежат пользователю, который выполняет mysqldump .

Лучше всего использовать --tab только для резервирования локального сервера. Если Вы используете удаленный сервер, каталог должен существовать на локальной и удаленной машинах, файлы .txt будут записаны на сервере, а файлы .sql уже на клиентском хосте.

Для mysqldump --tab сервер по умолчанию пишет табличные данные в файлы .txt одну строку на строку таблицы с табуляциями между значениями столбцов, без кавычек вокруг значений столбцов и новой строкой как разделителем строк. Такие же значения по умолчанию и у SELECT ... INTO OUTFILE .

Чтобы позволить файлам данных использовать иной формат, mysqldump поддерживает эти опции:

  • --fields-terminated-by=str

    Строка для того, чтобы разделить значения столбцов (значение по умолчанию: tab).

  • --fields-enclosed-by=char

    Символ, в который включить значения столбцов (значение по умолчанию: никакой символ не задан).

  • --fields-optionally-enclosed-by=char

    Символ, в который включить нечисловые значения столбцов (значение по умолчанию: никакой символ).

  • --fields-escaped-by=char

    Символ для того, чтобы экранировать специальные символы (значение по умолчанию: никакой символ не задан).

  • --lines-terminated-by=str

    Строка линейного окончания (значение по умолчанию: новая строка).

В зависимости от значения, которое Вы указываете для любой из этих опций, могло бы быть необходимо в командной строке заключить в кавычки или экранировать символы соответственно для Вашего интерпретатора команд. Либо укажите значение, используя шестнадцатеричную нотацию. Предположите, что Вы хотите предписать mysqldump заключить значения столбцов в двойные кавычки. Чтобы сделать так, укажите двойную кавычку как значение для опции --fields-enclosed-by . Но этот символ часто специальный для интерпретаторов команд и должен быть обработан особенно. Например, в Unix, можно заключить двойную кавычку в кавычки так: 

--fields-enclosed-by='"'

На любой платформе можно указать значение в шестнадцатеричном виде: 

--fields-enclosed-by=0x22

Это распространено, чтобы использовать несколько опций форматирования данных вместе. Например, чтобы вывести таблицы в виде разделенных точками значений со строками, завершенными парами возврата каретки/новой строки (\r\n), используйте эту команду (введите ее в одну строку): 

shell> mysqldump --tab=/tmp --fields-terminated-by=,
                    --fields-enclosed-by='"' --lines-terminated-by=0x0d0a db1

Если Вы используете любую из опций форматирования данных, чтобы вывести табличные данные, Вы должны будете указать тот же самый формат, когда Вы загрузите файлы данных позже, чтобы обеспечить правильную интерпретацию содержания файла.

8.4.4. Загрузка резервных копий формата разграниченного текста

Для резервных копий, произведенных mysqldump --tab , каждая таблица представлена в выходном каталоге файлом .sql, содержащим оператор CREATE TABLE для таблицы, и файлом .txt, содержащим табличные данные. Чтобы загрузить таблицу, сначала перейдите в выходной каталог. Затем обработайте файл .sql программой mysql, чтобы создать пустую таблицу, и обработайте файл .txt, чтобы зарядить данными таблицу: 

shell> mysql db1 < t1.sql
shell> mysqlimport db1 t1.txt

Можно использовать вместо mysqlimport для загрузки файла данных оператор LOAD DATA INFILE из клиента mysql: 

mysql> USE db1;
mysql> LOAD DATA INFILE 't1.txt' INTO TABLE t1;

Если бы Вы использовали какие-либо опции форматирования данных в mysqldump, когда Вы первоначально вывели таблицу, то следует использовать те же самые опции с mysqlimport или LOAD DATA INFILE, чтобы обеспечить правильную интерпретацию содержания файла данных: 

shell> mysqlimport --fields-terminated-by=, --fields-enclosed-by='"'
                      --lines-terminated-by=0x0d0a db1 t1.txt
или: 
mysql> USE db1;
mysql> LOAD DATA INFILE 't1.txt' INTO TABLE t1
    -> FIELDS TERMINATED BY ',' FIELDS ENCLOSED BY '"'
    -> LINES TERMINATED BY '\r\n';

8.4.5. Подсказки по mysqldump

Этот раздел рассматривает методы, которые позволяют Вам использовать mysqldump , чтобы решить определенные проблемы:

  • Как сделать копию базы данных.

  • Как скопировать базу данных с одного сервера на другой.
  • Как вывести хранимые программы (хранимые процедуры, триггеры и события).
  • Как вывести определение и данные отдельно.

8.4.5.1. Создание копии базы данных

shell> mysqldump db1 > dump.sql
shell> mysqladmin create db2
shell> mysql db2 < dump.sql

Не используйте --databases в командной строке mysqldump , потому что это вызывает включение в файл дампа оператора USE db1 , который переопределяет результат именования db2 в командной строке mysql .

8.4.5.2. Копирование базы данных с сервера на сервер

На Server 1: 

shell> mysqldump --databases db1 > dump.sql

Скопируйте файл дампа с Server 1 на Server 2.

На Server 2: 

shell> mysql < dump.sql

Используйте --databases в mysqldump, чтобы файл дампа включал операторы CREATE DATABASE и USE, которые создают базу данных, если она не существует и делают ее базой данных по умолчанию для загруженных данных.

Альтернативно можно пропустить --databases в mysqldump . Затем Вы должны будете создать базу данных на Server 2 (в случае необходимости) и указать ее как базу данных по умолчанию, когда загрузите файл дампа.

На Server 1: 

shell> mysqldump db1 > dump.sql

На Server 2: 

shell> mysqladmin create db1
shell> mysql db1 < dump.sql

Можно указать иное имя базы данных в этом случае, таким образом пропуская --databases в вызове mysqldump , ято позволяет Вам вывести данные из одной базы данных и зарядить ими другую.

8.4.5.3. Дамп хранимых программ

Несколько опций управляют тем, как mysqldump обрабатывает хранимые программы (хранимые процедуры, триггеры и события):

  • --events : Дамп событий Event Scheduler.

  • --routines : Дамп хранимых процедур.
  • --triggers : Дамп триггеров для таблиц.

Опция --triggers включается по умолчанию, чтобы, когда таблицы выводятся, они сопровождались любыми триггерами, которые они имеют. Другие опции отключаются по умолчанию и должны быть указаны явно, чтобы вывести соответствующие объекты. Чтобы отключить любую из этих опций явно, используйте форму: --skip-events , --skip-routines или --skip-triggers.

8.4.5.4. Дамп определения таблицы и контента отдельно

Опция --no-data говорит mysqldump не выводить табличные данные, файл дампа, содержит только операторы, чтобы создать таблицы. Наоборот опция --no-create-info предписывает mysqldump подавлять в выводе операторы CREATE, чтобы файл дампа содержал только табличные данные.

Например, чтобы вывести табличное определение и данные отдельно для базы данных test, используйте эти команды: 

shell> mysqldump --no-data test > dump-defs.sql
shell> mysqldump --no-create-info test > dump-data.sql

Для дампа только определения добавьте опции --routines и --events, чтобы также включить сохраненные подпрограммы и события: 

shell> mysqldump --no-data --routines --events test > dump-defs.sql

8.4.5.5. Применение mysqldump, чтобы тестировать на совместимость обновления

Рассматривая обновление MySQL, благоразумно установить более новую версию отдельно от Вашей текущей версии. Затем можно вывести определения объектов и базы данных с рабочего сервера и зарядить ими новый, чтобы проверить, что они обрабатываются должным образом.

На рабочем сервере:

shell> mysqldump --all-databases --no-data --routines --events > dump-defs.sql

На новом сервере: 

shell> mysql < dump-defs.sql

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

На рабочем сервере: 

shell> mysqldump --all-databases --no-create-info > dump-data.sql

На новом сервере: 

shell> mysql < dump-data.sql

Теперь проверьте табличное содержание и выполните тестовые запросы.

8.5. Инкрементное восстановление, используя двоичный журнал

Восстановление момента времени обращается к восстановлению изменений данных, произведенных начиная с данного момента времени. Как правило, этот тип восстановления выполняется после восстановления полной резервной копии, которое приводит сервер к его состоянию во время, когда было сделано резервное копирование. Полное резервное копирование может быть сделано несколькими способами, такими как перечисленные в разделе 8.2. Восстановление момента времени затем вводит сервер в курс дела со времени полного резервного копирования.

Восстановление момента времени основано на этих принципах:

  • Источник информации для восстановления момента времени это набор инкрементных резервных копий, представленных файлами двоичного журнала, сгенерированными вслед за полной операцией резервного копирования. Поэтому, сервер должен быть запущен с опцией --log-bin, чтобы включить двоичное журналирование.

    Чтобы восстановить данные из двоичного журнала, следует знать название и местоположение текущих файлов журнала. По умолчанию сервер создает файлы двоичного журнала в каталоге базы данных, но путь может быть указан с помощью опции --log-bin , чтобы поместить файлы в иное расположение.

    Чтобы увидеть список всех файлов двоичного журнала, используйте этот оператор: 

    mysql> SHOW BINARY LOGS;

    Чтобы определить имя текущего файла журнала, сделайте следующее: 

    mysql> SHOW MASTER STATUS;
  • Утилита mysqlbinlog преобразует события в файлах журнала из двоичного формата в текстовый, чтобы они могли быть выполнены или просмотрены. mysqlbinlog имеет опции для того, чтобы выбрать разделы двоичного журнала, основанные на времени события или позиции событий в пределах журнала.
  • Выполнение событий двоичного журнала вызывает модификации данных. Это включает восстановление изменений данных для данного промежутка времени. Чтобы выполнить события из двоичного журнала, обработайте вывод mysqlbinlog , используя клиент mysql : 
    shell> mysqlbinlog binlog_files | mysql -u root -p
  • Просмотр содержания журнала может быть полезным, когда Вы должны определить время события или позицию, чтобы выбрать частичное содержание журнала до выполняющихся событий. Чтобы просмотреть события в журнале, отправьте вывод mysqlbinlog в любую программу оповещения: 
    shell> mysqlbinlog binlog_files | more

    Альтернативно можно сохранить вывод в файле и просмотреть этот файл в текстовом редакторе: 

    shell> mysqlbinlog binlog_files > tmpfile
shell> ... edit tmpfile ...
  • Сохранение вывода в файле полезно как предварительное мероприятие перед выполнением содержания журнала с удаленными достоверными событиями, такими как DROP DATABASE. Можно стереть из файла любые операторы, которые не будут выполняться, прежде чем выполнить его содержание. После редактирования файла выполните содержание следующим образом: 
    shell> mysql -u root -p < tmpfile

Если у Вас есть больше, чем один двоичный журнал, надежный метод должен обработать их все, используя одно и то же соединение. Вот пример, который демонстрирует то, что может быть небезопасно: 

shell> mysqlbinlog binlog.000001 | mysql -u root -p # DANGER!!
shell> mysqlbinlog binlog.000002 | mysql -u root -p # DANGER!!

Обработка двоичного файла этим способом использует различные соединения, что гарантирует проблемы, если первый файл журнала содержит оператор CREATE TEMPORARY TABLE , а второй использует временную таблицу. Когда первый процесс mysql завершается, сервер отбрасывает временную таблицу. Когда второй процесс mysql пытается использовать таблицу, сервер сообщает "unknown table".

Чтобы избежать таких проблем, используйте одно соединение, чтобы обработать содержание всех двоичных журналов, которые Вы хотите обработать. Например, так: 

shell> mysqlbinlog binlog.000001 binlog.000002 | mysql -u root -p

Другой подход записать все журналы в один файл и затем обработать файл: 

shell> mysqlbinlog binlog.000001 >  /tmp/statements.sql
shell> mysqlbinlog binlog.000002 >> /tmp/statements.sql
shell> mysql -u root -p -e "source /tmp/statements.sql"

При записи в файл дампа, при чтении двоичного журнала, содержащего GTID (см. раздел 19.1.3), используйте опцию --skip-gtids с mysqlbinlog: 

shell> mysqlbinlog --skip-gtids binlog.000001 >  /tmp/dump.sql
shell> mysqlbinlog --skip-gtids binlog.000002 >> /tmp/dump.sql
shell> mysql -u root -p -e "source /tmp/dump.sql"

8.5.1. Восстановление момента времени, используя время события

Чтобы указать начало и конец для восстановления, укажите опции --start-datetime и --stop-datetime программы mysqlbinlog в формате DATETIME. Например, предположите, что точно 20 апреля 2005 в 10:00 выполнялся SQL-оператор, который стер большую таблицу. Чтобы восстановить таблицу и данные, Вы могли восстановить резервную копию с предыдущей ночи, а затем выполнить следующую команду: 

shell> mysqlbinlog --stop-datetime="2005-04-20 9:59:59" \
                      /var/log/mysql/bin.123456 | mysql -u root -p

Эта команда восстанавливает все данные вплоть до даты и времени, заданной опцией --stop-datetime. Если Вы не обнаруживали ошибочный SQL-оператор, Вы будете, вероятно, также хотеть восстановить действие, которое встречалось позже. Исходя из этого, Вы могли выполнить mysqlbinlog , указав дату и время начала: 

shell> mysqlbinlog --start-datetime="2005-04-20 10:01:00" \
                      /var/log/mysql/bin.123456 | mysql -u root -p

В этой команде будут повторно выполняться SQL-операторы, зарегистрированные с 10:01. Сочетание восстановления файла дампа с предыдущей ночи и двух команд mysqlbinlog восстанавливает все вплоть до одной секунды до 10:00 и все после 10:01.

Чтобы использовать этот метод восстановления момента времени, следует исследовать журнал, чтобы убедиться в точных временах для команд. Чтобы вывести на экран содержание файла журнала, не выполняя его, используйте эту команду: 

shell> mysqlbinlog /var/log/mysql/bin.123456 > /tmp/mysql_restore.sql

Затем откройте файл /tmp/mysql_restore.sql текстовым редактором, чтобы исследовать его. Исключая определенные изменения, указание времени для mysqlbinlog не подходит, если несколько операторов выполнились одновременно с тем, который будет исключен.

8.5.2. Восстановление момента времени, используя позиции события

Вместо того, чтобы указать даты и времена, опции --start-position и --stop-position для mysqlbinlog могут использоваться для того, чтобы указать позиции журнала. Они работают аналогично дате и времени, за исключением того, что Вы указываете номера позиции журнала, а не даты. Использование позиции может позволить Вам быть более точным, особенно если много транзакций встречались в то же самое время, что и повреждающий SQL-оператор. Чтобы определить числа позиции, выполните mysqlbinlog для диапазона времен около времени, когда нежелательная транзакция выполнялась, но перенаправьте результаты к текстовому файлу для исследования. Это может быть сделано как-то так: 

shell> mysqlbinlog --start-datetime="2005-04-20 9:55:00" \
                      --stop-datetime="2005-04-20 10:05:00" \
                      /var/log/mysql/bin.123456 > /tmp/mysql_restore.sql

Эта команда создает малый текстовый файл в каталоге /tmp, который содержит SQL-операторы во время, когда вредный SQL-оператор выполнялся. Откройте этот файл текстовым редактором и ищите оператор, который Вы не хотите повторить. Определите позиции в двоичном журнале для остановки и возобновления восстановления и сделайте запись их. Позиции маркируются как log_pos сопровождаемый числом. После восстановления предыдущей резервной копии файла, используйте числа позиции, чтобы обработать файл двоичного журнала. Например, Вы использовали бы команды: 

shell> mysqlbinlog --stop-position=368312 /var/log/mysql/bin.123456 \
                      | mysql -u root -p
shell> mysqlbinlog --start-position=368315 /var/log/mysql/bin.123456 \
                      | mysql -u root -p

Первая команда восстанавливает все транзакции вплоть до данного положения остановки. Вторая команда восстанавливает все транзакции с исходного положения до конца двоичного журнала. Поскольку вывод mysqlbinlog включает операторы SET TIMESTAMP перед каждым записанным SQL-оператором, восстановленные данные и связанные журналы MySQL отразят исходные времена, в которые выполнялись транзакции.

8.6. Поддержка и аварийное восстановление таблиц MyISAM

Этот раздел обсуждает, как использовать myisamchk, чтобы проверить или восстановить таблицы MyISAM (таблицы, которые имеют файлы .MYD и .MYI для данных и индексов). Для общего обзора myisamchk см. раздел 5.6.4.

Можно использовать myisamchk, чтобы проверить, восстановить или оптимизировать таблицы базы данных. Следующие разделы описывают, как выполнить эти операции. Для получения информации об использовании myisamchk, чтобы получить информацию о Ваших таблицах, см. раздел 5.6.4.5.

Даже при том, что табличное восстановление с myisamchk довольно безопасно, всегда хорошая идея сделать резервную копию прежде, чем сделать восстановление или любую операцию по техническому обслуживанию, которая могла произвести много изменений таблиц.

Действия myisamchk , которые влияют на индексы, могут пересоздать индексы MyISAM FULLTEXT с полнотекстовыми параметрами, которые являются несовместимыми со значениями, используемыми сервером MySQL. Чтобы избежать этой проблемы, следуйте инструкциям в разделе 5.6.4.1.

Поддержка таблиц MyISAM может также быть сделана, используя SQL-операторы, которые выполняют операции, подобные тем, что может сделать myisamchk :

  • Проверять таблицы MyISAM с помощью CHECK TABLE.

  • Восстановить таблицы MyISAM с помощью REPAIR TABLE.
  • Оптимизировать таблицы MyISAM с помощью OPTIMIZE TABLE.
  • Анализировать таблицы MyISAM с помощью ANALYZE TABLE.

Эти операторы могут использоваться прямо или посредством программы mysqlcheck . Одно преимущество этих операторов перед myisamchk в том, что сервер делает всю работу. С myisamchk следует удостовериться, что сервер не использует таблицы одновременно так, чтобы не было никакого нежелательного взаимодействия между ним и myisamchk.

8.6.1. Применение myisamchk для аварийного восстановления

Этот раздел описывает, как проверить нарушение целостности данных в базах данных MySQL. Если Ваши таблицы становятся поврежденными часто, следует попытаться обнаружить причину. См. раздел B.5.3.3.

Причины повреждения таблиц MyISAM подробно рассмотрены в разделе 17.2.4.

Если Вы выполняете mysqld с отключенной внешней блокировкой (что является значением по умолчанию), невозможно адекватно использовать myisamchk , чтобы проверить таблицу, когда mysqld использует ту же самую таблицу. Если можно быть уверенным, что никто не получит доступ к таблицам через mysqld, в то время как Вы выполняете myisamchk, необходимо выполнить mysqladmin flush-tables прежде, чем Вы начнете проверять таблицы. Если невозможно гарантировать это, следует остановить mysqld, в то время как Вы проверяете таблицы. Если Вы выполняете myisamchk , чтобы проверить таблицы, которые mysqld обновляет, можно получить предупреждение, что таблицы повреждены, даже когда это не так.

Если сервер выполняется с внешней включенной блокировкой, можно использовать myisamchk , чтобы проверить таблицы в любое время. В этом случае, если сервер пытается обновить таблицу, которую проверяет myisamchk , сервер будут ожидать завершения myisamchk.

Если Вы используете myisamchk, чтобы восстановить или оптимизировать таблицы, Вы должны убедиться, что сервер не использует таблицу (это также применяется, если внешняя блокировка отключается). Если Вы не останавливаете mysqld , следует, по крайней мере, сделать mysqladmin flush-tables прежде, чем Вы выполните myisamchk . Ваши таблицы могут быть повреждены, если сервер и myisamchk имеют доступ к таблице одновременно.

Выполняя аварийное восстановление, важно понять что каждая таблица MyISAM tbl_name в базе данных соответствует этим файлам в каталоге базы данных, показанным в следующей таблице.

ФайлОписание
tbl_name.MYD Данные
tbl_name.MYI Индекс

Каждый из этих файлов подвержен повреждению различными способами, но проблемы встречаются чаще всего в файлах данных

myisamchk работает, создавая копию файла данных .MYD построчно. Это заканчивает этап восстановления, удаляя старый файл .MYD и переименовывая новый файл к имени файла оригинала. Если Вы используете --quick, myisamchk не создает временный файл.MYD, а вместо этого предполагает, что файл .MYD корректен и генерирует только новый индексный файл, не затрагивая файл .MYD. Это безопасно, потому что myisamchk автоматически обнаруживает, что файл .MYD поврежден и прерывает восстановление, если это так. Можно также указать опцию --quick дважды. В этом случае myisamchk не прерывается на некоторых погрешностях (таких как ошибка дублирования ключа), но вместо этого пытается их решить, модифицируя файл .MYD. Обычно использование двух опций --quick полезно, только если слишком мало свободного пространства на диске, чтобы выполнить нормальное восстановление. В этом случае следует, по крайней мере, сделать резервное копирование таблицы прежде, чем выполнить myisamchk .

8.6.2. Как проверить на ошибки таблицу MyISAM

Для проверки таблицы MyISAM, используйте следующие команды:

  • myisamchk tbl_name

    Это обнаруживает 99.99% всех погрешностей. То, что это не может обнаружить, является повреждением, которое включает только файл данных (что очень необычно). Если Вы хотите проверить таблицу, следует обычно выполнять myisamchk без опций или с опцией -s.

  • myisamchk -m tbl_name

    Это обнаруживает 99.999% всех погрешностей. Это сначала проверяет все элементы индекса на погрешности и затем прочитывает все строки. Это вычисляет контрольную сумму для всех значений ключа в строках и проверяет, что контрольная сумма соответствует контрольным суммам для ключей в индексном дереве.

  • myisamchk -e tbl_name

    Это делает полную и тщательную проверку всех данных. Это делает контрольное считывание каждого ключа для каждой строки, чтобы проверить, что они действительно указывают на корректную строку. Это может занять много времени для большой таблицы, у которой есть много индексов. Обычно myisamchk прерывается после первой погрешности, которую это обнаруживает. Если Вы хотите получить больше информации, можно добавить опцию -v (verbose). Это позвоялет myisamchk продолжать, но обработается максимум 20 сбоев.

  • myisamchk -e -i tbl_name

    Это походит на предыдущую команду, но опция -i говорит myisamchk печатать дополнительную статистическую информацию.

В большинстве случаев простая команда myisamchk без параметров кроме имени таблицы достаточна, чтобы проверить таблицу.

8.6.3. Как восстановить таблицы MyISAM

Обсуждение в этом разделе описывает, как использовать myisamchk на таблицах MyISAM (расширения .MYI и .MYD).

Можно также использовать CHECK TABLE и REPAIR TABLE , чтобы проверить и восстановить таблицы MyISAM.

Симптомы поврежденных таблиц включают запросы, которые неожиданно прерываются и наблюдаемые погрешности, такие как:

  • Не может обнаружить файл tbl_name.MYI (Errcode: nnn).

  • Неожиданный конец файла.
  • Запись в файле разрушена.
  • Получена ошибка nnn от табличного обработчика.

Чтобы получить больше информации о погрешности, выполните perror nnn, где nnn задает номер ошибки. Следующий пример показывает, как использовать perror, чтобы обнаружить значения для наиболее распространенных кодов ошибки, которые указывают на проблему с таблицей: 

shell> perror 126 127 132 134 135 136 141 144 145
MySQL error code 126 = Index file is crashed
MySQL error code 127 = Record-file is crashed
MySQL error code 132 = Old database file
MySQL error code 134 = Record was already deleted (or record file crashed)
MySQL error code 135 = No more room in record file
MySQL error code 136 = No more room in index file
MySQL error code 141 = Duplicate unique key or constraint on write or update
MySQL error code 144 = Table is crashed and last repair failed
MySQL error code 145 = Table was marked as crashed and should be repaired

Отметьте, что ошибки 135 (no more room in record file) и 136 (no more room in index file) не являются ошибками, которые могут быть исправлены простым восстановлением. В этом случае следует использовать ALTER TABLE для увеличения значений опций MAX_ROWS и AVG_ROW_LENGTH таблицы: 

ALTER TABLE tbl_name MAX_ROWS=xxx AVG_ROW_LENGTH=yyy;

Если Вы не знаете текущие значения опции, используйте SHOW CREATE TABLE .

Для других погрешностей следует восстановить свои таблицы. myisamchk может обычно обнаруживать и решать большинство проблем, которые встречаются.

Процесс восстановления включает до трех этапов, описанных здесь. Прежде, чем Вы начнете, следует перейти в каталог базы данных и проверить права доступа табличных файлов. В Unix удостоверьтесь, что они читаемы пользователем, от имени которого работает mysqld (и Вами, потому что Вы должны получить доступ к файлам, которые проверяете). Если придется модифицировать файлы, они также должны быть перезаписываемыми Вами.

Этот раздел для случаев, где проверка таблиц перестала работать (такие, как описанные в разделе 8.6.2 ), или Вы хотите использовать расширенные функции, которые обеспечивает myisamchk .

Если Вы собираетесь восстановить таблицу из командной строки, следует сначала остановить сервер mysqld . Отметьте, что когда Вы делаете mysqladmin shutdown на удаленном сервере, mysqld сервер все еще доступен некоторое время, после mysqladmin , пока вся обработка операторов не остановилась, а все индексные изменения не были сброшены.

Этап 1: Проверка Ваших таблиц

Запустите myisamchk *.MYI или myisamchk -e *.MYI, если у Вас есть больше времени. Используйте опцию -s, чтобы подавить ненужную информацию.

Если mysqld остановлен, следует использовать опцию --update-state , чтобы сказать myisamchk отмечать таблицу как проверенную.

Необходимо восстановить только те таблицы, для которых myisamchk сообщает об ошибке.

Если Вы получаете неожиданные ошибки при проверке (например, out of memory) или myisamchk вылетает, перейдите к этапу 3.

Этап 2: Легкое безопасное восстановление

Во-первых, попробуйте myisamchk -r -q tbl_name (-r -q означает quick recovery mode). Это пытается восстановить индексный файл, не касаясь файла данных. Если файл данных содержит все, что он должен, это должно работать. Начните восстанавливать следующую таблицу. Иначе, используйте следующую процедуру:

  1. Сделайте резервное копирование файла данных перед продолжением.

  2. Примените myisamchk -r tbl_name (-r значит recovery mode). Это удаляет неправильные и стертые строки от файла данных и реконструирует индексный файл.
  3. Если предшествующая стадия перестала работать, используйте myisamchk --safe-recover tbl_name. Безопасный режим восстановления использует старый метод, который обрабатывает несколько случаев, которые регулярный режим восстановления не делает (но медленнее).

Если Вы хотите, чтобы ремонтная операция пошла намного быстрее, следует установить значения переменных sort_buffer_size и key_buffer_size приблизительно к 25% Вашей доступной памяти, запуская myisamchk .

Если Вы получаете неожиданные ошибки при проверке (например, out of memory) или myisamchk вылетает, перейдите к этапу 3.

Этап 3: Трудное восстановление

Следует достичь этой стадии, только если первые 16KB индексного файла уничтожены или содержат неправильную информацию, или если индексный файл разрушен. В этом случае необходимо создать новый индексный файл. Сделайте следующим образом:

  1. Переместите файл данных в безопасное место.

  2. Используйте табличный файл описания, чтобы создать новые (пустые) данные и индексные файлы: 
    shell> mysql db_name
mysql> SET autocommit=1;
mysql> TRUNCATE TABLE tbl_name;
mysql> quit
  3. Скопируйте старый файл данных назад на недавно создаваемый файл данных (не просто положите обратно старый файл на новый файл. Вы хотите сохранить копию в случае, если что-то пойдет в раскосец).

Если Вы используете репликацию, следует остановить ее до выполнения вышеупомянутой процедуры, так как она включает операции файловой системы, а они не регистрируются MySQL.

Вернитесь на этап 2. myisamchk -r -q должен работать (это не должно быть бесконечным циклом).

Можно также использовать SQL-оператор REPAIR TABLE tbl_name USE_FRM, который выполняет процедуру автоматически. Нет также никакой возможности нежелательного взаимодействия между утилитой и сервером, потому что сервер делает всю работу, когда Вы используете REPAIR TABLE .

8.6.4. Оптимизация таблиц MyISAM

Чтобы объединить фрагментированные строки и отщепить потраченное впустую пространство, которое следует из удаления или обновления строк, выполните myisamchk в режиме восстановления: 

shell> myisamchk -r tbl_name

Можно оптимизировать таблицу таким же образом при использовании SQL-оператора OPTIMIZE TABLE . OPTIMIZE TABLE делает табличное восстановление и ключевое исследование, а также сортирует индексное дерево так, чтобы ключевые поиски были более быстрыми. Нет также никакой возможности нежелательного взаимодействия между утилитой и сервером, потому что сервер делает всю работу, когда Вы используете OPTIMIZE TABLE.

У myisamchk есть много других опций, которые можно использовать, чтобы улучшить производительность таблицы:

  • --analyze или -a: Выполнить исследование распределения ключей. Это улучшает производительность связывания, позволяя оптимизатору лучше выбрать порядок, в котором можно соединить таблицы, а также какие индекы он должен использовать.

  • --sort-index или -S: Сортировать индексные блоки. Это оптимизирует поиск и делает сканирование таблиц, которые используют индексы, быстрее.
  • --sort-records=index_num или -R index_num: Сортировать строки данных, согласно данному индексу. Это делает Ваши данные намного больше локализованными и может ускорить основанные на диапазоне операции SELECT и ORDER BY , которые используют этот индекс.

8.6.5. Установка графика текущего обслуживания таблиц MyISAM

Стоит проверять таблицы регулярно, а не ждать проблем. Одним из вариантов для проверки и ремонта таблиц MyISAM являются операторы CHECK TABLE и REPAIR TABLE.

Другой способ проверить таблицы состоит в том, чтобы использовать myisamchk . Для целей поддержки можно использовать myisamchk -s . Опция -s (сокращение для --silent) предписывает myisamchk работать в тихом режиме и выводить сообщения только, когда погрешности встречаются.

Также хорошая идея включить автоматическую табличную проверку MyISAM. Например, всякий раз, когда машина сделала рестарт в середине обновления, Вы обычно должны проверять каждую таблицу, на которую, возможно, влияли прежде, чем она будет использоваться далее. Чтобы сервер проверял таблицы MyISAM автоматически, запустите с опцией --myisam-recover-options.

Следует также регулярно проверять свои таблицы во время работы нормальной системы. Например, можно выполнить задание cron , чтобы проверить важные таблицы один раз в неделю, используя строку в файле crontab: 

35 0 * * 0 /path/to/myisamchk --fast --silent /path/to/datadir/*/*.MYI

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

Обычно таблицы MySQL нуждаются в небольшой поддержке. Если Вы выполняете много обновлений с доведенными до требуемого размера строками (таблицы со столбцами VARCHAR, BLOB или TEXT) или имеете таблицы со многими стертыми строками, нужно дефрагментировать пространство таблиц время от времени. Можно сделать это при использовании OPTIMIZE TABLE на рассматриваемых таблицах. Либо, если можно остановить сервер mysqld на некоторое время, перейдите в каталог базы данных и используйте эту команду, в то время как сервер остановлен: 

shell> myisamchk -r -s --sort-index --myisam_sort_buffer_size=16M */*.MYI

Поиск

  

Найди своих коллег!

Вы можете направить письмо администратору этой странички, Алексею Паутову. mailto:alexey.v.pautov@mail.ru