I am running Mysql server with master and slave replication. From past few days i found that backups on my master server are incomplete.

When i ran manually on master it's returning the below error

"mysqldump: Error 1317: Query execution was interrupted when dumping table table_name at row: 36705562"

Command i am using to take backups

mysqldump -u dbdump -p dbname --routines --single-transaction --lock-tables=false --max-allowed-packet=512M --master-data=2 -q -C -E -e -f -R | gzip -c > backup.sql.gz

Ideally i am using the same command on both master and slave, but it's failing on master.

I tried to do backup from remote machine but the same error.

In /etc/my.cnf --> max packet size is set to 512M


I have dealt with this in the past.

There are three(3) aspects you need to consider

ASPECT #1 : Table Corruption

Please take note of the row number 36705562. If that row number comes back in the error message every single time your mysqldump fails on that table, then maybe you can suspect data corruption.

ASPECT #2 : Networking

I suggested increasing networking timeout like this

mysql> SET GLOBAL net_read_timeout = 31536000;
mysql> SET GLOBAL net_write_timeout = 31536000; 

Basically, this will make a DB Connection sit longer until all data from a SELECT comes.

If your get a different row number with every mysqldump attempt, this suggests mysqldump connection is able to move along the large table and just timeout and some random point.

ASPECT #3 : max_allowed_packet (Optional)

While 512M is a good size, the maximum value for the max_allowed_packet is 1G. It does not hurt to increase it to 1G for mysqldump. Why ??? The MySQL Packet is not sized by max_allowed_packet. It is sized by net_buffer_length (default size is 16K). MySQL will dynamically stretch a MySQL Packet up to whatever max_allowed_packet is set to and then shrink back to its original size when data transmission through the MySQL Packet is done. You should set max_allowed_packet to 1G or 1024M.

| improve this answer | |
  • Tried with max_allowed_packet=1024M got the same error but for different table. Should try this by changing read and write time out values. I cross check those values on slave server both are set to (30 and 60) but it's doing good. – rahuls36 Aug 6 '19 at 19:01
  • Please don't forget to check the error message and note at what table mysqldump fails and at what row. – RolandoMySQLDBA Aug 6 '19 at 19:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.