Как делать mysqldump на продакшне

Mysqldump остается одним из популярных решений для выполнения бекапов. Если на данном этапе развития проекта более продвинутые средства создания бекапов не используются, стоит использовать mysqldump с правильным параметрами.

Наиболее критичное влияние, которое mysqldump оказывает в продакшне, это блокировка таблиц:

root@webminer-manager-1:~# mysql -e "show processlist"
+-------+------+-----------------+-----------+---------+------+---------------------------------+------------------------------+
| Id    | User | Host            | db | Command | Time | State                           | Info                                |
+-------+------+-----------------+-----------+---------+------+---------------------------------+------------------------------+
|   416 | user | localhost:52102 | db | Query   |  632 | Waiting for table metadata lock | INSERT INTO products SET url = '... |
|   417 | user | localhost:52104 | db | Query   |  633 | Waiting for table metadata lock | INSERT INTO products SET url = '... |
|   418 | user | localhost:52106 | db | Query   |  633 | Waiting for table metadata lock | INSERT INTO products SET url = '... |
...

# блокировка таблицы во время выполнения дампа

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

-l, --lock-tables   Lock all tables for read.
                      (Defaults to on; use --skip-lock-tables to disable.)
  --single-transaction 
                      Creates a consistent snapshot by dumping all tables in a
                      single transaction. Works ONLY for tables stored in
                      storage engines which support multiversioning (currently
                      only InnoDB does); the dump is NOT guaranteed to be
                      consistent for other storage engines. While a
                      --single-transaction dump is in process, to ensure a
                      valid dump file (correct table contents and binary log
                      position), no other connection should use the following
                      statements: ALTER TABLE, DROP TABLE, RENAME TABLE,
                      TRUNCATE TABLE, as consistent snapshot is not isolated
                      from them. Option automatically turns off --lock-tables.

# выключение блокировки во время дампа

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

Кроме этого, полезно включить опцию --insert-ignore, чтобы избежать ошибок при последующем восстановлении (например, если изменились ключи).

И конечно, стоит использовать потоковое сжатие с помощью gzip, чтобы сэкономить ресурсы дисковой подсистемы. Опция -v также полезна при дампе нескольких таблиц — выводит системную информацию о прогрессе дампа.

Финальная команда дампа для выполнения на продакшне может выглядеть так:
mysqldump -v --insert-ignore --skip-lock-tables --single-transaction=TRUE db products | gzip > /root/products.sql.gz

Такой дамп не будет блокировать таблицы и минимизирует влияние на основное приложение:

mysql> show processlist;
+-------+------+-----------------+----+---------+------+-------------------+------------------------------------------------------------------------------------------------------+
| Id    | User | Host            | db | Command | Time | State             | Info                                                                                                 |
+-------+------+-----------------+----+---------+------+-------------------+------------------------------------------------------------------------------------------------------+
| 15655 | user | localhost:39430 | db | Query   |    0 | query end         | INSERT INTO products SET url = '... 
| 15656 | user | localhost:39450 | db | Sleep   |  614 |                   | NULL                                                                                                 |
| 15661 | root | localhost       | db | Query   |  589 | Sending to client | SELECT /*!40001 SQL_NO_CACHE */ * FROM `products`
| 15682 | user | localhost:39504 | db | Sleep   |    0 |                   | NULL                                                                                           
...

# Отсутствие блокировки при дампе

TL;DR

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