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
- The error row changes but the table is the same at which mysqldump errors out
- Table has 4M records and size is around 1.5 GB
- 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!