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

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

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

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

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

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

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

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

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

• 
• 

• 
• 

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

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

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

Получить курс сейчас!

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

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

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

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

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

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

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

• Как восстановить базу данных сайта из бэкапа в phpMyAdmin

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

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

Запускаем Denwer.

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

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

• 
• 

• 
• 

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

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

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

Получить курс сейчас!

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

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

Код примеров использования обеих команд:

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

• SQL - Восстановление в базу с другим именем - Для сисадминов и не только

uroot – это имя пользователя. Оно указывается сразу после параметра u (без пробела). Кроме этого синтаксис команд включает еще несколько параметров. Среди них: пароль, адрес удаленного хоста. С помощью команды mysqldump можно экспортировать БД в различные форматы, сжимать данные. Также это проверенный способ, как можно восстановить таблицу MySQL.

Утилита MySQLdump (с помощью одноименной команды) позволяет экспортировать базы в простом текстовом формате (.txt). А так как текст обладает высокой степенью сжатия, то это эффективный способ уменьшения объемов бэкапов, в ситуации, когда наблюдается «дефицит» серверного пространства.

Что можно сделать еще

Кроме рассмотренных двух основных методов для восстановления данных MySQL можно использовать еще несколько консольных утилит:

MySQL-консоль

Mysqlbinlog – для формирования бэкапов программа импортирует данные из серверных журналов бинарных логов. В них записываются все пользовательские и системные запросы (в бинарном коде), после выполнения которых были изменены данные баз.

Также для популярных CMS разработано множество специализированных плагинов. Например, для WordPress:

Keep Backup Daily.

UpdraftPlus Backup and Restoration.

Perfect Dashboard.

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

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

• 
• 

• 
• 

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

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

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

Получить курс сейчас!

Хотите изучить MySQL?

Посмотрите курс по базе данных MySQL!

Смотреть

Защита от спама на WordPress

20 полезных плагинов календаря для WordPress

—>

Метки:
MySQL

Похожие статьи:

Комментарии Вконтакте:

Комментарии Facebook:

https://webformyself.com/vosstanovlenie-mysql-baz-dannyx-ruchnymi-i-mexanicheskimi-sposobami/
—>

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

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

Бекап и восстановление баз данных MySQL

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

  SELECT table_schema "Data Base Name",  sum( data_length + index_length )/1024/1024 "Data Base Size in MB"  FROM information_schema.TABLES  GROUP BY table_schema;  

результом будет следующая таблица:

  +--------------------+----------------------+  | Data Base Name     | Data Base Size in MB |  +--------------------+----------------------+  | db_1               |         106.53884888 |  | db_10              |          30.42764664 |  | db_2               |        3608.50179482 |  | db_20              |          75.88463402 |  | db_3               |        2505.82299519 |  | db_4               |         914.69011402 |  | db_7               |        1871.93762398 |  | information_schema |           0.00878906 |  | mysql              |           0.66736794 |  | performance_schema |           0.00000000 |  | roundcube          |           0.42187500 |  +--------------------+----------------------+  

