Syed Jahanzaib Personal Blog to Share Knowledge !

July 17, 2019

BASH: Exporting MYSQL DB to Remote Server

Filed under: Linux Related — Tags: , , — Syed Jahanzaib / Pinochio~:) @ 10:28 AM

mysql-export-import

Disclaimer: This post is shared just for reference & learning purposes. You must modify and add more failsafe check before using it in production.

Regards
Syed Jahanzaib


Scenario:

We are using Freeradius server which uses mySQL as its backend DB. Ideally the mysql server should have replica server so that if Primary goes down dueto any fault, the secondary replica should come in action.

For high availability purposes we we want to have a standby server. Mysql Master-Slave or Master-Master replication is ideal for real time replication. We successfully implemented this model at few sites, but yes replication requires constant monitoring, and at one place the secondary replica server backfired & caused data loss.

For one particular Remote Site we wanted to avoid the complications of REPLICATION. What we wanted is a standby server, and the DB from primary should be exported to secondary replica server daily in morning and emails for the actions taken by the script should be emailed to us.

We made custom script that is running successfully from quite some time.

The BASH script performs following function …

  • Checks secondary server PING response
  • Check secondary server SSH access
  • Checks primary server MYSQL DB access
  • Checks secondary server MYSQL DB access
  • Check if exported DB is of valid size, (I set it to min 10 KB, yes you may want to adjust it according to your setup)
  • If all OK, then export primary server DB, and import it to secondary server

Script Requirements:

https://aacable.wordpress.com/2011/11/25/howto-login-on-remote-mikrotik-linux-without-password-to-execute-commands/


BASH Script Code:

  • touch /temp/update_radius_from_10.0.0.1__TO__10.0.0.2.sh
  • chmod +x /temp/update_radius_from_10.0.0.1__TO__10.0.0.2.sh
  • nano /temp/update_radius_from_10.0.0.1__TO__110.0.0.2.sh
