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

В данном примере показано восстановление из заранее сделанного dump-файла (с помощью mysqldump). Если нужна инструкция по созданию резервной копии, читайте Как сделать дамп базы MySQL.

Подготовка базы

Подключаемся к командной оболочке mysql:

* данной командой мы подключимся к СУБД под пользователем root. Опция -p потребует ввода пароля.

Для восстановления базы сначала необходимо ее создать:

> CREATE DATABASE db DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;

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

> GRANT ALL PRIVILEGES ON db.* TO dbuser@localhost IDENTIFIED BY ‘password’ WITH GRANT OPTION;

Подробнее про создание баз читайте на странице Создание и удаление баз в MySQL/MariaDB.

Из файла через командную строку

Команда выполняется из UNIX-shell:

mysql -v -u root -p db SHOW VARIABLES LIKE ‘max_allowed_packet’;

Чтобы увеличить значение параметра, открываем конфигурационный файл my.cnf:

* в некоторых версиях СУБД конфиг может находится по пути /etc/my.cnf.d/server.cnf.

В разделе [mysqld] редактируем или добавляем:

[mysqld]
.
max_allowed_packet = 512M

* значение для данного параметра не обязательно должно быть таким большим.

systemctl restart mariadb || systemctl restart mysql

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

C:Program FilesMySQLMySQL Server 5.7

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

C:ProgramDataMySQLMySQL Server 5.7Data

Расположение данных файлов указано в меню 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:ProgramDataMySQLMySQL Server 5.7Data

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

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

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

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

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

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

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

Запустив после этого 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. Произойти это может по множеству причин, например, вследствие аварийного выключения базы данных из-за отключенного электричества в помещении, где находятся сервера, вследствие повреждений компьютерного железа, а также из-за новой версии CMS.

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

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

Довольно простым является восстановление, если таблицы находятся в формате MYISAM. Устройство phpMyAdmin дает возможность довольно быстро восстановить данные, если они были повреждены.

Более сложная ситуация в случае InnoDB. Хоть InnoDB и обладает такими преимуществами, как быстродействие, самостоятельное восстановление, а также устойчивость к сбоям электрической сети, для работы с ним вам потребуется немного больше знаний и усилий.

Правила восстановления MySQL

Для того, чтобы восстановить базу данных описанного выше формата, нужно воспользоваться опцией innodb_force_recovery, которую можно найти в файле настроек СУБД. Но перед тем, как ей воспользоваться, необходимо попытаться ввести команду SELECT … I NTO OUT FILE, которая дает возможность быстро и просто сохранить данные, не совершая лишней работы.

Если это не помогло, придется обратиться к помощи innodb_force_recovery. Найти его можно по следующим ссылкам (они разные для разных операционных систем):

• /etc/my.cnf;
• /etc/mysql/my.cnf – в случае Linux

Обратите внимание, что это местоположение может быть не совсем точным, так как оно верно только по умолчанию. Но даже если этот файл был перенесен в другое место, его поиск не займет у вас много времени.

В данной опции вы можете ввести сразу несколько значений. Заменив в стандартной опции innodb_force_recovery = 0 значение ноль на любое число от одного до шести, вы получите возможность восстановить, кроме самих таблиц, еще и незавершенные по каким-либо причинам процессы. Чтобы включить опцию, перезапустите сервер MySQL.

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

• должны иметься копии файлов данных;
• должны иметься файлы журналов InnoDB;
• должен иметься файл настроек базы данных – my.cnf;
• должны иметься файлы таблиц .frm InnoDB.

Все клиенты AdminVPS получают бесплатную настройку по системе “Всё включено”.

Использование innodb_force_recovery для восстановления базы данных MySQL

Главным методом использования данной опции при восстановлении является изменение значений от одного до шести по порядку.

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

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

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

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

Снижение вероятности повреждения MySQL

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

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