[mysql] - Dump. ( Magento )

backup-database.sh

#!/bin/bash

function startduration {
	TIMESTART=$(date +%s.%N)
}

function printduration {

	TEMPO=$(python -c "print($(date +%s.%N) - $1)")
	TEMPO=(${TEMPO//./ }) 

	DIAS=$(( ${TEMPO[0]} / 86400 ))
	HORAS=$(python -c "print((${TEMPO[0]}-($DIAS*86400))/3600)")
	MINUTOS=$(python -c "print((${TEMPO[0]}-($DIAS*86400+$HORAS*3600))/060)")
	SEGUNDOS=$(python -c "print(${TEMPO[0]}-($DIAS*86400+$HORAS*3600+$MINUTOS*060))")
	
	echo -e "Dia(s) "${DIAS}" - Duração(H:M:S:N) "${HORAS}":"${MINUTOS}":"${SEGUNDOS}":"${TEMPO[1]}
}


STARTOTALSCRIPT=$(date +%s.%N)
SIMPLE=''
## Comandos usados ##
command -v mysqldump >/dev/null 2>&1 || { echo >&2 "Não existe o comando mysqldump.  Aborting."; exit 1; }
command -v tar >/dev/null 2>&1 || { echo >&2 "Não existe o comando tar.  Aborting."; exit 1; }

if [ $# -lt 2 ]; then
  echo "=[ Info ]====================================================="
  echo "Use $0 -d ( DATABASE | Arquivo com N Bancos ) -u DBUSUARIO -h DBHOST -p DBSENHA -P DBPORTA -T ( TABELA | Arquivo com N Tabelas ) -D /diretorio/backups/ -S" 
  echo "=============================================================="
  echo "Arquivo de (BANCO | TABELA) deve conter um(a) por linha."
  echo "=[ Parameters ]==============================================="
  echo " -d : Databases para fazer dump.                              "
  echo " -u : Usuário para se conectar no Banco.                      "
  echo " -p : Senha para se conectar no Banco.                        "
  echo " -P : Porta para se conectar no Banco.                        "
  echo "      Obs.: Default ( 3306 )                                  "
  echo " -T : Tabelas a serem ignoradas ao fazer dump dos dados.      "
  echo "      Obs.: A estrutura entrará no dump.                      "
  echo " -D : Diretório aonde ficará o dump.                          "
  echo "      Obs.: Default ( /backup/ )                              "
  echo " -C : Exibe exemplo de Cron.                                  "
  echo " -S : Sem criar Lock das tabelas. (--single-transaction)      "

  exit 0
fi

while getopts "d:h:p:u:D:T:P:C:S" OPTION
do
  case $OPTION in
	'd') DATABASES=${OPTARG}
	  ;;
    'u') DB_USER=${OPTARG}
	  ;;
	'h') DB_HOST=${OPTARG}
	  ;;
    'p') DB_PASSWD=${OPTARG}
      ;;
    'P') DB_PORT=${OPTARG}
      ;;
    'D') BACKUP_DIR=${OPTARG}
      ;;
    'T') EXCLUDE_DATA_TABLES=${OPTARG}
      ;;
    'S') SIMPLE='--single-transaction'
      ;;
    'C') echo  "17 3 * * * root $0 -d ${DATABASES} -u ${DB_USER:-backup} -h ${DB_HOST:-127.0.0.1} -p ${DB_PASSWD:-$(hostname|md5sum|cut -d' ' -f 1)} -P ${DB_PORT:-3306} -D ${BACKUP_DIR:-\/backup\/}" && exit 0
      ;;
  	  *) echo "=[ Info ]====================================================="
         echo "Use $0 -d ( DATABASE | Arquivo com N Bancos ) -u DBUSUARIO -h DBHOST -p DBSENHA -P DBPORTA -T ( TABELA | Arquivo com N Tabelas ) -D /diretorio/backups/ -S"  
         echo "=============================================================="
         echo "Arquivo de (BANCO | TABELA) deve conter um(a) por linha."
         echo "=[ Parameters ]==============================================="
         echo " -d : Databases para fazer dump.                              "
         echo " -u : Usuário para se conectar no Banco.                      "
         echo " -p : Senha para se conectar no Banco.                        "
         echo " -P : Porta para se conectar no Banco.                        "
         echo "      Obs.: Default ( 3306 )                                  "
         echo " -T : Tabelas a serem ignoradas ao fazer dump dos dados.      "
         echo "      Obs.: A estrutura entrará no dump.                      "
         echo " -D : Diretório aonde ficará o dump.                          "
         echo "      Obs.: Default ( /backup/ )                              "
         echo " -C : Exibe exemplo de Cron.                                  "
         echo " -S : Sem criar Lock das tabelas. (--single-transaction)      "
         exit 0
	  ;;
	esac
