Subversion Repositories ALCASAR

Rev

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

Rev 1800 Rev 1827
1
#! /bin/bash
1
#! /bin/bash
2
# $Id: alcasar-mysql.sh 1800 2016-02-28 18:02:27Z richard $
2
# $Id: alcasar-mysql.sh 1827 2016-04-19 09:47:29Z raphael.pion $
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="MotdePasse"
15
radiuspwd="MotdePasse"
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="alcasar-users-database-$new.$ext"		# nom du fichier de sauvegarde
17
fichier="alcasar-users-database-$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 ()	# remove users whom expiration date has passed to 7 days
31
expire_user ()	# remove users whom expiration date has passed to 7 days
32
{
32
{
33
	del_date=`date +%F`
33
	del_date=`date +%F`
34
	MYSQL_USER=""
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';"`
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
36
	for u in $MYSQL_USER
37
	do
37
	do
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
		 /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';"
39
		if [ $? = 0 ]
39
		if [ $? = 0 ]
40
		then
40
		then
41
			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
42
		else
42
		else
43
			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
44
		fi
44
		fi
45
 	 done
45
 	 done
46
}
46
}
47
 
47
 
48
expire_group () # remove users of group whom expiration date has passed to 7 days
48
expire_group () # remove users of group whom expiration date has passed to 7 days
49
{
49
{
50
	del_date=`date +%F`
50
	del_date=`date +%F`
51
	MYSQL_GROUP=""
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';"`
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
53
	for g in $MYSQL_GROUP
54
	do
54
	do
55
		MYSQL_USERGROUP=""
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';"`
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
57
		for u in $MYSQL_USERGROUP
58
		do
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';"
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 ]
60
			if [ $? = 0 ]
61
			then
61
			then
62
				echo "User $u was deleted $del_date" >> /var/log/mysqld/delete_user.log
62
				echo "User $u was deleted $del_date" >> /var/log/mysqld/delete_user.log
63
			else
63
			else
64
				echo "Delete User $u : Error $del_date" >> /var/log/mysqld/delete_user.log
64
				echo "Delete User $u : Error $del_date" >> /var/log/mysqld/delete_user.log
65
			fi
65
			fi
66
	 	 done
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';"
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 ]
68
		if [ $? = 0 ]
69
		then
69
		then
70
			echo "Group $g was deleted $del_date" >> /var/log/mysqld/delete_group.log
70
			echo "Group $g was deleted $del_date" >> /var/log/mysqld/delete_group.log
71
		else
71
		else
72
			echo "Delete Group $g : Error $del_date" >> /var/log/mysqld/delete_group.log
72
			echo "Delete Group $g : Error $del_date" >> /var/log/mysqld/delete_group.log
73
		fi
73
		fi
74
 	 done
74
 	 done
75
 }
75
 }
76
 
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 ]"
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 ]"
78
nb_args=$#
78
nb_args=$#
79
args=$1
79
args=$1
80
if [ $nb_args -eq 0 ]
80
if [ $nb_args -eq 0 ]
81
then
81
then
82
	nb_args=1
82
	nb_args=1
83
	args="-h"
83
	args="-h"
84
fi
84
fi
85
case $args in
85
case $args in
86
	-\? | -h* | --h*)
86
	-\? | -h* | --h*)
87
		echo "$usage"
87
		echo "$usage"
88
		exit 0
88
		exit 0
89
		;;
89
		;;
90
	-d | --dump | -dump)	
90
	-d | --dump | -dump)	
91
		[ -d $rep_tr ] || mkdir -p $rep_tr
91
		[ -d $rep_tr ] || mkdir -p $rep_tr
92
		if [ -e  $fichier ];
92
		if [ -e  $fichier ];
93
			then rm -f  $fichier 
93
			then rm -f  $fichier 
94
		fi
94
		fi
95
		check
95
		check
96
		echo "Export the database in file : $fichier"
96
		echo "Export the database in file : $fichier"
97
		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
98
		gzip -f $rep_tr/$fichier
98
		gzip -f $rep_tr/$fichier
99
		echo "End of export $( date "+%Hh %Mmn" )"
99
		echo "End of export $( date "+%Hh %Mmn" )"
100
		;;
100
		;;
101
	-c | --check | -check)	
101
	-c | --check | -check)	
102
		check
102
		check
103
		;;
103
		;;
104
	-i | --import | -import)
104
	-i | --import | -import)
105
		if [ $nb_args -ne 2 ]
105
		if [ $nb_args -ne 2 ]
106
			then
106
			then
107
				echo "Enter a SQL file name ('.sql' or '.sql.gz')"
107
				echo "Enter a SQL file name ('.sql' or '.sql.gz')"
108
			exit 0
108
			exit 0
109
		else
109
		else
110
			case $2 in
110
			case $2 in
111
			*.sql.gz )
111
			*.sql.gz )
112
				gunzip -f < $2 | mysql -u $DB_USER -p$radiuspwd
112
				gunzip -f < $2 | mysql -u $DB_USER -p$radiuspwd
113
				stop_acct	
113
				stop_acct	
114
				;;
114
				;;
115
			*.sql )
115
			*.sql )
116
				mysql -u $DB_USER -p$radiuspwd < $2
116
				mysql -u $DB_USER -p$radiuspwd < $2
117
				stop_acct	
117
				stop_acct	
118
				;;
118
				;;
119
			esac
119
			esac
120
		fi
120
		fi
121
		;;
121
		;;
122
	-r | --raz | -raz)
122
	-r | --raz | -raz)
123
		mysqldump -u $DB_USER -p$radiuspwd --opt -BcQC  $DB_RADIUS > $rep_tr/$fichier
123
		mysqldump -u $DB_USER -p$radiuspwd --opt -BcQC  $DB_RADIUS > $rep_tr/$fichier
124
		gzip -f $rep_tr/$fichier
124
		gzip -f $rep_tr/$fichier
125
		mysql -u$DB_USER -p$radiuspwd $DB_RADIUS < /etc/raddb/empty-radiusd-db.sql
125
		mysql -u$DB_USER -p$radiuspwd $DB_RADIUS < /etc/raddb/empty-radiusd-db.sql
126
		;;
126
		;;
127
	-a | --acct_stop | -acct_stop)
127
	-a | --acct_stop | -acct_stop)
128
		stop_acct
128
		stop_acct
129
		;;
129
		;;
130
	-e | --expire_user)
130
	-e | --expire_user)
131
		expire_user	
131
		expire_user	
132
		expire_group	
132
		expire_group	
133
		;;
133
		;;
134
	*)
134
	*)
135
		echo "Unknown argument :$1";
135
		echo "Unknown argument :$1";
136
		echo "$usage"
136
		echo "$usage"
137
		exit 1
137
		exit 1
138
		;;
138
		;;
139
esac
139
esac
140
 
140