backup.sh

#!/usr/bin/env bash

V_DIR_SQL="/home/sysadmin/data"
V_DIR_BACKUP="/home/sysadmin/backup"

V_HOST="localhost"
V_DATABASE="database"
V_USER="root"
V_PASSWORD="root"

I_HOST="homologacao"
I_USER="sysadmin"

echo ".............................."
echo $(date)
echo " - "

echo "Starting dump .......... ready"
START=$(date +%s)

echo " 1/5"
echo -n "Dumping functions"
mysqldump --login-path=user --skip-opt --no-create-info --add-drop-table --no-data --routines ${V_SOURCE} > ${V_DIR_SQL}/FUNCTION.sql
echo " ...... ready"

echo " 2/5 "
echo -n "Dumping views"
mysql --login-path=user INFORMATION_SCHEMA --skip-column-names --batch -e "SELECT table_name FROM tables WHERE table_type = 'VIEW' AND table_schema = '$V_SOURCE'" | xargs mysqldump -u ${V_USER} -p${V_PASSWORD} ${V_SOURCE} > ${V_DIR_SQL}/VIEW.sql
echo " .......... ready"

echo " 3/5 "
echo -n "Dumping tables"
mysql --login-path=user INFORMATION_SCHEMA --skip-column-names --batch -e "SELECT table_name FROM tables WHERE table_type = 'BASE TABLE' AND table_schema = '$V_SOURCE'" | xargs mysqldump -u ${V_USER} -p${V_PASSWORD} ${V_SOURCE} > ${V_DIR_SQL}/TABLE.sql
echo " ......... ready"

echo " 4/5"
echo -n "Ziping backup"
rm ${V_DIR_BACKUP}/backup.zip
zip -qrj ${V_DIR_BACKUP}/backup.zip ${V_DIR_SQL}
echo " .......... ready"

echo " 5/5"
echo -n "Sending file"
scp ${V_DIR_BACKUP}/backup.zip ${I_USER}@${I_HOST}:backup/ >> ${DIR}backup.log
echo " ........... ready"

END=$(date +%s)
TIME=$(( $END - $START ))

echo ".............................."
echo "Backup finished ($TIME seconds)"

restore.sh

#!/usr/bin/env bash

source=$1
milestone=$2
download=$3

source ${source}

echo "----------------------------------------------------------------------------------------"
echo $(date)
echo " - "

if [[ ${download} == "true" ]];
  then
  scp -P${remote_port} ${remote_user}@${remote_host}:${remote_data_source} ${project_data_source}
fi

if [[ ! -z ${milestone} ]];
  then
    database_name="${database_prefix}${milestone}"
fi

if [ ! -f "${project_data}/${database_name}/${project_data_zip}" ]
  then
    mkdir ${project_data}/${database_name}
    cp ${project_data_source} ${project_data}/${database_name}/${project_data_zip}
fi

echo "Starting dump .................... ready"
START=$(date +%s)
echo "( ~> '${project_data}/${database_name}/${project_data_zip}')"

echo " 1/6"
echo -n "Unzipping file"
if [ ! -f "${project_data}/${database_name}/.done" ]
  then
    unzip -o -q ${project_data}/${database_name}/${project_data_zip} -d ${project_data}/${database_name}
    touch "${project_data}/${database_name}/.done"
fi
echo " .................... ready"

echo " 2/6"
echo -n "Defining database"
echo "DROP DATABASE IF EXISTS \`${database_name}\`" | mysql --login-path=root --force
echo "CREATE DATABASE \`${database_name}\`" | mysql --login-path=root --force
echo "GRANT ALL PRIVILEGES ON *.* TO 'database'@'%' WITH GRANT OPTION" | mysql --login-path=root --force
echo " ................ ready"

sed -ie 's/ROW_FORMAT=FIXED//g' ${project_data}/${database_name}/TABLE.sql

echo " 3/6"
echo -n "Restoring tables"
mysql --login-path=user --force  ${database_name} < ${project_data}/${database_name}/TABLE.sql
echo " ................. ready"

echo " 4/6"
echo -n "Restoring functions"

V_FILE="${project_data}/${database_name}/FUNCTION.sql"
sed -i -e "${database_clear}" "${V_FILE}"

mysql --login-path=user --force  ${database_name} < ${V_FILE}
echo " .............. ready"

echo " 5/6"
echo -n "Restoring views"

V_FILE="${project_data}/${database_name}/VIEW.sql"
sed -i -e "${database_clear}" "${V_FILE}"

mysql --login-path=user --force  ${database_name} < ${V_FILE}
echo " .................. ready"

echo " 6/6"
echo -n "Adapting database"
#echo "UPDATE \`TBL_PARAMETROS\` SET \`par_valor\` = '${database_name}' WHERE \`par_variavel\` = 'default_database'" | mysql   ${database_name}
mysql --login-path=user --force  ${database_name} < ${project_shell}/local/database/adapta.sql
echo " ................ ready"


END=$(date +%s)
TIME=$(( ${END} - ${START} ))

echo ""
echo "----------------------------------------------------------------------------------------"
echo "DATABASE: Its done! ($TIME seconds)"