Edward1442 Edward1442 - 2 months ago 17
MySQL Question

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?

Answer Source

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