Subversion Repositories ALCASAR

Rev

Rev 1827 | Rev 2664 | Go to most recent revision | Show entire file | Ignore whitespace | Details | Blame | Last modification | View Log

Rev 1827 Rev 2412
Line 1... Line 1...
1 
#! /bin/bash
 1 
#!/bin/bash
2 
# $Id: alcasar-mysql.sh 1827 2016-04-19 09:47:29Z raphael.pion $
 2 
# $Id: alcasar-mysql.sh 2412 2017-09-17 12:11:23Z tom.houdayer $
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 
PASSWD_FILE="/root/ALCASAR-passwords.txt"
13 
DB_RADIUS="radius"
 13 
DB_RADIUS="radius"
14 
DB_USER="radius"
 14 
DB_USER=$(grep '^db_user=' $PASSWD_FILE | cut -d'=' -f 2-)
15 
radiuspwd="MotdePasse"
 15 
DB_PASS=$(grep '^db_password=' $PASSWD_FILE | cut -d'=' -f 2-)
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.sql"	# 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$DB_PASS $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$DB_PASS --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$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';"`
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$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';"
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
Line 47... Line 47...
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$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';"`
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$DB_PASS $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$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';"
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$DB_PASS $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
Line 91... Line 91...
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.gz"
97 
		mysqldump -u $DB_USER -p$radiuspwd --opt -BcQC  $DB_RADIUS > $rep_tr/$fichier
 97 
		mysqldump -u $DB_USER -p$DB_PASS --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
Line 107... Line 107...
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$DB_PASS
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$DB_PASS < $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$DB_PASS --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$DB_PASS $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)