-
Notifications
You must be signed in to change notification settings - Fork 0
/
Dump_copy_restore_on_remote_server
112 lines (86 loc) · 6.05 KB
/
Dump_copy_restore_on_remote_server
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
#!/bin/bash
SSH_ARGS='-o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null'
DATE=$(date +%d-%m-%Y)
MAILS="abc@gmail.com"
SERVERS=(172.29.0.110 172.29.8.110 172.29.16.110 172.29.26.110 172.29.32.110 172.29.40.110 172.29.50.110 172.29.56.110 192.168.17.110)
COUNTFILE="/home/dba_user/sp_completion_count_dont_delete.txt"
function ERROR() {
if [ $? -ne 0 ]; then
echo "dump restore has failed on 6.180" | mail -s "FAILED: dump restore has failed on $DATE" $MAILS
/scripts/standard_sms_sending_script.sh -m "dump restore has failed on 0.110" -f /scripts/groupon_sms_list.txt
exit 1
fi
}
function SUCCESS(){
if [ "$?" -eq "0" ]; then
echo "dump restore completed successfully on 6.180" | mail -s "SUCCESS: dump restore completed Successfully on $DATE" $MAILS
/scripts/standard_sms_sending_script.sh -m "dump restore completed successfully on 0.110" -f /scripts/groupon_sms_list.txt
fi
}
#########################
echo "Truncating count file"
truncate -s0 /home/dba_user/sp_completion_count_dont_delete.txt
#########################
for i in ${SERVERS[@]};do
echo -e "\nremoving companymaster_extract_temp.txt on ${i}"
ssh ${SSH_ARGS} -q dba_user@${i} "rm -fv /tmp/companymaster_extract_temp.txt"
ERROR
done
echo -e "\nStep 2.1 : Running db_report_script on all 110"
for i in ${SERVERS[@]};do
echo "Copying the SP script to ${i}"
scp ${SSH_ARGS} -q /scripts/db_report_script.sh dba_user@${i}:/home/dba_user/
ERROR
done
ssh -ttt ${SSH_ARGS} -q dba_user@172.29.0.110 "sudo sh /home/dba_user/db_report_script.sh > /tmp/db_report_script.log 2>&1 " &
echo -e "\nRunning simultaneously on all 110"
COUNT2=0
while true
do
COUNT1=`wc -l ${COUNTFILE} | awk '{print $1}'`
if [ $COUNT1 -eq 9 ]
then
###################
echo -e "db_report_script completed on all 110 successfully proceeding further... `date +%H:%M:%S` \n"
###################
break
fi
sleep 300
let COUNT2=$COUNT2+1
if [ $COUNT2 -eq 36 ]
then
###################
echo "db_report_script timed out... check /tmp/db_report_script.log on all 110 `date +%H:%M:%S`"
###################
exit 1
fi
echo "waiting for completion... `date +%H:%M:%S`"
done
############################################
echo -e "\nStep 3.0 : Transferring tbl_sf_weekly_report & cat_data_count_db_report_script to 17.110"
echo "Dumping tables tbl_sf_weekly_report & cat_data_count_db_report_script from 0.110 & Compressing it"
ssh ${SSH_ARGS} -q dba_user@172.29.0.110 "sudo mysqldump dbteam_temp table_test > /tmp/table_test.sql && gzip -fv /tmp/table_test.sql && sudo mysqldump dbteam_temp table_log_backup > /tmp/table_log_backup.sql && gzip -fv /tmp/table_log_backup.sql"
ERROR
echo -e "\nTransferring tables from all 110 to 17.110"
scp ${SSH_ARGS} -q dba_user@172.29.0.110:/tmp/table_log_backup.sql.gz /home/dba_user/
ERROR
scp ${SSH_ARGS} -q dba_user@172.29.0.110:/tmp/table_test.sql.gz /home/dba_user/
ERROR
echo -e "\nDecompressing && Restoring cat_data_count_db_report_script to dbteam_temp on 6.180 from all 110"
gunzip -fv /home/dba_user/table_log_backup.sql.gz && sudo mysql --socket=/var/lib/SQL/mysql/mysql.sock dbteam_temp < /home/dba_user/table_log_backup.sql && rm -fv /home/dba_user/table_log_backup.sql
ERROR
echo -e "\nDecompressing && Restoring tbl_sf_weekly_report to dbteam_temp on 17.110 from all 110"
gunzip -fv /home/dba_user/table_test.sql.gz && sudo mysql --socket=/var/lib/SQL/mysql/mysql.sock dbteam_temp < /home/dba_user/table_test.sql && rm -fv /home/dba_user/table_test.sql
ERROR
###########################################
echo "cleaning companymaster_extract_temp.txt,data_city_extract_temp.txt,idgenerator_temp.txt"
rm -fv /tmp/companymaster_extract_temp.txt /tmp/data_city_extract_temp.txt /tmp/idgenerator_temp.txt
sudo mysql -Bse "DROP TABLE IF EXISTS dbteam_temp.tbl_product ; CREATE TABLE dbteam_temp.tbl_product(parentid VARCHAR(255) DEFAULT '', productname VARCHAR(255), catidlineage_search TEXT, website_type_flag BIGINT(20)UNSIGNED DEFAULT '0', businesstags BIGINT(20)UNSIGNED DEFAULT '0',PRIMARY KEY(parentid),FULLTEXT KEY idx_ft(catidlineage_search));
SELECT parentid,productname,catidlineage_search,website_type_flag,businesstags INTO OUTFILE '/tmp/companymaster_extract_temp.txt' FIELDS TERMINATED BY '||' ENCLOSED BY '\"' LINES TERMINATED BY '\r\n' FROM db_iro.tbl_companymaster_extradetails WHERE mask=0 AND freeze=0 AND closedown_flag=0;LOAD DATA INFILE '/tmp/companymaster_extract_temp.txt' INTO TABLE dbteam_temp.tbl_product FIELDS TERMINATED BY '||' ENCLOSED BY '\"' LINES TERMINATED BY '\r\n'(parentid,productname,catidlineage_search,website_type_flag,businesstags); CALL sp_db_report_script(); DROP TABLE IF EXISTS dbteam_temp.tbl_datacity_extract; CREATE TABLE dbteam_temp.tbl_datacity_extract (cityname VARCHAR(100) NOT NULL,stdcode VARCHAR(15) DEFAULT NULL,PRIMARY KEY (cityname)) ENGINE=MYISAM DEFAULT CHARSET=latin1; SELECT cityname,stdcode INTO OUTFILE '/tmp/data_city_extract_temp.txt' FIELDS TERMINATED BY '||' ENCLOSED BY '\"' LINES TERMINATED BY '\r\n' FROM d_jds.tbl_data_city;
LOAD DATA INFILE '/tmp/data_city_extract_temp.txt' INTO TABLE dbteam_temp.tbl_datacity_extract FIELDS TERMINATED BY '||' ENCLOSED BY '\"' LINES TERMINATED BY '\r\n'(cityname,stdcode);
DROP TABLE IF EXISTS dbteam_temp.tbl_idgenerator_extract;CREATE TABLE dbteam_temp.tbl_idgenerator_extract ( parentid VARCHAR(100) NOT NULL DEFAULT '', data_city VARCHAR(100) DEFAULT '', PRIMARY KEY parentid (parentid), KEY idx_data_city (data_city)) ENGINE=INNODB DEFAULT CHARSET=latin1;SELECT parentid,data_city INTO OUTFILE '/tmp/idgenerator_temp.txt' FIELDS TERMINATED BY '||' ENCLOSED BY '\"' LINES TERMINATED BY '\r\n' db_iro.tbl_id_generator;LOAD DATA INFILE '/tmp/idgenerator_temp.txt' INTO TABLE dbteam_temp.tbl_datacity_extract FIELDS TERMINATED BY '||' ENCLOSED BY '\"' LINES TERMINATED BY '\r\n'(parentid,data_city);CALL sp_db_report_script();"
sudo mysqldump dbteam_temp tbl_weekly_report > /tmp/tbl_weekly_report.sql && gzip -fv /tmp/tbl_weekly_report.sql
sudo scp /tmp/tbl_weekly_report.sql.gz dba_user@172.29.67.110:/tmp
ERROR
SUCCESS
echo -e "\nprocess completed"