Стаття також доступна російською (перейти до перегляду).

Вступ

• Основні причини пошкодження таблиць з даними у СУБД MySQL
• Огляд існуючих підсистем зберігання даних
• Відновлення таблиць типу MyISAM
• Відновлення таблиць типу InnoDB

Пошкодження таблиць бази даних MySQL є досить поширеною проблемою, яку системні Адміністратори повині вміти оперативно усувати. Найчастіше такі пошкодження пов'язані із умовами експлуатації баз даних або ж некоректними діями користувачів. Існує декілька алгоритмів пошуку помилок та відновлення таблиць в залежності від типу механізму збереження даних, застосованого для певної таблиці. З'ясуємо причини помилок та розглянемо використання алгоритмів відновлення таблиць на конкретних прикладах.

Основні причини пошкодження таблиць з даними у СУБД MySQL

Незважаючи на постійний розвиток та вдосконалення методів впорядкування та збереження даних, вказана СУБД все одно не може забезпечити достатню надійність та відмово стійкість баз даних, побудованих на її основі. Це пов'язано не тільки із недосконалістю алгоритмів роботи та технологій, а й із цілком об'єктивними чинниками, такими, наприклад, як умови експлуатації бази. Перерахуємо найбільш розповсюджені причини:

• Вихід з ладу апаратного забезпечення комп'ютера, що призводить до аварійної зупинки серверу MySQL;
• Виникаючі збої у мережі енергопостачання або вихід з ладу автономного джерела живлення;
• Наявність помилки у коді MySQL;
• Зупинка серверу під час запису інформації;
• Зміна зовнішньою програмою параметрів таблиці під час її обслуговування сервером MySQL.

Усі вказані причини врешті решт призводять до пошкодження структури таблиць реляційної бази даних, що вимагає запуску процесу її відновлення. В залежності від типу підсистеми низького рівня, котра реалізує механізм зберігання даних, процес відновлення може бути запущений внутрішньо самою СУБД, або ж ззовні за допомогою сторонніх програм.

Огляд існуючих підсистем зберігання даних

До версії MySQL 5.5 включно, за замовчуванням використовувалась підсистема типу MyISAM, котра є вдосконаленим варіантом механізму ISAM (Indexed Sequential Access Method), що реалізує індексно-послідовний метод доступу до даних для забезпечення високої швидкості їх вибірки. І це дійсно підтверджується на практиці, наприклад, при застосуванні оператора SELECT у запитах. Разом з тим, виграш у швидкості тут «компенсується» низьким рівнем відмово стійкості та надійності, що пов'язані із специфікою організації збереження табличних даних і, відповідно, відсутністю підтримки механізмів транзакцій та зовнішніх ключів. У зв'язку з цим, вказана підсистема не відповідає вимогам стандарту ACID (atomicity, consistency, isolation, durability), щодо надійності роботи транзакційних систем і тому не може бути рекомендованою для використання у високонавантажених проектах, у яких обсяг даних становить більш ніж 2 Гб та використовуються складні запити, пов'язані із модифікацією даних. MyISAM відноситься до підсистем, котрі не дають змогу СУБД в автоматичному режимі ініціювати запуск процесу відновлення таблиць у випадку їх пошкодження. І тому, «вилікувати» таблицю можливо лише зовнішніми утилітами, запуск яких повинен бути ініційований користувачем.

Починаючи з версії MySQL 8.0, СУБД за замовчуванням використовує іншу, більш досконалу підсистему зберігання даних - InnoDB, у котрій дані всіх таблиць зберігаються у загальному файлі, що дозволяє забезпечити підтримку транзакцій та зовнішніх ключів та, відповідно, гарантувати певний рівень відмово стійкості та надійності роботи. Проекти MySQL, створені на базі підсистеми InnoDB здатні ініціювати самозапуск процесу відновлення пошкоджених таблиць і, відповідно, в автоматичному режимі виправляти помилки та відновлювати таблиці без втручання користувача.

Відновлення таблиць типу MyISAM

Після того, як з'ясувалося, що доступ до даних став неконтрольовано обмеженим, необхідно виконати наступні дії:

• Зупинити службу MySQL;
• Скопіювати всі дані у каталог резервного копіювання;
• Ініціювати процеси перевірки таблиць та їх відновлення.

Зупинку MySQL-серверу можна виконати за допомогою наступної команди:

$ systemctl stop mysql

Звісно, користувач при цьому повинен мати адміністраторські права, або ж скористатися командою sudo.

Зробимо резервну копію наших даних. У системах Ubuntu за замовчуванням всі дані зберігаються у каталозі /var/lib/mysql/ . Виходячи з цього, введемо у терміналі:

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

Для з'ясування того, яка з таблиць пошкоджена виконаємо їх перевірку за допомогою утиліти CHECK TABLE. Для цього знову необхідно запустити сервер MySQL та увійти у командний режим роботи з сервером. Після цього оберемо базу даних, котру ми використовуємо та переглянемо назви таблиць, котрі до неї входять:

