Восстановления базы из дампа

Через phpMyAdmin

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

  1. Открыть phpMyAdmin и выбрать требуемую БД.
  2. Затем перейти по ссылке «Импорт» – она находится в главном меню.
    phpmyadmin импорт базы данных
  3. В ней нужно найти и открыть раздел «Импортируемый файл», где нужно указать источник бэкапа базы.
  4. Подтвердить операцию нажатием «Вперед».
  5. Теперь остается только перезагрузить сервер MySQL.

Как увеличить объем импортируемых баз данных

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

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

Во втором случае (в файлах php.ini /.htaccess) потребуется увеличить значения по умолчанию ряда опций, влияющих на загрузку:

  • upload_max_filesize («максимальный размер загружаемого файла»). Первоначальное значение: «2Mб».
  • post_max_size («максимальный размер POST-запросов»). Значение параметра должно быть больше, чем у «upload_max_filesize».
  • max_execution_time («время исполнения скрипта»). Чтобы снять ограничения с параметра, ему нужно присвоить значение «0».
  • max_input_time («время обработки входящих запросов»).

Способы увеличения лимитов на исполнение php-скриптов

  1. В настройках конфигурационного файла phpMyAdmin (config.inc.php). В файл нужно добавить строки:
$ cfg['UploadDir'] = './upload';

После чего добавить туда же переменную, снимающую ограничения со времени исполнения скриптов (после загрузки базы данных ее лучше убрать):

$ cfg['ExecTimeLimit'] = 0;
  1. В пользовательском файле сайта (php.ini), где хранятся настройки исполнения php-скриптов. Файл «php.ini» можно найти, если открыть в браузере ранее добавленный (в корень сайта) php-файл. Например, ввести запрос вида «https://mysitename.com/myphpinfo.php», где «mysitename.com» — имя сайта, а «myphpinfo.php» — название php-файла. В открывшемся окне нужно найти параметры «Loaded Configuration File» или «Configuration File (php.ini) Path», где и будет указан путь к «php.ini».

Добавляем в конце файла строки:

post_max_size = 500M
upload_max_filesize = 400M
max_execution_time = 3000
max_input_time = 6000
  1. В конфигурационном файле сервера (.htaccess), отвечающем, в том числе, за настройку обработки файлов на определенном сайте. Чтобы изменения сработали для всех файлов сайта, «.htaccess» должен обязательно находится в его корневой папке.

Добавляем в файл строки:

php_value post_max_size 500M
php_value upload_max_filesize 400M
php_value max_execution_time 3000
php_value max_input_time 6000

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

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

Пример:

mysql> CREATE DATABASE customers_db;

2. Далее следует загрузить файл дампа SQL с помощью команды «mysql»:

$ mysql -u [username] -p[password] [db_to_restore] <  /(Путь к файлу)/ [backupfile.sql]

Пример:

$ mysql -u root -pSeCrEt customers_db < /(Путь к файлу)/customers_db_backup.sql

3. В случае, когда дамп был сделан до того, поможет следующая команда:

$ gunzip < [backupfile.sql.gz] | mysql -u [username] -p[password] [dbname]

Пример:

$ gunzip < customers_db_backup.sql.gz | mysql -u root -pSeCrEt customers_db

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

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

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

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

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

Другой популярный формат таблиц — InnoDB. Он обладает более высоким быстродействием, снабжен функцией автоматического восстановления и устойчив к сбоям. Однако, для его ручного ремонта потребуется приложить некоторые усилия.

Общий принцип восстановления

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

Перед тем, как ее запустить, можно попытаться получить результат при помощи команды select … into out file. В большинстве случаев она дает возможность сохранить информацию, не прибегая к дополнительным операциям.

Однако, если select … into out file не сработала (например, помешали незаконченные процессы), чтобы восстановить базу из файлов .frm, остается прибегнуть к расширенному параметру innodb_force_recovery.

1. Сначала нужно прописать в конфигурационном файле MySQL опцию innodb_force_recovery.

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

В ОС Windows:

  1. %WINDIR%\my.ini (%WINDIR%\my.cnf)
  2. C:\my.ini (C:\my.cnf)
  3. BASEDIR\my.ini (BASEDIR\my.cnf)

«WINDIR» — папка установки Windows. Обычно, путь к ней выглядит так: C:\WINDOWS.
«BASEDIR» — папка, где установлена база MySQL. Например, для MySQL 8.0 полный путь к ней выглядит так: C:\PROGRAMDIR\MySQL\MySQL 8.0 Server (где «PROGRAMDIR» — папка c программами Windows, обычно это Program Files).

В дистрибутивах Linux:

  1. /etc/my.cnf
  2. /etc/mysql/my.cnf
  3. SYSCONFDIR/my.cnf

«SYSCONFDIR» — подкаталог, который использовался для компилирования MySQL (по умолчанию — etc).

