Plesk & Server Architecture Matt | 18 Oct 2007 10:49 pm
Backup all mysql databases as seperate sql files
When backing up mysql databases via mysqldump you can either specify a database name to backup or use the –all-databases switch to dump all databases in one big file. I’d like to backup all my databases but I’d like them in separate files so they’re easier to manage and I can do a quick restore of a single db if needed.
I wrote the following bash script to help out.
#!/bin/sh
for customerdb in `mysql -uroot -pROOTUSERPASS -e "show databases" -B -N`
do
mysqldump -uroot -pROOTUSERPASS $customerdb > /home/matt/backup/$customerdb.sql
done
Or on my plesk server, I’ve got a table in plesk’s psa database that lists out all the databases created within plesk. A modification to this script reads the table names in the psa database table and makes backups of all those.
#!/bin/sh
for customerdb in `mysql -uadmin -pADMINUSERPASS -e "select name from data_bases" -B -N psa`
do
mysqldump -uadmin -pADMINUSERPASS $customerdb > /home/matt/backup/$customerdb.sql
done
