streaming binlogs from MDS HA to Object Storage

binlog_to_object_storage.sh

#!/bin/bash

if [ $# -eq 0 ]
then
   echo "A configuration file is required"
   exit 1
fi

CONF_FILE=$1

if [ ! -f "$CONF_FILE" ]
then
   echo "Configuration file [$CONF_FILE] doesn't exist"
   exit 2
fi

# default config
MYSQLCONFIGED=/bin/mysql_config_editor
MYSQLBINLOG=/bin/mysqlbinlog
RESPAWN=10    # time to wait before reconnecting after failure

source $CONF_FILE

# check for some files
for i in $MYSQLCONFIGED $MYSQLBINLOG
do
  which $i >/dev/null 2>&1
  if [[ $? -ne 0 ]]
  then
     echo "$i is missing, please install"
     exit 6
  fi
done 


# check is object storage is mounted
mount | grep $OBJECT_STORAGE_MOUNT | grep s3fs >/dev/null
if [[ $? -ne 0 ]]
then
   echo "$OBJECT_STORAGE_MOUNT is not mounted or it's not a Object Storage mount point"
   exit 3
fi

if [ ! -d "$OBJECT_STORAGE_MOUNT/$BINLOGS_DIR" ]
then
   echo "[$OBJECT_STORAGE_MOUNT/$BINLOGS_DIR] doesn't exist... creating it"
   mkdir $OBJECT_STORAGE_MOUNT/$BINLOGS_DIR
   if [[ $? -ne 0 ]]
   then
      echo "Error creating [$OBJECT_STORAGE_MOUNT/$BINLOGS_DIR], aborting !"
      exit 4
   fi
fi

# check is credentials are available
$MYSQLCONFIGED print --login-path=$MYSQL_LOGIN_PATH | grep $MYSQL_LOGIN_PATH >/dev/null
if [[ $? -ne 0 ]]
then
   echo "please use $MYSQLCONFIGED to add the credentials for login-path=$MYSQL_LOGIN_PATH"
   exit 7
fi


cd $OBJECT_STORAGE_MOUNT/$BINLOGS_DIR

echo "Streaming binary logs to $OBJECT_STORAGE_MOUNT/$BINLOGS_DIR"


while true
do
   MYSQL_HOST=$(mysql --login-path=$MYSQL_LOGIN_PATH -BN -e "select @@hostname")
   echo "MySQL Host Name is ${MYSQL_HOST}"
   LASTFILE=`ls -1 $OBJECT_STORAGE_MOUNT/$BINLOGS_DIR/${MYSQL_HOST}* 2>/dev/null|grep -v orig|tail -n 1|xargs -n1 basename`
   TIMESTAMP=`date +%s`
   if [ -z "$LASTFILE" ]
   then
      if [ $# -eq 2 ]
      then
        LASTFILE_SERVER=$2
      else
        LASTFILE_SERVER=$(mysql --login-path=$MYSQL_LOGIN_PATH -BN -e "select SUBSTRING_INDEX(FILE_NAME,'/', -1) binlog from performance_schema.file_instances WHERE EVENT_NAME='wait/io/file/sql/binlog' ORDER BY 1 limit 1;")
      fi
   else
      LASTFILE_SERVER=${LASTFILE#"${MYSQL_HOST}-"} 
      FILESIZE=$(stat -c%s "$LASTFILE")
 
      if [ $FILESIZE -gt 0 ]; then
        echo "Backing up last binlog"
        mv $LASTFILE $LASTFILE.orig$TIMESTAMP
     fi
     touch $LASTFILE
   fi
   echo "Starting live binlog streaming from $LASTFILE_SERVER"

   $MYSQLBINLOG --login-path=$MYSQL_LOGIN_PATH --raw --result-file="${MYSQL_HOST}-" --read-from-remote-server --stop-never $LASTFILE_SERVER 
 
   echo "mysqlbinlog exited with $? trying to reconnect in $RESPAWN seconds."
 
   sleep $RESPAWN
done