mysqldump - Dump multiple databases from separate mysql accounts to one file -

the standard mysqldump command use is

mysqldump --opt --databases $dbname --host=$dbhost --user=$dbuser --password=$dbpass | gzip > $filename 

to dump multiple databases

mysqldump --opt --databases $dbname1 $dbname2 $dbname3 $dbname_etc --host=$dbhost --user=$dbuser --password=$dbpass | gzip > $filename 

my question how dump multiple databases different mysql accounts 1 file?

update: when meant 1 file, mean 1 gzipped file difference sql dumps different sites inside it.

nobody seems have clarified this, i'm going give 2 cents.

going note here, experiences in bash, , may exclusive it, variables , looping might work different in environment.

the best way achieve archive separate files inside of use either zip or tar, prefer use tar due simplicity , availability.

tar doesn't compression, bundled bzip2 or gzip can provide excellent results. since example uses gzip i'll use in demonstration.

first lets attack problem of mysql dumps, mysqldump command not separate files (to knowledge anyways). lets make small workaround creating 1 file per database.

mysql -s -r -p$dbpass --user=$dbuser -e 'show databases' | while read db; mysqldump p$dbpass --user=$dbuser $db > ${db}.sql; done 

so have string show databases per file, , export databases out ever need edit part after > symbol

next lets add @ syntax tar

tar -czf <output-file> <input-file-1> <input-file-2> 

because of configuration allows specify great number of files archive.

the options broken down follows.

c - compress/create archive

z - gzip compression

f - output file

j - bzip compression

our next problem keeping list of newly created files, we'll expand our while statement append variable while running through each database found inside of mysql.

dblist=""; mysql -s -r -p$dbpass --user=$dbuser -e 'show databases' | while read db; mysqldump p$dbpass --user=$dbuser $db > ${db}.sql; dblist="$dblist $db";  done  

now have dblist variable can use have output of our files created, can modify our 1 line statement run tar command after every thing has been handled.

dblist=""; mysql -s -r -p$dbpass --user=$dbuser -e 'show databases' | while read db; mysqldump p$dbpass --user=$dbuser $db > ${db}.sql; dblist="$dblist $db";  done && tar -czf $filename "$dblist" 

this rough approach , doesn't allow manually specify databases, achieve that, using following command create tar file contains of specified databases.

dblist=""; db in "<database1-name> <database2-name>"; mysqldump -p$dbpass --user=$dbuser $db > ${db}.sql; dblist="$dblist $db.sql";  done && tar -czf $filename "$dblist" 

the looping through mysql databases mysql database comes following stackoverflow.com question "mysqldump db in separate file" modified in order fit needs.

and have script automatically clean in 1 liner add following @ end of command

&& rm "$dblist" 

making command this

dblist=""; db in "<database1-name> <database2-name>"; mysqldump -p$dbpass --user=$dbuser $db > ${db}.sql; dblist="$dblist $db.sql";  done && tar -czf $filename "$dblist" && rm "$dblist"