Backup all plesk MySQL Databases to individual files

Question

Hy,

Because I'm new to shell scripting I need a hand. I currently backup all mydatabases to a single file, thing that makes the restore preaty hard. The second problem that my MySQL password dosen't work because of a Plesk bug and i get the password from "/etc/psa/.psa.shadow". Here is the code that I use to backup all my databases to a single file.

mysqldump -uadmin -p`cat /etc/psa/.psa.shadow` --all-databases | bzip2 -c > /root/21.10.2013.sql.bz2

I found some scripts on the web that backup each database to individual files but I don't know how to make them work for my situation. Here is a example script:

for db in $(mysql -e 'show databases' -s --skip-column-names); do mysqldump $db | gzip > "/backups/mysqldump-$(hostname)-$db-$(date +%Y-%m-%d-%H.%M.%S).gz"; done

Can someone help me make the script above work for my situation? Requirements:

1. Backup each database to individual file using plesk password location.

Answer 1

Try this:

for db in $(mysql -e 'show databases' -s --skip-column-names); do mysqldump -uadmin -p`cat /etc/psa/.psa.shadow` $db | bzip2 -c > "/root/$(date +%d-%m-%Y).sql.bz2"; done

Answer 2

So to backup each database for PLESK to individual files and skip some files I found/made the code:

for db in $(mysql -uadmin -p`cat /etc/psa/.psa.shadow` -e 'show databases' -s --skip-column-names); do
if [[ "$db" != *schema* && "$db" != *phpmyadmin* && "$db" != "event" && "$db" != "psa" && "$db" != "mysql" ]]; then
mysqldump -uadmin -p`cat /etc/psa/.psa.shadow` $db | bzip2 -c > /root/backup/mysql/$db.sql.bz2
fi
done

Feel free to make this code better.

Answer 3

Try:

for db in $(mysql -uadmin -p`cat /etc/psa/.psa.shadow` -e 'show databases' -s --skip-column-names); do mysqldump -uadmin -p`cat /etc/psa/.psa.shadow` --skip-lock-tables $db | gzip > "/var/www/mysql/mysqldump-$(hostname)-$db-$(date +%Y-%m-%d-%H.%M.%S).gz"; done

this works great as used in Plesk server:: Tested use Path as per requirement.