done

if [ ! "${DATABASES}" ]; then
  echo "Você não definiu o banco de dados a ser copiado, utilize o parametro -d NOMEDODB para isto."
  exit 0
fi

# Se no parametro do DATABASE vier um arquivo.
if [ -f ${DATABASES} ]; then
	DATABASES=($(cat  ${DATABASES} | tr "\n" " "))
fi

if [ -n "${BACKUP_DIR}"  ]; then
	if [[ "${BACKUP_DIR:${#BACKUP_DIR}-1}" != "/" ]]; then
		BACKUP_DIR="${BACKUP_DIR}/"
	fi
fi

if [ ! -e ${BACKUP_DIR:-\/backup\/} ]; then
  mkdir -p ${BACKUP_DIR:-\/backup\/}
fi

## Tabelas para não copiar os dados. ##
EXCLUDED_TABLES=(
	log_url
	log_url_info
	log_visitor
	log_visitor_info
	log_customer
	log_quote
	report_event
	index_event
	index_process_event
	report_viewed_product_index
)
#	dataflow_batch_export   abacos usa(confirmar)
#	dataflow_batch_import   abacos usa(confirmar)

if [ -n "${EXCLUDE_DATA_TABLES}" ]; then
	# Se no parametro do EXCLUDE_DATA_TABLES vier um arquivo.
	if [ -f ${EXCLUDE_DATA_TABLES} ]; then
		EXCLUDE_DATA_TABLES=($(cat  ${EXCLUDE_DATA_TABLES} | tr "\n" " "))
	fi

	# Juntar com as tabelas defaul para se ignorar
	for TABLE in "${EXCLUDE_DATA_TABLES[@]}"
	do :
	   EXCLUDED_TABLES+=${TABLE}
	done
	
fi

for DATABASE in "${DATABASES[@]}"
do :
	# Tabelas do banco a serem ignoradas quando buscar os dados
	IGNORED_TABLES_STRING=''
	for TABLE in "${EXCLUDED_TABLES[@]}"
	do :
	   IGNORED_TABLES_STRING+=" --ignore-table=${DATABASE}.${TABLE}"
	done

	STARTOTAL=$(date +%s.%N)
	# START=$(date +%s.%N)
	startduration

	DATE=$(date +%d%m%Y)
	FILENAME="dump_${DATABASE}"	
	TMP_FILE=${FILENAME}-${DATE}.sql

	echo "[ ${DATABASE} ] Inicio"
	echo " - Dump structure "
	mysqldump --host=${DB_HOST:-127.0.0.1} --port=${DB_PORT:-3306} --user=${DB_USER} --password=${DB_PASSWD} --single-transaction --no-data --databases ${DATABASE} --result-file=${BACKUP_DIR:-\/backup\/}${TMP_FILE}
	
	# END=$(date +%s.%N)
	echo "   -> ( Time ) "$(printduration ${TIMESTART})
	# START=$(date +%s.%N)
	startduration
	echo " - Dump content "
	
	mysqldump --host=${DB_HOST:-127.0.0.1} --port=${DB_PORT:-3306} --user=${DB_USER} --password=${DB_PASSWD} ${SIMPLE} ${DATABASE} ${IGNORED_TABLES_STRING} >> ${BACKUP_DIR:-\/backup\/}${TMP_FILE}

	# END=$(date +%s.%N)
	echo "   -> ( Time ) "$(printduration ${TIMESTART})

	# START=$(date +%s.%N)
	startduration
	echo " - Create ${FILENAME}-${DATE}.tar.bz2 file "
	
	cd ${BACKUP_DIR:-\/backup\/}
	tar -cjf ./${FILENAME}-${DATE}.tar.bz2 ./${TMP_FILE}
	rm ./${TMP_FILE}

	TAMANHO=($(du -h ./$FILENAME-$DATE.tar.bz2))
	echo "   -> ( Tamanho ) "${TAMANHO[0]}

	# END=$(date +%s.%N)
	echo "   -> ( Time ) "$(printduration ${TIMESTART})

	# ENDTOTAL=$(date +%s.%N)
	echo "[ ${DATABASE} ] Finish - Total Time "$(printduration ${STARTOTAL})
