forked from lobbywatch/lobbywatch
-
Notifications
You must be signed in to change notification settings - Fork 0
/
run_db_script.sh
executable file
·201 lines (182 loc) · 8.37 KB
/
run_db_script.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
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
#!/bin/bash
########
# REMOTE
########
# cron commands:
# Test:
# cd /home/csvimsne/sql_scripts/test/; ./run_db_script.sh csvimsne_lobbywatchtest csvimsne_script dbdump cron; ./run_db_script.sh csvimsne_lobbywatchtest csvimsne_script dbdump_data cron; /home/csvimsne/sql_scripts/test/run_db_script.sh csvimsne_lobbywatchtest csvimsne_script /home/csvimsne/sql_scripts/test/db_views.sql cron
# Prod:
# cd /home/csvimsne/sql_scripts/; ./run_db_script.sh csvimsne_lobbywatch csvimsne_script dbdump cron; ./run_db_script.sh csvimsne_lobbywatch csvimsne_script dbdump_data cron; /home/csvimsne/sql_scripts/run_db_script.sh csvimsne_lobbywatch csvimsne_script /home/csvimsne/sql_scripts/db_views.sql cron
# TODO use \ only where necessary http://stackoverflow.com/questions/1455988/commenting-in-bash-script
# For DB operations, add config to ~/.my.cnf:
# This config avoid to show the password in the process list.
#
# [client]
# user = lobbywat_script
# password = CHANGE_IT
# Abort on errors
# set -e
db=$1
username=$2
# script=db_check.sql
# script: dbdump | dbdump_data | dbdump_struct : mysql_dump
script=$3
# mode = cron | interactive | cronverbose
mode=$4
PW=$5
HOST=127.0.0.1
logfile="$script.log"
last_dbdump_file="last_dbdump.txt"
last_dbdump_data_file="last_dbdump_data.txt"
last_dbdump_struct_file="last_dbdump_struct.txt"
last_dbdump_op_file="last_dbdump_file.txt"
SRC_DB="lobbywat_lobbywatch"
# Ref: http://stackoverflow.com/questions/12199631/convert-seconds-to-hours-minutes-seconds-in-bash
# Input: Parameter $1=time in s
convertsecs() {
((h=${1}/3600))
((m=(${1}%3600)/60))
((s=${1}%60))
printf "%02d:%02d:%02d\n" $h $m $s
}
DATE=`date +"%Y%m%d_%H%M%S"`
BAK_DIR="bak"
DUMP_FILE="$BAK_DIR/${script}_${db}_$DATE.sql"
DUMP_FILE_GZ="$DUMP_FILE.gz"
# Colors,
# http://webhome.csc.uvic.ca/~sae/seng265/fall04/tips/s265s047-tips/bash-using-colors.html
# http://misc.flogisoft.com/bash/tip_colors_and_formatting
# Attribute codes:
# 00=none 01=bold 04=underscore 05=blink 07=reverse 08=concealed
#
# Text color codes:
# 30=black 31=red 32=green 33=yellow 34=blue 35=magenta 36=cyan 37=white
#
# Background color codes:
# 40=black 41=red 42=green 43=yellow 44=blue 45=magenta 46=cyan 47=white
green='\e[0;32m'
greenBold='\e[1;32m'
red='\e[0;31m'
redBold='\e[1;31m'
reset='\e[0m'
# Display welcome message
#echo -e "${green}Welcome \e[5;32;47m $USER \n${reset}"
echo "DB: $db" > $logfile
echo "User: $username" >> $logfile
echo "Mode: $mode" >> $logfile
echo "Script: $script" >> $logfile
date +"%d.%m.%Y %T" >> $logfile
echo -e "" >> $logfile
if [[ "$mode" != "cron" ]] ; then
cat $logfile
fi
export LW_SRC_DB=$SRC_DB
export LW_DEST_DB=$db
# http://www.cyberciti.biz/faq/shell-script-to-get-the-time-difference/
START=$(date +%s)
echo -e "+++++++++++++++++++++++++" >> $logfile
#mysql -vvv -ucsvimsne_script csvimsne_lobbywatch$env_suffix < $script 2>&1 > lobbywatch$env_suffix_sql.log
if [[ "$script" == "dbdump" ]] ; then
# http://stackoverflow.com/questions/1221833/bash-pipe-output-and-capture-exit-status
# --add-drop-database --routines --skip-extended-insert
# Add --skip-quote-names http://www.iheavy.com/2012/08/09/5-things-you-overlooked-with-mysql-dumps/
# http://unix.stackexchange.com/questions/20573/sed-insert-something-to-the-last-line
(set -o pipefail; mysqldump -h $HOST -u$username $PW --databases $db --dump-date --hex-blob --complete-insert --skip-lock-tables --single-transaction --routines --log-error=$logfile 2>>$logfile |
sed -r "s/^\s*USE.*;/-- Created: `date +"%d.%m.%Y %T"`\n\n\0\n\nSET @disable_triggers = 1; -- ibex disable triggers/i" |
sed -e "\$aSET @disable_triggers = NULL; -- ibex enable triggers" |
perl -p -e's/DEFINER=.*? SQL SECURITY DEFINER//ig' |
perl -p -e's/DEFINER=`.*?`@`localhost` //ig' |
gzip -9 >$DUMP_FILE_GZ 2>>$logfile)
elif [[ "$script" == "dbdump_data" ]] ; then
# http://stackoverflow.com/questions/5109993/mysqldump-data-only
# http://stackoverflow.com/questions/25778365/add-truncate-table-command-in-mysqldump-before-create-table-if-not-exist
# Add --skip-quote-names http://www.iheavy.com/2012/08/09/5-things-you-overlooked-with-mysql-dumps/
# http://unix.stackexchange.com/questions/20573/sed-insert-something-to-the-last-line
(set -o pipefail; mysqldump -h $HOST -u$username $PW --databases $db --dump-date --hex-blob --complete-insert --skip-lock-tables --single-transaction --no-create-db --no-create-info --skip-triggers --log-error=$logfile 2>>$logfile |
sed -r "s/^\s*USE.*;/-- Created: `date +"%d.%m.%Y %T"`\n\n-- \0 -- ibex Disable setting of original DB\n\nSET @disable_triggers = 1; -- ibex disable triggers/i" |
sed -r 's/^\s*LOCK TABLES (`[^`]+`) WRITE;/\0\nTRUNCATE \1; -- ibex added/ig' |
sed -e "\$aSET @disable_triggers = NULL; -- ibex enable triggers" |
gzip -9 >$DUMP_FILE_GZ 2>>$logfile)
elif [[ "$script" == "dbdump_struct" ]] ; then
# http://stackoverflow.com/questions/2389468/compare-structures-of-two-databases
# --routines : Routines may need additional permissions, otherwise "mysqldump: csvimsne_script has insufficent privileges to SHOW CREATE PROCEDURE"
# Conditional MySQL execution comments are sometimes multiline comments, thus use perl -0 -pe with modifier s
# http://stackoverflow.com/questions/1916392/how-can-i-get-rid-of-these-comments-in-a-mysql-dump
# http://stackoverflow.com/questions/1103149/non-greedy-regex-matching-in-sed
# mysqldump -u$username --databases $db --dump-date --no-data --skip-lock-tables --routines --log-error=$logfile >$DUMP_FILE 2>>$logfile
(set -o pipefail; mysqldump -h $HOST -u$username $PW --databases $db --dump-date --no-data --skip-lock-tables --routines --log-error=$logfile |
perl -p -e's/DEFINER=.*? SQL SECURITY DEFINER//ig' |
perl -p -e's/DEFINER=`.*?`@`localhost` //ig' |
perl -0 -pe 's|/\*![0-5][0-9]{4} (.*?)\*/|\1|sg' >$DUMP_FILE 2>>$logfile)
elif [[ "$script" == *.sql.gz ]] ; then
(set -o pipefail; zcat $script |
perl -p -e's/DEFINER=.*? SQL SECURITY DEFINER//ig' |
perl -p -e's/DEFINER=`.*?`@`localhost` ?//ig' |
perl -p -e's/csvimsne/lobbywat/ig' |
perl -p -e's/$ENV{LW_SRC_DB}/$ENV{LW_DEST_DB}/ig' |
mysql -h $HOST -u$username $db >>$logfile 2>&1)
# less -r)
else
(set -o pipefail; cat $script |
perl -p -e's/DEFINER=.*? SQL SECURITY DEFINER//ig' |
perl -p -e's/DEFINER=`.*?`@`localhost` ?//ig' |
perl -p -e's/csvimsne/lobbywat/ig' |
perl -p -e's/$ENV{LW_SRC_DB}/$ENV{LW_DEST_DB}/ig' |
mysql -h $HOST -vvv --comments -u$username $PW $db >>$logfile 2>&1)
# less -r)
fi
OK=$?
echo -e "+++++++++++++++++++++++++" >> $logfile
# http://blog.sanctum.geek.nz/testing-exit-values-bash/
if (($OK != 0)); then
date +"%d.%m.%Y %T" >> $logfile
echo -e "\n*** ERROR ***" >> $logfile
echo -e "\nFAILED" >> $logfile
END=$(date +%s)
DIFF=$(( $END - $START ))
echo "Elapsed: ${DIFF}s" >> $logfile
echo $(convertsecs $DIFF) >> $logfile
if [[ "$mode" == "interactive" ]] ; then
less -r $logfile
echo -e "\n${redBold}FAILED${reset}"
else
echo -e "\n*** ERROR, see $logfile ***\n----------------------------------------"
tail -20 $logfile
echo -e "----------------------------------------\n*** ERROR, see $logfile ***"
echo -e "\nFAILED"
fi
exit 1
else
if [[ "$script" == "dbdump" || "$script" == "dbdump_data" || "$script" == "dbdump_struct" ]] ; then
if [[ "$script" == "dbdump_data" ]] ; then
echo $DUMP_FILE_GZ > $last_dbdump_data_file
echo $DUMP_FILE_GZ > $last_dbdump_op_file
written_dump_file=$DUMP_FILE_GZ
elif [[ "$script" == "dbdump_struct" ]] ; then
echo $DUMP_FILE_GZ > $last_dbdump_struct_file
echo $DUMP_FILE > $last_dbdump_op_file
written_dump_file=$DUMP_FILE
else
echo $DUMP_FILE_GZ > $last_dbdump_file
echo $DUMP_FILE_GZ > $last_dbdump_op_file
written_dump_file=$DUMP_FILE_GZ
fi
if [[ "$mode" != "cron" ]] ; then
echo -e "\nDelete dbdumps older than 7d:" >>$logfile 2>&1
delete_verbose='-print'
fi
# http://unix.stackexchange.com/questions/136804/cron-job-to-delete-files-older-than-3-days
find $BAK_DIR/*.sql.gz $BAK_DIR/*.sql -type f -mtime +7 -delete $delete_verbose >>$logfile 2>&1
echo "File written: $written_dump_file" >>$logfile
echo -e "" >>$logfile
fi
date +"%d.%m.%Y %T" >> $logfile
END=$(date +%s)
DIFF=$(( $END - $START ))
echo "Elapsed: ${DIFF}s" >> $logfile
echo $(convertsecs $DIFF) >> $logfile
if [[ "$mode" != "cron" ]] ; then
tail -15 $logfile
echo -e "\n${greenBold}OK${reset}"
fi
fi