With my hosting provider I have all Drupal sites installed under my public_html directory. Each Drupal instance uses a different database. I could generate a mysqldump command for each instance – but that would become out of date very quickly with the immanent upgrades to version 6. The solution was to create a small script that searches all the sites folders for a settings.php file, extracts the relevant database connection information, and dumps and gzips the database. The database dumps are named according to the database name – all mine are different so no problems there – and the day of the week is also used. So, if the script is run every day, the last seven days of backups are kept.
#!/bin/sh weekday=$(date +"%A") backpath=/usr/home/amadain/files for setting in `find /usr/home/amadain/public_html/*/sites/* -maxdepth 1 -name 'settings.php'` do db_url=`grep '^$db_url' $setting | cut -d -f 3` if [ "${db_url}" != "" ] then username=`echo ${db_url} | cut -d : -f 2 | cut -d / -f 3` if [ "${username}" != "username" ] then password=`echo ${db_url} | cut -d : -f 3 | cut -d @ -f 1` hostname=`echo ${db_url} | cut -d : -f 3 | cut -d @ -f 2 | cut -d / -f 1` database=`echo ${db_url} | cut -d : -f 3 | cut -d @ -f 2 | cut -d / -f 2| cut -d ' -f 1`; echo MySQL dumping ${weekday}'s DB ${database} on host ${hostname} /usr/local/bin/mysqldump -h${hostname} -u${username} -p${password} --opt --lock-tables ${database} | /usr/bin/gzip -9 > ${backpath}/${database}_${weekday}.sql.gz fi fi done