mysqldump постоянно дает сбой – ubuntu 20.04

Вопрос или проблема

mysqldump: Ошибка 2013: Соединение с сервером MySQL потеряно во время запроса при дампе таблицы `[TABLE]` на строке: [ROW-number]

 mysqldump --compression-algorithms=zlib --quick --single-transaction db_name.sql

[mysqld]
max_allowed_packet=2048M

wait_timeout = 86400
interactive_timeout = 86400

net_read_timeout=4800
net_write_timeout=4800

[mysqldump]
    max_allowed_packet = 2048M

SET GLOBAL innodb_force_recovery = 1;

mysql> SET GLOBAL innodb_force_recovery = 0;

innodb_force_recovery = 0       #  Предоставить помощь в восстановлении от повреждений InnoDB и может позволить вам выполнить mysql dump

mysql> SET GLOBAL innodb_force_recovery = 0;

# mysql --defaults-extra-file="$your_mysql_password_cfg" -e "SET GLOBAL innodb_force_recovery = 0;"

[mysqld]    
innodb_buffer_pool_size = 45GB    # 70-80% от общего объёма ОЗУ
max_allowed_packet= 2G   # max_allowed_packet - максимальный размер 2GB
wait_timeout = 28800
interactive_timeout = 28800
net_read_timeout = 1200         # 20 минут
net_write_timeout= 1200         # 20 минут
net_buffer_length = 64K

[mysqldump]
# max_allowed_packet - наибольшее допустимое значение (2GB)
max_allowed_packet = 2G

Ответ или решение

Проблема, с которой вы сталкиваетесь при использовании mysqldump для создания дампа вашей базы данных на MySQL 8.0.40, заключается в ошибке 2013, что указывает на потерю соединения с сервером MySQL во время выполнения запроса. Эта проблема нередко возникает в результате различных факторов, и её решение требует тщательного анализа конфигурации и состояния вашей базы данных.

Основные причины возникновения ошибки 2013

1. Настройки подключения: Значения wait_timeout, interactive_timeout, net_read_timeout и net_write_timeout могут оказывать значительное влияние на стабильность соединения. В вашем случае, несмотря на то что вы установили высокие значения для этих параметров, существуют другие факторы, которые также могут вызывать разрывы соединения.

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

3. Проблемы с InnoDB: Если ваша база данных содержит поврежденные строки или таблицы, это может быть потенциальной причиной возникновения ошибок. Установка параметра innodb_force_recovery на 1 позволяет игнорировать некоторые ошибки, что может помочь выполнить дамп, но может не решить основную проблему.

Рекомендации по устранению проблемы

1. Оптимизация настроек MySQL:
   Убедитесь, что настройки в вашем конфигурационном файле MySQL (/etc/mysql/my.cnf) оптимизированы:

   [mysqld]
   innodb_buffer_pool_size = 45G  # Около 70-80% от общего объема оперативной памяти
   max_allowed_packet = 2G         # Максимальный размер пакета
   wait_timeout = 28800
   interactive_timeout = 28800
   net_read_timeout = 1200         # 20 минут
   net_write_timeout = 1200        # 20 минут
   net_buffer_length = 64K
   
   [mysqldump]
   max_allowed_packet = 2G

2. Проверка и восстановление InnoDB:
   Если вы подозреваете, что ваша база данных повреждена, выполните следующие шаги:

   • Установите параметр innodb_force_recovery на 1 через MySQL клиент:

     SET GLOBAL innodb_force_recovery = 1;

   • Попробуйте выполнить дамп снова с помощью mysqldump. Если это сработает, после завершения дампа верните параметр в 0:

     SET GLOBAL innodb_force_recovery = 0;

3. Разделение дампа на части:
   Если таблицы очень большие, рассмотрите возможность создания дампа по частям. Вы можете воспользоваться опцией --tables и указать только конкретные таблицы.

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

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

Заключение

Расследование проблемы с mysqldump в MySQL требует комплексного подхода и внимания к деталям. Следуйте рекомендованным шагам и внимательно следите за состоянием сервера и базы данных. Если после всех проведенных изменений ошибка продолжает возникать, возможно, стоит рассмотреть возможность обращения к профессионалу для дальнейшей диагностики и устранения ошибок.