mantencion_database.sh

#!/bin/bash

##
##  Sistema de respaldo y mantencion de tablas transaccionales
##  Autor: Nicolas Martinez V.  @_makunga
##  Allware LTDA.
##


##  README  ###############################################################################################################################################################
# 1.- Antes de ejecutar este script se deben hacer los cambios pertinentes en las variables
#
# 2.- una buena manera de obtener la informacion necesaria es con el siguiente script
#      SELECT table_name, table_rows
#      FROM INFORMATION_SCHEMA.TABLES
#      WHERE TABLE_SCHEMA = '[DATABASE]'
#      ORDER BY table_rows DESC;
#
# 3.- Tambien para obtener los parametros para filtrar y obtener id se recomienda ayudarse con el siguiente script
#      SHOW COLUMNS FROM [tabla];
#
# 4.- No se recomienda modificar el parametro $_fecha, pero si le es mas comodo puede cambiar el estilo de salida
#
# 5.- En caso de error siempre se puede recurrir al dump, este se comprime al final de todo el script en caso de que se necesite restaurar antes,
#      si se quiere restaurar posterior a la ejecucion completa, en el archivo log creado por el proceso sale el script para ejecutar desde el sql comprimido
#
# 6.- Se han dejado unas variables dummy utilizadas en las pruebas
#
# 7.- Se requiere tener instalado el cliente mysql, gzip, y bash, ademas de las credenciales para ejecutar.
#
##########################################################################################################################################################################




# variables

## mysql
_muser="lab"
_mpass="secret"
_mhost="localhost"
_tabla="bo_locales"
_base="lab_test"
_param="created_at"
_idhead="id"

## logs y directorio principal
_fecha=$(date '+%Y%m%d')
_dir="~/database"
_backup="$_dir/backup_$_tabla"_"$_base"_"$_fecha.sql"
_log="$_dir/log/log_$_tabla"_"$_base"_"$_fecha.log"


####################################################################################################################
##
##  no modificar desde este punto, si se necesita actualizar informar a pmartinez@allware.cl
##
####################################################################################################################



# respaldos de tabla
echo "INICIA PROCESO">>$_log
echo $(date '+%Y-%m-%d %H:%M:%S') >> $_log

# si no existen los directorios los crea
if [ -d $_dir/backup ] ; then
  if [ -d $_dir/log  ]; then
    echo "Directorios disponibles"
    echo $(date '+%Y-%m-%d %H:%M:%S') >> $_log
    echo "" >> $_log
  else
    mkdir -p $_dir/backup
    mkdir -p $_dir/log
    echo "se crean directorios de respaldo y log" >> $_log
    echo $_dir/backup >> $_log
    echo $_dir/log >> $_log
    echo $(date '+%Y-%m-%d %H:%M:%S') >> $_log
    echo "" >> $_log
  fi
fi

echo "se realiza respaldo de base" >> $_log
mysqldump -v --single-transaction --host=$_mhost --user=$_muser --password=$_mpass $_base $_tabla > $_backup
echo $(date '+%Y-%m-%d %H:%M:%S') >> $_log
# comprobar si existe respaldo y tiene lineas
echo "" >> $_log
echo "se comprueba que el respaldo sea valido">> $_loglast_charge
if [ -f $_backup ]; then
  if [[ $(wc -l $_backup | awk '{print $1}') -gt 0 ]]; then
    echo "OK: archivo disponible y con datos"  >> $_log
    #echo $(date '+%Y-%m-%d %H:%M:%S')| >> $_log
  else
    echo "Error!: archivo de respaldo disponible, pero este no tiene datos" >> $_log
    #echo $(date '+%Y-%m-%d %H:%M:%S')| >> $_log
    echo $(date '+%Y-%m-%d %H:%M:%S') >> $_log
    echo "" >> $_log
    exit 2
  fi
else
  echo "Error!: archivo de respaldo no disponble, favor confirmar" >> $_log
  echo $(date '+%Y-%m-%d %H:%M:%S') >> $_log
  exit 1
fi

# mantencion
echo "" >> $_log
echo "existen la siguiente cantidad de registros" >> $_log
mysql -h $_mhost -u$_muser -p$_mpass -e "SELECT COUNT(*) FROM $_base.$_tabla;" >> $_log
echo $(date '+%Y-%m-%d %H:%M:%S') >> $_log
echo "" >> $_log
echo "se eliminaran la siguiente cantidad de registros" >> $_log
mysql -h $_mhost  -u$_muser -p$_mpass -e "SELECT COUNT(*) FROM $_base.$_tabla WHERE $_param <= SUBDATE(now(), INTERVAL 1 YEAR);" >> $_log
echo $(date '+%Y-%m-%d %H:%M:%S') >> $_log
echo "" >> $_log

#### ELIMINACION DE registros
_query='DELETE from '$_base'.'$_tabla' WHERE '$_idhead' in (SELECT * FROM (SELECT MA.'$_idhead'	FROM '$_base'.'$_tabla' MA WHERE MA.'$_param' <= SUBDATE(now(), INTERVAL 1 YEAR)) as T);'
mysql -h $_mhost  -u$_muser -p$_mpass -e "$_query" $_base>> $_log

echo "posterior a la elimninacion queda la siguiente cantidad de registros"  >> $_log
mysql -h $_mhost  -u$_muser -p$_mpass -e "SELECT COUNT(*) FROM $_base.$_tabla;" >> $_log
echo $(date '+%Y-%m-%d %H:%M:%S') >> $_log
echo "" >> $_log

# comprime respaldo
echo "">> $_log
echo "se comprime respaldo">> $_log
gzip -v -f $_backup >> $_log 2>&1
echo "" >> $_log
echo "si desea restaurar los cambios ejecutar el siguiente comando">> $_log
echo "zcat $_backup.gz | mysql -h $_mhost  -u$_muser -p$_mpass $_base">> $_log
echo "">> $_log
echo "FINALIZA PROCESO">> $_log
echo $(date '+%Y-%m-%d %H:%M:%S') >> $_log
echo "###################################################################################################################">> $_log