#!/bin/bash
clear
#set -x
# Version 1.0 / 10-July-2019
# Syed Jahanzaib / Web: https://aacable.wordpress.com / Email: aacable@hotmail.com
# This script exports mysqldb and restores it to second remote server
# Requires passwordless login on remote server using SSH keys
# Settings various VARIABLES for the script
# adding dns for resolving
echo "nameserver 8.8.8.8" > /etc/resolv.conf
#SET DATE TIME
set $(date)
time=`date |awk '{print $4}'`
YESTERDAY=`date --date='yesterday' +%Y-%m-%d`
TODAY=`date +"%d-%b-%Y__%T"`
SCRIPTST=`date +"%d-%b-%Y__%T"`
HOSTNAME=`hostname | sed 's/ //g'`
IP1=10.0.0.1
IP2=10.0.0.2
IP2ROLE="RADIUS"
IP2_SSH_PORT=22
SQL_DIR="sql_replica"
#MYSQL DETAILS
SQLUSER="root"
SQLPASS="TYPE.YOUR.SQL.ROOT.PASS"
export MYSQL_PWD=$SQLPASS
CMD="mysql -u$SQLUSER --skip-column-names -s -e"
DB="radius"
FILE="/$SQL_DIR/$HOSTNAME.$TODAY.IP.$IP1.sql"
GMAILID="YOUR_SENDER_GMAILID@gmail.com"
GMAILPASS="GMAIL_PASS"
ADMINMAIL1="aacableATATAThotmail.com"
COMPANY="ZAIB"
RESULT="/tmp/$IP2.$IP2ROLE.txt"
CLIENTS_FILE="/usr/local/etc/raddb/clients.conf"
PING_ATTEMPTS="2"
PING_RESULT="/tmp/$IP2.$IP2ROLE.ping.result.txt"
IP2_SSH_CHK="/tmp/$IP2.ssh.chk.txt"
touch $RESULT
touch $PING_RESULT
> $RESULT
> $PING_RESULT
rm -f /$SQL_DIR/*.sql
# Test PING to device
count=$(ping -c $PING_ATTEMPTS $IP2 | awk -F, '/received/{print $2*1}')
if [ $count -eq 0 ]; then
echo "- $COMPANY ALERT: $IP2 - $IP2ROLE is not responding to PING Attempts, cannot continue without it , Please check !"
echo "- $COMPANY ALERT: $IP2 - $IP2ROLE is not responding to PING Attempts, cannot continue without it , Please check !" > $PING_RESULT
sendemail -t $email -u "ALERT: $IP2 $IP2ROLE NOT RESPONDING!" -o tls=yes -s smtp.gmail.com:587 -t $ADMINMAIL1 -xu $GMAILID -xp $GMAILPASS -f $GMAILID -o message-file=$PING_RESULT -o message-content-type=text
exit 1
fi
echo "- Script start time: $SCRIPTST

This report contains DB export results.

- Source Server : $HOSTNAME / $IP1
- Destination Server : $IP2

- PING Result to $IP2 : OK"

echo "- Script start time: $SCRIPTST

This report contains DB export results.

- Source Server : $HOSTNAME / $IP1
- Destination Server : $IP2
- PING Result to $IP2 : OK" >> $RESULT

#Cehck if SSH is accessible
scp -q -P $IP2_SSH_PORT root@$IP2:/etc/lsb-release $IP2_SSH_CHK
# Verify if file is downloaded from remote server via ssh
if [ ! -f $IP2_SSH_CHK ]; then
echo -e "- $COMPANY ALERT: $IP2 - $IP2ROLE is not responding to passwordless SSH ACCESS, cannot continue without it , Please check !"
exit 1
fi
echo -e "- SSH Access to $IP2 : OK"
echo -e "- SSH Access to $IP2 : OK" >> $RESULT

# Check if $DB (in this case radius )is accessible or not, if NOT, then exit the script
RESULT_DB_CHK=`$CMD "SHOW DATABASES LIKE '$DB'"`
if [ "$RESULT_DB_CHK" != "$DB" ]; then
echo "- ALERT: $IP1 - DB $DB not accessible !!!"
echo "- ALERT: $IP1 - DB $DB not accessible !!!" >> $RESULT
sendemail -t $email -u "- ALERT: $IP1 - DB $DB not accessible" -o tls=yes -t $ADMINMAIL1 -xu $GMAILID -xp $GMAILPASS -f $GMAILID -o message-file=$RESULT -o message-content-type=text
exit 1
fi

echo "- $DB - Database accessed on $IP1 : OK" >> $RESULT

#############################################
######## START the BACKUP PROCESS ... #######
#############################################
# Checking if $SQL_DIR folder is previously present or not . . .
{
if [ ! -d "/$SQL_DIR" ]; then
echo -e "- ALERT: /$SQL_DIR folder not found, Creating it MYSQL EXPORT/DUMP backup should be placed there . . ."
mkdir /$SQL_DIR
else
echo -e "- INFO: $SQL_DIR folder is already present , so no need to create it, Proceeding further . . ."
fi
}

mysqldump -u$SQLUSER -p$SQLPASS --single-transaction=TRUE --ignore-table={radius.radacct} $DB > $FILE
# CHECK FILE SIZE AND COMPARE, IF ITS LESS , THEN ALERT
SIZE=`ls -lh $FILE | awk '{print $5}'`
SIZEB=`ls -l $FILE | awk '{print $5}'`
if [ $SIZEB -lt 1 ]
then
echo "- ALERT: DB export failed on $IP1 - Size = $SIZE OR $SIZEB Bytes"
echo "- ALERT: DB export failed on $IP1 - Size = $SIZE OR $SIZEB Bytes" >> $RESULT
sendemail -t $email -u "ALERT: DB export failed on $IP1 - Size = $SIZE OR $SIZEB Bytes" -o tls=yes -s smtp.gmail.com:587 -t $ADMINMAIL1 -xu $GMAILID -xp $GMAILPASS -f $GMAILID -o message-file=$RESULT -o message-content-type=text
exit 1
fi
#ssh -p $IP2_SSH_PORT root@$IP2 mkdir /$SQL_DIR
#scp -P $IP2_SSH_PORT $FILE_FINAL root@$IP2:/$SQL_DIR
#ssh -p $IP2_SSH_PORT root@$IP2 ls -lh /$SQL_DIR
# Import file in secondary radius
#ssh -p $IP2_SSH_PORT root@$IP2 "mysql -u$SQLUSER -p$SQLPASS $DB < $FILE
#mysql -h $IP2 -u$SQLUSER -p$SQLPASS $DB < $FILE
ssh -p $IP2_SSH_PORT root@$IP2 mysql -u$SQLUSER -p$SQLPASS $DB  output
#scp -P $IP2_SSH_PORT $CLIENTS_FILE root@$IP2:/usr/local/etc/raddb/
ssh -p $IP2_SSH_PORT root@$IP2 'service freeradius restart'
SCRIPTET=`date +"%d-%b-%Y___%T"`

echo "- FILE NAME : $FILE
- FILE SIZE : $SIZE

- DONE : Backup from $IP1 to $IP2 have been Exported OK

- Script End Time: $SCRIPTET

Regard's
Syed Jahanzaib"

echo "- FILE NAME : $FILE
- FILE SIZE : $SIZE

- DONE : Backup from $IP1 to $IP2 have been Exported OK

- Script End Time: $SCRIPTET

Regard's
Syed Jahanzaib" >> $RESULT

sendemail -t $email -u "$TODAY $HOSTNAME DB Exported from $IP1 to $IP2 Report OK" -o tls=yes -s smtp.gmail.com:587 -t $ADMINMAIL1 -xu $GMAILID -xp $GMAILPASS -f $GMAILID -o message-file=$RESULT -o message-content-type=text

#cat $RESULT
rm $IP2_SSH_CHK
rm $RESULT
rm $PING_RESULT
rm $FILE

Email Report Sample:

replica export done.PNG


Cron schedule to run the script Daily at 7am

# To run the script daily at 7 AM in morning
00 07 * * * /temp/update_radius_from_10.0.0.1__TO__10.0.0.2.sh

# To run the script every 6th hours 30 mnts
30 */6 * * * /temp/update_radius_from_10.0.0.1__TO__10.0.0.2.sh

Regard's
Syed Jahanzaib

%d bloggers like this: