Skip to main content

backing up a mysql DB incrementally using mysql dump 

This script will backup your mysql DB using mysqldump, but because mysql dump locks the entire DB, this script only backs up one table at a time, thus only locking one table at a time. 
 
 
#!/bin/sh
# System + MySQL backup script
# Copyright (c) 2008 Marchost
# This script is licensed under GNU GPL version 2.0 or above
# Modified by dfish to use rsync
# ---------------------------------------------------------------------

echo `date`

#########################
######TO BE MODIFIED#####

### System Setup ###
BACKUP=/backups/

### MySQL Setup ###
MUSER="foo"
MPASS="bar"
MHOST="localhost"

### REMOTE SETUP ###
RUSER="root"
RHOST="192.168.0.70"
RPATH="/backups/db/."

### MYSQL PATHS ### 
MYSQL="/home/mysql/mysql-standard-4.1.13-pc-linux-gnu-i686/bin/mysql"
MYSQLDUMP="/home/mysql/mysql-standard-4.1.13-pc-linux-gnu-i686/bin/mysqldump"
 
 
######SHOULD NOT NEED TO MAKE MODIFICATION BELOW#####
#########################################

### Binaries ###
TAR="$(which tar)"
GZIP="$(which gzip)"
FTP="$(which ftp)"


### Today + hour in 24h format ###
NOW=$(date +"%A")

### Create hourly dir ###
rm -rf $BACKUP/$NOW
mkdir $BACKUP/$NOW

### Get all databases name ###
DBS="ihp"
#DBS="$($MYSQL -u $MUSER -h $MHOST -p$MPASS -Bse 'show databases')"
for db in $DBS
do

### Create dir for each databases, backup tables in individual files ###
  mkdir $BACKUP/$NOW/$db

  for i in `echo "show tables" | $MYSQL -u $MUSER -h $MHOST -p$MPASS $db|grep -v Tables_in_`;
  do
#    FILE=$BACKUP/$NOW/$db/$i.sql.gz
#    echo $i; $MYSQLDUMP --add-drop-table --allow-keywords -q -c -u $MUSER -h $MHOST -p$MPASS $db $i | $GZIP -9 > $FILE
    FILE=$BACKUP/$NOW/$db/$i.sql
    echo $i; $MYSQLDUMP --add-drop-table --allow-keywords -q -c -u $MUSER -h $MHOST -p$MPASS $db $i > $FILE
  done
done

### Compress all tables in one nice file to upload ###

ARCHIVE=$BACKUP/$NOW.tar.gz
ARCHIVED=$BACKUP/$NOW

$TAR -cvzf $ARCHIVE $ARCHIVED

### Dump backup using FTP ###
#cd $BACKUP
#DUMPFILE=$NOW.tar.gz
#$FTP -n $FTPS <
#quote USER $FTPU
#quote PASS $FTPP
#cd $FTPD
#mput $DUMPFILE
#quit
#END_SCRIPT

### Delete the backup dir and keep archive ###

rm -rf $ARCHIVED

### rsync the directory with the backup machine
### This preassumes that you have made it so no password is needed to 
### login to backupmachine
rsync -Cavz $BACKUP $RUSER@$RHOST:$RPATH














































Comments








Post a Comment














Popular posts from this blog



















October is Cyber Security Month




























The President has declared October as Cybersecurity month.  It's not a bad idea -- just like you change the batteries in your smoke detectors once a year, maybe you should review your electronic vulbnerabilities? My top ten security tips:   1) Change your passwords.  You've had them too long, you use the same password in too many places.  Somewhere someone has hacked a site that has your username and password in plain text.  Now they are getting ready to try that username/password somewhere else.  Beat them to the punch.   2) Use a safe browser.   That means anything that's not Internet Explorer.   I prefer chrome.   3) Use 2 step verification for your email account.   If your email doesn't provide 2 step authentication  consider switching.   4) Get a free credit report  and review it.  You are entitled to one free report a year.   BE VERY CAREFUL!  There are man scam sites that offer free credit reports.  Go through the s...




















16 comments









Read more




























So You're Looking for Work in Tech




























So You're Looking for Work in Tech   It's a more competitive market than it's ever  been—but don't despair! There are still plenty of jobs out there for humans who can demonstrate insight, creativity, and the ability to execute. Here's a practical guide to help you prove you can do just that.     0. File for Unemployment (If Applicable)  If you were recently laid off, file for unemployment right now . This won’t help your job search directly, but it will help you financially. Get that support—you earned it.     1. Buy a Domain and Invest in Yourself  If you don’t own a domain, buy one  today. Get a GSuite (Google Workspace) account and start using a professional email like yourname@yourdomain.com . Avoid using email services like GoDaddy, Zoho, or Office365. Google is the gold standard—invest in the best.     2. Hire Yourself  Give yourself a tech project—because this is  your job now. Choose a project that will add value to your life while forcing you to learn new ...




















4 comments









Read more




























Using hosts.deny against bad guys.




























Executive Summary:  This article details how to minimize hack attempts on open facing ports on a *nix machine, by reading the attackers IP addresses from a log file, and adding the address to hosts.deny Chances are pretty good if you've got a port open to the internet you're seeing unwanted traffic on it.  If you've got a service such as telnet or FTP or POP that transmits passwords unencrypted, you're significantly at risk for a breach from a packet being intercepted, but even if you're using a secure protocol such as SSH, you're vulnerable to a brute force attempt to break into your site.  As an example, here is a sample of a log file showing a break-in attempt on one of my servers: Oct  8 13:54:18 chapelle sshd(pam_unix)[4554]: authentication failure; logname= uid=0 euid=0 tty=ssh ruser= rhost=59.13.157.12 Oct  8 13:54:22 chapelle sshd(pam_unix)[4557]: authentication failure; logname= uid=0 euid=0 tty=ssh ruser= rhost=59.13.157.12 Oct  8 13:54:27 chapelle ssh...




















3 comments









Read more