Rev 2412 | Rev 2811 | Go to most recent revision | Details | Compare with Previous | Last modification | View Log
Rev | Author | Line No. | Line |
---|---|---|---|
2412 | tom.houday | 1 | #!/bin/bash |
63 | franck | 2 | # $Id: alcasar-mysql.sh 2664 2018-11-19 17:35:12Z tom.houdayer $ |
3 | |||
672 | richard | 4 | # alcasar-mysql.sh |
5 | # by Franck BOUIJOUX, Pascal LEVANT and Richard REY |
||
6 | # This script is distributed under the Gnu General Public License (GPL) |
||
7 | |||
8 | # Gestion (sauvegarde / import / RAZ) de la base MySQL 'radius'. Fermeture des sessions de comptabilité ouvertes |
||
9 | # Management of mysql 'radius' database (save / import / RAZ). Close the accounting open sessions |
||
10 | |||
2412 | tom.houday | 11 | rep_tr="/var/Save/base" # répertoire d'accueil des sauvegardes |
2664 | tom.houday | 12 | DIR_BIN="/usr/local/bin" |
2412 | tom.houday | 13 | PASSWD_FILE="/root/ALCASAR-passwords.txt" |
904 | franck | 14 | DB_RADIUS="radius" |
2412 | tom.houday | 15 | DB_USER=$(grep '^db_user=' $PASSWD_FILE | cut -d'=' -f 2-) |
16 | DB_PASS=$(grep '^db_password=' $PASSWD_FILE | cut -d'=' -f 2-) |
||
17 | new="$(date +%G%m%d-%Hh%M)" # date et heure des fichiers |
||
18 | fichier="alcasar-users-database-$new.sql" # nom du fichier de sauvegarde |
||
1 | root | 19 | |
904 | franck | 20 | |
613 | richard | 21 | stop_acct () |
22 | { |
||
23 | date_now=`date "+%F %X"` |
||
2412 | tom.houday | 24 | echo "UPDATE radacct SET acctstoptime = '$date_now', acctterminatecause = 'Admin-Reset' WHERE acctstoptime IS NULL" | mysql -u$DB_USER -p$DB_PASS $DB_RADIUS |
613 | richard | 25 | } |
955 | richard | 26 | check () |
27 | { |
||
28 | echo "check (and repair if needed) the database :" |
||
2412 | tom.houday | 29 | mysqlcheck --databases $DB_RADIUS -u $DB_USER -p$DB_PASS --auto-repair |
955 | richard | 30 | } |
904 | franck | 31 | |
1583 | richard | 32 | expire_user () # remove users whom expiration date has passed to 7 days |
904 | franck | 33 | { |
909 | franck | 34 | del_date=`date +%F` |
1477 | franck | 35 | MYSQL_USER="" |
2412 | tom.houday | 36 | MYSQL_USER=`/usr/bin/mysql -u$DB_USER -p$DB_PASS $DB_RADIUS -ss --exec "SELECT username FROM radcheck WHERE ( DATE_SUB(CURDATE(),INTERVAL 7 DAY) > STR_TO_DATE(value,'%d %M %Y')) AND attribute='Expiration';"` |
1477 | franck | 37 | for u in $MYSQL_USER |
904 | franck | 38 | do |
2412 | tom.houday | 39 | /usr/bin/mysql -u$DB_USER -p$DB_PASS $DB_RADIUS --exec "DELETE FROM radusergroup WHERE username = '$u'; DELETE FROM radreply WHERE username = '$u'; DELETE FROM userinfo WHERE UserName = '$u'; DELETE FROM radcheck WHERE username = '$u';" |
909 | franck | 40 | if [ $? = 0 ] |
41 | then |
||
42 | echo "User $u was deleted $del_date" >> /var/log/mysqld/delete_user.log |
||
43 | else |
||
44 | echo "Delete User $u : Error $del_date" >> /var/log/mysqld/delete_user.log |
||
45 | fi |
||
46 | done |
||
904 | franck | 47 | } |
1477 | franck | 48 | |
1583 | richard | 49 | expire_group () # remove users of group whom expiration date has passed to 7 days |
1477 | franck | 50 | { |
51 | del_date=`date +%F` |
||
52 | MYSQL_GROUP="" |
||
2412 | tom.houday | 53 | MYSQL_GROUP=`/usr/bin/mysql -u$DB_USER -p$DB_PASS $DB_RADIUS -ss --exec "SELECT groupname FROM radgroupcheck WHERE ( DATE_SUB(CURDATE(),INTERVAL 7 DAY) > STR_TO_DATE(value,'%d %M %Y')) AND attribute='Expiration';"` |
1477 | franck | 54 | for g in $MYSQL_GROUP |
55 | do |
||
56 | MYSQL_USERGROUP="" |
||
2412 | tom.houday | 57 | MYSQL_USERGROUP=`/usr/bin/mysql -u$DB_USER -p$DB_PASS $DB_RADIUS -ss --exec "SELECT username FROM radusergroup WHERE groupname = '$g';"` |
1477 | franck | 58 | for u in $MYSQL_USERGROUP |
59 | do |
||
2412 | tom.houday | 60 | /usr/bin/mysql -u$DB_USER -p$DB_PASS $DB_RADIUS --exec "DELETE FROM radusergroup WHERE username = '$u'; DELETE FROM radreply WHERE username = '$u'; DELETE FROM userinfo WHERE UserName = '$u'; DELETE FROM radcheck WHERE username = '$u';" |
1477 | franck | 61 | if [ $? = 0 ] |
62 | then |
||
63 | echo "User $u was deleted $del_date" >> /var/log/mysqld/delete_user.log |
||
64 | else |
||
65 | echo "Delete User $u : Error $del_date" >> /var/log/mysqld/delete_user.log |
||
66 | fi |
||
67 | done |
||
2412 | tom.houday | 68 | /usr/bin/mysql -u$DB_USER -p$DB_PASS $DB_RADIUS --exec "DELETE FROM radgroupreply WHERE groupname = '$g'; DELETE FROM radgroupcheck WHERE groupname = '$g';" |
1477 | franck | 69 | if [ $? = 0 ] |
70 | then |
||
71 | echo "Group $g was deleted $del_date" >> /var/log/mysqld/delete_group.log |
||
72 | else |
||
73 | echo "Delete Group $g : Error $del_date" >> /var/log/mysqld/delete_group.log |
||
74 | fi |
||
75 | done |
||
76 | } |
||
77 | |||
78 | usage="Usage: alcasar-mysql.sh { -d or --dump } | { -c or --check } | { -i or --import } | { -r or --raz } | { -a or --acct_stop } | [ -e or --expire_user ]" |
||
1 | root | 79 | nb_args=$# |
80 | args=$1 |
||
81 | if [ $nb_args -eq 0 ] |
||
82 | then |
||
83 | nb_args=1 |
||
84 | args="-h" |
||
85 | fi |
||
86 | case $args in |
||
87 | -\? | -h* | --h*) |
||
88 | echo "$usage" |
||
89 | exit 0 |
||
90 | ;; |
||
873 | franck | 91 | -d | --dump | -dump) |
1 | root | 92 | [ -d $rep_tr ] || mkdir -p $rep_tr |
93 | if [ -e $fichier ]; |
||
94 | then rm -f $fichier |
||
95 | fi |
||
955 | richard | 96 | check |
2412 | tom.houday | 97 | echo "Export the database in file : $fichier.gz" |
98 | mysqldump -u $DB_USER -p$DB_PASS --opt -BcQC $DB_RADIUS > $rep_tr/$fichier |
||
1584 | richard | 99 | gzip -f $rep_tr/$fichier |
955 | richard | 100 | echo "End of export $( date "+%Hh %Mmn" )" |
1 | root | 101 | ;; |
955 | richard | 102 | -c | --check | -check) |
103 | check |
||
104 | ;; |
||
873 | franck | 105 | -i | --import | -import) |
1 | root | 106 | if [ $nb_args -ne 2 ] |
107 | then |
||
1583 | richard | 108 | echo "Enter a SQL file name ('.sql' or '.sql.gz')" |
1 | root | 109 | exit 0 |
110 | else |
||
1584 | richard | 111 | case $2 in |
112 | *.sql.gz ) |
||
2412 | tom.houday | 113 | gunzip -f < $2 | mysql -u $DB_USER -p$DB_PASS |
1584 | richard | 114 | stop_acct |
115 | ;; |
||
116 | *.sql ) |
||
2412 | tom.houday | 117 | mysql -u $DB_USER -p$DB_PASS < $2 |
1584 | richard | 118 | stop_acct |
119 | ;; |
||
120 | esac |
||
2664 | tom.houday | 121 | migrationsPath="$DIR_BIN/alcasar-migrations" |
122 | "$migrationsPath/alcasar-migration-3.2.0_dbStructure.sh" |
||
123 | "$migrationsPath/alcasar-migration-3.3.0_dbRadiusAttrs.sh" |
||
124 | "$migrationsPath/alcasar-migration-3.3.1_dbRadiusAttrs.sh" |
||
1 | root | 125 | fi |
126 | ;; |
||
873 | franck | 127 | -r | --raz | -raz) |
2412 | tom.houday | 128 | mysqldump -u $DB_USER -p$DB_PASS --opt -BcQC $DB_RADIUS > $rep_tr/$fichier |
1584 | richard | 129 | gzip -f $rep_tr/$fichier |
2412 | tom.houday | 130 | mysql -u$DB_USER -p$DB_PASS $DB_RADIUS < /etc/raddb/empty-radiusd-db.sql |
1 | root | 131 | ;; |
1477 | franck | 132 | -a | --acct_stop | -acct_stop) |
613 | richard | 133 | stop_acct |
134 | ;; |
||
1477 | franck | 135 | -e | --expire_user) |
904 | franck | 136 | expire_user |
1477 | franck | 137 | expire_group |
904 | franck | 138 | ;; |
1 | root | 139 | *) |
955 | richard | 140 | echo "Unknown argument :$1"; |
1 | root | 141 | echo "$usage" |
142 | exit 1 |
||
143 | ;; |
||
144 | esac |