Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I have a VPS with Kloxo control panel, I want to have database backup from one of my websites in every 2 hour. How i can do this? Kloxo doesnt support Hourly backups, only support daily, weekly and monthly.

configuration: CentOS+Apache+PHP+MysqlAdmin

share|improve this question

3 Answers 3

up vote 2 down vote accepted

Write a small script like:

#!/bin/bash
#
# Do the Backup
#
CURTIME=`date "+%Y%m%d-%H%M"`

mysqldump --user=<dbusername> --password=<dbpassword> --all-databases | lzma -c -9 -z >/backup/db-${CURTIME}.dump.lzma

and put this to crontab. (It would be easier to run in hourly: then you can just put the script in /etc/cron.hourly.

share|improve this answer

Here is the bash script I have running on my webserver. It has served me well for some time now.

It includes a part at the end to remove old backups. You can specify the number of files you would like to keep in the variables section. You have to uncomment that part of the script to get it to run.

#!/bin/sh

#################################################################
#  Define your variables here:
#################################################################

FILESTOKEEP=7
BACKUP_DIR=/home/user/backups
BMYSQL_USER=mysql_user
BMYSQL_PWD=mypassword

DATE=$(date +"%m-%d-%Y")_$(date +"%T")

BMYSQL_HOST=localhost
BMYSQL_DBNAME=--all-databases
BMYSQL_DBFILENAME=MYSQL_BACKUP_$DATE


#################################################################
#  Make sure output directory exists.
#################################################################

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


#################################################################
#  Create backup
#################################################################

        mysqldump --host=$BMYSQL_HOST --user=$BMYSQL_USER --pass=$BMYSQL_PWD $BMYSQL_DBNAME | gzip > $BACKUP_DIR/$BMYSQL_DBFILENAME.gz

#################################################################
#  Remove old backups 
#  - this will list files according to date (DESC)
#  - skip the first few files (FILESTOKEEP)
#  - remove all files past that
#  NOTE: Make sure not to save the backups into any directory
#  where there are other files other than these backup ones.
#
#  Uncomment when you are confident in rest of setup
#################################################################

#      cd $BACKUP_DIR
#      ls -t1 | tail -n +$(($FILESTOKEEP+1)) | xargs rm

I like saving my scripts into a directory in my user home. This may not be standard practice, but it sure makes it easy to find and edit them later on (read a couple years later). I would save this script as:

/home/user/scripts/hourly_backup_mysql.sh

To put that in your cron for every two hours open a terminal and type (as root):

crontab -e

In there, enter a new line like this:

0 0-23/2 * * * /home/user/scripts/hourly_backup_mysql.sh

(If you are unfamiliar with VIM, to add that line you will first need to press 'I' for Insert. Then go to the end of the file and press Enter for a new line. Paste the string and edit it. When you are finished editing the string, press ESC to leave the Insert mode. Then type ':wq', which will Write & Quit the VIM editor.)

This will now run every 2 hours and if you uncommented the remove section, will also remove old backups.

Cheers!

share|improve this answer

Per table db dump and gzip and rsync to some place else:

#!/usr/bin/ruby
require 'mysql'

rsyncTargets = [
        ["files1" , "/path/to/backup/dir/"],
        ["files2", "/path/to/backup/dir/"],
        ["files3", "/path/to/backup/dir/"]
          ]

tempDir = "/mnt/extra-space"
dumpUser = "root"

`rm -r /mnt/extra-space/*`

con = Mysql.real_connect('localhost',dumpUser,'','')

con.query('show databases').each do |db|
  print " Dumping - " + db[0] + "\n"
  Dir.mkdir("#{tempDir}/#{db[0]}")
  con.query("use #{db[0]}")
  con.query('show tables').each do |table|
    `mysqldump -u #{dumpUser} #{db[0]} #{table[0]} | gzip > #{tempDir}/#{db[0]}/#{table[0]}.sql.gz`
  end
end
con.close()

rsyncTargets.each do |server|
  `rsync -av --delete /mnt/extra-space/ rsync://#{server[0]}:#{server[1]}`
end

Taken from: http://techhelplist.com/index.php/tech-tutorials/42-databases/80-ruby-script-for-mysql-dump-gzip-cron-jobs

share|improve this answer
    
Nice first post. Keep up the good work! –  slm Dec 24 '12 at 1:45

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.