Subversion Repositories ALCASAR

Rev

Rev 1056 | Rev 1583 | Go to most recent revision | Only display areas with differences | Ignore whitespace | Details | Blame | Last modification | View Log

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