Thesqldump takes forever to dump tables from a remote server

backupmysqldumpschema-copy

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.

Best Answer

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.

Related Solutions

Mysql – Can thesqldump dump triggers and procedures

I normally do not separate triggers from the tables they were meant for. I dump like this:

mysqldump -u... -p... --no-data --routines --triggers dbname > DBSchema.sql

Check for presence of routines and triggers like this:

SELECT COUNT(1) FROM mysql.proc;

SELECT COUNT(1) FROM information_schema.triggers;

SELECT * FROM information_schema.triggers\G

If you want to get this done to all DBs in the MySQL Instance, do this:

mysql -u... -p... -A -N -e"SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN ('information_schema','mysql')" > /tmp/dblist.txt
for DB in `cat /tmp/dblist.txt`
do
    mysqldump -u... -p... --no-data --no-create-info --routines dbname > ${DB}-routines.sql
    mysqldump -u... -p... --no-data --triggers dbname > ${DB}-schema-triggers.sql
done

That way, stored procedures go in a routines dump for the DB, while the schema and triggers go in another dump.

MySQLDump wrong dump

Just reading the header you put in the question shows something interesting. In fact, the question shows three things:

MySQL dump 10.13 Distrib 5.1.34, for apple-darwin9.5.0 (i386) indicates you used mysqldump from apple-darwin9.5.0 (i386) binaries
Server version 5.0.51a-24+lenny2 shows the version of mysql you used mysqldump to dump from.
You wanted to load the mysqldump file into Ver 14.14 Distrib 5.1.57, for apple-darwin10.3.0 (i386) using readline 5.1

What a jumble of versions to do this with.

If you want to see if mysqldump has an issue with the line that has DATEDIFF, try dumping just the schema.

mysqldump --no-data --all-databases ... > MySQLSchema.sql

This will display ony the schema. No INSERTs will be in the output. You can then hunt down that lines. You may also want to dump the data onyl without the schema,

mysqldump --no-create-info --all-databases ... > MySQLData.sql

Splitting the dumps allows you to load the schema into an editor and see if there are any problems. If you do not see any problems, load the MySQLSchema.sql into the target server. If the error is reproduced, you can fix the schema file and reload. Once the schema is loaded, you can separate load MySQLData.sql

BTW you should use mysqldump binary whose version is 5.0.51a-24+lenny2. Use dumps from version as mysqld is usually better to port and may minimize problems like this.

Give it a Try !!!

Best Answer

Related Solutions

Mysql – Can thesqldump dump triggers and procedures

MySQLDump wrong dump

Related Question