mysqldump with db in a separate file

Question

I'm writing a single line command that backups all databases into their respective names instead using of dumping all in one sql.

Eg: db1 get saved to db1.sql and db2 gets saved to db2.sql

So far, I'd gathered the following commands to retrieve all databases.

mysql -uuname -ppwd -e 'show databases' | grep -v 'Database' 

I'm planning to pipe it with awk to do something like

awk '{mysqldump -uuname -ppwd $1 > $1.sql}' 

But that doesn't work.

I'm new to bash, so I could be wrong in my thinking.
What should I do to make it export the db in their respective names?

update:
Ok, have to finally managed to get it working from the hints below.
This is the final script

# replace [] with your own config # replace own dir to save # echo doesn't work. hmm...  mysql -u[uname] -p'[pwd]' -e "show databases" \ | grep -Ev 'Database|information_schema' \ | while read dbname; \ do \ echo 'Dumping $dbname' \ mysqldump -u[uanme] -p'[pwd]' $dbname > ~/db_backup/$dbname.sql;\ done 

The echoing part of doesn't work though.

Answer 1

mysql -uroot -N -e 'show databases' | while read dbname; do mysqldump -uroot --complete-insert --some-other-options "$dbname" > "$dbname".sql; done 

Answer 2

Creating backups per database is indeed much more efficient. Not only easier to restore once needed, but also I experienced that making a backup of the whole database would break in case one table was broken/corrupt. And by creating backups per database it will only break for that database and the rest is still valid.

The oneliner we created to backup our mysql databases is:

mysql -s -r -u bupuser -pSecret -e 'show databases' | while read db; do mysqldump -u bupuser -pSecret $db -r /var/db-bup/${db}.sql; [[ $? -eq 0 ]] && gzip /var/db-bup/${db}.sql; done 

Best to create a new readonly mysql user 'bupuser' with passsword 'Secret' (change!). It will first retrieve the list of databases. Then loop and for each database create a dump.sql file to /var/db-bup (you can change). And only when there are no errors encountered then also gzip the file which will really drastically save storage. When some databases encountered errors then you will see the .sql file and not the .sql.qz file.