Backup mysql and sync to google drive

mysql2drive.sh

#!/bin/bash
# This tool help you backup mysql database to google drive
# Worked & tested on RHEL/DEB

# Folder ID https://drive.google.com/drive/u/0/folders/111111111111111111111111111111111
# That is folder_id (33 chars), make sure is yours =>  ^_______________________________^
REMOTE_FOLDER=111111111111111111111111111111111

# Your local folder to store some backup, it will be automatically created
LOCAL_FOLDER=/root/gdrive

# Date format is Y-m-d_H-M-S (2019-10-30_15-45-59)
DATE=`date +%Y-%m-%d_%H-%M-%S`

# Your database config
DBNAME=database_name
DBUSER=database_user
DBPASS=database_pass

# Do you need truncate some table data?
# Uncomment it to enable truncate data
TRUNCATE_TABLES="table_1,table_2"



check_exist_gdrive=$(command -v gdrive)
if [ $check_exist_gdrive = "" ] ; then
	if [ "x$(id -u)" != 'x0' ]; then
		echo 'You must install gdrive manual, please login to `root` and go to https://github.com/gdrive-org/gdrive/releases to download compatibility version of gdrive.'
		exit 1
	else
		arch=$(uname -i)
		if [ $arch = "x86_64" ] ; then
			wget https://github.com/gdrive-org/gdrive/releases/download/2.1.0/gdrive-linux-x64 -O /usr/local/bin/gdrive && chmod +x /usr/local/bin/gdrive
		else
			wget https://github.com/gdrive-org/gdrive/releases/download/2.1.0/gdrive-linux-386 -O /usr/local/bin/gdrive && chmod +x /usr/local/bin/gdrive
		fi
	fi
fi

if [ ! -d $LOCAL_FOLDER ]
then
  mkdir -p $LOCAL_FOLDER
fi

if [ ! -d $HOME/.gdrive ]
then
  gdrive list
fi
echo "########################################"
echo "Starting backup at "$DATE

cd $LOCAL_FOLDER
if [ -z ${TRUNCATE_TABLES+x} ]; then 
	echo ""
	echo "########################################"
	echo "No need truncate"
else
	echo ""
	echo "########################################"
	echo "Truncate table "$TRUNCATE_TABLES
	IFS=','	read -ra ADDR <<< $TRUNCATE_TABLES
	for STATEMENT in "${ADDR[@]}"; do
		MYSQL_PWD=$DBPASS mysql -u $DBUSER $DBNAME -e 'TRUNCATE '"$STATEMENT"';'
	done
	echo "Truncated"
fi


echo ""
echo "########################################"
echo "Backing up database"
MYSQL_PWD=$DBPASS mysqldump -v -u $DBUSER $DBNAME | gzip > $DBNAME"_"$DATE".sql.gz"
echo "Backed up"

echo ""
echo "########################################"
echo "Looking for oldest remote file if the folder has more than 50 files"
NUMBER_REMOTE_FILE=$(gdrive sync content --no-header $REMOTE_FOLDER | wc -l)
if [ $NUMBER_REMOTE_FILE -gt 10 ]
then
  OLDEST_REMOTE_FILE=$(gdrive sync content --order createdTime --no-header $REMOTE_FOLDER | head -n 1)
  OLDEST_REMOTE_FILE_ID=$(echo $OLDEST_REMOTE_FILE | cut -c1-33)
  gdrive delete $OLDEST_REMOTE_FILE_ID
  echo "Found a file & deleted it"
fi

echo ""
echo "########################################"
echo "Looking for oldest local file if the folder has more than 5 files"
for LOCAL_DIR in $LOCAL_FOLDER
do
  COUNT=$(find $LOCAL_DIR | wc -l)
  if [ $COUNT -gt 6 ]
  then
    cd $LOCAL_FOLDER && rm "$(ls -t | tail -1)"
	echo "Found a file & deleted it"
  fi
done

echo ""
echo "########################################"
echo "Syncing to google drive"
/usr/local/bin/gdrive sync upload $LOCAL_FOLDER $REMOTE_FOLDER

echo ""
echo "########################################"
echo "Database was backed up at "$DATE
echo "Done~!!!"