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

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

Пожалуй, наиболее часто встречающейся проблемой, является нехватки места на сервере. Автор данной статьи столкнулся с такой проблемой, - после обновлений сайта на CMS Bitrix расположенном на VPS сервере, престали удаляться файлы устаревшего кэша и старые резервные копии сайта, создаваемые при автоматическом резервном копировании. В результате закончилось место, и сервер упал. После и удаления лишнего и освобождения места,  сервере был перезапущен, но mysql – сервер постоянно падал, выдавая ошибки, типа:

Mysql connect error [localhost]: (2002) Connection refused (400)

Опишем пошагово процесс восстановления базы данных, с примерами консольных команд с пояснениями.

В данном случае, будут приведены примеры восстановления InnoDB таблиц базы данных на Web окружении Bitrix (VMBitrix v7.x на Linux-платформе CentOS 7). Примерно такой же порядок восстановления баз данных и на других Linux серверах.

Анализ и диагностика проблем в работе mysql сервера и базы данных.

Проверка наличия свободного места на диске

Первым делом, для анализа и поиска проблемы, проверим наличие места на диске. Команда:

df -h

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

Смотрим логи mysql

Log - файл указывающий на ошибки в работе mysql расположен, /var/log/mysql/error.log.

В данном примере приведен фрагмент лог-файла,  где видно наличие проблем в таблице базы данных - b_stat_session_data.

2022-08-16T03:33:08.384347Z 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.7.38-41' socket: '/var/lib/mysqld/mysqld.sock' port: 3306 Percona Server (GPL), Release 41, Revision da46e5474f9
2022-08-16T03:33:08.491425Z 2 [ERROR] InnoDB: Corruption of an index tree: table `dbpechi-tula`.`b_stat_session_data` index `PRIMARY`, father ptr page no 409142, child page no 409143
PHYSICAL RECORD: n_fields 8; compact format; info bits 0
.......

Способы решения проблемы, восстановление базы данных

Если место на диске закончилось, отчистим его. В данном случае содержимое папок кэша Bitrix:

• /bitrix/cache
• /bitrix/managed_cache
• /upload/resize_cache

Так же, возможно backup - файлы резервных копий, которые так же могут занимать много места на диске.

• /bitrix/backup

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

После очистки места на диске попробуем перезапустить mysql,

sudo systemctl restart mysqld.service

Если mysql сервер запустился и работает корректно – вам повезло!

Если же mysql не запускается, переходим к следующему варианту: задействуем механизм восстановления InnoDB баз данных - это позволит исправить ошибки и восстановить работоспособность БД.

Процесс восстановления базы данных:

1. Запуск MySql сервера с опцией innodb_force_recovery;
2. Сохранение данных, создание дампа баз данных;
3. Удаление поврежденных баз данных;
4. Перезапуск MySql сервера в обычном режиме, без опции innodb_force_recovery;
5. Восстановление баз данных из полученного дампа.

Теперь каждый шаг рассмотрим подробней

Останавливаем работу mysql

service mysql stop

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

# mkdir /root/mysql_backup
# cp -r /var/lib/mysql/* /root/mysql_backup/

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

Следующим шагом запустим mysql в защитном режиме, для этого добавим в облсть [mysqld] конфигурационного файла my.cnf параметр:

[mysqld]
...
innodb_force_recovery = 1

Опция innodb_force_recovery, с параметром от 1 до 6 позволяет запустить mysql сервер даже при наличии проблем с целостностью баз данных, последовательно отключая некоторые операции приводящие к остановке mysql сервера.

С увеличением числового значение innodb_force_recovery от 1 до 6, MySQL становится менее чувствителен к целостности таблиц, сервер запускается и продолжает работать даже при наличии ошибок. К примеру, параметр innodb_force_recovery = 1, позволяет серверу работать, даже если он обнаруживает поврежденную таблицу, а при параметре = 4, дополнительно отключаются другие операции, и устанавливается таблицы InnoDB в режим только для чтения. Однако с увеличением числового значения параметра опции возникает и увеличивается риск потери данных.