В файле предусмотрена возможность установить для innodb_force_recovery несколько параметров. По умолчанию опция будет иметь вид «innodb_force_recovery = 0». Если установить другие числа (от 1 до 3) можно не только восстановить данные таблицы MySQL, но и процессы, незавершенные из-за аварийной остановки.

2. После открытия my.cnf или my.ini необходимо найти в нем блок [mysqld], куда и нужно прописать innodb_force_recovery. Пример:

[mysqld] 
innodb_force_recovery = 1

Чтобы применить данный параметр, следует перезапустить сервер MySQL.

Восстановить структуру таблицы MySQL подобным образом можно только в случае, если имеются сохраненные копии файлов данных, журнала InnoDB и таблиц .frm InnoDB, а также конфигурационного файла my.cnf или my.ini.

Замена значений параметра

Главное, чего нужно придерживаться во время работы с innodb_force_recovery – последовательно менять значения параметров от 1 до 3 и перезапускать после этого сервер.

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

  • 1 (SRV_FORCE_IGNORE_CORRUPT) — позволит серверу запуститься даже в случае обнаружения поврежденной таблицы.
  • 2 (SRV_FORCE_NO_BACKGROUND) — предотвращает запуск основного процесса и других процессов очистки. Иными словами, если случится сбой во время операции очистки фоновых процессов он будет предотвращен благодаря этому значению.
  • 3 (SRV_FORCE_NO_TRX_UNDO) — не происходит откат транзакций по завершению восстановления после сбоя.
  • 4 (SRV_FORCE_NO_IBUF_MERGE) — предотвращает операции объединения вставленных данных из буфера, при этом не собираются данные статистики. Устанавливается режим InnoDB «только для чтения».
  • 5 (SRV_FORCE_NO_UNDO_LOG_SCAN) — не учитываются логи отмены (undo logs) при запуске базы данных. InnoDB учитывает даже незавершенные операции как выполненные. Устанавливается режим InnoDB «только для чтения».
  • 6 (SRV_FORCE_NO_LOG_REDO) — отключение ведения логов отката изменений (redo logs) в связи с восстановлением. Оставляет таблицы в неактуальном состоянии, что в свою очередь может внести больше повреждений в Б-деревья и другие структуры базы данных. Устанавливается режим InnoDB «только для чтения».

Пользоваться значениями 4, 5 и 6 не стоит тем, кто не имеет достаточного опыта работы с MySQL таблицами. С их применением в несколько раз возрастает вероятность полной потери информации.

Восстановление пароля

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

Использование init-file

Во время запуска MySQL есть возможность сообщить сервису о файле, в котором находятся исполняемые команды SQL. Его адрес следует указать с помощью параметра «init-file».

1. В первую очередь необходимо создать файл «init-file»:

vi /home/user/init-file.txt

2. Далее нужно добавить в файл следующую строку:

UPDATE mysql.user SET password=password('новый пароль') WHERE user='root';

3. Далее следует отключить сервис, если он работает:

sudo systemctl stop mysql

4. Затем можно запустить свой файл:

sudo mysqld --user=mysql --init-file=/home/sergiy/init-file.txt --console

5. Остается подождать немного, пока все будет работать, как надо, и далее остановить данный процесс. В терминале будет отображен вывод «started as proccess» и PID (номер-идентификатор) процесса. Последний как раз и нужно выключить. К примеру*:

sudo kill -TERM 1111

* Значение PID приведено для примера. Следует заменить его на актуальное.

6. Теперь можно запустить MySQL стандартным способом и попробовать авторизоваться с помощью нового пароля:

sudo systemctl start mysql
mysql -u root -p

Использование skip-grant-tables

Помимо — init-file можно выполнить сброс пароля с использованием другого параметра —skip-grant-tables. Если запустить с ним сервис, будет пропущена загрузка данных пользователей, что позволяет войти без необходимости вводить логин и пароль.

1. Здесь также сначала требуется отключить базу данных:

udo systemctl stop mysqls

2. Дальше нужно запустить вручную MySQL следующей командой:

sudo mysqld --user=mysql --skip-grant-tables

3. Теперь можно открыть консоль для работы с MySQL:

mysql -u root

4. Поскольку загрузка была осуществлена без привилегий пользователей, таблицы с ними теперь нужно подгрузить:

FLUSH PRIVILEGES;

5. На этой стадии можно менять пароль пользователя root:

UPDATE mysql.user SET password=password('новый пароль') WHERE user='root';

6. Можно закрывать консоль управления:

Exit (quit)

7. Остается выключить сервис*, как и в приведенном выше способе:

sudo kill -TERM 1111

* Значение PID приведено для примера. Следует заменить его на актуальное.

8. И, наконец, запустить MySQL в стандартном режиме работы:

sudo systemctl start mysql

9. После этого появится возможность авторизации с помощью нового пароля:

sudo mysql -u root -p