-1

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

RolandoMySQLDBA
  • 171,728
  • 30
  • 293
  • 486
rahuls36
  • 1
  • 1

1 Answers1

1

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.

Community
  • 1
RolandoMySQLDBA
  • 171,728
  • 30
  • 293
  • 486
  • 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 06 '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 06 '19 at 19:08
  • When using `mysqldump` I put the global variables in the `/etc/my.cnf` and restarted the server. Otherwise they are only for the current mysql session. – Jeremy May 28 '21 at 13:03