usercore

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
# all db separate files

for customerdb in `mysql -uadmin -pweb7A8u -e "show databases" -B -N`
do
        mysqldump -uadmin -pweb7A8u $customerdb > /var/www/vhosts/mysql/$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 -pweb7A8u -e "select name from data_bases" -B -N psa`
do
        mysqldump -uadmin -pweb7A8u --protocol=tcp --port=3307 $customerdb > /storage/mysqlbackup/$customerdb.sql
done