Как правило, запуск mysql – сервера с параметром innodb_force_recovery = 4, позволяет запустить сервер в 99% случаев.

Продолжим, для запуска mysql в защитном режиме, добавим в конфигурационный файл my.cnf указанный параметр.

Расположение my.cnf – зависит от операционной системы, в веб-окружении Bitrix данный файл находится: /root/my.cnf.

Для в несения изменений воспользуемся встроенным редактором nano:

sudo nano /etc/my.cnf 

В область [mysqld]
Добавим innodb_force_recovery = 1

Для сохранения изменений и выхода из редактора – используем клавиши:
Ctrl+X, затем подтверждаем сохранение изменений: Y.

После сохранения изменений, запускаем mysql.

sudo systemctl start mysql

Проверяем статус mysql, команда:

service mysql status

Статус успешно запущенного, активного mysql сервера выглядит так:

В случае неудачи повторяем процесс, останавливаем mysql, заново редактируем my.cnf, изменяем значение innodb_force_recovery = 2, и пробуем запустить mysql сервер заново.

Так, последовательно изменяя значение innodb_force_recovery от 1 до 6, пробуем запустить mysql сервер. Как правило с innodb_force_recovery = 4, позволяет запустить mysql в 99% случаев.

Создание дампа базы данных

Следующим шагом, после успешного запуска mysql, будет создание дампа базы данных.

Для создания дампа базы данных, и работы с mysql сервером нам потребуются:

• Названия базы данных,
• Имя пользователя баз данных
• Пароль пользователя базы данных

В Bitrix эти данные указаны в файле /bitrix/php_interface/dbconn.php. Так же, можно использовать root пользователя mysql – сервера, пароль к которому указан в файле: /etc/my.cnf.

Возможно, будут полезны следующие команды:

Выведет список всех баз данных:

mysql -u root -p
mysql> SHOW DATABASES;

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

mysql -u root -p
mysql> SELECT User,Host FROM mysql.user;

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

mysqldump -uИМЯ_ПОЛЬЗОВАТЕЛЯ -pПАРОЛЬ НАЗВАНИЕ_ДБ > ФАЙЛ_ДАМПА.sql

Пример создания дампа базы данных с названием db_name для пользователя: name_user с паролем: 123password

mysqldump -uname_user -p123password db_name > /tmp/damp_file.sql

Созданный файл дампа базы данных damp_file.sql будет находится в директории: /tmp.

Пример создания дампа базы данных от root пользователя:

mysqldump -uroot -p db_name  > /tmp/ damp_file.sql 

После введения данной команды потребуется ввести пароль root - пользователя mysql сервера.

Возможно, если таблица имеет большие размеры, целесообразней сжать дамп, создать в виде архива:

mysqldump -uroot -p db_name | gzip > /tmp/damp_file.sql.gz

Пример создания дампа одной таблицы базы данных:

mysqldump -uname_user -p123password db_name db_name_table > /tmp/damp_table_file.sql

Пример создания дампа всех баз данных сразу:

mysqldump -uroot -p --all-databases > /tmp/all_db.sql

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

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

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

mysql -u root -p;
mysql > DROP DATABASE  db_name;

Или одну таблицу базы данных:

mysql -u root -p;
mysql >  USE db_name;
mysql > DROP TABLE table_name; 

Далее необходимо удалить из

/etc/my.cnf

innodb_force_recovery = 1

и перезапускаем mysql:

sudo systemctl restart mysqld.service

И после успешного запуска mysql, восстанавливаем базы данных из дампа:

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

mysql -u root -p db_name  <  /tmp/damp_file.sql

Как Вы можете видеть, мы восстанавливаем базу данных из файла, дампа БД находящегося в /tmp/damp_file.sql.

Таким образом, мы восстановим потерянные данные и работу сервера.