Меню

Как восстановить базу данных MySQL



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

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

Причины повреждения таблиц

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

  • аппаратный сбой, когда произошло внезапное отключение системы, например из-за сбоев в электропитании;
  • внезапная остановка работы сервера MySQL, когда в этот момент осуществлялись доступ к БД и/или чтение и запись таблиц;
  • несанкционированный доступ к файлам БД со стороны сторонних программ.

В любом случае, первое, что нужно сделать — это остановить работу MySQL-сервера, сделать резервные копии файлов таблиц, которые по умолчанию находятся в каталоге /var/lib/mysql:

Для выполнения этой команды могут потребоваться привилегии суперпользователя или пользователя, имеющего доступ к каталогу /var/lib/mysql. Только после этого можно приступить к разбору ситуации и проверить, действительно ли таблицы повреждены. Для этого можно воспользоваться командной консолью MySQL. Но предварительно необходимо снова запустить MySQL-сервер. И авторизоваться на нём (от имени пользователя-администратора) с помощью команды:

Здесь username – имя пользователя-администратора в системе MySQL.

Проверка таблиц на предмет повреждений

Проверить таблицы на предмет повреждения можно встроенными средствами MySQL, если для них используется движок MyISAM. Для этого используется специальный запрос «CHECK TABLE». Итак, для начала, после авторизации в системе MySQL (как описано в предыдущей главе) необходимо выбрать интересующую БД:

Здесь db_name – имя требуемой базы данных, таблицы которой нужно проверить. Для получения списка всех имеющихся на сервере БД можно выполнить следующий запрос:

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

Как нетрудно догадаться, «db_table» здесь — это имя требуемой таблицы. В данном случае вывод в столбце «Msg-text» говорит о том, что с таблицей всё в порядке.

Восстановление MyISAM-таблиц

В случае, когда повреждения таблиц всё же есть. То с большой степенью вероятности таблицу можно починить, используя запрос «REPAIR TABLE»:

Теперь, если починка завершилась нормально, будет получен следующий вывод:

Как можно видеть, всё довольно просто. Однако, если повреждённых таблиц много, то процесс восстановления может быть довольно трудоёмким. Ну а если же данный способ успеха не принёс, то следует обращаться к более сложным методам починки таблиц, рекомендованных самими разработчиками MySQL.

Восстановление InnoDB-таблиц

В случае, если повреждённые таблицы обслуживались движком InnoDB, то придётся использовать более сложный метод восстановления. Дело в том, что движок InnoDB сам способен следить за исправностью таблиц и устранять повреждения. Однако, если он по каким-либо причинам не справляется с этой задачей, то сервер MySQL останавливается.

Метод восстановления для InnoDB-таблиц рекомендуется разработчиками MySQL и суть его в следующем:

  • необходимо сначала восстановить доступ к повреждённой таблице, поскольку сервер в этом случае останавливается;
  • выполнить резервную копию в виде дампа повреждённых таблиц, используя утилиту mysqldump, которая сохраняет структуру таблицы и данные;
  • удалить повреждённую таблицу;
  • выполнить загрузку дампа таблицы, но уже во вновь созданную таблицу БД, что будет сделано утилитой mysqldump автоматически.

Для восстановления доступа к таблицам можно сначала попытаться перезагрузить сервер MySQL. Если это не помогло, то можно запустить его с опцией innodb_force_recovery. Для этого необходимо отредактировать конфигурационный файл /etc/mysql/mysql.conf.d/mysqld.cnf , например, используя текстовый редактор nano:

Необходимо сделать изменения в разделе [mysqld], добавив в него строку:

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

Дамп back.dump будет сохранён в подкаталоге backups домашнего каталога текущего пользователя.

Теперь необходимо удалить из БД повреждённую таблицу. Для этого после авторизации в командной консоли MySQL (как описано в главе «Причины повреждения таблиц»), нужно выполнить следующий запрос:

Если БД была предварительно выбрана командой use, то в запросе следует указать только таблицу:

Теперь можно выйти из командной консоли MySQL командой exit. И выполнить загрузку дампа таблицы в БД:

В данном случае вместо «/home/username/backups/back.dump» следует использовать местоположение, куда ранее был сохранён дамп.

Заключение

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

Если вы нашли ошибку, пожалуйста, выделите фрагмент текста и нажмите Ctrl+Enter.

Читайте также:  Статистика матчей Брисбен Роар

Источник

Восстановление удаленных файлов базы данных MySQL

Читайте, как восстановить утерянную или удалённую базу MySQL. Как восстановить повреждённые таблицы базы MySQL с помощью myisamchk.

База данных MySQL по умолчанию устанавливается на компьютере в папку не диске С:

C:\Program Files\MySQL\MySQL Server 5.7

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

C:\ProgramData\MySQL\MySQL Server 5.7\Data

Расположение данных файлов указано в меню Server Status приложения MySQL Workbench, в разделе Server Directories.

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

Файлы базы данных MySQL

