Backup mysql from terminal

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?.

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

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