MySqldump Error 2013: Lost Connection MySQL During create database backup

google-cloud-platformlinuxMySQL

I am use Prestashop.
am create a script for auto backup of whole database of my site
but from last few given this error and backup terminate at this table.

mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table ps_layered_filter_block at row: 107002

am change some value in my.cnf file but not work for me
my.cnf file

#Copyright 2019 Bitnami.com All Rights Reserved

#Note: This file will be modified on server size changes

[mysqld]
net_read_timeout = 1200
net_write_timeout = 1900
wait_timeout = 320
long_query_time = 1
query_cache_limit=20M
query_cache_type=1
query_cache_size=512M
innodb_buffer_pool_size=4096M
innodb_force_recovery = 1
innodb_log_file_size=128M
tmp_table_size=64M
max_connections = 2500
max_user_connections = 2500
innodb_flush_method=O_DIRECT
key_buffer_size=64M

[mysqladmin]
user=root

[mysqld]
sql_mode=NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION
basedir=/opt/bitnami/mysql
datadir="/opt/bitnami/mysql/data"
port=3306
socket=/opt/bitnami/mysql/tmp/mysql.sock
tmpdir=/opt/bitnami/mysql/tmp
max_allowed_packet=512M
bind-address=127.0.0.1
skip-name-resolve=1
default-authentication-plugin=mysql_native_password

character-set-server=UTF8
collation-server=utf8_general_ci
[client]
port=3306
socket=/opt/bitnami/mysql/tmp/mysql.sock

default-character-set=UTF8
[manager]
port=3306
socket=/opt/bitnami/mysql/tmp/mysql.sock
pid-file=/opt/bitnami/mysql/tmp/manager.pid
default-mysqld-path=/opt/bitnami/mysql/bin/mysqld.bin

!include /opt/bitnami/mysql/bitnami/my.cnf

but Problem Stil Same.

mysqldump -u root -p bitnami_prestashop > /home/{username}/dbbackup/db_13_07_2020.sql
also try
mysqldump -u root -p –extended-insert bitnami_prestashop > /home/{username}/dbbackup/db_13_07_2020.sql

our database size

| bitnami_prestashop | Size in (MB) |
+--------------------+--------------+
| bitnami_prestashop | 8515.77 |
|
| information_schema | 0.16 |
| mysql | 3.82 |
| performance_schema | 0.00 |
| sys | 0.02 |
+--------------------+--------------+```

**on-disk** size

11G /opt/bitnami/mysql/data/bitnami_prestashop
21M /opt/bitnami/mysql/data/mysql
11G /opt/bitnami/mysql/data

Please Provide solution as soon as posibal thanks

Best Answer

Given your DB dimensions try increasing those values a little more ,

Example :

max_allowed_packet=2048M
net_read_timeout=3600
net_write_timeout=3600

Don't forget to restart/reload mysqld aftewards.

Related Question