mysqldump: робимо бекапи

shell> mysqldump [OPTIONS] database [tables]
или  mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
или  mysqldump [OPTIONS] --all-databases [OPTIONS]

Найчастіше утиліта mysqldump використовується для отримання резервної копії всіх баз даних. Див. Розділ 4.4.1, «Резервне копіювання баз даних».

mysqldump --opt database > backup-file.sql

Можна, навпаки, прочитати цей файл на MySQL-сервері за допомогою команди:

mysql database < backup-file.sql

або

mysql -e "source /patch-to-backup/backup-file.sql" database

Ця утиліта досить часто використовується і для перенесення інформації з бази даних на інший MySQL-сервер:

mysqldump --opt database | mysql --host=remote-host -C database

Цілком можливо отримати дамп кількох баз даних за допомогою однієї команди:

mysqldump --databases database1 [database2 ...] > my_databases.sql

Якщо потрібен дамп усіх баз даних, можна використати:

mysqldump --all-databases > all_databases.sql

Розглянемо приклад найпростішого використання, зробимо дамп бази даних «database» за допомогою перенаправлення потоку у файл «database.sql»:

де:

• -u або -–user=... — ім'я користувача
• -h або --host=... — віддалений хост (для локального хоста можна опустити цей параметр)
• -p або --password — запитувати пароль
• database — ім'я бази даних
• database.sql — файл для дамп

Щоб зробити дамп кількох баз даних, необхідно використати параметр --databases (або скорочено -B), приклад:

А щоб зробити дамп усіх баз даних, необхідно використати параметр --all-databases (або скорочено -A), приклад:

Розгортання дамп

Перенаправляємо потік у зворотному напрямку і розгортаємо базу даних:

Або через mysql-console:

Ну, а якщо у нас gz-архів, наприклад, то:

Приклад використання деяких параметрів

Наприклад, нам потрібні дані з «продакшн версії бази» для «версії розробника», тобто нам потрібна «пісочниця». Обираємо не більше 100 записів:

Або нам потрібна тільки структура, без даних:

Приклади натхненні дописом Олександра Макарова — http://rmcreative.ru/blog/post/ljogkiy-damp-mysql

Створюємо дамп лише тригерів, процедур та подій:

Шпаргалка по параметрах

Наведу деякі параметри, які можуть знадобитися при роботі з утилітою mysqldump.

--add-drop-database

Додає оператор DROP DATABASE перед кожним оператором CREATE DATABASE.

--add-drop-table

Додає оператор DROP TABLE перед кожним оператором CREATE TABLE.

--add-locks

Додає оператор LOCK TABLES перед виконанням та UNLOCK TABLE після виконання кожного дампу таблиці (для прискорення доступу до MySQL).

--all-databases, -A

Зберігає всі таблиці з усіх баз даних, якими керує поточний сервер.

--allow-keywords

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

--comments, -i

Цей параметр дозволяє додати до дампу додаткову інформацію, таку як версія mysqldump, версія MySQL, ім'я хоста, на якому розташований сервер MySQL.

--compact

Цей параметр вимагає від mysqldump створити дамп, використовуючи максимально компактний формат. Параметр є протилежним до --comments.

--compatible=name

Параметр генерує вивід, який сумісний з іншими СУБД або старішими версіями MySQL. Замість ключового слова name можна використовувати: «ansi», «mysql323», «mysql40», «postgresql», «oracle», «mssql», «db2», «maxdb», «no_key_options», «no_table_options», «no_field_options». Можна використовувати кілька значень, розділивши їх комами.

--complete-insert, -c

Використовується повна форма оператора INSERT (з іменами стовпців).

--create-options

Додає додаткову інформацію в оператори CREATE TABLE. Це може бути тип таблиці, початкове значення AUTO_INCREMENT та інші параметри.

--databases, -B

Параметр дозволяє вказати імена кількох баз даних, для яких необхідно створити дамп.

--delayed

Використовувати команду INSERT DELAYED при вставці рядків.

--delete-master-logs

На головному сервері реплікації автоматично видаляються бінарні логи (logbin) після того, як дамп був успішно створений за допомогою mysqldump. Цей параметр автоматично включає параметр «--master-data».

--disable-keys, -K

Для кожної таблиці оточує оператор INSERT виразами /*!40000 ALTER TABLE tbl_name DISABLE KEYS */; та /*!40000 ALTER TABLE tbl_name ENABLE KEYS */; у виводі результату дампу. Це прискорить завантаження даних на сервер для таблиць типу MyISAM, оскільки індекси створюються після внесення всіх даних.