MySQL совместима с большим количеством форматов файлов, как например: .sql, .arm, .cnf, .dbs, .ddl, .frm, .ibd, .ism, .mrg, .myd, .myi, .mysql, .opt, .phl, .sal, .sqr, .tmd, .arz, .ibz, .ibc, .qbquery, .rul. Но данная статья не об этом. Сегодня нам интересны именно те файлы, в которых хранятся данные и таблицы базы, восстановив которые пользователь будет иметь возможность вернуть важную информацию и избежать её возможной утери в будущем.

Данные каждой базы данных хранятся в папке с её названием, и в зависимости от типа таблицы хранятся в файлах с такими расширениями:

  • db.opt – файл в котором хранятся характеристики базы данных, указанные при её создании;
  • .frm – файл структуры таблиц;
  • .myd – файл, в котором хранятся данные MyISAM таблиц;
  • .myi – файл, в котором хранятся индексы MyISAM таблиц;
  • .ibd – файл, в котором хранятся данные и индексы InnoDB таблиц.

Как восстановить базу данных MySQL

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

Кроме этого, возможно создание резервной копии и восстановление базы данных MySQL с помощью mysqldump (которую мы детально описали в одной из наших статей).

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

  • Создание копии данных таблиц базы MySQL возможно путём копирования файлов структуры и данных (*.opt, *.frm, *.myd, *.myi для MyIsam; *.opt, *.frm, *.ibd для InnoDB) и сохранения их в другой папке.
  • Восстановление данных таблиц базы MySQL возможно путём подстановки скопированных раннее файлов структуры и данных в папки уже существующих баз (в нашем случае это две базы: my_db и my_db2).

Восстановление утерянной или удалённой базы MySQL

Так, если по какой-то причине вы удалили базу данных MySQL, переустановили Windows или отформатировали жесткий диск, то восстановить её можно описанным выше способом, путём подставки скопированных раннее файлов базы данных в папку с названием базы:

C:\ProgramData\MySQL\MySQL Server 5.7\Data

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

Partition Recovery™ 3.8 The tool recovers data from any devices, regardless of the cause of data loss.

Запустите Hetman Partition Recovery и просканируйте с его помощью диск на котором хранилась база данных MySQL

Найдите и перейдите с помощью программы в папку C:\ProgramData\MySQL\MySQL Server 5.7\Data,

или осуществите поиск необходимых файлов базы данных с помощью функции поиска:

Обратите внимание: файлы с данными и форматами таблиц будут иметь название таблицы, а не базы данных.

Восстановите файлы базы данных

Перенесите их в папку с названием базы данных.

Запустив после этого MySQL Workbench, помещённые в папку базы восстановленные файлы таблиц будут доступны.

Таким же способом можно восстанавливать утерянные файла дампа (*.sql) или его архив (*.zip, *.gzip, *.bzip2).

Восстановление повреждённых таблиц базы MySQL с помощью myisamchk

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

В результате повреждения из таблицы могут исчезнуть или неправильно отображаться данные, но чаще всего в результате повреждения таблицы пользователи сталкиваются с ошибкой: «Incorrect key file for table: ‘название_таблицы’. Try to repair it»

Для восстановления повреждённых MyISAM таблиц можно использовать команду myisamchk.

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

Так, чтобы восстановить повреждённую таблицу, используйте команду:

myisamchk -r -q ИМЯ_ТАБЛИЦЫ

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

Читайте также:  Форматирование и редактирование диаграммы

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

myisamchk -r ИМЯ_ТАБЛИЦЫ

где, -r – это просто режим восстановления. В таком случае из файла данных будут удалены некорректные и утерянные записи, и индексный файл будет создан заново (как описано выше).

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

Источник

Восстановление MySQL баз данных ручными и «механическими» способами

Дата публикации: 2016-04-04

Восстановление MySQL баз данных

От автора: добрый день, уважаемые. У вас что-то случилось? Опять «выкинули» не ту базу данных? Ну, это не смертельно, если знать все про восстановление MySQL. Сейчас мы расскажем вам все тонкости данного ритуала. Для этого нужен бубен, козявка из носа белохвостого тюленя… Это шутка! А все серьезное по этой теме будет изложено дальше.

Горе поправимо, если удалили базу

Без баз данных и систем управления ими (СУБД) в интернете никуда. Большая часть современных CMS и «самописных» движков, на которых развернуты сайты, используют MySQL. Поэтому ее можно смело назвать «всея интернетной» системой управления базами данных.

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

Быстрый способ восстановления

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

Зайдите в phpMyAdmin и выберете нужную БД.

Бесплатный курс по PHP программированию

Освойте курс и узнайте, как создать динамичный сайт на PHP и MySQL с полного нуля, используя модель MVC

В курсе 39 уроков | 15 часов видео | исходники для каждого урока

Перейдите по вкладке «Импорт», которая расположена в главном верхнем меню.

В разделе «Импортируемый файл» выберете источник резервной копии нужной базы.

Нажатие на кнопку «Ok».

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

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

Работа через MySQLdump

