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.

  • Have you tried scripting getting the ssh dump over to your own machine - i.e. make it a two-step process? You could pipe dump sql to rcp or similar?
    – Vérace
    Feb 21 '17 at 17:13
  • @Vérace yeah, the issue is not with the steps but with the need to use ssh to make it in a decent speed. MySQL credentials should be enough, why's its protocol so slow in this case? Feb 21 '17 at 17:14
  • Do you have this problem with other remote servers?
    – Vérace
    Feb 21 '17 at 17:17
  • I don't have any other to test... My other projects' dbs are either not accessible through the internet or pgsql-based :( Feb 21 '17 at 17:46
  • Can you share the full SSH command you are using and the full command you are using with --host? What is the size on disk of dump.sql? How long does it take to transfer dump.sql with scp from the remote machine to your local machine? Is compression turned on for ssh? If not you could change the end of your command slightly to be mysqldump ... | gzip -c > dump.sql.gz.
    – volker238
    Feb 22 '17 at 17:28

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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