Find the answer to your Linux question:
Page 1 of 4 1 2 3 4 LastLast
Results 1 to 10 of 40
Right now I use below script to get a full backup of a db mysqldump --opt -Q -u DATABASEUSER --password=PASSWORD -h DBHOST DBNAME | gzip > /home/524124/arcade.gz But now i ...
Enjoy an ad free experience by logging in. Not a member yet? Register.
  1. #1
    Just Joined!
    Join Date
    Aug 2012
    Posts
    54

    Exclamation Cron Job for backing up multiple dbs at once


    Right now I use below script to get a full backup of a db

    mysqldump --opt -Q -u DATABASEUSER --password=PASSWORD -h DBHOST DBNAME | gzip > /home/524124/arcade.gz

    But now i have so many dbs and my hosting allows only 5 cron jobs. So I have to either write a .sh to execute which will get a backup of each DB in order and one after each other. Not all at the same time.
    Or
    I have to come up with one command which will get backups of all DBs but right after each other. Not all at the same time.

    Can somebody help me please?

  2. #2
    Just Joined! mrbruno's Avatar
    Join Date
    Jan 2013
    Location
    /MilkyWay/Sol/Earth/USA/NC/Raleigh
    Posts
    60
    You might do them in background jobs which will run asynchronously. The script need consist of only:

    Code:
    mysqldump --opt -Q -u DATABASEUSER --password=PASSWORD -h DBHOST DBNAME | gzip > /home/524124/arcade.gz &
    mysqldump --opt -Q -u DATABASEUSER --password=PASSWORD -h DBHOST DBNAME2 | gzip > /home/524124/dbname2.gz &
    This way, you just have cron run the script as a one command which does things concurrently.

    You could even modularize it:

    Code:
    function dobackup {
      mysqldump --opt -Q -u $1 --password=$2 -h $3 $4 | gzip > /home/524124/${4}.gz &
    }
    
    dobackup DATABASEUSER PASSWORD DBHOST DBNAME
    dobackup DATABASEUSER PASSWORD DBHOST DBNAME2
    You could put a lot of bells and whistles on this: adding a date to the file name, cleaning up old backups, saving stdout/stderr, etc. If you wanted, you could have cron kick off your own sort of scheduler in case you wanted to do backups on different intervals: db1 every 12 hours, db2 every 2 days, etc.

  3. #3
    Just Joined!
    Join Date
    Aug 2012
    Posts
    54
    That should so good.

    Now my first question is the below one should be saved as FILE.sh and run from the cron job right?
    Code:
    function dobackup {
      mysqldump --opt -Q -u $1 --password=$2 -h $3 $4 | gzip > /home/524124/${4}.gz &
    }
    
    dobackup DATABASEUSER PASSWORD DBHOST DBNAME
    dobackup DATABASEUSER PASSWORD DBHOST DBNAME2
    also
    I dont know what is stdout/stderr
    Also how can make my own schedule in the .sh file. Can you give me an example?

  4. #4
    Just Joined! mrbruno's Avatar
    Join Date
    Jan 2013
    Location
    /MilkyWay/Sol/Earth/USA/NC/Raleigh
    Posts
    60
    Quote Originally Posted by Website View Post
    Now my first question is the below one should be saved as FILE.sh and run from the cron job right?
    You might want a name more descriptive like dbbackups.sh. Use a full path name to refer to it in the cron statement. If I were using crontab to add it for myself, I might use a statement like:

    Code:
    0 0 * * * /home/524124/dbbackups.sh
    to have it run every night at midnight.

    Quote Originally Posted by Website View Post
    I dont know what is stdout/stderr
    Basically, stdout and stderr is anything usually written to the screen by the shell or commands. In this example, I guess stdout is going to be your backup so that's actually already taken care of. stderr will usually have error messages which you may wish to keep them: database unavailable, out of disk space, etc. You could so something like:

    Code:
    0 0 * * * /home/524124/dbbackups.sh 2>>/home/524124/dbbackups.stderr
    Quote Originally Posted by Website View Post
    Also how can make my own schedule in the .sh file. Can you give me an example?
    That could be pretty advanced, depending on how flexible you wanted to make it. It might be better to stick with something simple for now but it is possible.

  5. #5
    Just Joined!
    Join Date
    Aug 2012
    Posts
    54
    Ok. Below is what i have in backup.sh

    Code:
    function dobackup {
      mysqldump --opt -Q -u $1 --password=$2 -h $3 $4 | gzip > /home/999999/domains/${4}.gz &
    }
    
    dobackup db999999 PASSWORD internal-db.s999999.gridserver.com db999999_arcade.gz
    dobackup db999999 PASSWORD internal-db.s999999.gridserver.com db999999_gamificationprogramguide.gz
    It gives me only db999999_arcade.gz.gz and that is all. So it puts .gz 2 times and it doesnt give me db999999_gamificationprogramguide.gz at all.
    Plus when i try to download and unzip the db999999_arcade.gz.gz it says the file is corrupted

  6. #6
    Just Joined!
    Join Date
    Aug 2012
    Posts
    54
    I wrote it like this as cron job command but it says command is not correct

    Code:
     mysqldump --opt -Q -u db999999 --password=PASSWORD -h internal-db.s131092.gridserver.com db999999_gsc | gzip > /home/999999/domains/db999999_arcade.gz & mysqldump --opt -Q -u db999999 --password=PASSWORD -h internal-db.999999.gridserver.com db999999_arcade | gzip > /home/999999/domain/db999999_gamificationprogramguide.gz

  7. #7
    Just Joined! mrbruno's Avatar
    Join Date
    Jan 2013
    Location
    /MilkyWay/Sol/Earth/USA/NC/Raleigh
    Posts
    60
    Quote Originally Posted by Website View Post
    Ok. Below is what i have in backup.sh

    Code:
    function dobackup {
      mysqldump --opt -Q -u $1 --password=$2 -h $3 $4 | gzip > /home/999999/domains/${4}.gz &
    }
    
    dobackup db999999 PASSWORD internal-db.s999999.gridserver.com db999999_arcade.gz
    dobackup db999999 PASSWORD internal-db.s999999.gridserver.com db999999_gamificationprogramguide.gz
    It gives me only db999999_arcade.gz.gz and that is all. So it puts .gz 2 times and it doesnt give me db999999_gamificationprogramguide.gz at all. ...
    You've got some of the parameters to dobackup confused. You're passing in the name of the backup file as the fourth parameter but you don't want to do that because it's used for two purposes:

    1. the name of the database to backup
    2. to build the name of the backup file - you only pass in a part of the name and the function supplies the rest, including the parent directory


    Just pass in the name of the database.

  8. #8
    Just Joined! mrbruno's Avatar
    Join Date
    Jan 2013
    Location
    /MilkyWay/Sol/Earth/USA/NC/Raleigh
    Posts
    60
    Quote Originally Posted by Website View Post
    I wrote it like this as cron job command but it says command is not correct

    Code:
     mysqldump --opt -Q -u db999999 --password=PASSWORD -h internal-db.s131092.gridserver.com db999999_gsc | gzip > /home/999999/domains/db999999_arcade.gz & mysqldump --opt -Q -u db999999 --password=PASSWORD -h internal-db.999999.gridserver.com db999999_arcade | gzip > /home/999999/domain/db999999_gamificationprogramguide.gz
    This might accomplish some of the things you want but you want to use a double ampersand (&&) to have the second command execute after the first one executes successfully. A single ampersand is used to start a background job. Even if it's used in the middle like that, I think the shell interprets it as a single command and everything after the ampersand are just operands.

    Two problems with this approach:

    1. The second command doesn't start until the first one completes successfully
    2. If the first command fails for whatever reason, the second one will not run

  9. #9
    Just Joined! msohail's Avatar
    Join Date
    Nov 2011
    Posts
    47
    Quote Originally Posted by Website View Post
    Right now I use below script to get a full backup of a db

    mysqldump --opt -Q -u DATABASEUSER --password=PASSWORD -h DBHOST DBNAME | gzip > /home/524124/arcade.gz

    But now i have so many dbs and my hosting allows only 5 cron jobs. So I have to either write a .sh to execute which will get a backup of each DB in order and one after each other. Not all at the same time.
    Or
    I have to come up with one command which will get backups of all DBs but right after each other. Not all at the same time.

    Can somebody help me please?
    You can use this script in cron job. You can add the tarring feature if you are interested in saving disk space.

    Code:
    count=2
    i=`mysql -uroot -pPASSWORD -e "show databases;" | awk '{print $1}' | wc -l`
    while [ $count -le $i ]
    do
            db=`mysql -uroot -pPASSWORD -e "show databases;" | awk '{print $1}' | sed -n "$count"p`
            if [ "$db" == "information_schema" ] || [ "$db" == "performance_schema" ] || [ "$db" == "mysql" ];
            then
                    ((++count))
                    echo "Skipping" >> /dev/null
            else
                    mysqldump -uroot -pPASSWORD $db > /opt/test/$db.sql
                    ((++count))
            fi
    done
    Jazak Allah
    Sohail

  10. #10
    Just Joined!
    Join Date
    Aug 2012
    Posts
    54
    This might accomplish some of the things you want but you want to use a double ampersand (&&) to have the second command execute after the first one executes successfully. A single ampersand is used to start a background job. Even if it's used in the middle like that, I think the shell interprets it as a single command and everything after the ampersand are just operands.
    That code looks the easiest and the best but I couldnt get it to work.
    It works perfectly when i put only one command like this
    mysqldump --opt -Q -u db999999 --password=PASSWORD -h internal-db.s131092.gridserver.com db999999_gsc | gzip > /home/999999/domains/db999999_arcade.gz
    but when i put && or & & it says the format of the command is wrong

    so If somehow i can tie the commands to each other, it will work perfectly


    Quote Originally Posted by msohail View Post
    You can use this script in cron job. You can add the tarring feature if you are interested in saving disk space.

    Code:
    count=2
    i=`mysql -uroot -pPASSWORD -e "show databases;" | awk '{print $1}' | wc -l`
    while [ $count -le $i ]
    do
            db=`mysql -uroot -pPASSWORD -e "show databases;" | awk '{print $1}' | sed -n "$count"p`
            if [ "$db" == "information_schema" ] || [ "$db" == "performance_schema" ] || [ "$db" == "mysql" ];
            then
                    ((++count))
                    echo "Skipping" >> /dev/null
            else
                    mysqldump -uroot -pPASSWORD $db > /opt/test/$db.sql
                    ((++count))
            fi
    done
    Jazak Allah
    Sohail
    Can you please give me more info about this?
    Shoudl i put this in a .sh?
    Which parts exactly I should replace with my User, Pass, path and DBnames?

Page 1 of 4 1 2 3 4 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •