1 | #!/bin/bash |
---|
2 | ########################################################### |
---|
3 | # |
---|
4 | # This script helps to manage dbase installation and upgrade |
---|
5 | # include functionalies such as dbbackup, translation templates |
---|
6 | # dbuser creation, ... |
---|
7 | # |
---|
8 | ########################################################### |
---|
9 | # OPTIONS |
---|
10 | # |
---|
11 | # --config package_name -> generate a configfile to indicate database, dbuser , ... to be created |
---|
12 | # --template package_name -> generate a template file to personalize questions during instalation |
---|
13 | # --install package_name -> create database, dbuser, inject data, ... especified in configfile |
---|
14 | # --upgrade package_name -> used to preserve database during package upgrade (backup) |
---|
15 | # --remove package_name -> remove dbinclude file but preserve database |
---|
16 | # --purge package_name -> remove dbinclude file and database |
---|
17 | # --inject_sql package_name sql_file_with_path -> inject additional data in database |
---|
18 | # --is_include_present -> check if include file exists |
---|
19 | # --add_include_file -> create include file |
---|
20 | # --db_is_present -> check if database exists |
---|
21 | # --sql_mode -> change sql_mode variable to correct perfomance |
---|
22 | ########################################################### |
---|
23 | |
---|
24 | PACKAGE_NAME=$2 |
---|
25 | DEFAULT_SAMPLES_PATH="/usr/share/lliurex-sgbd/" |
---|
26 | CONFIG_FILE_PATH="/etc/lliurex-sgbd/" |
---|
27 | CONFIG_FILE=$CONFIG_FILE_PATH$PACKAGE_NAME".sgbd" |
---|
28 | |
---|
29 | DB_USER_PASS="`pwgen --capitalize --numerals 7 1`" |
---|
30 | |
---|
31 | INCLUDE_DB_FILE="debian-db.php" |
---|
32 | TEMPLATE="lliurex-sgbd" |
---|
33 | DB_IS_IN_MYSQL="NO" |
---|
34 | TEST=0 |
---|
35 | |
---|
36 | debug=0 |
---|
37 | NOW=$(date +"%Y.%m.%d.%H.%M") |
---|
38 | |
---|
39 | read_config_file(){ |
---|
40 | if [ -e $CONFIG_FILE ]; then |
---|
41 | DB_NAME=$(sed -ne "/^\[parameters\]/,/^\[/s%^DB_NAME[^=]\+=[[:space:]]\+%%p" "$CONFIG_FILE") |
---|
42 | DB_USER=$(sed -ne "/^\[parameters\]/,/^\[/s%^DB_USER[^=]\+=[[:space:]]\+%%p" "$CONFIG_FILE") |
---|
43 | DB_PASS=$(sed -ne "/^\[parameters\]/,/^\[/s%^DB_PASS[^=]\+=[[:space:]]\+%%p" "$CONFIG_FILE") |
---|
44 | MYSQL_DATA_FILE=$(sed -ne "/^\[parameters\]/,/^\[/s%^MYSQL_DATA_FILE[^=]\+=[[:space:]]\+%%p" "$CONFIG_FILE") |
---|
45 | INCLUDE_PATH=$(sed -ne "/^\[parameters\]/,/^\[/s%^INCLUDE_PATH[^=]\+=[[:space:]]\+%%p" "$CONFIG_FILE") |
---|
46 | BACKUP_PATH=$(sed -ne "/^\[parameters\]/,/^\[/s%^BACKUP_PATH[^=]\+=[[:space:]]\+%%p" "$CONFIG_FILE") |
---|
47 | USE_TEMPLATE=$(sed -ne "/^\[parameters\]/,/^\[/s%^USE_TEMPLATE[^=]\+=[[:space:]]\+%%p" "$CONFIG_FILE") |
---|
48 | if [ "$USE_TEMPLATE" = "YES" ] ; then |
---|
49 | TEMPLATE=$PACKAGE_NAME |
---|
50 | fi |
---|
51 | DB_PASS=$(echo $DB_PASS | tr -d ' ') |
---|
52 | |
---|
53 | if [ "$DB_PASS" != "" ] ; then |
---|
54 | DB_USER_PASS=$DB_PASS |
---|
55 | fi |
---|
56 | |
---|
57 | INCLUDE_FILE=$INCLUDE_PATH/$INCLUDE_DB_FILE |
---|
58 | return 0 |
---|
59 | else |
---|
60 | logger -s -t lliurex-sgbd "Config file for database not found!!, expecting ${CONFIG_FILE}" |
---|
61 | return 1 |
---|
62 | fi |
---|
63 | } |
---|
64 | |
---|
65 | inject_sql_file(){ |
---|
66 | cat $1 | mysql -u root -p$MYSQL_ROOT_PASS $DB_NAME |
---|
67 | } |
---|
68 | |
---|
69 | create_database(){ |
---|
70 | mysql -u root -p$MYSQL_ROOT_PASS -e "CREATE DATABASE $DB_NAME DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;" |
---|
71 | if [ ! -z $MYSQL_DATA_FILE ]; then |
---|
72 | inject_sql_file $MYSQL_DATA_FILE |
---|
73 | fi |
---|
74 | } |
---|
75 | |
---|
76 | create_user(){ |
---|
77 | local sqlQuery |
---|
78 | sqlQuery="GRANT ALL ON $DB_NAME.* TO $DB_USER@localhost IDENTIFIED BY '$DB_USER_PASS'; " |
---|
79 | sqlQuery=$sqlQuery"FLUSH PRIVILEGES;" |
---|
80 | mysql -u root -p$MYSQL_ROOT_PASS -e "$sqlQuery" |
---|
81 | } |
---|
82 | |
---|
83 | mysql_version_upper(){ |
---|
84 | local ret=1; |
---|
85 | ver=$(mysqld --version 2>&1|egrep -o '[0-9]+\.[0-9]+\.[0-9]+') |
---|
86 | is_maria=$(mysqld --version 2>&1|grep -io 'mariadb'|wc -l) |
---|
87 | if [ $is_maria -gt 0 ]; then |
---|
88 | type_db='maria'; |
---|
89 | else |
---|
90 | type_db='mysql'; |
---|
91 | fi |
---|
92 | |
---|
93 | if [ "$type_db" = "maria" ]; then |
---|
94 | ret=0 |
---|
95 | else |
---|
96 | if dpkg --compare-versions $ver gt "5.7.6"; then ret=0; fi; |
---|
97 | fi |
---|
98 | |
---|
99 | |
---|
100 | if [ $debug -eq 1 ]; then |
---|
101 | if [ $ret -eq 0 ]; then |
---|
102 | echo "Compare versions: Detected $type_db version $ver (mysql > 5.7.6 new user tables)" |
---|
103 | else |
---|
104 | echo "Compare versions: Detected $type_db version $ver (mysql < 5.7.6)" |
---|
105 | fi |
---|
106 | fi |
---|
107 | |
---|
108 | return $ret |
---|
109 | } |
---|
110 | |
---|
111 | update_passwd() { |
---|
112 | local sqlQuery |
---|
113 | |
---|
114 | if mysql_version_upper; then |
---|
115 | sqlQuery="UPDATE mysql.user SET authentication_string=PASSWORD('$passwd'),plugin='mysql_native_password',password_expired='N' WHERE User='root'; " |
---|
116 | else |
---|
117 | sqlQuery="UPDATE mysql.user SET Password=PASSWORD('$passwd') WHERE User='root'; " |
---|
118 | fi |
---|
119 | sqlQuery=$sqlQuery"FLUSH PRIVILEGES;" |
---|
120 | |
---|
121 | mysql -u root -p$MYSQL_ROOT_PASS -e "$sqlQuery" |
---|
122 | } |
---|
123 | |
---|
124 | create_include_file(){ |
---|
125 | if [ ! -d $INCLUDE_PATH ] ; then |
---|
126 | mkdir $INCLUDE_PATH |
---|
127 | fi |
---|
128 | fecha=$(date) |
---|
129 | echo "Creating include file: $INCLUDE_FILE" |
---|
130 | echo "<?php"> $INCLUDE_FILE |
---|
131 | echo "##">> $INCLUDE_FILE |
---|
132 | echo "## database access settings in php format ">> $INCLUDE_FILE |
---|
133 | echo "## automatically generated by lliurex-sgbd ">> $INCLUDE_FILE |
---|
134 | echo "## on "$fecha >> $INCLUDE_FILE |
---|
135 | echo "## ">> $INCLUDE_FILE |
---|
136 | echo "\$dbuser='$DB_USER'; ">> $INCLUDE_FILE |
---|
137 | echo "\$dbpass='$DB_USER_PASS'; ">> $INCLUDE_FILE |
---|
138 | echo "\$basepath=''; ">> $INCLUDE_FILE |
---|
139 | echo "\$dbname='$DB_NAME'; ">> $INCLUDE_FILE |
---|
140 | echo "\$dbserver=''; ">> $INCLUDE_FILE |
---|
141 | echo "\$dbport=''; ">> $INCLUDE_FILE |
---|
142 | echo "\$dbtype='mysql'; ">> $INCLUDE_FILE |
---|
143 | chmod 640 $INCLUDE_FILE || true |
---|
144 | chgrp www-data $INCLUDE_FILE || true |
---|
145 | } |
---|
146 | |
---|
147 | database_backup(){ |
---|
148 | if [ ! -d $BACKUP_PATH ] ; then |
---|
149 | mkdir -p $BACKUP_PATH |
---|
150 | fi |
---|
151 | #NOW=$(date +"%Y.%m.%d.%H.%M") |
---|
152 | echo "Doing database backup: $BACKUP_PATH/backup.$NOW.sql" |
---|
153 | mysqldump -u root -p$MYSQL_ROOT_PASS $DB_NAME > $BACKUP_PATH"/backup."$NOW".sql" 2>/dev/null |
---|
154 | } |
---|
155 | |
---|
156 | install_db(){ |
---|
157 | if [ $TEST -ne 0 ] ; then |
---|
158 | # Existe la base de datos. Hacer copia base de datos" |
---|
159 | database_backup |
---|
160 | lliurex-sgbd-purge-question |
---|
161 | if [ "$RET" = "true" ] ; then |
---|
162 | mysql -u root -p$MYSQL_ROOT_PASS -e "DROP DATABASE $DB_NAME;" |
---|
163 | create_database |
---|
164 | fi |
---|
165 | else |
---|
166 | # No existe la base de datos |
---|
167 | create_database |
---|
168 | fi |
---|
169 | |
---|
170 | SENTENCIA="SELECT user from mysql.user where user='$DB_USER'" |
---|
171 | TEST=$(mysql -u root -p$MYSQL_ROOT_PASS -e "$SENTENCIA" | wc -l) |
---|
172 | |
---|
173 | if [ $TEST -ne 0 ] ; then |
---|
174 | # Existe usuario. Mantener usuario y cambiar contraseña |
---|
175 | update_passwd |
---|
176 | else |
---|
177 | create_user |
---|
178 | fi |
---|
179 | } |
---|
180 | |
---|
181 | create_template_file(){ |
---|
182 | echo "Template: "$PACKAGE_NAME"/purge">> $NEW_TEMPLATE_FILE |
---|
183 | echo "Type: boolean ">> $NEW_TEMPLATE_FILE |
---|
184 | echo "Description: Do you want to purge "$PACKAGE_NAME"'s database?" >> $NEW_TEMPLATE_FILE |
---|
185 | echo "Description-es.UTF-8: ¿Desea eliminar la base de datos de "$PACKAGE_NAME"?">> $NEW_TEMPLATE_FILE |
---|
186 | echo "Description-qcv.UTF-8: Desitja eliminar la base de dades de "$PACKAGE_NAME"?">> $NEW_TEMPLATE_FILE |
---|
187 | } |
---|
188 | |
---|
189 | get_config_data(){ |
---|
190 | local nolog=0; |
---|
191 | if [ "x$1" = "xnolog" ]; then |
---|
192 | nolog=1 |
---|
193 | fi |
---|
194 | if [[ $EUID -ne 0 ]]; then |
---|
195 | echo "This script must be run as root" 1>&2 |
---|
196 | exit 1 |
---|
197 | fi |
---|
198 | |
---|
199 | mysql_root_passwd --initialize |
---|
200 | if [ $? -ne 0 ] ; then |
---|
201 | echo "Error en mysql_root_passwd" |
---|
202 | exit 1 |
---|
203 | fi |
---|
204 | MYSQL_ROOT_PASS=$(mysql_root_passwd -g) |
---|
205 | |
---|
206 | rc=0 |
---|
207 | read_config_file |
---|
208 | if [ $? -eq 1 ]; then |
---|
209 | logger -s -t lliurex-sgbd "Trying to find '$PACKAGE_NAME' as DB_NAME" |
---|
210 | local DB_NAME |
---|
211 | DB_NAME=$PACKAGE_NAME |
---|
212 | fi |
---|
213 | #old method: TEST=$(mysqlshow -u root -p$MYSQL_ROOT_PASS | grep " "$DB_NAME" " | wc -l) |
---|
214 | #new method |
---|
215 | mysql -u root -p${MYSQL_ROOT_PASS} -e "show databases" | egrep ^$DB_NAME\$ >/dev/null|| rc=1 |
---|
216 | |
---|
217 | if [ ${rc} -eq 0 ]; then |
---|
218 | if [ $nolog -ne 1 ]; then |
---|
219 | logger -s -t lliurex-sgbd "Database '$DB_NAME' FOUND " |
---|
220 | fi |
---|
221 | DB_IS_IN_MYSQL="YES" |
---|
222 | touch /tmp/db_is_present |
---|
223 | TEST=1 |
---|
224 | else |
---|
225 | if [ $nolog -ne 1 ]; then |
---|
226 | logger -s -t lliurex-sgbd "Database '$DB_NAME' NOT found!" |
---|
227 | fi |
---|
228 | DB_IS_IN_MYSQL="NO" |
---|
229 | TEST=0 |
---|
230 | fi |
---|
231 | } |
---|
232 | |
---|
233 | edit_sql_mode(){ |
---|
234 | MYSQLCNF_PATH="/etc/mysql/mysql.cnf" |
---|
235 | MYSQLCNF_DIR=$(dirname ${MYSQLCNF_PATH}) |
---|
236 | SQLCONFIG_NEW='sql_mode="NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"' |
---|
237 | |
---|
238 | if [ -f ${MYSQLCNF_PATH} ]; then |
---|
239 | SQLCONFIG_OLD=$(sed -e '/sql_mode'/!d ${MYSQLCNF_PATH}) |
---|
240 | |
---|
241 | if [ -z ${SQLCONFIG_OLD} ]; then |
---|
242 | |
---|
243 | echo "Doing backup of file ${MYSQLCNF_PATH}" |
---|
244 | cp ${MYSQLCNF_PATH} ${MYSQLCNF_DIR}"/mysql_"${NOW}".cnf" |
---|
245 | echo "Adding sql_mode in file ${MYSQLCNF_PATH}" |
---|
246 | sed -i '$a'${SQLCONFIG_NEW} ${MYSQLCNF_PATH} |
---|
247 | systemctl restart mysql |
---|
248 | else |
---|
249 | if [[ ${SQLCONFIG_OLD} == ${SQLCONFIG_NEW} ]]; then |
---|
250 | |
---|
251 | echo "NOTHING TO DO. File ${MYSQLCNF_PATH} already update" |
---|
252 | else |
---|
253 | echo "Doing backup of file ${MYSQLCNF_PATH}" |
---|
254 | cp ${MYSQLCNF_PATH} ${MYSQLCNF_DIR}"/mysql_"${NOW}".cnf" |
---|
255 | echo "Adding new sql_mode configuration in file ${MYSQLCNF_PATH}" |
---|
256 | sed -i 's/'${SQLCONFIG_OLD}'/'${SQLCONFIG_NEW}'/g' ${MYSQLCNF_PATH} |
---|
257 | systemctl restart mysql |
---|
258 | fi |
---|
259 | fi |
---|
260 | else |
---|
261 | echo "NOTHING TO DO. File ${MYSQLCNF_PATH} NOT FOUND" |
---|
262 | fi |
---|
263 | } |
---|
264 | |
---|
265 | usage (){ |
---|
266 | echo "usage: lliurex-sgbd --config package_name | --template package_name | --install package_name | --upgrade package_name | --remove package_name | --purge package_name | --inject_sql package_name sql_file_with_path | --db_is_present package_name | --add_include_file package_name | --is_include_present package_name | --sql_mode package_name" |
---|
267 | } |
---|
268 | |
---|
269 | #main |
---|
270 | |
---|
271 | if [ $# -eq 0 ] ; then |
---|
272 | usage |
---|
273 | exit 1 |
---|
274 | fi |
---|
275 | |
---|
276 | if [ $# -lt 2 ] ; then |
---|
277 | echo "" |
---|
278 | echo "Wrong parameters number!!" |
---|
279 | echo "" |
---|
280 | usage |
---|
281 | exit 1 |
---|
282 | fi |
---|
283 | |
---|
284 | case $1 in |
---|
285 | --config) # generate config file |
---|
286 | NEW_CONFIG_FILE=`pwd`"/"$PACKAGE_NAME".sgbd" |
---|
287 | cp $DEFAULT_SAMPLES_PATH"sample.sgbd" $NEW_CONFIG_FILE |
---|
288 | echo "generated file "$NEW_CONFIG_FILE", after modify, please move to /etc/lliurex-sgbd" |
---|
289 | ;; |
---|
290 | --template) # generate questions template |
---|
291 | NEW_TEMPLATE_FILE=`pwd`"/"$PACKAGE_NAME".templates" |
---|
292 | create_template_file |
---|
293 | echo "" |
---|
294 | echo "generated file "$NEW_TEMPLATE_FILE" remember to move it to your package debian directory" |
---|
295 | echo "and execute debconf-gettextize debian/"$PACKAGE_NAME".templates" |
---|
296 | echo "After that, remove obsolete files: master.old and localized templates (ie, templates.xx)" |
---|
297 | echo "" |
---|
298 | echo "More info: http://manpages.ubuntu.com/manpages/dapper/man7/po-debconf.7.html" |
---|
299 | echo "" |
---|
300 | ;; |
---|
301 | --install) # install new database |
---|
302 | get_config_data nolog |
---|
303 | install_db |
---|
304 | create_include_file |
---|
305 | echo "Install complete" |
---|
306 | ;; |
---|
307 | --upgrade) # upgrade database |
---|
308 | get_config_data |
---|
309 | if [ $TEST -ne 0 ] ; then |
---|
310 | # Existe la base de datos. Hacer copia base de datos" |
---|
311 | database_backup |
---|
312 | fi |
---|
313 | #si por algún motivo se ha perdido la configuració se vuelve a crear |
---|
314 | if [ ! -e $INCLUDE_FILE ] ; then |
---|
315 | create_include_file |
---|
316 | fi |
---|
317 | ;; |
---|
318 | --remove) |
---|
319 | get_config_data |
---|
320 | if [ -e $INCLUDE_FILE ] ; then |
---|
321 | rm -f $INCLUDE_FILE |
---|
322 | fi |
---|
323 | ;; |
---|
324 | --purge) |
---|
325 | get_config_data nolog |
---|
326 | if [ $TEST -eq 0 ]; then |
---|
327 | echo "Database not found, exiting" |
---|
328 | exit 1 |
---|
329 | fi |
---|
330 | /usr/sbin/lliurex-sgbd-purge-question $TEMPLATE |
---|
331 | if [ $? -ne 0 ] ; then |
---|
332 | # Existe la base de datos. Hacer copia base de datos" |
---|
333 | database_backup |
---|
334 | mysql -u root -p$MYSQL_ROOT_PASS -e "DROP DATABASE $DB_NAME;" 2>/dev/null |
---|
335 | mysql -u root -p$MYSQL_ROOT_PASS -e "DROP USER $DB_USER@localhost;" 2>/dev/null |
---|
336 | mysql -u root -p$MYSQL_ROOT_PASS -e "flush privileges;" 2> /dev/null |
---|
337 | fi |
---|
338 | |
---|
339 | if [ -e $INCLUDE_FILE ] ; then |
---|
340 | rm -f $INCLUDE_FILE |
---|
341 | fi |
---|
342 | echo "Purge complete" |
---|
343 | ;; |
---|
344 | --inject_sql) |
---|
345 | get_config_data |
---|
346 | inject_sql_file $3 |
---|
347 | ;; |
---|
348 | --is_include_present) |
---|
349 | get_config_data > /dev/null |
---|
350 | if [ -e $INCLUDE_FILE ] ; then |
---|
351 | echo "YES" |
---|
352 | else |
---|
353 | echo "NO" |
---|
354 | fi |
---|
355 | ;; |
---|
356 | --add_include_file) |
---|
357 | get_config_data |
---|
358 | create_include_file |
---|
359 | ;; |
---|
360 | --db_is_present) |
---|
361 | [ ! -e /tmp/db_is_present ] || rm -f /tmp/db_is_present |
---|
362 | get_config_data #> /dev/null |
---|
363 | echo "$DB_IS_IN_MYSQL" |
---|
364 | ;; |
---|
365 | |
---|
366 | --sql_mode) |
---|
367 | edit_sql_mode |
---|
368 | ;; |
---|
369 | |
---|
370 | *) |
---|
371 | usage |
---|
372 | ;; |
---|
373 | esac |
---|
374 | |
---|
375 | exit 0 |
---|
376 | |
---|