Главная » Шпаргалка » PHP MYSQL javascript » SQL » MySQL востановление

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

Здесь мы обсуждаем, как восстановить данные после:

  1. сбоя операционной системы
  2. отказа электропитания
  3. сбоя файловой системы
  4. аппаратных сбоев(жесткого диска, материнской платы...)

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

Случаи 1) и 2)

В этих случаях мы можем предположить, что диск с данными MySQL доступен после рестарта операционной системы. Во время рестарта непротиворечивость данных в файлах 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

Случаи 3) и 4)

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

Политика резервирования данных

Все мы знаем, что периодическое создание резервных копий (откатов) должно планироваться заранее. Полные откаты (моментальные снимки данных на определенный момент времени) создаются при помощи нескольких инструментальных средств MySQL. Горячий откат (Hot Backup) InnoDB обеспечивает онлайновый (не блокирующий) физический (копии файлов данных) откат. mysqldump обеспечивает онлайновый логический откат, который мы рассмотрим далее в ходе обсуждения. Пример:

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

Так создается откат всех наших InnoDB-таблиц из всех баз данных, не препятствующий выполнению текущих операций чтения/записи этих таблиц. Содержимое .sql-файла - это набор SQL-операторов INSERT. (Предположим, что это полный откат на 1 час дня воскресенья, когда загрузка сервера мала.)

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

Чтобы делать откаты изменений, нам необходимо их фиксировать. Чтобы сервер сохранял информацию об изменениях в файле во время модификации данных, он должен быть запущен с опцией --log-bin. Тогда каждый SQL-оператор, модифицирующий данные, будет записан в файл (который мы называем "двоичным протоколом MySQL"). Посмотрим на содержимое директория данных сервера MySQL, работающего несколько дней в режиме --log-bin. Мы увидим здесь двоичные протоколы MySQL:

-rw-rw----    1 guilhem  guilhem   1277324 Nov 10 23:59 gbichot2-bin.001
-rw-rw----    1 guilhem  guilhem         4 Nov 10 23:59 gbichot2-bin.002
-rw-rw----    1 guilhem  guilhem        79 Nov 11 11:06 gbichot2-bin.003
-rw-rw----    1 guilhem  guilhem       508 Nov 11 11:08 gbichot2-bin.004
-rw-rw----    1 guilhem  guilhem  220047446 Nov 12 16:47 gbichot2-bin.005
-rw-rw----    1 guilhem  guilhem    998412 Nov 14 10:08 gbichot2-bin.006
-rw-rw----    1 guilhem  guilhem       361 Nov 14 10:07 gbichot2-bin.index

Каждый раз во время рестарта сервер MySQL прекращает запись в текущий файл двоичного протокола, создает новый и с этого момента новый файл станет текущим (тем, в который происходит запись). Такое переключение можно осуществить вручную по команде SQL FLUSH LOGS. .index-файл содержит список всех двоичных протоколов, сохраненных в директории (он используется для репликации).

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

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

Теперь мы видим в директории данных файл gbichot2-bin.007. Наш .sql-файл содержит строки:

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

Это значит, что все модификации данных, отраженные в двоичных протоколах, более старых чем gbichot2-bin.007, присутствуют в .sql-файле, а все модификации данных, отраженные в файле gbichot2-bin.007 или более новых, отсутствуют в .sql-файле. В понедельник в 1 час дня мы, чтобы сделать откат изменений, всего лишь введем команду mysqladmin --flush-logs, которая создаст gbichot2-bin.008. Все изменения, сделанные между 1 часом дня воскресенья, когда был сделан полный откат, и 1 часом дня понедельника отражены в файле gbichot2-bin.007. Копируем этот драгоценный файл в место безопасного хранения резервных копий (на ленту, резервную машину, DVD-диск и т.д.).

Во вторник, в 1 час дня, снова выполним mysqladmin --flush-logs. Все изменения, сделанные между 1 часом дня понедельника и 1 часом дня вторника, отражены в файле gbichot2-bin.008. Копируем и его в место безопасного хранения.

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

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

Назад к восстановлению из резервных копий

