Home / ubuntu / Questions / 7131
Accepted
user3215
Asked: 2010-10-15 04:34:02 +0800 CST

Backup mysql from terminal

  • 772

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

mysql

2 Answers

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

    • 12

  2. 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
    • 2