0

Im taking a db backup from AWS RDS (MySQL) slave from an EC2 (m5.large - 2c/8GB) in same AZ using - 

mysqldump -hxx -u xx -p --extended-insert  --single-transaction  --max_allowed_packet=1G db > db.sql

The backup dies at around 60-70 minutes with error : mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table xx at row: 1743503

  1. The error row changes but the table is the same at which mysqldump errors out
  2. Table has 4M records and size is around 1.5 GB
  3. max_allowed_packet on source is 4MB (although this is inconsequential I believe)

So I tried with single table backup with pv enabled : 

mysqldump -hxx -u xx -p --extended-insert  --single-transaction  --max_allowed_packet=1G db tbl_xx | pv > db_tbl_xx.sql

The pv stream was interesting as I saw ~10 MiB/s speed for sometime, then froze to 0 B/s for a few seconds. When it resumed, it hovered around 700-800 KiB/s for the rest of the time till the backup finished (1.79GB - 35 minutes). Only in case of entire DB, the backup never finished but died throwing the "Error 2013".

Interestingly, a backup from SQLyog GUI for the same table finished in around 5 minutes over the internet.

I'm unsure if the mysqldump parameters are not supporting large backups or some network level configuration is the problem here. Some help with pointers to check on either side will be great, thanks!

Improve this question
3
  • It is more likely to be a timeout.
    – Rick James
    Commented Nov 28, 2018 at 2:47
  • a timeout should terminate the thread as soon it happens. But this process keeps crawling for a good duration before stopping/timing out.
    – gnyanendra
    Commented Dec 6, 2018 at 9:19
  • Timing out the client does nothing for stopping the server since the server won't communicate until it is ready to.
    – Rick James
    Commented Dec 6, 2018 at 17:41

1 Answer 1

Reset to default
0

On a re:Invent conference I watched once, an AWS engineer suggested when we are going to use high IOPS processes, to increase the instance type. My suggestion would be to change your instance type to db.m5.4xlarge, wait 20 minutes and then try again the dump.

Chances are that it will work like a charm!

Improve this answer
1
  • 1
    shouldn't mysqldump consume the resources of machine it is initiated on, rather than using remote host's? I would believe for the remote host - only disk reads are going to be a bottleneck, not the CPU or memory. Also, resizing the instance for backups doesn't sound a convincing solution.
    – gnyanendra
    Commented Dec 6, 2018 at 9:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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