| 1 |
root |
1 |
#! /bin/bash
|
| 63 |
franck |
2 |
# $Id: alcasar-mysql.sh 1477 2014-11-04 21:36:39Z franck $
|
|
|
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 |
|
| 5 |
franck |
11 |
rep_tr="/var/Save/base" # répertoire d'accueil des sauvegardes
|
| 386 |
franck |
12 |
ext="sql" # extension des fichiers de sauvegarde
|
| 904 |
franck |
13 |
DB_RADIUS="radius"
|
|
|
14 |
DB_USER="radius"
|
| 1477 |
franck |
15 |
radiuspwd="MotdePasse" # Mot de passe adapté à l'install
|
| 865 |
richard |
16 |
new="$(date +%G%m%d-%Hh%M)" # date et heure des fichiers
|
| 5 |
franck |
17 |
fichier="$DB_RADIUS-$new.$ext" # nom du fichier de sauvegarde
|
| 1 |
root |
18 |
|
| 904 |
franck |
19 |
|
| 613 |
richard |
20 |
stop_acct ()
|
|
|
21 |
{
|
|
|
22 |
date_now=`date "+%F %X"`
|
|
|
23 |
echo "UPDATE radacct SET acctstoptime = '$date_now', acctterminatecause = 'Admin-Reset' WHERE acctstoptime IS NULL" | mysql -u$DB_USER -p$radiuspwd $DB_RADIUS
|
|
|
24 |
}
|
| 955 |
richard |
25 |
check ()
|
|
|
26 |
{
|
|
|
27 |
echo "check (and repair if needed) the database :"
|
|
|
28 |
mysqlcheck --databases $DB_RADIUS -u $DB_USER -p$radiuspwd --auto-repair
|
|
|
29 |
}
|
| 904 |
franck |
30 |
|
|
|
31 |
expire_user ()
|
|
|
32 |
{
|
| 909 |
franck |
33 |
del_date=`date +%F`
|
| 1477 |
franck |
34 |
MYSQL_USER=""
|
|
|
35 |
MYSQL_USER=`/usr/bin/mysql -u$DB_USER -p$radiuspwd $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';"`
|
|
|
36 |
for u in $MYSQL_USER
|
| 904 |
franck |
37 |
do
|
| 1477 |
franck |
38 |
/usr/bin/mysql -u$DB_USER -p$radiuspwd $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 |
39 |
if [ $? = 0 ]
|
|
|
40 |
then
|
|
|
41 |
echo "User $u was deleted $del_date" >> /var/log/mysqld/delete_user.log
|
|
|
42 |
else
|
|
|
43 |
echo "Delete User $u : Error $del_date" >> /var/log/mysqld/delete_user.log
|
|
|
44 |
fi
|
|
|
45 |
done
|
| 904 |
franck |
46 |
}
|
| 1477 |
franck |
47 |
|
|
|
48 |
expire_group ()
|
|
|
49 |
{
|
|
|
50 |
del_date=`date +%F`
|
|
|
51 |
MYSQL_GROUP=""
|
|
|
52 |
MYSQL_GROUP=`/usr/bin/mysql -u$DB_USER -p$radiuspwd $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';"`
|
|
|
53 |
for g in $MYSQL_GROUP
|
|
|
54 |
do
|
|
|
55 |
MYSQL_USERGROUP=""
|
|
|
56 |
MYSQL_USERGROUP=`/usr/bin/mysql -u$DB_USER -p$radiuspwd $DB_RADIUS -ss --exec "SELECT username FROM radusergroup WHERE groupname = '$g';"`
|
|
|
57 |
for u in $MYSQL_USERGROUP
|
|
|
58 |
do
|
|
|
59 |
/usr/bin/mysql -u$DB_USER -p$radiuspwd $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';"
|
|
|
60 |
if [ $? = 0 ]
|
|
|
61 |
then
|
|
|
62 |
echo "User $u was deleted $del_date" >> /var/log/mysqld/delete_user.log
|
|
|
63 |
else
|
|
|
64 |
echo "Delete User $u : Error $del_date" >> /var/log/mysqld/delete_user.log
|
|
|
65 |
fi
|
|
|
66 |
done
|
|
|
67 |
/usr/bin/mysql -u$DB_USER -p$radiuspwd $DB_RADIUS --exec "DELETE FROM radgroupreply WHERE groupname = '$g'; DELETE FROM radgroupcheck WHERE groupname = '$g';"
|
|
|
68 |
if [ $? = 0 ]
|
|
|
69 |
then
|
|
|
70 |
echo "Group $g was deleted $del_date" >> /var/log/mysqld/delete_group.log
|
|
|
71 |
else
|
|
|
72 |
echo "Delete Group $g : Error $del_date" >> /var/log/mysqld/delete_group.log
|
|
|
73 |
fi
|
|
|
74 |
done
|
|
|
75 |
}
|
|
|
76 |
|
|
|
77 |
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 |
78 |
nb_args=$#
|
|
|
79 |
args=$1
|
|
|
80 |
if [ $nb_args -eq 0 ]
|
|
|
81 |
then
|
|
|
82 |
nb_args=1
|
|
|
83 |
args="-h"
|
|
|
84 |
fi
|
|
|
85 |
case $args in
|
|
|
86 |
-\? | -h* | --h*)
|
|
|
87 |
echo "$usage"
|
|
|
88 |
exit 0
|
|
|
89 |
;;
|
| 873 |
franck |
90 |
-d | --dump | -dump)
|
| 1 |
root |
91 |
[ -d $rep_tr ] || mkdir -p $rep_tr
|
|
|
92 |
if [ -e $fichier ];
|
|
|
93 |
then rm -f $fichier
|
|
|
94 |
fi
|
| 955 |
richard |
95 |
check
|
|
|
96 |
echo "Export the database in file : $fichier"
|
| 1 |
root |
97 |
mysqldump -u $DB_USER -p$radiuspwd --opt -BcQC $DB_RADIUS > $rep_tr/$fichier
|
| 955 |
richard |
98 |
echo "End of export $( date "+%Hh %Mmn" )"
|
| 1 |
root |
99 |
;;
|
| 955 |
richard |
100 |
-c | --check | -check)
|
|
|
101 |
check
|
|
|
102 |
;;
|
| 873 |
franck |
103 |
-i | --import | -import)
|
| 1 |
root |
104 |
if [ $nb_args -ne 2 ]
|
|
|
105 |
then
|
| 955 |
richard |
106 |
echo "Enter a SQL file name (.sql)"
|
| 1 |
root |
107 |
exit 0
|
|
|
108 |
else
|
| 613 |
richard |
109 |
mysql -u $DB_USER -p$radiuspwd < $2
|
|
|
110 |
stop_acct
|
| 1 |
root |
111 |
fi
|
|
|
112 |
;;
|
| 873 |
franck |
113 |
-r | --raz | -raz)
|
| 417 |
franck |
114 |
mysqldump -u $DB_USER -p$radiuspwd --opt -BcQC $DB_RADIUS > $rep_tr/$fichier && \
|
| 1 |
root |
115 |
mysql -u$DB_USER -p$radiuspwd $DB_RADIUS < /etc/raddb/radiusd-db-vierge.sql
|
|
|
116 |
;;
|
| 1477 |
franck |
117 |
-a | --acct_stop | -acct_stop)
|
| 613 |
richard |
118 |
stop_acct
|
|
|
119 |
;;
|
| 1477 |
franck |
120 |
-e | --expire_user)
|
| 904 |
franck |
121 |
expire_user
|
| 1477 |
franck |
122 |
expire_group
|
| 904 |
franck |
123 |
;;
|
| 1 |
root |
124 |
*)
|
| 955 |
richard |
125 |
echo "Unknown argument :$1";
|
| 1 |
root |
126 |
echo "$usage"
|
|
|
127 |
exit 1
|
|
|
128 |
;;
|
|
|
129 |
esac
|