Difference between revisions of "Retain Database Dump Tables Script"

Revision as of 15:34, 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. The script can found in the /mysql directory of the Software Repository.

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)

The tables will be saved to the file __restoreData.sql

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