Hi Guys,

I’d really appreciate if anyone can help me setting up a script/cronjob that would automatically backup all the SQL databases in my cPanel. Here is the full story :)

I have a VPS on which run a site and a forum. Both have SQL databases. For safety I backup the entire server which is 20GB using rsync with a backup plan I purchased from a separate vendor. But I figured out that instead of backing up the entire 20GB server It’d save me a few bucks and other additional trouble if I backup only the public html folder and the databases. For my site and forum, there’s no special requirement except php and MySQL , so any default cPanel configuration would be enough (I don’t need any stat files or logs either).

I have both whm and cPanel and can set up rsync backup for the public html folder. But can anyone suggest me a way that would automatically backup all the databases (currently I have two DBs but planning to put up a blog as well) in my cPanel, and mail me the backups or save them in some location. I have a very basic understanding about cPanel and Linux server administration so basically what I’m talking about is something like the automatic wordpress plugin that backups the wordpress DB and mails it to me.

Appreciate if anyone can help me on this. As I have a limited knowledge on cronjobs and stuff, please guide me through the process. (whether to do it in whm or cPanel, and if so, how etc.)

P.S. I need the DB backup frequency to be every four hours as my site is getting updated every minute by users.

Thanks a lot!!

Doesn't anyone know how this can be done?? :( :((. Please, if at least someone knows a good article or some source, post it here....

Hi,

I suggest backing up the bulk database every hour wont be so efficient. Try mysql with innoDB as storage. And configure fail-over mysql server.

shahs21

You can set this up as a cron job, and then set the timing accordingly:

mysqldump -u db_user --password=db_password db_name > /home/username/file.sql

Another way would be to use a php script and run it through cron to backup your db's.

How do you make the command save the backup to another ftp instead of the current hosting?

Hi Website_Ist,

If you have CPanel, you can use our free MySQL backup script located at: www .hostliketoast .com/developer-channel/
(apparently I can't post links yet...silly forum...I'm actually trying to help...urrghh)

Run this on the server that would store the backup. This does away with the need for FTP as it simply fetched a backup from the CPanel interface.

Hope that helps.

Dave.

Yeah but i dont wanna run a script. I want to do it with a command through cpanel.

I already got the first part. I can get a backup. But it saves the backup in my ftp and my hosting company says i cant store my backup files in the same ftp. So i need my cron job to store the backup files somewhere else.

PLEASE, PLEASE TEST BEFORE USE IN PRODUCTION!!

#

logfile="/var/log/backupalldatabases.log"

backup_dir="/backupdir/"

username="root"
password="xxxxxxxxxxxxxx"


if [ ! -d $backup_dir ];
then
mkdir -p $backup_dir
fi


#Find and delete backups with more than 7 days
for i in $(find $backup_dir -iname "*.bz2" -mtime +7); do rm -f $i; done

#Create a new log
rm -rf $logfile
touch $logfile

#Get date and time
timeslot=`date +%Y%m%d_%H%M`

cd $backup_dir

# Get all databases that user(root) can read
for BBDD in $( mysql -u $username --password=$password -h localhost -Bse 'show databases' ); do

# Dumping DB to temp file
mysqldump --user=$username --password=$password $BBDD > $BBDD.sql

# compressing $BBDD.sql
gzip -9 $BBDD-$timeslot.gz $BBDD.sql

echo "Backup : $BBDD ($BBDD-$timeslot.tar.bz2)" >> $logfile

# Borramos el fichero temporal
rm $BBDD.sql

# ncftpput options
# -m Attempt to mkdir the dstdir before copying.
# -DD Delete local file after successfully uploading it.
# -y Try using "SITE UTIME" to preserve timestamps on remote host.
# -R Recursive mode; copy whole directory trees.

##ncftpput -u $username -p $password -m -DD -y -R $remotehost $remotedir $files
# or maybe, rsync with keys
#rsync options
# -a, --archive archive mode; equals -rlptgoD (no -H,-A,-X)
# --delete delete extraneous files from dest dirs

