Miskatonic University Press

Backing up multiple MySQL databases when only one [client] section is allowed

unix

I now have two MySQL databases at my hosting provider that I want to back up regularly: one for the FRBR Blog, one for this site. When I just had one database, I put the username and password in .my.cnf: 

# MySQL configuration file
[client]
user=my_username
password=my_s3cr3t_p@ssw0rd
host=db99.pair.com

Then I could say mysqldump --complete-insert --add-drop-table wdenton_frbr | gzip > $DATE-frbr.sql.gz and it would dump the wdenton_frbr database with the username and password from the file, saving me from having to specify them on the command line where other people might see them.

Bizarrely, there is no way to specify multiple [client] entries in .my.cnf. The --defaults-extra-file option mentioned there didn't work for me, so I made .my.cnf.frbr and .my.cnf.drupal6, each with different information, and then wrote two shell scripts that look like this: 

#!/bin/sh
DATE=`date "+%Y%m%d"`
cp ~/.my.cnf.frbr ~/.my.cnf
mysqldump --complete-insert --add-drop-table  wdenton_frbr | \
  gzip > ~/$DATE-frbr.sql.gz
rm ~/.my.cnf