--extended-insert, -e

Використовувати команду INSERT з новим багаторядковим синтаксисом (підвищує компактність та швидкодію операторів введення).

--flush-logs, -F

Записати на диск дані системного журналу з буфера MySQL-сервера перед початком виконання дампу.

--force, -f

Продовжувати навіть якщо під час створення дампу сталася помилка.

--hex-blob

Параметр дозволяє представити бінарні дані в полях типу BINARY, VARBINARY, BLOB та BIT у шістнадцятковому форматі. Так послідовність «abc» буде замінена на 0×616263.

--ignore-table=db_name.tbl_name

Дозволяє ігнорувати таблицю tbl_name бази даних db_name під час створення дампу. Якщо з дампу необхідно виключити кілька таблиць, необхідно використати кілька параметрів «--ignore-table», вказуючи по одній таблиці в кожному з параметрів.

--insert-ignore

Додає ключове слово IGNORE в оператор INSERT.

--lock-all-tables, -x

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

--lock-tables, -l

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

--no-autocommit

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

--no-create-db, -n

Пригнічує створення в дампі операторів CREATE DATABASE, які автоматично додаються при використанні параметрів --databases та --all-databases.

--no-data, -d

Пригнічує створення операторів INSERT в дампі, що може бути корисним при створенні дампу структури бази даних без самих даних.

--opt

Параметр призначений для оптимізації швидкості резервування даних і є скороченням, що включає наступні опції: --quick --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --set-charset. Починаючи з MySQL 4.1, параметр --opt використовується за замовчуванням, тобто всі вищезгадані параметри вмикаються за замовчуванням, навіть якщо вони не вказуються. Для того щоб виключити таку поведінку, необхідно скористатися параметром --skip-opt

--order-by-primary

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

--port, -P

Номер TCP порту, що використовується для підключення до хоста.

--protocol={TCP|SOCKET|PIPE|MEMORY}

Параметр дозволяє задати протокол підключення до сервера.

--quick, -q

Дозволяє почати формування дампу, не чекаючи повного завантаження даних з сервера і економлячи тим самим пам'ять.

--quote-names, -Q

Поміщає імена баз даних, таблиць і стовпців у зворотні апострофи `. Починаючи з MySQL 4.1, цей параметр увімкнено за замовчуванням.

--replace

Додає ключове слово REPLACE в оператор INSERT. Цей параметр вперше з'явився в MySQL 5.1.3.

--result-file=/path/to/file, -r /path/to/file

Параметр спрямовує дамп у файл file. Цей параметр особливо зручний у Windows, без використання командного рядка, коли можна перенаправити результат у файл за допомогою послідовностей > та >>.

--routines, -R

Цей параметр створює дамп збережених процедур і функцій. Доступний з MySQL 5.1.2.

--single-transaction

Параметр створює дамп у вигляді однієї транзакції.

--skip-comments

Цей параметр дозволяє пригнічити вивід в дамп додаткової інформації.

--socket=/path/to/socket, -S /path/to/socket

Файл сокета для підключення до localhost.

--tab=/path/, -T /path/

При використанні цього параметра в каталозі path для кожної таблиці створюються два окремі файли: tbl_name.sql, що містить оператор CREATE TABLE, та tbl_name.txt, який містить дані таблиць, розділені символом табуляції. Формат даних може бути перевизначений явно за допомогою параметрів --fields-xxx та --lines-xxx.

--tables

Перекриває дії параметра --databases (-B). Всі аргументи, що йдуть після цього параметра, трактуються як імена таблиць.

--triggers

Створюється дамп тригерів. Цей параметр увімкнено за замовчуванням. Для його вимкнення слід використати параметр --skip-triggers.

--events, -E

Створюється дамп подій. Дивіться MySQL Event Scheduler або вбудований диспетчер подій в MySQL.

--tz-utc

При використанні цього параметра в дамп буде додано оператор виду SET TIME_ZONE='+00:00', що дозволить обмінюватися дампами в різних часових поясах.

--verbose, -v

Розширений режим виводу. Вивід більш детальної інформації про роботу програми.

--version, -V

Вивести інформацію про версію програми.

--where='where-condition', -w 'where-condition'

Виконати дамп лише вибраних записів. Зверніть увагу, що лапки є обов'язковими.

--xml, -X

Представляє дамп бази даних у вигляді XML.

--first-slave, -x

Блокує всі таблиці у всіх базах даних.

--debug=..., -#

Відстежувати проходження програми (для налагодження).

--help

Вивести довідкову інформацію і вийти з програми.