Mysqldump в MySQL

Mysqldump имеет множество опций (я насчитал 111 штук). Большинство из нас, скорее всего, придерживаются простого подхода. Вот как я обычно экспортирую одну базу данных:

mysqldump some_database > some_database.sql

# Or with user auth
mysqldump -u some_user -p some_database > some_database.sql

# Or with gzip compression
mysqldump some_database | gzip > some_database.sql.gz

# Or with the "pv" tool, which let's us know how much data is
# flowing between our pipes - useful for knowing if the msyqldump
# has stalled
mysqldump some_database | pv | gzip > some_database.sql.gz
# 102kB 0:01:23 [1.38MB/s] [  <=>

Однако стоит немного разобраться в этой команде. Ведь если вы используете mysqldump для работы с боевой базой данных, то это может вызвать реальные проблемы у пользователей.

Значения по умолчанию

Сначала рассмотрим настройки mysqldump по умолчанию. Если мы явно не просим что-то другое, mysqldump использует флаг --opt. Опция opt является алиасом для следующих флагов:

--add-drop-table - добавляет оператор DROP TABLE перед каждым оператором CREATE TABLE. Таким образом, перед восстановлением таблицы существующая таблица с таким же именем будет удалена. Мы сможем использовать полученный файл .sql снова и снова с идемпотентностью.

--add-locks - применяется при импорте файла дампа (не при запуске mysqldump). Окружает каждый дамп таблицы операторами LOCK TABLES и UNLOCK TABLES. Это приводит к более быстрой вставке при перезагрузке файла дампа. Это означает, что пока вы импортируете данные, каждая таблица будет заблокирована от чтения и записи на время (пере)создания таблицы.

--create-options - включает все специфические для MySQL опции таблиц в операторе CREATE TABLE. При тестировании этой опции (отключите её, используя -create-options=false), я обнаружил, что основным и самым очевидным различием было отсутствие AUTO_INCREMENT для первичных ключей при установке этой опции в false.

--disable-keys - опция эффективна только для неуникальных индексов таблиц MyISAM. Это ускоряет загрузку файла дампа (для таблиц MyISAM), поскольку индексы создаются после вставки всех строк.

--extended-insert - запись операторов INSERT с использованием синтаксиса многострочной вставки, включающего несколько списков VALUES. Неиспользование этой опции может потребоваться для таблиц с большими столбцами (обычно блобами), которые вызывают запросы, превышающие конфигурацию "max_allowed_packet" клиента/сервера, но в целом её полезно включать. Использование одного запроса на вставку значительно замедляет импорт.

--lock-tables - в отличие от add-locks, эта опция применяется, когда вы запускаете mysqldump. Она блокирует все таблицы на время выполнения mysqldump, что делает её плохой опцией для использования в производственной среде. В основном она используется для защиты целостности данных при дампе таблиц MyISAM. Поскольку в настоящее время InnoDB является механизмом хранения таблиц по умолчанию, эту опцию есть смысл отменить, используя --skip-lock-tables, чтобы остановить такое поведение, или --single-transaction, чтобы запустить mysqldump внутри транзакции, о чём я расскажу чуть позже.

--quick - считывание больших таблиц таким образом, чтобы не требовалось много RAM для размещения всей таблицы в памяти.

--set-charset - запись SET NAMES default_character_set на вывод. Это НЕ выполняет никакого преобразования набора символов (mysqldump не будет делать этого с любым флагом), а просто говорит, что вы хотите добавить информацию о наборе символов, чтобы он был установлен при повторном импорте файла дампа.

Итак, по умолчанию всё довольно хорошо, за исключением --lock-tables. Из-за этой опции база данных становится непригодной для использования во время работы mysqldump. Но мы можем использовать mysqldump более разумно!

Mysqldump и блокировки таблиц

При использовании mysqldump необходимо найти компромисс между остановкой/влиянием на производительность базы данных и обеспечением целостности данных. Ваша стратегия будет в значительной степени определяться тем, какой механизм(ы) хранения данных вы используете в таблицах базы данных.

Поскольку каждая таблица может иметь отдельный механизм хранения, это может стать интересным :D

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

Согласованность данных

Согласованность (консистентность) данных означает, что если до выполнения транзакции данные в БД находятся в неком состоянии, то они будут в этом же состоянии и после выполнения транзакции. Все отношения должны совпадать. 

Представьте, что mysqldump экспортирует первые 5 таблиц из 20. Если таблица 1 и таблица 20 получили новые строки, связанные друг с другом первичными/внешними ключами после того, как mysqldump сделал дамп таблицы 1, но до того, как он сделал дамп таблицы 20, то мы находимся в несогласованном (неконсистентном) состоянии. В таблице 20 есть данные, относящиеся к строке в таблице 1, которые не попали в файл дампа.

