Восстановление базы MySQL: подробное руководство

15 Февраля 2016
1 289

Аварийное завершение работы базы данных MySQL (в России, увы¸ частой причиной аварийного завершения работы являются отключения электричества), механические повреждения серверного «железа» или обновление CMS – все это может стать причиной повреждения базы данных MySQL. 

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


Форматы таблиц

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


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


Общая схема восстановления MySQL

Задача по восстановлению базы данных в формате InnoDB решается с помощью опции innodb_force_recovery, которая будет размещена в файле конфигурации MySQL. До того, как она активирована, стоит попробовать воспользоваться командой SELECT ... I NTO OUT FILE. Чаще всего она позволяет сохранить данные без каких-либо дополнительных действий.


Если предыдущая операция не помогла (например, помешать нормальному восстановлению могли незавершенные из-за аварийного прекращения работы процессы/транзакции) придется воспользоваться возможностями, которые дает innodb_force_recovery.


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

Опция innodb_force_recovery прописывается в файле конфигурации MySQL, расположение которого зависит от того, какая операционная система используется. Путь к нему может иметь вид:


• /etc/my.cnf;
• /etc/mysql/my.cnf – если это операционная система Linux.


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


Важно! Для опции innodb_force_recovery можно прописать несколько значений. В варианте «по умолчанию» она выглядит так: innodb_force_recovery = 0. Любое число вместо нуля (от 1 до 6) позволяет провести восстановление не только собственно таблиц базы данных, но и процессов, которые не были завершены из-за аварийного завершения работы.


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

В файле my.cnf innodb_force_recovery прописывается в раздел [mysqld]. Включение опции потребует перезапуска сервера базы данных MySQL.


Начинать восстановление с помощью этой опции можно лишь при наличии, как минимум, копий:


• файлов данных;
• файлов журнала InnoDB;
• файла конфигурации БД (my.cnf);
• файлов таблиц .frm InnoDB.


Работа с опцией innodb_force_recovery

Основное правило работы по восстановлению с использованием innodb_force_recovery – последовательное изменение значений от 1 до 6:


• при значении innodb_force_recovery = 1 сервер пытается начать работу независимо от того, есть ли поврежденные данные InnoDB или нет;
• при значении = 2 удается восстановить работу за счет остановки потока команд, которые были частично выполнены или не выполнены.
• значение = 3 отменяет откат после восстановления поврежденных файлов.


Выставлять значения от 4 до 6 не рекомендуется, особенно если у вас нет большого опыта в работе с БД MySQL: риск потерять данные многократно возрастает. Однако опция innodb_force_recovery = 4/5/6 дает возможность, с использованием простых функций, делать выборку из таблиц, выявляя поврежденные.


Как снизит вероятность повреждения MySQL

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


Например, в Timeweb можно воспользоваться удобной дополнительной услугой "резервная копия по требованию". Запросить бекап можно в любой момент и хранить неограниченный период времени. 


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