Ubuntu – Backup thesql from terminal

MySQL

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

Best Answer

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

Related Question