mysqldump takes forever to dump tables from a remote server

Question

I'm trying to pull the database schema from a remote server, but mysqldump is too slow to output (125+) tables' structures.

• If I enter the said server and run mysqldump --compact --compress --no-data --add-drop-database --databases --user=myuser --password=123 mydb > dump.sql it's almost instant.
• If I run the same thing over ssh it takes 2.5 secs (ssh myserver mysqldump [...] > dump.sql)
• However, if I use the --host so I can use the database credentials only, instead of the SSH credentials, it takes 2min40s to run!

What makes mysqldump so slow when running over its own protocol? It dumps each table (all at once) every few seconds, then waits, dumps the next one...
Is there anything I could do to make things faster?

EDIT: I wrote about that db adventure, to show the final solutions.

Answer 1

I ran into the same issue and regarding the "why" this happens, my theory is that it's due to network overhead of opening/closing connections. My theory is that mysqldump needs to open and closes a lot of connections and that's what's slowing it down.

I ran it on the machine itself and it took 10 seconds. From another machine, through a VPN, it took like 10 minutes. From another machine on the same network (connected to the same switch), it took 20 seconds.

It's just a theory but it fits those observations.

Of course to work around that issue, the ssh trick works.