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?

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.

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?

Quote:

---

Originally Posted by Website

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

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

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.

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

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

---

Quote:

---

Originally Posted by Website

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.

Quote:

---

Originally Posted by Website

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

Quote:

---

Originally Posted by Website

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

Quote:

---

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

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?