Использование mysqldump

Содержание

  1. Содержание
  2. Введение
  3. Действия на источнике
  4. Действия на реплике
  5. Прекращение репликации
  6. Решение проблем
    1. Проблема с max_allowed_packet
    2. Ошибка с привилегией PROCESS
    3. Проблема с max_prepared_stmt_count
    4. Проблема с доступом

Введение

С баз данных нужно снимать резервные копии на случай возможного выхода из строя сервера или повреждения данных из-за ошибок в работе пользователей или приложений.

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

Ниже в несколько сумбурном виде приведены команды и опции, а также описаны ошибки, с которыми можно столкнуться при решении указанных задач с использованием утилиты mysqldump.

Действия на источнике

Утилитой mysqldump можно снять резервные копии интересующих базы данных:

$ mysqldump -uroot -p --single-transaction --routines --triggers --events --master-data --hex-blob --quick --databases db01 db02 | gzip > dbs.sql.gz

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

$ mysqldump -uroot -p --single-transaction --routines --triggers --events --master-data --hex-blob --quick --all-databases | gzip > dbs.sql.gz

Объяснение используемых опций:

  • --single-transaction - операции чтения данных из таблиц выполняются в рамках одной транзакции для получения согласованной копии данных,
  • --routines - сохранить хранимые процедуры,
  • --triggers - сохранить триггеры,
  • --events - сохранить периодические задачи,
  • --master-data - сохранить в резервную копию имя файла журнала репликации и позицию в нём для настройки реплики,
  • --hex-blob - сохранять двоичные данных из таблиц в шестнадцатеричном виде,
  • --quick - сохранять строки таблиц по мере чтения, не пытаясь считать таблицу целиком в оперативную память перед записью.

Если на сервере, с которого снимаются резервные копии, настроена запись в журнал репликации GTID, то для настройки реплики понадобится информация о GIT. Для этого нужно добавить к команде резервного копирования опцию --set-gtid-purged=ON. Если же нужна просто резервная копия, то к команде резервного копирования нужно добавить опцию, отключающую запись информации о GTID в резервную копию - --set-gtid-purged=OFF.

Действия на реплике

Если нужно просто восстановить ранее настроенную, но по каким-то причинам сломавшуюся репликацию, то предварительно может понадобиться сохранить права доступа на существующей реплике при помощи команды следующего вида:

$ pt-show-grants > grants.sql

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

STOP SLAVE;

Затем можно восстановить резервную копию на сервере, который станет репликой:

$ zcat dbs.sql.gz | mysql

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

CHANGE MASTER TO MASTER_LOG_FILE='node-1.005814', MASTER_LOG_POS=16159;

Увидеть эти значения можно в строках Master_Log_File и Exec_Master_Log_Pos в выводе следующей команды:

SHOW SLAVE STATUS\G

Перед запуском репликации нужно указать дополнительно сервер-источник и учётные данные пользователя для подключения, а имя журнала и позицию взять из вывода предыдущей команды. Для настройки источника репликации воспользуемся командой вида:

CHANGE MASTER TO MASTER_HOST = '192.168.0.101',
                 MASTER_USER = 'repl',
                 MASTER_PASSWORD = 'xxxxxxxxxx',
                 MASTER_LOG_FILE = 'node-1.005814',
                 MASTER_LOG_POS = 16159;

После чего можно запустить репликацию:

START SLAVE;

Прекращение репликации

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

STOP SLAVE;
RESET SLAVE ALL;

Новый сервер теперь будет обладать собственной копией данных, а данные на старом сервере становятся неактуальными и их на старом сервере можно будет удалить:

DROP DATABASE db01;

Решение проблем

Проблема с max_allowed_packet

В процессе снятия резервной копии mysqldump таблицы с колонкой типа longblob, содержащей двоичные данные большого объёма, выводит сообщение следующего вида и завершает работу:

mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' bytes when dumping table `PLUGINDATA` at row: 3

Для решения этой проблемы проще всего указать mysqldump опцию с указанием большого значения --max_allowed_packet следующим образом:

# mysqldump --single-transaction --routines --triggers --events --master-data --hex-blob --quick --max-allowed-packet=1G --databases db01 db02 | gzip > dbs.sql.gz

Ошибка с привилегией PROCESS

Для снятия резервной копии с базы данных достаточно иметь права на чтение всех таблиц, представлений и триггеров в этой базе данных. Кроме этого mysqldump пытается сохранить в резервной копии информацию о табличных пространствах. При отсутствии у пользователя привилегии PROCESS в таком случае будет выводиться ошибка:

mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces

Резервное копирование при этом успешно продолжится, так что можно не обращать внимание на эту ошибку. Но можно и избежать появления этой ошибки простым добавлением опции --no-tablespaces:

# mysqldump --single-transaction --routines --triggers --events --master-data --hex-blob --quick --no-tablespaces --databases db01 db02 | gzip > dbs.sql.gz

Проблема с max_prepared_stmt_count

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

# zcat dbs.sql.gz | mysql
ERROR 1461 (42000) at line 283987: Can't create more than max_prepared_stmt_count statements (current value: 16382)

Если посмотреть в окрестности строки 283987 резервной копии, то можно увидеть следующее:

# zcat /srv/db0.sql.gz | awk 'NR > 283980 && NR < 284000 { print $0; }'
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `user_login_22` AS select `user_login_3`.`id` AS `id`,`user_login_3`.`cid` AS `cid`,`user_login_3`.`login` AS `login`,`user_login_3`.`pswd` AS `pswd`,`user_login_3`.`date1` AS `date1`,`user_login_3`.`date2` AS `date2`,`user_login_3`.`status` AS `status`,3 AS `session`,`user_login_3`.`rp_mode` AS `rp_mode`,`user_login_3`.`realm_group` AS `realm_group`,`user_login_3`.`comment` AS `comment`,`user_login_3`.`object_id` AS `object_id` from `user_login_3` */;
/*!50001 SET character_set_client      = @saved_cs_client */;
/*!50001 SET character_set_results     = @saved_cs_results */;
/*!50001 SET collation_connection      = @saved_col_connection */;
/*!50112 SET @disable_bulk_load = IF (@is_rocksdb_supported, 'SET SESSION rocksdb_bulk_load = @old_rocksdb_bulk_load', 'SET @dummy_rocksdb_bulk_load = 0') */;
/*!50112 PREPARE s FROM @disable_bulk_load */;
/*!50112 EXECUTE s */;
/*!50112 DEALLOCATE PREPARE s */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

Ошибка произошла в строке:

/*!50112 PREPARE s FROM @disable_bulk_load */;

Как можно догадаться, при восстановлении каждой таблицы создаётся новое подготовленное выражение, которое в дальнейшем должно быть удалено в этой строке:

/*!50112 DEALLOCATE PREPARE s */;

Однако этого по каким-то причинам не происходит. Одно из решений заключается в том, чтобы выставить большее значение max_prepared_stmt_count перед восстановлением из резервной копии, например, следующим образом:

SET GLOBAL max_prepared_stmt_count = 32*1024;

Другое решение заключается в том, чтобы вырезать из резервной копии строчки, начинающиеся с текста /*!50112, например, следущим образом:

# mysqldump --single-transaction --routines --triggers --events --master-data --hex-blob --quick --databases db01 db02 | grep -UEv '^\/\*!50112' | gzip > dbs.sql.gz

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

Проблема с доступом

Даже если перенос базы данных успешно выполнен, приложение может не заработать со странными ошибками доступа. Некоторые таблицы могут на самом деле оказаться представлениями:

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `RackObject` AS ...

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

Если на новом сервере нет нужного пользователя, то его нужно либо создать, либо заменить на другого существующего пользователя с достаточными правами. Можно просто убрать это поле из резервной копии следующим образом:

# mysqldump --single-transaction --routines --triggers --events --master-data --hex-blob --quick --databases db01 db02 | sed 's/ DEFINER=[^ ]* / /g' | gzip > dbs.sql.gz

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