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











































 In software engineering, accumulating code behind a release wall is akin to gathering water behind a dam. Just as a dam must be built higher and stronger to contain an increasing volume of water, the more code we delay releasing, the more resources we must allocate to prevent a catastrophic flood—major bugs or system failures—while also managing the inevitable trickles—minor issues and defects. Frequent, smaller releases act like controlled spillways, effectively managing the flow of updates and reducing the risk of overwhelming both the system and the team. The ideal of ci/cd  may not be achievable for all teams, but smaller and faster is always better.




















Post a Comment









Read more




























Preventing accidental large deletes.




























     Instructions for Developers on Using the safe_delete  Stored Procedure      To enhance safety and auditability of delete operations within our databases, we have implemented a controlled deletion process using a stored procedure named safe_delete . This procedure relies on a temporary table ( temp_delete_table ) that lists complete records intended for deletion, not just their IDs. This approach helps prevent accidental deletions and provides a traceable audit log of delete actions.       Why We Are Doing This               Controlled Deletions : Centralizing delete operations through a stored procedure reduces the risk of erroneous or unauthorized deletions.          Auditability : Using a temporary table to store complete records before deletion allows for an in-depth review and verification process, enhancing our ability to confirm and audit delete operations accurately.          Security : Restricting direct delete permissions and channeling deletions through a specific proced...




















Post a Comment









Read more




























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