Dumping all the mysql databases

[garan]

Been meaning to tidy up my database backups for a while. I noticed that the mysql backups that Plesk does only backups up the psa and associated databases (e.g. horde).

Rather than re-invent the wheel, I took a copy of the mysqldump.sh script that's in /usr/local/psa/bin and modified it. I removed the bottom line that does the backup and replaced it with the following:

for dbname in ` mysqlshow -u admin --password=$admin_passwd | grep -v "+" | grep -v "Databases" | tr ["|"] [" "]`; do
  ${MYSQL_BIN_D}/mysqldump --quote-names --user=admin --password=$admin_passwd --database $dbname > /root/mysqlbackups/$dbname.sql && rotate $dbname.sql 8 7 6 5 4 3 2 1 0 ;
done

I'm not pretending that it's neat and welcome any comments or alternative methods..

Cheers,

Garan.

 

[wagnerch]

How about ...

mysqldump --quote-names --user=admin --password=$admin_passwd --all-databases

Only difference is it will create one backup of all databases. It is fairly easy to split out one database, if needed.

 

[garan]

Originally posted by wagnerch
How about ...

mysqldump --quote-names --user=admin --password=$admin_passwd --all-databases

Only difference is it will create one backup of all databases. It is fairly easy to split out one database, if needed.

I did think about doing it that way but decided to go for single files per database for a couple of reasons:

1. Makes it easy to rebuild a database using mysql databasename < databasename.sql
2. If a customer wants a copy of their database I can be certain that they're only getting their own data without having to think about it!
   [/list=1]
   
   Guess it's horses for courses at the end of the day though
   
   Cheers,
   
   Garan.

 

[dasmo]

Thanks! This works great!

Edit: Except for when I update plesk and it overwrites it

I guess I'll save it somewhere else

 

[garan]

Edit: Except for when I update plesk and it overwrites it

Reading through my first post I should have been a little more descriptive about 'taking a copy of the script'. I copied the mysqldump.sh into another place, modified the script and used it from there. My Plesk installation is therefore unchanged.