CODEWITHSUNDEEP
mysqlcron
Edward1442
Edward1442

Reputation: 143

Backup multiple MySQL Databases by Cron simply

I'm wondering what the best way would be to create a backup of every mysql database that I have with a certain prefix, on a daily basis.

Currently I have cron setup with rows for each database, which I add to manually whenever I create a new one:

0 0 * * *    root    mysqldump -u backup -p 123456 website_website1 | gzip > /var/backups/websites/daily/website1_daily_`date +\%d-\%m-\%y`.sql.gz

What I would like to be able to do is have a script which checks for each database with the website prefix and dump it to its own file.

How can I achieve this?

Upvotes: 1

Views: 246

Answers (1)

AndySavage
AndySavage

Reputation: 1769

You can enumerate all of the databases, and check them for a prefix with a simple shell script. Something like ...

echo show databases | mysql -u backup -p 123456 | while read database; do
    if  [[ $database == my_prefix* ]]; then
        mysqldump -u backup -p 123456 $database | gzip > /var/backups/websites/daily/${database}_`date +\%d-\%m-\%y`.sql.gz
    fi
done

(untested)

Upvotes: 2

Related Questions