I wrote a simple bash script: rsync .SQL, mysqldump backup, import .SQL

[OrganizedFellow]

I've been developing on Ubuntu and Debian for years now. It's simpler  I don't do much bash scripting, but wanted to share this with you all.

I know we are always trying to find the best workflow and always modifying how we do things to make our development more stream-lined.

 

#!/bin/bash

#----------------------------------------------
# I WROTE A SIMPLE SCRIPT TO:
# - RSYNC A DUMP SQL FILE TO REMOTE SERVER
# - THEN EXPORT A BACKUP SQL FILE
# - THEN IMPORT THE DUMP SQL FILE
# - THEN REMOVE THE DUMP SQL FILE
#----------------------------------------------
#  This work is licensed under a Creative Commons 
#  Attribution-ShareAlike 3.0 Unported License;
#  see http://creativecommons.org/licenses/by-sa/3.0/ 
#  for more information.
#----------------------------------------------


## CD INTO LOCAL WORKING DIRECTORY
## this is where I keep my local dump SQL files.
## the most recent one is always named dump.sql
cd ~/www/website.dev/DB

## RSYNC LATEST DUMP.SQL FILE TO REMOTE SERVER
rsync -avzP dump.sql _USER_@111.222.333.444:/home/_USER_/website.com/backups
wait

## SSH INTO SERVER
ssh _USER_@111.222.333.444 /bin/bash << EOF
    echo "**************************";
    echo "** Connected to remote. **"
    echo "**************************";
    echo "";

    ## CD INTO REMOTE WORKING NON-PUBLIC DIRECTORY
    ## where the dump.sql file was rsynced to
    cd website.com/backups
    wait
    sleep 1
    
    ## RUN MYSQLDUMP COMMAND
    ## save the SQL with date stamp
    mysqldump --host=localhost --user=root --password=_PASSWORD_ _DATABASE_ > `date +%Y-%m-%d`.sql;
    echo "***************************************";
    echo "** `date +%Y-%m-%d`.SQL has been imported. **"
    echo "***************************************";
    echo "";
    wait
    sleep 1

    ## IMPORT DUMP.SQL COMMAND
    mysql --host=localhost --user=root --password=_PASSWORD_ _DATABASE_ < dump.sql;
    echo "*********************************";
    echo "** DUMP.SQL has been imported. **"
    echo "*********************************";
    echo "";
    wait
    sleep 1

    ## REMOVE DUMP.SQL FILE
    rm dump.sql
    echo "********************************";
    echo "** DUMP.SQL has been removed. **"
    echo "********************************";
    exit
EOF

 

[OrganizedFellow]

I don't do much bash scripting, but this works well for me  

If you can minimize the amount of code, be my guest  

 

[Hector Nguyen]

I added interactive mode into your script, hope it helped to someone is too lazy like me

#!/bin/bash
#----------------------------------------------
# INTERACTIVE REMOTE DATABASE DUMP SCRIPT
#----------------------------------------------
#  This work is licensed under a Creative Commons 
#  Attribution-ShareAlike 3.0 Unported License;
#  see http://creativecommons.org/licenses/by-sa/3.0/ 
#  for more information.
#----------------------------------------------
SCRIPT=${0##*/}
IFS=$'\n'
HISTFILE="$HOME/.remotedump.history"

# Use colors, but only if connected to a terminal, and that terminal supports them.
if which tput >/dev/null 2>&1; then
  ncolors=$(tput colors)
fi
if [ -t 1 ] && [ -n "$ncolors" ] && [ "$ncolors" -ge 8 ]; then
  RED="$(tput setaf 1)"
  GREEN="$(tput setaf 2)"
  YELLOW="$(tput setaf 3)"
  BLUE="$(tput setaf 4)"
  BOLD="$(tput bold)"
  NORMAL="$(tput sgr0)"
else
  RED=""
  GREEN=""
  YELLOW=""
  BLUE=""
  BOLD=""
  NORMAL=""
fi

# Case-insensitive for regex matching
shopt -s nocasematch

# Prepare history mode
set -i
history -c
history -r

# Input method text
get_input()
{
  read -e -p "${BLUE}$1${NORMAL}" "$2"
  history -s "${!2}"
}

# Input method password
get_input_pw()
{
  read -s -p "${BLUE}$1${NORMAL}" "$2"
  history -s "${!2}"
}

# Echo in bold
echo_b()
{
  if [ "$1" = "-e" ]; then
    echo -e "${BOLD}$2${NORMAL}"
  else
    echo "${BOLD}$1${NORMAL}"
  fi
}

# Echo in colour
echo_c()
{
  case "$1" in
    red | r | -red | -r | --red | --r ) echo "${RED}$2${NORMAL}" ;;
    green | g | -green | -g | --green | --g ) echo "${GREEN}$2${NORMAL}" ;;
    blue | b | -blue | -b | --blue | --b ) echo "${BLUE}$2${NORMAL}" ;;
    yellow | y | -yellow | -y | --yellow | --y ) echo "${YELLOW}$2${NORMAL}" ;;
    * ) echo "$(BOLD)$2$(RESET)" ;;
  esac
}

