×

BASH: Exporting MYSQL DB to Remote Server

  • Home
  • Blog
  • BASH: Exporting MYSQL DB to Remote Server
BASH: Exporting MYSQL DB to Remote Server


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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
#!/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

1
2
3
4
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

Related Post

MySql Database Recovery from Raw Files

Assigning friendly/fix name to USB device

Short notes for UNBOUND Caching DNS Server under Ubuntu 18

Syslog-ng – Part 3: Minimized logging to mysql with dynamic tables & trimming

Bash Script for General Customizable Report via Email

Blog Categories

Related Posts

×

Notice!!

All Quantic user are requested to use our hybrid cloud drive for you project and Data base . We had added new module of cronjob to schedule and optimise your backup .