MySQLDump Failing via Shell command, but working via Workbench...

[Tony Snorek]

Hello,

I am wondering if someone else out there has seen this issue and can potentially give me some help. I am working on trying to fix some automated mysqldump commands that my company runs for DR Purposes.. Here is some information and what I am seeing:

Problem:

When running the dump via shell, after a few minutes, I typically receive an Error 2013: Lost connection to MySQL server during query when dumping table 'notrealtable' at row: 894329 *I say typically because I have also had it where it just stops writing to the file and does not give me an error code, until I try to run a CTRL+C in PuTTy to stop the process and then it causes PuTTy to crash..*

The row number changes every time, as well as the line that it stops at in the actual file, it's also not a consistent size or anything like that.

I can run this dump via Workbench and it completes without any issues... In the shell I have tried using different users, I have tried adding a max packet size flag, I have tried adding the --quick flag

I see numerous recommendations about increasing the Net_read_timeout and net_write_timeout, but my boss will not let me make these changes because he insists that if it works via workbench, it should work via shell with the exact same settings...

I have also noticed by watching the "Client Connections" tab in Workbench, after executing the dump command on the CentOS EC2, that I can see the backup user make the call and I can watch what it is doing, but eventually that connection data goes away, the file is still increasing in size on the linux EC2 when this happens...

Info:

Database: Amazon RDS MySQL 5.7.19

Dump server: CentOS EC2 hosted on AWS

Workbench mysqldump Version: 5.7.19

Shell Command: mysqldump -h"amazondatabasendpoint" -unotrealuser -p'notrealpassword' --ssl-mode=REQUIRED --single-transaction --databases databasename --routines --triggers > /path/to/directory.sql