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
Hi, Great script! But how
Hi,
Great script!
But how about when in one MYSQL database are more than one Drupal-site database. With using the database PREFIX off course?
Thanks for your answer in advance,
greetings,
Martijn
db_prefix to limit tables
db_prefix to limit tables
#!/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`
db_prefix=`grep '^$db_prefix' $settings | cut -d\' -f 2`
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`;
if [ "${db_prefix}" != "" ]
then
tables=`echo show tables | /usr/local/bin/mysql -E -h${hostname} -u${username} -p${password} ${database} | grep Tables_in_ | cut -d \ -f 2i | grep ^${db_prefix}`
if [ "${tables}" != "" ]
then
tables=`echo --tables ${tables}`
fi
fi
echo MySQL dumping ${weekday}\'s DB ${database} on host ${hostname}
/usr/local/bin/mysqldump -h${hostname} -u${username} -p${password} --opt --lock-tables ${database} ${tables} | /usr/bin/gzip -9 > ${backpath}/${database}_${weekday}.sql.gz
fi
fi
done
If a db_prefix exists, I search tables beginning with that prefix. If some tables are found with that prefix, I restrict the backup to those tables (--tables ....). Otherwise all tables are backed up.
Could work, should work....
Post new comment