Subversion Repositories ALCASAR

Rev

Rev 1800 | Rev 2412 | Go to most recent revision | Details | Compare with Previous | Last modification | View Log

Rev Author Line No. Line
1 root 1
#! /bin/bash
63 franck 2
# $Id: alcasar-mysql.sh 1827 2016-04-19 09:47:29Z raphael.pion $
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"
1584 richard 15
radiuspwd="MotdePasse"
865 richard 16
new="$(date +%G%m%d-%Hh%M)"  		# date et heure des fichiers
1583 richard 17
fichier="alcasar-users-database-$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
 
1583 richard 31
expire_user ()	# remove users whom expiration date has passed to 7 days
904 franck 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
 
1583 richard 48
expire_group () # remove users of group whom expiration date has passed to 7 days
1477 franck 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
1584 richard 98
		gzip -f $rep_tr/$fichier
955 richard 99
		echo "End of export $( date "+%Hh %Mmn" )"
1 root 100
		;;
955 richard 101
	-c | --check | -check)	
102
		check
103
		;;
873 franck 104
	-i | --import | -import)
1 root 105
		if [ $nb_args -ne 2 ]
106
			then
1583 richard 107
				echo "Enter a SQL file name ('.sql' or '.sql.gz')"
1 root 108
			exit 0
109
		else
1584 richard 110
			case $2 in
111
			*.sql.gz )
112
				gunzip -f < $2 | mysql -u $DB_USER -p$radiuspwd
113
				stop_acct	
114
				;;
115
			*.sql )
116
				mysql -u $DB_USER -p$radiuspwd < $2
117
				stop_acct	
118
				;;
119
			esac
1 root 120
		fi
121
		;;
873 franck 122
	-r | --raz | -raz)
1583 richard 123
		mysqldump -u $DB_USER -p$radiuspwd --opt -BcQC  $DB_RADIUS > $rep_tr/$fichier
1584 richard 124
		gzip -f $rep_tr/$fichier
1800 richard 125
		mysql -u$DB_USER -p$radiuspwd $DB_RADIUS < /etc/raddb/empty-radiusd-db.sql
1 root 126
		;;
1477 franck 127
	-a | --acct_stop | -acct_stop)
613 richard 128
		stop_acct
129
		;;
1477 franck 130
	-e | --expire_user)
904 franck 131
		expire_user	
1477 franck 132
		expire_group	
904 franck 133
		;;
1 root 134
	*)
955 richard 135
		echo "Unknown argument :$1";
1 root 136
		echo "$usage"
137
		exit 1
138
		;;
139
esac