##rsync -az --delete /backup/ backupuser@remotehost:/backup/

done
#-------------------------------------------------
#
# Send log by email
#
cat $logfile|mail -s "Proceso Backup $HOSTNAME = MySQL" email@address.com

Is this a cron job command?
Cause i need a cron job command.

Perhaps I should breathe for you?

¬¬

59 23 * * * /path/to/script

....
Edit: and you say that ' Main >> Backup >> Configure Backup' is not working for you? Configure Backup can send backup over ftp

I dont have a VPS, i use cron jobs in cpanel. That is why i just need to command to store the backup file in another place then my host because the hosting company told me not to keep the backup files there.

Here a cronjob I've been using for years to backup my database (in the same account). It backs up one database. Of course you can repeat as many cronjobs as you need for your other databases. The way this works is that it backs up the database, then compress it to save space, and name it according to the day the DB was backed up. Again, repeat for how many days you want to perform the backup.

mysqldump --add-drop-table --user=DB_USERNAME --password=PASSWORD DB_NAME | gzip >/DESTINATION/FOLDER/NAME/FORUM_MON.dmp.gz

Replace DB_USERNAME with your DB username
Replace PASSWORD with db user password
Replace /DESTINATION/FOLDER/NAME/ with the destination where you want to save the backup file
Replace FORUM with any word you want to call your backup file with.
**Pay special attention to spaces in the above command.

Then repeat the same command, changing only the "MON" part of the file.
For example, you can repeat the command 7 times, changing each with the name of the week. This will give you 7 separate backup files, and they will not be over-written until the next week, which gives your space in case something went wrong and it was several days before you knew it.

As for the timing, type something like this for the MON command:
10 3 * * 1
This will trigger the backup process at 03:10am each Monday.

And type something like this for the TUE command:
10 3 * * 2
This will trigger the backup process at 03:10am each Tuesday.

Do the same for each command your add, changing only the last number:
for SUN type 0
for MON type 1
for TUE type 2
for WED type 3
for THU type 4
for FRI type 5
for SAT type 6


With the same idea, you can do 4-hour backups as you wish.
Best wishes.

Hi,

Start mysql binary logging for backups.
I think its one of the BEST and least resource intensive.

Here a cronjob I've been using for years to backup my database (in the same account). It backs up one database. Of course you can repeat as many cronjobs as you need for your other databases. The way this works is that it backs up the database, then compress it to save space, and name it according to the day the DB was backed up. Again, repeat for how many days you want to perform the backup.

mysqldump --add-drop-table --user=DB_USERNAME --password=PASSWORD DB_NAME | gzip >/DESTINATION/FOLDER/NAME/FORUM_MON.dmp.gz

Replace DB_USERNAME with your DB username
Replace PASSWORD with db user password
Replace /DESTINATION/FOLDER/NAME/ with the destination where you want to save the backup file
Replace FORUM with any word you want to call your backup file with.
**Pay special attention to spaces in the above command.

Then repeat the same command, changing only the "MON" part of the file.
For example, you can repeat the command 7 times, changing each with the name of the week. This will give you 7 separate backup files, and they will not be over-written until the next week, which gives your space in case something went wrong and it was several days before you knew it.

As for the timing, type something like this for the MON command:

This will trigger the backup process at 03:10am each Monday.

And type something like this for the TUE command:

This will trigger the backup process at 03:10am each Tuesday.

Do the same for each command your add, changing only the last number:
for SUN type 0
for MON type 1
for TUE type 2
for WED type 3
for THU type 4
for FRI type 5
for SAT type 6


With the same idea, you can do 4-hour backups as you wish.
Best wishes.

I have been using the same method. Even almost the same command but my problem is how to save it to another location other than your ftp? My hosting company doesnt let me store the file in the same account.