mysql> use motoinfc_kiev;

mysql> show tables;

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

mysql> check table control_ip;

У даному випадку помилок не виявлено. Таким же чином ми можемо перевірити будь-яку іншу таблицю із нашої бази. У випадку, якщо б таблиця була пошкоджена, то у результатах перевірки було б виведене відповідне повідомлення. І тоді, для того, щоб усунути пошкодження ми можемо скористатися утилітою REPAIR TABLE, котра у більшості випадків успішно справляється із своєю задачею:

mysql> repair table control_ip;

На виході також отримуємо звіт, де вказана застосована до таблиці операція, та результати її виконання. Тобто, у даному випадку все ОК.

Відновлення таблиць типу InnoDB

У випадку, якщо у таблицях використовується підсистема типу InnoDB, то, як вже зазначалося, сама СУБД буде ініціювати процес відновлення і в автоматичному режимі виправляти помилки. Достатньо лише перезавантажити MySQL сервер. Однак, коли Адміністратору все ж таки необхідно переналаштувати пошкоджену таблицю, в документації MySQL пропонується застосувати метод «Дамп та перезавантаження».

Алгоритм дій у цьому випадку повинен бути наступним:

• Поновлення доступу до пошкодженої таблиці;
• Створення логічної резервної копії таблиці зі збереженням структури і даних;
• Видалення пошкодженої таблиці;
• Завантаження до бази відновленої таблиці з даними із збереженої копії.

Для можливості забезпечення доступу, можна спробувати перезавантажити службу MySQL, щоб отримати доступ до серверу:

$ systemctl restart mysql

Якщо це не допомогло і сервер, як і раніш залишається недоступним, можна спробувати включити параметр innodb_force_recovery, у конфігураційному файлі mysqld.cnf. Відредагуємо вказаний файл. Для цього введемо у терміналі наступну команду:

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

Внесемо у файл відповідні зміни, як показано нижче. Збережемо зміни (ctrl+O) та вийдемо з редактору (ctrl+X).

Після цього знову можна спробувати перезавантажити службу MySQL і у випадку, якщо доступ до серверу і, відповідно, до пошкодженої таблиці з'явиться, створити резервну копію або дамп таблиці за допомогою утиліти mysqldump. Для цього введемо у терміналі:

$ mysqldump -u alexander -p motoinfc_kiev control_ip > dumpcontrolip.sql

Роз'яснити призначення параметрів даного формату команди mysqldump:

• u – вказівка на ім'я користувача бази даних;
• p – вказівка на пароль доступу до бази даних, котрий можна вказати одразу після параметру або ж ввести потім по запиту системи;
• alexander – логін користувача бази даних;
• motoinfc_kiev – назва бази даних;
• control_ip – назва таблиці, копія котрої нам потрібна;
• dumpcontrolip.sql – файл для збереження структури та даних таблиці control_ip або дамп.

Після підтвердження набраної команди, система виведе запит на введення паролю доступу до бази, як показано нижче.

Після введення паролю дамп буде створений у поточному каталозі. Щоб переконатися в цьому, виведемо вміст файлу дампа у терміналі:

$ cat dumpcontrolip.sql

Кінцевий екран виводу команди буде виглядати наступним чином:

Можна переконатися, що дамп був успішно створений і готовий для завантаження до бази.

Розгортання таблиці на сервері буде відбуватися наступним чином. Спочатку за допомогою команди DROP TABLE IF EXISTS control_ip, котру містить у собі файл дампа, буде видалена пошкоджена таблиця control_ip. Навіть, якщо таблиця буде видалена раніше, помилка не буде виведена завдяки наявності параметру IF EXISTS у вказаній команді. На другому етапі за допомогою команди CREATE TABLE буде створена нова структура таблиці, що у свою чергу виключить можливість повторення помилок. І вже на третьому етапі за допомогою команди INSERT INTO будуть завантажені дані, котрі зберігалися у пошкодженій таблиці.

Тепер завантажимо дамп до бази motoinfc_kiev. Для цього введемо наступну команду:

$ mysql -u alexander -p motoinfc_kiev < dumpcontrolip.sql

Після введення паролю, команда буде успішно виконана.

Таким же чином ми можемо відновити будь-яку пошкоджену таблицю типу InnoDB.

Пропонуємо Вам послуги ВПС хостингу в Україні та Польщі. До всіх тарифних планів включено базову підтримку. Тому такі проблеми, як допомога з відновлення таблиць баз даних, технічна підтримка FREEhost.UA бере на себе.

Підписуйтесь на наш телеграм–канал https://t.me/freehostua, щоб бути в курсі нових корисних матеріалів.

Дивіться наш канал Youtube на https://www.youtube.com/freehostua.

Ми у чомусь помилилися, чи щось пропустили?

Напишіть Про це у коментарях, ми з задоволенням відповімо та обговорюємо Ваші зауваження та пропозиції.

Дата: 21.11.2022 Автор: Олександр #mysql

Голосування 1 2 3 4 5