done

echo "sed -i 's/ROW_FORMAT=FIXED//g' ${FILENAME}-${DATE}.sql"
echo "tar -xjf ${FILENAME}-${DATE}.tar.bz2 | mysql -u [usuario] -p"
echo "(Magento dump 5.6 to 5.7) tar -xjf ${FILENAME}-${DATE}.tar.bz2 | sed -e 's/ROW_FORMAT=FIXED/ROW_FORMAT=COMPACT/' > ${FILENAME}-${DATE}.sql | mysql -u [usuario] -p"
TAMANHOTOTAL=($(du -lhs ${BACKUP_DIR:-\/backup\/}))
echo "======================================================================================="
echo " - Data                   : "$(date +%d/%m/%Y)
echo " - Total Size Dumps       : "${TAMANHOTOTAL[0]}
echo " - Total Time of Script   : "$(printduration ${STARTOTALSCRIPT})
echo "======================================================================================="

study.sh

# script para analisar 
#!/usr/bin/env bash

#
# Created 2005-06-24 by Matthew Montgomery - mmontgom@rackspace.com 
# 
# Change: 2006-06-01 by Matthew Montgomery
#  Add support for ibbbackup for InnoDB tables
#  Add support for MySQL 4.1 and 5.0
#

DATE=`date -I`
DATADIR="/var/lib/mysql"
BASE_DIR="/mnt/drive2/backup"
BACKUP_DIR="$BASE_DIR/current"
INTERVAL="$1"
RETENTION=14 # days
HOST=`hostname -s`
MYVERSION=`mysql -Bse "SELECT substring_index(version(),'.',2)"`
###  Uncomment this line to specify the path to and enable ibbackup for hotcopy of InnoDB tables.
# IBBACKUP="/usr/local/bin/ibbackup"

if [ "$MYVERSION" = '4.1' ] || [ "$MYVERSION" = '5.0' ] ; then
	PURGELOGS='mysql -e "PURGE MASTER LOGS BEFORE DATE_SUB( NOW(), INTERVAL 1 HOUR )"'
elif [ $MYVERSION = '3.23' -o "$MYVERSION" = '4.0' ]; then
	PURGELOGS='mysql -e "RESET MASTER"'
else
echo "UNSUPPORTED MYSQL VERSION"
exit 1
fi

if [ ! $1 ];
then
	read -p "Backup Interval? (Hourly|Daily) : " INTERVAL
fi

case $INTERVAL in
	hourly | HOURLY | Hourly | 1 )
	echo "Performing HOURLY level backup -- `date`"
	mysql -e "FLUSH LOGS"
	if [ -d $BASE_DIR/$DATE ] && [ "$MYVERSION" = '4.1' -o "$MYVERSION" = '5.0' ] ; then
		rsync -aub $DATADIR/$HOST-bin.?????? $BASE_DIR/$DATE
	elif [ -d $BASE_DIR/$DATE ] && [ "$MYVERSION" = '3.23' -o "$MYVERSION" = '4.0' ] ; then
		rsync -aub $DATADIR/$HOST-bin.??? $BASE_DIR/$DATE
	else 
		echo "No destination dir! please run daily backup first." 1>&2
		exit 1
	fi
	sleep 1
	find $BASE_DIR -size 98c -exec rm -rf '{}' \;
	exit 0
	;;
	daily | DAILY | Daily | 2 )
	echo "Performing DAILY level backup -- `date`"
	if [ ! -d $BACKUP_DIR ];
	then
		echo Creating $BACKUP_DIR
		mkdir -p $BACKUP_DIR
	fi

	if [ ! -z "$IBBACKUP" ] ; then
		$IBBACKUP /etc/my.cnf /etc/my.cnf.ibbackup 2>&1
		$IBBACKUP --apply-log /etc/my.cnf.ibbackup 2>&1
		rm $BACKUP_DIR/ibbackup_logfile
	fi
	mysqlhotcopy --regexp=.* $BACKUP_DIR
	chown -R mysql: $BACKUP_DIR/
	mv $BACKUP_DIR $BASE_DIR/$DATE
	eval $PURGELOGS
	find $BASE_DIR -ctime +$RETENTION -exec rm -rf '{}' \;
	exit 0
	;;
	* )
	echo "Invalid Selection" 1>&2
	exit 1
esac