Retain Database Dump Tables Script

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:

1. 1. 1. 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

1. !/bin/bash

1. 1. 1. change the values below where needed.....

DBNAMES="retain" HOST="--host=localhost" USER="--user=root" PASSWORD="--password=k<l1nux" BACKUP_DIR="/var/lib/mysql/dump"

1. 1. 1. 1. you can change these values, they are optional but know what you're doing....
2. -- InnoDB --##

OPTIONS="--default-character-set=utf8 --complete-insert --extended-insert --quick --opt --single-transaction"

1. -- MyISAM --##
2. 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"

1. -- GZip Individual Tables --#
2. -- Turning this on disables the easy restore --#
3. -- Values: yes/no --#

ZIP="no"

1. 1. 1. 1. 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))

}

1. 1. 1. 1. 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

1. 1. 1. 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

1. 1. 1. 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 "=========================================="