Итак, происходит отказ, скажем в среду, в 8 часов утра. Мы восстанавливаем последний имеющийся у нас полный откат, на воскресенье, 1 час дня. Поскольку он представляет собой всего лишь набор SQL-операторов, процесс восстановления очень прост:

mysql < backup_sunday_1_PM.sql

Теперь наши данные находятся в состоянии, в котором они были в воскресенье, в 1 час дня. Для восстановления откатов изменений всего лишь извлеките их из места безопасного хранения и сделайте:

mysqlbinlog gbichot2-bin.007 | mysql
mysqlbinlog gbichot2-bin.008 | mysql

Теперь наши данные находятся в состоянии на вторник, 1 час дня. Мы все же теряем изменения с этого момента до момента сбоя. Чтобы не потерять их, нужно было, чтобы сервер MySQL сохранял свои двоичные протоколы на устройстве (диски RAID, SAN...) отличном от того, где он хранит файлы данных, и тогда эти протоколы не оказались бы на вышедшем из строя диске. Если бы мы позаботились об этом (теперь мы сделаем это, обещаем!), у нас был бы файл gbichot2-bin.009, и мы восстановили бы его, и наши данные были бы такими, как в момент сбоя (ничего бы не потерялось). Мы могли бы действовать даже еще более гибко; если бы вместо отказа ошибочно выполнился оператор DROP DATABASE, мы могли бы откатить изменения из gbichot2-bin.009 на момент непосредственно предшествующий DROP DATABASE так, чтобы наши данные пришли в состояние, непосредственно предшествующее его исполнению! Например, если бы Вы знали, что ошибочный оператор сработал около 7:30 утра, то сделали бы откат до состояния на 7 утра (с 30-минутным запасом):

mysqlbinlog --stop-datetime=2004-11-17\ 07:00:00 gbichot2-bin.009 | mysql

Это немного неточно (Вы теряете приблизительно получасовые изменения), но вполне подходит в ряде случаев; в тех случаях, когда Вы реально не можете позволить себе потерять хоть что-нибудь, Вы захотите, чтобы mysqlbinlog доработал как раз до этого DROP DATABASE. Есть несколько способов сделать это, вот один:

mysqlbinlog gbichot2-bin.009 > a_temp_file.sql.

Мы редактируем a_temp_file.sql в нашем любимом редакторе; мы пользуемся функцией текстового поиска, чтобы найти в нем "DROP DATABASE", редактор немедленно находит, мы видим:

# at 79
#041117 07:26:08 server id 1  log_pos 79        Query   thread_id=1
exec_time=0     error_code=0
use test;
DELETE FROM `test`.`hea` WHERE col=879865;
# at 138
#041117 07:26:08 server id 1  log_pos 138       Query   thread_id=1
exec_time=0     error_code=0
DROP DATABASE our_cherished_database;
# at 198
#041117 07:26:08 server id 1  log_pos 198       Query   thread_id=1
exec_time=0     error_code=0
DELETE FROM `test`.`hea3`;

Мы удаляем все строки, начиная с DROP DATABASE, сохраняем a_temp_file.sql и запускаем

mysql < a_temp_file.sql.

Готово!

Резюме

Чтобы спать спокойно:

  1. В случае сбоя ОС или отключения электроэнергии InnoDB делает всю необходимую работу.
  2. Сервер MySQL всегда должен быть запущен с опцией --log-bin, или даже --log-bin=устройство_отличное_от_диска_с_данными если у Вас есть такое устройство; так или иначе это будет хорошо для сбалансированной нагрузки на диски (увеличения производительности).
  3. Периодически делайте полные резервные копии данных, используя последнюю приведенную выше команду mysqldump (это должен быть онлайновый, неблокирующий откат).
  4. Периодически делайте откаты изменений, используя mysqladmin.

Замечание 1: в реальной жизни требовалось бы включение опций --user и --password (и указание правильных имени и пароля) в вызовы программ mysqldump и mysql, чтобы они могли установить соединение с сервером MySQL.

Замечание 2: удаление двоичных протоколов MySQL по команде mysqldump --delete-master-logs может быть опасным, если ваш сервер является главным сервером репликации;в разделе "Репликация" объясняется, в чем необходимо убедиться перед удалением двоичных протоколов.