Take the 2-minute tour ×
Ask Ubuntu is a question and answer site for Ubuntu users and developers. It's 100% free, no registration required.

I have scheduled a cron job to backup mysql databases on ubuntu9.10. I used the following entries in the cron file.

37 4 * * * root /usr/bin/mysqldump -u root -p<mysql-password> --all-databases > /usr/backup/mysqlbkup/all-databases.sql

It backs up all the databases puts in a single file called 'all-databases.sql'. This is what I wanted. But if I want to restore a single database I will have to restore all the databases. Is there any trick to backup all mysql databases to a directory with their database name individually?.

share|improve this question
add comment

1 Answer

up vote 7 down vote accepted

I suggest you read: http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html

You can dump different databases with: mysqldump [options] --databases DB1 [DB2 DB3...]

You just have to add a cronjob for each database:

37 4 * * * root /usr/bin/mysqldump -u root -p<mysql-password> --databases database1 > /usr/backup/mysqlbkup/databases1.sql
38 4 * * * root /usr/bin/mysqldump -u root -p<mysql-password> --databases database2  database3 > /usr/backup/mysqlbkup/databases2_3.sql

Ofcourse you could make one bash script for all different databases and run that script from the crontab. Also i would gzip it up.

Goodluck

share|improve this answer
1  
Good answer. I thought it could be achieved in a single command –  user3215 Oct 14 '10 at 12:53
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.