MySQLdump представляет собой веб-приложение, работающее на стороне сервера. Оно предназначено для восстановления баз MySQL из резервных копий, созданных с помощью приложения. Чтобы сильно «не зарываться», мы продемонстрируем создание простого бэкапа и восстановление из него БД. В качестве площадки для эксперимента используем самый популярный локальный сервер Рунета Denwer.

Для начала нужно скачать MySQLdump и поместить его по следующему адресу: F:\Webserver\usr\local\mysql-5.5\bin

MySQLdump является консольным приложением, поэтому вся последующая работа с ним будет происходить через командную строку (cmd.exe). Теперь поэтапно:

Через командную строку заходим на виртуальный диск (в примере – это диск Z).

Заходим в папку, где «лежит» MySQLdump.

Бесплатный курс по PHP программированию

Освойте курс и узнайте, как создать динамичный сайт на PHP и MySQL с полного нуля, используя модель MVC

В курсе 39 уроков | 15 часов видео | исходники для каждого урока

После этого запускаем утилиту на выполнение. Перед тем, как восстановить БД MySQL, в качестве примера создадим в папке bin резервную копию базы my_db1, и назовем бэкап «db1». Для этого мы используем команду mysqldump.

Теперь восстановим из созданной копии (db1) другую базу данных. Для этого используем команду mysql.

Источник

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

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

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

Вот некоторые общие причины повреждения таблиц:

  • Сервер MySQL остановился, не завершив операцию записи.
  • Таблица изменяется внешней программой и сервером одновременно.
  • Машина внезапно остановилась.
  • Произошла поломка аппаратного обеспечения.
  • В коде MySQL появился баг.

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

Сначала остановите сервис MySQL:

sudo systemctl stop mysql

Затем скопируйте все ваши данные в резервный каталог. В системе Ubuntu каталогом данных по умолчанию является /var/lib/mysql/:

cp -r /var/lib/mysql /var/lib/mysql_bkp

Читайте также:  Время алкоголя пива крови таблица

После этого вы можете попробовать узнать, действительно ли повреждена таблица. Если таблица использует движок MyISAM, вы можете проверить ее целостность с помощью оператора CHECK TABLE в командной строке MySQL:

CHECK TABLE table_name;

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

REPAIR TABLE table_name;

Если восстановление данных прошло успешно, вы увидите такое сообщение:

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

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

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

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

sudo systemctl restart mysql

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

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

В раздел [mysqld] добавьте следующую строку:

Сохраните и закройте файл, а затем попробуйте перезапустить MySQL снова. Если вы можете получить доступ к поврежденной таблице, используйте утилиту mysqldump, чтобы выгрузить данные из таблицы в новый файл. Вы можете назвать этот файл как угодно (здесь мы назовем его out.sql):

mysqldump database_name table_name > out.sql

Затем удалите таблицу из базы данных. Чтобы избежать повторного открытия командной строки MySQL, вы можете использовать следующий синтаксис:

mysql -u user -p —execute=»DROP TABLE database_name.table_name»

После этого восстановите таблицу с помощью только что созданного дампа:

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

Источник

Как восстановить данные таблицы MySQL InnoDB из файлов ibdata и .frm

В этой статье объясняется, как восстанавливать таблицы MySQL при утрате всех или некоторых таблиц или когда MySQL не загружает данные таблицы.

Одна из причин этого – когда данные таблицы повреждены.

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

В этом сценарии в файле журнала MySQL содержались следующие сообщения:

Метод, описанный ниже, будет работать только для базы данных InnoDB.

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

Чтобы восстановить данные таблицы, убедитесь, что каталог данных и его содержимое не повреждены. В моем случае это было хорошо.

  • Ibdata1 – этот файл представляет собой системное табличное пространство InnoDB, которое содержит несколько таблиц InnoDB и связанных индексов.
  • * .frm – хранит информацию метаданных для всех таблиц MySQL. Эти файлы находятся внутри папки соответствующей базы данных MySQL. (например, внутри каталога «bugs»)
  • ib_logfile * – Все изменения данных записываются в эти файлы журналов. Это похоже на концепции архивных журналов, которые мы находим в других СУБД.

Скопировать файлы

Чтобы восстановить данные из вышеуказанных файлов, сначала остановите сервер MySQL.

Скопируйте файлы ibdata и папку схемы базы данных в другой каталог. Мы будем использовать это для восстановления нашей базы данных MySQL. В этом случае мы скопируем его в каталог /tmp. Имя схемы базы данных в этом примере – это ошибки.

Запустите сервер MySQL:

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

Восстановить данные

Затем восстановите данные таблицы, как описано ниже.

В файле конфигурации my.cnf установите значение следующего параметра в текущий размер файла ib_logfile0. В следующем примере я установил его на 48M, так как это размер, который мы видим для файла ib_logfile0, когда делали «ls -lh ib_logfile0»,

Обратите внимание, что размер файла ib_logfile0 и ib_logfile1 будет таким же.

Скопируйте предыдущие файлы ibdata в соответствующую позицию, в каталог данных mysql.

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

Наконец, перезапустите сервер MySQL.

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

Источник