Як зробити дамп MySQL

Як зберегти базу даних MySQL

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

MySQL надає:

• Логічне резервне копіювання, що виконується за допомогою .sql-файлу та інструкцій створення/вставки. Пізніше ви можете відновити базу даних з цього файлу. Цей тип резервного копіювання вважається повільнішим, ніж інші, і підходить для баз даних середнього розміру.
• Фізичне резервне копіювання виконується шляхом копіювання файлів в оригінальному форматі. Недоліком цього типу резервного копіювання є те, що базу даних можна відновити тільки на сервері MySQL.
• Консистентні резервні копії, що виконуються за допомогою автоматизованого регулярного резервного копіювання файлів.

Також можна створити повну резервну копію (копію всієї бази даних), інкрементну резервну копію (повторювану копію змін, внесених до бази даних) і диференціальну резервну копію (копію змін, внесених з останньої резервної копії).

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

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

Майте на увазі, що mysqldump не може створювати дамп певних таблиць, зокрема тимчасових таблиць, системних таблиць, таблиць схеми продуктивності (з бази даних Performance Schema) і таблиць інформаційної схеми (з бази даних Information Schema). Втім, команда пропонує можливості налаштування: ви можете вказати, щоб певні типи таблиць або даних не потрапляли до дампа за допомогою опції --ignore-table, або скористатися опцією --no-data, щоб зберегти структуру таблиць, але позбутися даних.

Які завдання виконує утиліта Mysqldump?

Утиліта Mysqldump полегшує:

• Резервне копіювання баз даних;
• Міграцію бази даних;
• Версіонування бази даних (ви можете створювати різні версії вашої бази даних, до яких ви можете повернутися пізніше);
• Реплікацію середовища (ця команда може допомогти реплікувати робочі дані для створення стабільного середовища для розробки або тестування);

​Структура експортованих даних

Експортовані дані будуть складатися з:

• Стислих файлів у форматах gz (gzip) або .bz2 (bzip2);
• Операторів SQL у вигляді SQL-скрипту, що використовується для відтворення структури та даних бази даних;
• XML-формату даних для полегшення інтеграції даних з іншими системами;
• Текстових файлів, розділених табуляцією, що використовуються для імпорту та експорту даних;
• Значення, розділеного комами, або файлів CSV, що використовуються для обміну даними;
• Користувацьких форматів, визначених користувачем, що використовуються для налаштування формату виводу;

Привілеї для використання mysqldump

Щоб почати використовувати команду mysqldump, потрібно мати як мінімум повні привілеї на читання. Для використання розширеної функціональності потрібні наступні привілеї:

• SHOW VIEW (для доступу до визначень подання);
• SELECT (для читання даних);
• LOCK TABLES (для блокування таблиць);
• RELOAD (для використання опції --flush-privileges);

Приклади дампа MySQL

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

Створення резервних копій окремих таблиць MySQL

Щоб створити резервну копію певних таблиць з бази даних MySQL, виконайте наступну команду:

mysqldump -u my_user -p database_name table1 table2 > tables.sql

Тут:

• my_user - ваше ім'я користувача MySQL;
• -p- пароль користувача MySQL;
• database_name - ім'я бази даних, що містить необхідні таблиці;
• table1 table2 - імена таблиць, з яких потрібно створити резервну копію;

Якщо ви хочете створити резервну копію більшої кількості таблиць, ви можете використовувати той самий принцип і додати імена баз даних, які ви хочете додатково створити резервну копію.

Після завершення файл "tables.sql", створений mysqldump, міститиме SQL-команди, необхідні для відтворення таблиць і даних у них.

Створення резервної копії однієї бази даних

Якщо ви хочете створити резервну копію однієї бази даних, скористайтеся наведеною нижче командою:

mysqldump -u my_user -p database_name > backup.sql

Тут:

• my_user - ім'я користувача MySQL;
• -p- пароль користувача MySQL;
• database_name - ім'я бази даних, яку ви хочете створити резервну копію;

Після запуску команди файл "backup.sql " міститиме дані, необхідні для відновлення бази даних у початковому вигляді.

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

Щоб створити резервну копію декількох баз даних, вам потрібно додати прапорець --databases до команди mysqldump, наприклад, так:

mysqldump -u my_user -p --databases db1 db2 db3 > backup.sql

Ось тут:

• my_user - ваше ім'я користувача MySQL;
• -p- пароль користувача MySQL;
• db1 db2 db3 - назви баз даних, з яких ви хочете створити резервну копію;

Резервне копіювання всіх баз даних з сервера MySQL

Якщо ви хочете створити резервну копію всіх баз даних з сервера MySQL, вам потрібно додати прапорець --all-databases при використанні команди mysqldump:

mysqldump -u my_user -p --all-databases > all_databases.sql

Кінцевий файл all_databases.sql міститиме необхідні SQL-команди для відтворення резервних копій баз даних.

Як відновити базу даних на віддаленому сервері

Щоб реплікувати резервні копії баз даних на новий сервер, потрібно спочатку переконатися, що на цьому сервері є порожня база даних або її контур.

Для цього можна перевірити, чи є на сервері база даних, або створити її, якщо її немає:

mysql -u root -pYOUR_PASSWORD -e "CREATE DATABASE destination_db

Ця команда створить базу даних на новому сервері.

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

mysql -u root -pYOUR_PASSWORD destination_db < single_table_dump.sql

Заключення

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

FAQ

Який вплив має прапорець --single-transaction в mysqldump?

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

Які методи можна використовувати для ефективного створення дампа великих таблиць?

Ви можете комбінувати --single-transaction і -quick для швидшого створення дампа великих баз даних.

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

Як можна ігнорувати таблиці під час процесу mysqldump?

Ви можете ігнорувати певні таблиці за допомогою опції --ignore-table. Це може виглядати приблизно так:

mysqldump -u root -pmypassword my_db --ignore-table=my_db.table_to_ignore > my_db.sql

Таким чином, таблиці, які ви вкажете ігнорувати, будуть сповіщені за допомогою --ignore-table=DATABASE_NAME.TABLE_TO_IGNORE.

Ви також можете ігнорувати всі таблиці в базі даних або всю базу даних, для цього вам потрібно буде повторити частину повідомлення, щоб воно включало всі таблиці:

mysqldump -u root -pmypassword --ignore-table="my_db.table1" --ignore-table="my_db.table2" --ignore-table="my_db.table3" > all_databases.sql

Який підхід слід застосувати для дампа двійкових BLOB-даних за допомогою mysqldump?

Якщо база даних містить бінарні дані, ви можете зіткнутися з деякими проблемами. Якщо вам потрібно створити дамп бази даних MySQL, яка містить бінарні дані, використовуйте прапорець --hex-blob. Цей параметр сортує двійкові рядки і переводить їх у формат, який можна розрізнити. Ось як потрібно створити дамп бінарних даних:

mysqldump -u root -pmypassword my_bin_db --hex-blob > my_bin_db.sql

Чи можна використовувати оператор "where" з mysqldump?

Ви можете використовувати оператор "where" з mysqldump, щоб полегшити пошук даних, з яких ви хочете створити резервну копію з бази даних. Оператор "where" ініціює рядок умов і вилучає дані, які відповідають критеріям:

mysqldump -u root -pmypassword wpdb --tables thetable --where="date_created > '2017-04-27'" > wpdb_myrecord.sql