Таблицы MyISAM требуют такой блокировки, поскольку они не поддерживают транзакции. Однако InnoDB (механизм хранения данных по умолчанию в MySQL 5.5.5) поддерживает транзакции. Mysqldump по умолчанию использует консервативную настройку блокировки всего, но нам не нужно использовать это значение — мы можем полностью избежать блокировки таблиц.

Mysqldump с транзакциями

Как правило, если у вас есть причина не использовать MyISAM, то есть смысл выбрать механизм хранения InnoDB для всех таблиц. Если вы переносили базу данных на различные серверы MySQL в течение многих лет (еще когда MyISAM был движком хранения по умолчанию), убедитесь, что ваши таблицы используют InnoDB.

Это очень важно:

Если вы используете таблицы InnoDB, ваш mysqldump должен выглядеть примерно так:

mysqldump --single-transaction --skip-lock-tables some_database > some_database.sql

Флаг --single-transaction запускает транзакцию перед выполнением. Это позволит не блокировать всю базу данных, а дать возможность mysqldump прочитать базу данных в текущем состоянии на момент транзакции, что обеспечит консистентность дампа данных.

В опции --single-transaction используется режим изоляции транзакций по умолчанию: REPEATABLE READ.

Обратите внимание, если у вас есть смесь таблиц MyISAM и InnoDB, использование вышеописанных опций может оставить вашу MyISAM (или таблицы Memory, если на то пошло) в неконсистентном состоянии, так как это не блокирует чтение/запись в таблицы MyISAM.

В этом случае я предлагаю сделать дамп таблиц MyISAM отдельно от таблиц InnoDB.

Однако, если это все равно приводит к неконсистентному состоянию (если таблица MyISAM имеет PK/FK связи с таблицами InnoDB), то использование опции --lock-tables становится единственным способом гарантировать, что база данных находится в консистентном состоянии при использовании mysqldump.

Это означает, что в такой ситуации вам придётся быть осторожным при запуске mysqldump на работающей базе данных. Например, запустите его на реплике базы данных, а не на основной базе данных, или изучить такие варианты, как Xtrabackup, который копирует каталог данных mysql и не вызывает простоя.

Репликация

Если вы используете репликацию, то у вас уже есть резервные копии на других серверах. Это замечательно! Тем не менее, всё ещё полезно иметь резервное копирование за пределами площадки. При такой установке я стараюсь запускать mysqldump на сервере реплики.

С точки зрения mysqldump это имеет несколько последствий:

1. Запуск mysqldump на сервере-реплике означает, что данные, которые он получает, могут немного отставать от главного сервера.  Для регулярного резервного копирования это нормально. Если вам нужно, чтобы данные находились в определённой точке, то нужно подождать, пока они перенесутся на сервер-реплику.

2. Запуск mysqldump на реплике предпочтительнее (ИМХО), поскольку с точки зрения теории серверы реплики будут отставать в любом случае. И добавление некоторой нагрузки от mysqldump не должно стать проблемой. 

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

Master Data

Флаг --master-data добавляет вывод в файл дампа, что позволяет использовать его для настройки другого сервера в качестве реплики главного. Реплике нужны основные данные, чтобы знать, с чего начать репликацию.

Опция --master-data автоматически отключает --lock-tables, так как включённые двоичные журналы покажут, с чего начать репликацию. Это позволит вам не потерять запросы, если дамп окажется в неконсистентном состоянии. (Опять же, это важно, только если у вас есть таблицы MyISAM).

Если также используется --single-transaction, глобальная блокировка чтения будет получена только на короткое время в начале дампа.

Используйте это при дампе с главного сервера.

Репликация дампа

Опция --dump-slave очень похожа на опцию --master-data, но есть два важных исключения:

1. Вместо того, чтобы быть дампом главного сервера, он предназначен для дампа сервера-реплики.

2. Он будет содержать ту же информацию о мастере, что и сбрасываемый сервер-реплика, в то время как опция --master-data устанавливает себя в качестве мастера.

Используйте это при создании дампа с сервера реплики.

Из документации: "Этот параметр не следует использовать, если сервер, на котором будет применяться дамп, использует gtid_mode=ON и MASTER_AUTOPOSITION=1"

GTID — это более новый способ репликации MySQL, начиная с MySQL 5.6. Это более приятный метод, поэтому --dump-slave теоретически можно игнорировать.”

Создание дампа нескольких или всех БД

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

mysqldump --single-transaction --skip-lock-tables --databases db1 db2 db3 \
    > db1_db2_and_db3.sql

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

mysqldump --single-transaction --skip-lock-tables some_database table_one table_two table_three \
    > some_database_only_three_tables.sql

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

mysqldump --single-transaction --skip-lock-tables --flush-privileges --all-databases > entire_database_server.sql

В приведенной выше команде использовалась опция --all-databases вместе с опцией --flush-privileges.

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

Вот и всё!

В mysqldump существует много-много опций, на которые стоит обратить внимание. Здесь я рассказал о тех, которые (с моей точки зрения) наиболее важны для использования mysqldump в современной реализации MySQL. Спасибо за внимание!