Cron Job for backing up multiple dbs at once

Quote:

---

Originally Posted by Website

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




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?

---

The only thing you need to replace in my script is PASSWORD and replace it with your actual mysql root password, like

-pPASSWORD to -pYOUR_MYSQL_ROOT_PASSWORD (DONT PUT SPACE AFTER -p like -p MYSQL_PASSWORD)

If you have mysql server installed in multiple machines (servers) the you can add -hIPADDRESS like -h192.168.1.2 in the script; if you have all databases in a single server then you can skip '-h' attribute.
If you want to put the backup files in some other destination then change /opt/test/$db.sql to
/YOUR_DESTINATION/$db.sql

Make sure that the script is having executable permissions.

Jazak Allah
Sohail

Quote:

---

Originally Posted by msohail

The only thing you need to replace in my script is PASSWORD and replace it with your actual mysql root password, like

-pPASSWORD to -pYOUR_MYSQL_ROOT_PASSWORD (DONT PUT SPACE AFTER -p like -p MYSQL_PASSWORD)

If you have mysql server installed in multiple machines (servers) the you can add -hIPADDRESS like -h192.168.1.2 in the script; if you have all databases in a single server then you can skip '-h' attribute.
If you want to put the backup files in some other destination then change /opt/test/$db.sql to
/YOUR_DESTINATION/$db.sql

Make sure that the script is having executable permissions.

Jazak Allah
Sohail

---

Didnt work. it didnt do anything. No error either. Weird.

Quote:

---

Originally Posted by Website

Didnt work. it didnt do anything. No error either. Weird.

---

Can you paste the script with the credentials and other details that u used to take the backup ?

Jazak Allah
Sohail

I cant write the User, Pass and the site address but the rest is right.

Code:

---

count=2
i=`mysql -uDBUSERNAME -pPASSWORD -e "show databases;" | awk '{print $1}' | wc -l`
while [ $count -le $i ]
do
        db=`mysql -uDBUSERNAME -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 -uDBUSERNAME -pPASSWORD $db > /nfs/c0434/hf0541/mnt/1162744/domains/site.com/html/backups/$db.sql
                ((++count))
        fi
done

---

I see that you did NOT replace the words DBUSERNAME and PASSWORD with your actual database username and password. Also check if the NFS mount point (if you are usingit) is writable or not. If not, then make it.

Jazak Allah
Sohail

As I stated above, I can't give you my user and pass but in the original script I replaced them with right info. Also the path is writable

Sorry for that. Dint realize you dint want to give out the credentials. In your case, it seems that the script is going in the IF loop. Can you give me the output of this script ?

Code:

---

mysql -uDBUSERNAME -pPASSWORD -e "show databases;" | awk '{print $1}'

---

Hope you are using username as root !!

Jazak Allah
Sohail

My username is not root. I dont have root proviliges. but i have ssh and i put the codes in a sh file and ran it through cron jobs.

I dont get any error or anything when i run the script through cron job. How can i get a output?

Here's a link to a script I wrote and use for backups of my site. Just uncomment/copy the lines for however many DBs you have. Includes compression and encryption.
Backup MySQL database
Not sure if your hosting provider's server includes the command mutt, but it's required for the automated email feature of the script. Probably will want to comment that out if they don't have mutt.

Works perfectly. Thank you.