09.12.2024

Повний посібник з mysqldump – програми для резервного копіювання баз даних

mysqldump – це утиліта командного рядка, що надається MySQL і дозволяє створювати логічні резервні копії баз даних MySQL. Цей інструмент широко використовується для створення резервних копій баз даних, експорту баз даних на інші сервери та міграції баз даних між версіями MySQL або різними системами. Це простий, потужний і гнучкий спосіб резервного копіювання та відновлення баз даних, що робить його важливим інструментом для будь-якого адміністратора баз даних.

Цей посібник охоплює все, що вам потрібно знати про mysqldump, включаючи встановлення, синтаксис, приклади використання та найкращі практики.

Що таке mysqldump?

mysqldump створює логічну резервну копію бази даних MySQL, тобто виводить дані і структуру у вигляді команд SQL.
Ці SQL-команди можуть бути використані для відтворення бази даних, що робить mysqldump ідеальним інструментом для резервного копіювання, міграції та перенесення даних.
Він підтримує експорт цілих баз даних, окремих таблиць або конкретних даних з різними опціями для налаштування.

Базовий синтаксис mysqldump

Базовий синтаксис команди mysqldump наступний:

mysqldump [ОПЦІЇ] ім’я_бази_даних [таблиці] > файл_бекапа.sql

[OPTIONS]: Додаткові прапорці, які змінюють поведінку mysqldump (наприклад, вказівка користувача, пароля, хоста тощо).
назва_бази_даних: Назва бази даних, яку ви хочете створити резервну копію.
[таблиці](Необов’язково) Конкретні таблиці для резервного копіювання. Якщо не вказано, буде створено резервну копію всієї бази даних.
> backup_file.sql: Перенаправляє висновок до файлу (backup_file.sql), який міститиме команди SQL для відтворення бази даних.

Необхідні умови

MySQL або MariaDB, встановлені на сервері або клієнті.
Обліковий запис користувача з достатніми привілеями (правами SELECT і LOCK) на базі даних, що підлягає резервному копіюванню.
Доступ до командного рядка (терміналу) в Linux/macOS або командного рядка в Windows.

Параметри, які часто використовуються з mysqldump

-u або –user: Вказує ім’я користувача MySQL.
-p або –password: запитує пароль користувача.
-h або –host: Вказує хост (наприклад, локальний хост або IP-адресу).
-P або –port: Вказує номер порту для сервера MySQL.
–databases: Дозволяє створити резервну копію декількох баз даних.
–all-databases: Створює резервну копію всіх баз даних на сервері.
–no-data: Експортує лише структуру бази даних (без даних).
–single-transaction: Робить послідовний знімок бази даних, корисний для таблиць InnoDB.
–add-drop-table: Додає оператори DROP TABLE перед операторами CREATE TABLE, гарантуючи, що таблиці будуть видалені і створені при відновленні.
–routines: Включає збережені процедури і функції в резервну копію.
–тригери: Включає тригери до резервної копії.
–compress: Стискає дані, що передаються між клієнтом і сервером MySQL, якщо підтримується.

Основні приклади

Приклад 1: Резервне копіювання однієї бази даних

mysqldump -u root -p ім’я_бази_даних > backup_файл.sql

Запитує пароль користувача root.
Збирає вміст database_name у файл backup_file.sql.

Приклад 2: Резервне копіювання кількох баз даних

mysqldump -u root -p –databases database1 database2 > multiple_databases_backup.sql

Використовуйте опцію –databases, за якою слідують назви баз даних.
Зробить дамп баз даних database1 і database2 у файл multiple_databases_backup.sql.

Приклад 3: Створення резервної копії всіх баз даних

mysqldump -u root -p –all-databases > all_databases_backup.sql

Створює резервну копію всіх баз даних на сервері MySQL.
Містить інструкції CREATE DATABASE і USE, що дозволяє легко відновити всі бази даних.

Приклад 4: Створення резервної копії певної таблиці

mysqldump -u root -p ім’я_бази_даних ім’я_таблиці > table_backup.sql

Збирає структуру і дані певної таблиці (назва_таблиці) з бази_назва_бази даних у файл table_backup.sql.

Приклад 5: Створення резервної копії лише структури бази даних

mysqldump -u root -p –no-data назва_бази_даних > структура_бази_даних.sql

Параметр –no-data вилучає дані і експортує лише структури таблиць.

Розширені приклади

Приклад 6: Використання –single-transaction для великих баз даних

mysqldump -u root -p –single-transaction назва_бази_даних > large_backup.sql

Параметр –single-transaction ідеально підходить для таблиць InnoDB і забезпечує послідовне резервне копіювання без блокування таблиць.
Рекомендується для великих баз даних, оскільки мінімізує час блокування таблиць.

Приклад 7: Стиснене резервне копіювання

mysqldump -u root -p ім’я_бази_даних | gzip > backup_файл.sql.gz

Використовує gzip для стиснення виводу mysqldump на льоту.
В результаті створюється менший файл резервної копії (backup_file.sql.gz), що економить місце на диску.

Приклад 8: Включення збережених процедур, функцій і тригерів

mysqldump -u root -p –routines –triggers назва_бази даних > full_backup.sql

Опція –routines включає збережені процедури і функції.
Параметр –triggers включає тригери в резервній копії.

Приклад 9: Виключення певних таблиць

mysqldump -u root -p назва_бази_даних –ignore-table=назва_бази_даних.table_to_ignore > backup.sql

Параметр –ignore-table виключає певну таблицю з резервної копії.
Ви можете повторити цей параметр для кожної таблиці, яку ви хочете виключити.

Відновлення резервної копії mysqldump

Щоб відновити резервну копію, створену за допомогою mysqldump, скористайтеся командою mysql:

Приклад 10: Відновлення однієї бази даних

mysql -u root -p ім’я_бази_даних < файл_резервування.sql

Запитує пароль користувача root.
Відновлює базу даних з backup_file.sql.

Приклад 11: Відновлення декількох або всіх баз даних

mysql -u root -p < all_databases_backup.sql

За допомогою цієї команди можна відновити кілька баз даних або всі бази даних, якщо файл all_databases_backup.sql було створено з опціями –databases або –all-databases.

Приклад 12: Відновлення зі стисненої резервної копії

gunzip < backup_file.sql.gz | mysql -u root -p назва_бази_даних

Використовує gunzip для розпакування файлу резервної копії на льоту і передає результат команді mysql.

Найкращі практики використання mysqldump

Використовуйте –single-transaction для InnoDB:
- Ця опція забезпечує послідовне резервне копіювання без блокування таблиць бази даних, що особливо важливо для великих баз даних.
Заплануйте регулярне резервне копіювання:
- Використовуйте завдання cron у Linux для автоматизації процесу резервного копіювання. Наприклад, створіть щоденне завдання резервного копіювання:
  0 2 * * * mysqldump -u root -pYourPassword назва_бази_даних > /шлях/до/backup/ назва_бази_даних_$(дата \%F).sql
- Це завдання створює резервну копію щодня о 2 годині ночі.
Зберігайте резервні копії за межами сайту:
- Зберігайте резервні копії на іншому сервері або в хмарному сховищі, такому як AWS S3 або Google Drive, щоб забезпечити відновлення даних у разі відмови обладнання.
Перевіряйте резервні копії:
- Періодично перевіряйте, чи можна відновити резервні копії належним чином, тестуючи їх в окремому середовищі.
Використовуйте стиснення для великих баз даних:
- Використовуйте gzip або bzip2 для стиснення файлів резервних копій та економії місця на диску.
Міркування безпеки:
- Не вказуйте паролі безпосередньо в командному рядку, оскільки вони можуть бути видимими для інших користувачів. Замість цього використовуйте конфігураційний файл або підказку для введення пароля.
- Захистіть файли резервних копій відповідними правами доступу, щоб запобігти несанкціонованому доступу.

Висновок

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