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

2 Answers 2

up vote 9 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

Yes there is.

For those who have multiple databases lets say 50 - 100, entering it one after the other will be pain staking

You will have to create a bash/shell file i.e file with ".sh" extension, after which you will make it executable by changing it mod

chmod 777 <file_name>.sh

then enter your code above with this few alterations;

what we are doing here is query mysql for the list of database(s) and puting the output in a loop for each turn

database=`mysql --user=<enter username> --password=<enter password> -e "SHOW DATABASES"`
for db in $database; do
    echo "$db"
    mysqldump --user="<enter username>" --password="<enter password>" --databases "$db" > <path_to_store_your_database>/"$db".sql
    gzip "$db"
done

Finally you will add the file to crontab and shedule the backup time. You can also run the command manually by typing:

bash <name of the file>.sh

or

sh <name of the file>.sh
share|improve this answer

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.