При создани бекапа, размер файла не будет совпадать с указанным в таблице. Но позволит хоть примерно опеределить занимаемое место на диске. Аналогичным способом можно подсчитать размер таблиц в базе данных. Меняем в запросе databasename
на имя вашей базы данных и получаем:

  SELECT TABLE_NAME,  table_rows,  data_length,  index_length,  round(((data_length + index_length) / 1024 / 1024),2) "Size in MB"  FROM information_schema.TABLES  WHERE table_schema = "databasename";  

  +-----------------------------------+------------+-------------+--------------+------------+  | TABLE_NAME                        | table_rows | data_length | index_length | Size in MB |  +-----------------------------------+------------+-------------+--------------+------------+  | wp_commentmeta                    |       1003 |     3319680 |        84992 |       3.25 |  | wp_comments                       |        934 |      746276 |        93184 |       0.80 |  | wp_gdsr_data_article              |         39 |        3240 |         2048 |       0.01 |  | wp_gdsr_data_category             |          0 |           0 |         1024 |       0.00 |  | wp_gdsr_data_comment              |        497 |       22204 |         7168 |       0.03 |  | wp_gdsr_ips                       |          0 |           0 |         1024 |       0.00 |  | wp_gdsr_moderate                  |          0 |           0 |         1024 |       0.00 |  | wp_gdsr_multis                    |          0 |           0 |         1024 |       0.00 |  | wp_gdsr_multis_data               |          0 |           0 |         1024 |       0.00 |  | wp_gdsr_multis_trend              |          0 |           0 |         1024 |       0.00 |  | wp_gdsr_multis_values             |          0 |           0 |         1024 |       0.00 |  | wp_gdsr_templates                 |         48 |       19224 |         3072 |       0.02 |  | wp_gdsr_votes_log                 |        478 |       24356 |        34816 |       0.06 |  | wp_gdsr_votes_trend               |        464 |       16708 |        14336 |       0.03 |  | wp_links                          |          0 |           0 |         1024 |       0.00 |  | wp_options                        |        349 |      895084 |        25600 |       0.88 |  | wp_postmeta                       |        662 |      135948 |        43008 |       0.17 |  | wp_posts                          |        471 |     1946908 |        55296 |       1.91 |  | wp_subscribe_reloaded_subscribers |        134 |        9488 |        11264 |       0.02 |  | wp_term_relationships             |        177 |        3717 |        11264 |       0.01 |  | wp_term_taxonomy                  |        119 |        4648 |         9216 |       0.01 |  | wp_terms                          |        119 |        3492 |        15360 |       0.02 |  | wp_usermeta                       |         34 |       46644 |        10240 |       0.05 |  | wp_users                          |          1 |         132 |         6144 |       0.01 |  +-----------------------------------+------------+-------------+--------------+------------+  24 rows in set (0.20 sec)  

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

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

  mysqldump -hlocalhost -uusername -p database_name > database_name.sql  mysqldump -hlocalhost -uusername -p database_name | gzip -9 > database_name.sql.gz  

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

Восстанавливаем базу данных database_name
из файл дампа database_name.sql
или её архива:

  mysql -hlocalhost -uusername -p database_name < database_name.sql    unzip -p database_name.sql.zip | mysql -uusername -p database_name  gunzip < database_name.sql.gz | mysql -uusername -p database_name  

Опции команд mysql и mysqldump очень просты в данном случае. Прописывать опцию и её значение можно как слитно, так и раздельно (кроме опции указания пароля «-p»). Часто используемые опции и некоторые полезные в особых случаях:

Не возникает каких-либо лишних вопросов, пока размер баз данных мал. С большими базами, может показаться, что процесс бекапа завис. В подобном случае, к нам на помощь придет утилита PV. С её помощью можно видеть процесс в удобом виде. Пример с восстановленим базы и pv:

  pv database_name.sql | mysql database_name  96.8MB 0:00:17 [5.51MB/s] [==>                             ] 11% ETA 0:02:10    unzip -p database_name.sql.zip | pv | mysql -uusername -p database_name  gunzip < database_name.sql.gz | pv | mysql -uusername -p database_name  

Аналогично с созданием дампа, PV и сжатием. Менее информативно, но все же лучше, чем ничего не видеть о процессе:

  mysqldump database_name | pv > database_name.sql.gz   239MB 0:00:03 [ 111MB/s] [                                              ]    mysqldump database_name | pv | gzip -9 -c > database_name.sql.gz  

Обязательно проверяйте целостность созданого дампа на тестовой базе данных! Если Вы преносите базу данных с одного сервера на другой, где различаются настройки кодировки соединения с базой данных, то бекап может получиться с «кривыми» данными в виде невнятных символов. В таких случая, при создании дампа, нужно указывать опции default-character-set
и skip-set-charset
. Пример:

  mysqldump -udatabase_name -p --default-character-set=latin1 --skip-set-charset database_name > database_name.sql  

где latin1 — кодировка установленная на сервере MySQL.

При создании дампа базы, процесс может завершить с некоторыми ошибками:

  ERROR 2013 (HY000) at line 222: Lost connection to MySQL server during query    # или  ERROR 2006 (HY000) at line 450: MySQL server has gone away    # или такие  MySQL server has gone away (error 2006)  

Избавить от них, можно увеличением значений переменных wait_timeout
и max_allowed_packet
в my.cnf и последующим перезапуском MySQL сервера:

  wait_timeout = 28800  max_allowed_packet = 64M  

К сожалению, имея дело с очень большими базами данных в гигабайты и десятки гигабайт, подобный процесс может длиться большое количество времени и не факт, что успешно. Здесь стоит использовать специальные утилиты mysqlhotcopy
для MyISAM, xtrabackup
для InnoDB от создателей Percona MySQL Server. А лучше всего иметь MySQL Slave сервер реплики и производит все бекапы на нем.