Running mysqldumps in parallel?

[bbennett@cortex.net]

Hello. I have a bash script with a line like this in it:

mysqldump -uusername -ppassword --master-data=2 --single-transaction --quick --add-drop-table --add-drop-database somedatabase | gzip > ./somedatabase.sql.gz

The problem is, I have about 16 databases I'm dumping like this, but it's dumping them sequentually. How do I do all of these dumps in parallel?

mysqldump -uusername -ppassword --master-data=2 --single-transaction --quick --add-drop-table --add-drop-database somedatabase | gzip > ./somedatabase.sql.gz &

Doing the above doesn't seem to work. The job just appears to hang and doesn't go anywhere. I'm guessing this is because gzip is being backgrounded and mysqldump doesn't like that because it has nowhere to put the output to.

So, does putting in a & after a command work in a bash script at all? The thought was, if I want to run them in parallel, should I do this?

#!/bin/bash
./scripts/dumpthis &
./scripts/dumpthat &
./scripts/dumpsomemore &

Where "dumpthis" is just the first snippet of code above.

Ideas?

[Irithori]

Dumping the 16 databases in parallel *only* makes sense, if they are on 16 different raids/drives AND also the dump directory(s) are separated.

Otherwise you will just cause massive IO load and end up with potentially all your databases locked at once (depending what database engine you use)

What are you trying to achieve?

[bbennett@cortex.net]

Thanks for the fast reply.

The database engine is mySQL.

Now, the reason for this is that the database dumps are taking about 10 hours to run. If there's a way I can shorten that by running them all at once instead of one after another, that would be great.

I don't care about massive I/O load, I just want the database dumps to take less than 8 hours to run.

[Irithori]

Maybe I should be more clear:
If all the DBs are on the same disc, parallel dumps would fight for one ressource: IO
Effectively blocking each other.
The dumps will take much longer than 10h.

About tuning mysql:
Depends on the DB engine (innodb, myisam, etc), the typical workload, etc
So you need to find the right settings for your setup.

But in general:
- 2 machines with 8 DBs each might be a start
- DBs love raid10s consisting of a lot of small and fast discs like 146GB 15k
- assigning DBs to different raids will enable the possibilty for parallel dumps