# Get input data and save to history
save_input()
{
  if [[ ! -n "$local_dir" ]]; then
    while get_input "Local DB Directory > " local_dir; do
      case ${local_dir%% *} in
        * )
            if [ -n "$local_dir" ]; then
              break
            else
              continue
            fi
        ;;
      esac
    done
  fi
  if [[ ! -n "$remote_user" ]]; then
    while get_input "SSH Username > " remote_user; do
      case ${remote_user%% *} in
        * )
            if [ -n "$remote_user" ]; then
              break
            else
              continue
            fi
        ;;
      esac
    done
  fi
  if [[ ! -n "$remote_ip" ]]; then
    while get_input "SSH Aliases/IP-address > " remote_ip; do
      case ${remote_ip%% *} in
        * )
            if [ -n "$remote_ip" ]; then
              break
            else
              continue
            fi
        ;;
      esac
    done
  fi
  if [[ ! -n "$remote_dir" ]]; then
    while get_input "Remote Backup Directory > " local_dir; do
      case ${remote_dir%% *} in
        * )
            if [ -n "$remote_dir" ]; then
              break
            else
              continue
            fi
        ;;
      esac
    done
  fi
  if [[ ! -n "$db_user" ]]; then
    while get_input "DB Username > " local_dir; do
      case ${db_user%% *} in
        * )
            if [ -n "$db_user" ]; then
              break
            else
              continue
            fi
        ;;
      esac
    done
  fi
  if [[ ! -n "$db_password" ]]; then
    while get_input_pw "DB Password > " local_dir; do
      case ${db_password%% *} in
        * )
            if [ -n "$db_password" ]; then
              break
            else
              continue
            fi
        ;;
      esac
    done
  fi
  if [[ ! -n "$db_name" ]]; then
    while get_input "DB Name > " local_dir; do
      case ${db_name%% *} in
        * )
            if [ -n "$db_name" ]; then
              break
            else
              continue
            fi
        ;;
      esac
    done
  fi
}

change_pwd_rsync()
{
  ## CD INTO LOCAL WORKING DIRECTORY
  ## this is where I keep my local dump SQL files.
  ## the most recent one is always named dump.sql
  cd "$local_dir"
  
  ## RSYNC LATEST DUMP.SQL FILE TO REMOTE SERVER
  rsync -avzP dump.sql $remote_user@$remote_ip:$remote_dir
  wait
}

remote_dump()
{
  ## SSH INTO SERVER
  ssh $remote_user@$remote_ip /bin/bash << EOF
    echo "**************************";
    echo "** Connected to remote. **"
    echo "**************************";
    echo "";

    ## CD INTO REMOTE WORKING NON-PUBLIC DIRECTORY
    ## where the dump.sql file was rsynced to
    cd "$remote_dir"
    wait
    sleep 1
    
    ## RUN MYSQLDUMP COMMAND
    ## save the SQL with date stamp
    mysqldump --host=localhost --user=$db_user --password=$db_password $db_name > `date +%Y-%m-%d`.sql;
    echo "***************************************";
    echo "** `date +%Y-%m-%d`.SQL has been imported. **"
    echo "***************************************";
    echo "";
    wait
    sleep 1

    ## IMPORT DUMP.SQL COMMAND
    mysql --host=localhost --user=$db_user --password=$db_password $db_name < dump.sql;
    echo "*********************************";
    echo "** DUMP.SQL has been imported. **"
    echo "*********************************";
    echo "";
    wait
    sleep 1

    ## REMOVE DUMP.SQL FILE
    rm dump.sql
    echo "********************************";
    echo "** DUMP.SQL has been removed. **"
    echo "********************************";
    exit
  EOF
}

main()
{
  save_input
  change_pwd_rsync
  remote_dump
}

main

 

[OrganizedFellow]

On 8/13/2016 at 6:05 AM, Hector Nguyen said:

I added interactive mode into your script, hope it helped to someone is too lazy like me

WHOA!

 

I'm trying to figure out what all this does

 

#BashNewb

[Hector Nguyen]

On August 17, 2016 at 0:02 AM, OrganizedFellow said:

WHOA!

 

I'm trying to figure out what all this does

 

#BashNewb

Let me explain it quickly,

Your original bash script need to be modify whenever you want to backup other database or same database but different server or the damn path. I really do hate it, it will take me over than 30 seconds. That is why I wrote this one to automate that.

The purpose of this script is let you enter all needed information to the shell (I also added history to this script, then next time if you still using credential, just press up or down to navigate among them) instead of open the script with vim editor, change them one by one.

Of course it is slower if you have only one server, only one database need to backup. But if you have more than one server or database, I should suggest you use my script  Good news is, I kept your workflow then you have no worries at all.

Cheers!

[OrganizedFellow]

Thank you Hector!

 

I gave it a try and it is MUCH smoother than mine.

[deltavik]

wow. That's a powerful script. I am going to use it. 

I had been running poor man's backup with these lines as cron. 

0 0 * * * mysqldump -uusername -ppassword databasename | gzip > /var/www/backups/sql/sql_$(date +\%m-\%d-\%Y).sql.gz
0 0 * * * cd /var/www/backups/files && zip -r files_$(date +\%m-\%d-\%Y).zip /var/www/docroot/site/assets/files

0 0 * * * find /var/www/backups/sql/ -mtime +7 -delete
0 0 * * * find /var/www/backups/files/ -mtime +7 -delete

The first two back up the database and files folder at midnight.

The last two remove backups older than a week.