Difference between revisions of "Retain Database Dump Tables Script"

Revision as of 15:22, 7 September 2016

There are occasions that going a regular dump of the database isn't enough when there is an issue. You can do a dump by tables instead.

This script does this for you. It logs into the retain database and dumps each table into a file.

Editing dumpTables

You need to edit the script to make it work for your system. Edit these values in the script to match your system's values:

Change the values below where needed.....

• DBNAMES="retain" (the database name)
• HOST="--host=localhost" (the server the database is on)
• USER="--user=root" (the root username)
• PASSWORD="--password=k<l1nux" (the root user password)
• BACKUP_DIR="/var/lib/mysql/dump" (the directory to save the table files to)

Save the script, give it execute permissions (chmod +x dumpTables.sh) and run it (./dumpTables.sh)

Restoring tables

To Restore a Database:

1. gzip -d [gz dump file]
2. tar -xf [tar dump file]
3. mysql -uroot -p[root pwd] < __restoreData.sql

dumpTables Script listing

#!/bin/bash

### change the values below where needed.....
DBNAMES="retain"
HOST="--host=localhost"
USER="--user=root"
PASSWORD="--password=k<l1nux"
BACKUP_DIR="/var/lib/mysql/dump"

#### you can change these values, they are optional but know what you're doing....
#-- InnoDB --##
OPTIONS="--default-character-set=utf8 --complete-insert --extended-insert --quick --opt --single-transaction"
#-- MyISAM --##
#OPTIONS="--default-character-set=utf8 --complete-insert --extended-insert --quick --opt --add-locks"

RESTORESCRIPT="$BACKUP_DIR/__restoreData.sql"
DATE=`/bin/date '+%y%m%d_%H%M%S'`
TAR="yes" 

#-- GZip Individual Tables --#
#-- Turning this on disables the easy restore --#
#-- Values: yes/no --#
ZIP="no"


#### make no changes after this....

date2stamp () {
    date --utc --date "$1" +%s
}

stamp2date (){
    date --utc --date "1970-01-01 $1 sec" "+%Y-%m-%d %T"
}

dateDiff (){
    case $1 in
        -s)   sec=1;      shift;;
        -m)   sec=60;     shift;;
        -h)   sec=3600;   shift;;
        -d)   sec=86400;  shift;;
        *)    sec=86400;;
    esac
    dte1=$(date2stamp $1)
    dte2=$(date2stamp $2)
    diffSec=$((dte2-dte1))
    if ((diffSec < 0)); then abs=-1; else abs=1; fi
    echo $((diffSec/sec*abs))
}


#### start script ####
echo Removing old temporary files if they exist...
rm -f ${BACKUP_DIR}/*.sql > /dev/null 2>&1
rm -f ${BACKUP_DIR}/*.gz > /dev/null 2>&1
rm -f ${BACKUP_DIR}/*.tar > /dev/null 2>&1
cd ${BACKUP_DIR}

TOTBEGIN=$(date2stamp now)
for DB in $DBNAMES
do
    echo "=========================================="
    echo    Dumping Database: ${DB}
    echo "=========================================="
    echo 'SET AUTOCOMMIT=0;' > $RESTORESCRIPT
    echo 'SET UNIQUE_CHECKS=0;' >> $RESTORESCRIPT
    echo 'SET FOREIGN_KEY_CHECKS=0;' >> $RESTORESCRIPT
###Ernie Research This:innodb_autoinc_lock_mode = 2

    mysqldump --databases --no-data $HOST $USER $PASSWORD $DB > ${BACKUP_DIR}/__createDatabase.sql
    echo 'source __createDatabase.sql;' >> $RESTORESCRIPT

    for TABLE in `mysql $HOST $USER $PASSWORD $DB -e 'show tables' | egrep -v 'Tables_in_' `; do
        TABLENAME=$(echo $TABLE|awk '{ printf "%s", $0 }')

	 if [ $ZIP == "yes" ]
          then FILENAME="${TABLENAME}.sql.gz"
	   else FILENAME="${TABLENAME}.sql"
	 fi

        echo Dumping Table: $TABLENAME
	 BEGIN=$(date2stamp now)
        echo 'source' $FILENAME';' >> $RESTORESCRIPT

	 if [ $ZIP == "yes" ]
          then mysqldump $OPTIONS $HOST $USER $PASSWORD $DB $TABLENAME | gzip > ${BACKUP_DIR}/${FILENAME}
          else mysqldump $OPTIONS $HOST $USER $PASSWORD $DB $TABLENAME > ${BACKUP_DIR}/${FILENAME}
	 fi
	
	 END=$(date2stamp now)
	 DIFF=$((END-BEGIN))
	 if ((DIFF < 300)); 
	  then echo "  " $((DIFF/1)) Seconds;
	  else echo "  " $((DIFF/60)) Minutes;
	 fi
    done

###Ernie Research This:innodb_autoinc_lock_mode = 1
    echo 'SET FOREIGN_KEY_CHECKS=1;' >> $RESTORESCRIPT
    echo 'SET UNIQUE_CHECKS=1;' >> $RESTORESCRIPT
    echo 'COMMIT;' >> $RESTORESCRIPT


    if [ $TAR == "yes" ] 
    then
      echo Making tar...
      tar -cf ${DB}_${DATE}.tar *.sql  > /dev/null 2>&1

      echo Compressing...
      gzip -9 ${DB}_${DATE}.tar > /dev/null 2>&1

      echo Removing temporary files...
      rm -f ${BACKUP_DIR}/*.sql > /dev/null 2>&1
      rm -f ${BACKUP_DIR}/*.tar > /dev/null 2>&1
    fi

    echo "Completed: " $DB
done
TOTEND=$(date2stamp now)

DIFF=$((TOTEND-TOTBEGIN))
 if ((DIFF < 300)); 
  then echo "  " $((DIFF/1)) Seconds;
  else echo "  " $((DIFF/60)) Minutes;
 fi

echo "=========================================="
echo "       All Databases Complete"
echo "       To Restore a Database:"
echo "         1. gzip -d [gz dump file]"
echo "         2. tar -xf [tar dump file]" 
echo "         3. mysql -uroot -p[root pwd] < __restoreData.sql"
echo "=========================================="