Subversion Repositories ALCASAR

Rev

Details | Last modification | View Log

Rev Author Line No. Line
2412 tom.houday 1 
#!/bin/bash
63 franck 2 
# $Id: alcasar-mysql.sh 2829 2020-06-01 21:56:41Z rexy $
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 
 
2829 rexy 11 
rep_tr="/var/Save/base"
2664 tom.houday 12 
DIR_BIN="/usr/local/bin"
2412 tom.houday 13 
PASSWD_FILE="/root/ALCASAR-passwords.txt"
904 franck 14 
DB_RADIUS="radius"
2412 tom.houday 15 
DB_USER=$(grep '^db_user=' $PASSWD_FILE | cut -d'=' -f 2-)
16 
DB_PASS=$(grep '^db_password=' $PASSWD_FILE | cut -d'=' -f 2-)
2829 rexy 17 
new="$(date +%G%m%d-%Hh%M)"			# date & hour of files
18 
fichier="alcasar-users-database-$new.sql"
1 root 19 
 
904 franck 20 
 
613 richard 21 
stop_acct ()
22 
{
23 
	date_now=`date "+%F %X"`
2412 tom.houday 24 
	echo "UPDATE radacct SET acctstoptime = '$date_now', acctterminatecause = 'Admin-Reset' WHERE acctstoptime IS NULL" | mysql -u$DB_USER -p$DB_PASS $DB_RADIUS
613 richard 25 
}
955 richard 26 
check ()
27 
{
28 
	echo "check (and repair if needed) the database :"
2412 tom.houday 29 
	mysqlcheck --databases $DB_RADIUS -u $DB_USER -p$DB_PASS --auto-repair
955 richard 30 
}
904 franck 31 
 
1583 richard 32 
expire_user ()	# remove users whom expiration date has passed to 7 days
904 franck 33 
{
909 franck 34 
	del_date=`date +%F`
1477 franck 35 
	MYSQL_USER=""
2412 tom.houday 36 
	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';"`
1477 franck 37 
	for u in $MYSQL_USER
904 franck 38 
	do
2412 tom.houday 39 
		 /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';"
909 franck 40 
		if [ $? = 0 ]
41 
		then
42 
			echo "User $u was deleted $del_date" >> /var/log/mysqld/delete_user.log
43 
		else
44 
			echo "Delete User $u : Error $del_date" >> /var/log/mysqld/delete_user.log
45 
		fi
46 
 	 done
904 franck 47 
}
1477 franck 48 
 
1583 richard 49 
expire_group () # remove users of group whom expiration date has passed to 7 days
1477 franck 50 
{
51 
	del_date=`date +%F`
52 
	MYSQL_GROUP=""
2412 tom.houday 53 
	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';"`
1477 franck 54 
	for g in $MYSQL_GROUP
55 
	do
56 
		MYSQL_USERGROUP=""
2412 tom.houday 57 
		MYSQL_USERGROUP=`/usr/bin/mysql -u$DB_USER -p$DB_PASS $DB_RADIUS -ss --exec  "SELECT username FROM radusergroup WHERE groupname = '$g';"`
1477 franck 58 
		for u in $MYSQL_USERGROUP
59 
		do
2412 tom.houday 60 
		 	/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';"
1477 franck 61 
			if [ $? = 0 ]
62 
			then
63 
				echo "User $u was deleted $del_date" >> /var/log/mysqld/delete_user.log
64 
			else
65 
				echo "Delete User $u : Error $del_date" >> /var/log/mysqld/delete_user.log
66 
			fi
67 
	 	 done
2412 tom.houday 68 
		 /usr/bin/mysql -u$DB_USER -p$DB_PASS $DB_RADIUS --exec "DELETE FROM radgroupreply WHERE groupname = '$g'; DELETE FROM radgroupcheck WHERE groupname = '$g';"
1477 franck 69 
		if [ $? = 0 ]
70 
		then
71 
			echo "Group $g was deleted $del_date" >> /var/log/mysqld/delete_group.log
72 
		else
73 
			echo "Delete Group $g : Error $del_date" >> /var/log/mysqld/delete_group.log
74 
		fi
75 
 	 done
76 
 }
77 
 
78 
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 79 
nb_args=$#
80 
args=$1
81 
if [ $nb_args -eq 0 ]
82 
then
83 
	nb_args=1
84 
	args="-h"
85 
fi
86 
case $args in
87 
	-\? | -h* | --h*)
88 
		echo "$usage"
89 
		exit 0
90 
		;;
873 franck 91 
	-d | --dump | -dump)
1 root 92 
		[ -d $rep_tr ] || mkdir -p $rep_tr
93 
		if [ -e  $fichier ];
94 
			then rm -f  $fichier
95 
		fi
955 richard 96 
		check
2412 tom.houday 97 
		echo "Export the database in file : $fichier.gz"
98 
		mysqldump -u $DB_USER -p$DB_PASS --opt -BcQC  $DB_RADIUS > $rep_tr/$fichier
1584 richard 99 
		gzip -f $rep_tr/$fichier
955 richard 100 
		echo "End of export $( date "+%Hh %Mmn" )"
1 root 101 
		;;
955 richard 102 
	-c | --check | -check)
103 
		check
104 
		;;
873 franck 105 
	-i | --import | -import)
1 root 106 
		if [ $nb_args -ne 2 ]
107 
			then
1583 richard 108 
				echo "Enter a SQL file name ('.sql' or '.sql.gz')"
1 root 109 
			exit 0
110 
		else
1584 richard 111 
			case $2 in
112 
			*.sql.gz )
2412 tom.houday 113 
				gunzip -f < $2 | mysql -u $DB_USER -p$DB_PASS
1584 richard 114 
				stop_acct
115 
				;;
116 
			*.sql )
2412 tom.houday 117 
				mysql -u $DB_USER -p$DB_PASS < $2
1584 richard 118 
				stop_acct
119 
				;;
120 
			esac
2811 rexy 121 
			migrationsPath="$DIR_BIN/alcasar-db-migrations"
2664 tom.houday 122 
			"$migrationsPath/alcasar-migration-3.2.0_dbStructure.sh"
123 
			"$migrationsPath/alcasar-migration-3.3.0_dbRadiusAttrs.sh"
124 
			"$migrationsPath/alcasar-migration-3.3.1_dbRadiusAttrs.sh"
1 root 125 
		fi
126 
		;;
873 franck 127 
	-r | --raz | -raz)
2412 tom.houday 128 
		mysqldump -u $DB_USER -p$DB_PASS --opt -BcQC  $DB_RADIUS > $rep_tr/$fichier
1584 richard 129 
		gzip -f $rep_tr/$fichier
2412 tom.houday 130 
		mysql -u$DB_USER -p$DB_PASS $DB_RADIUS < /etc/raddb/empty-radiusd-db.sql
1 root 131 
		;;
1477 franck 132 
	-a | --acct_stop | -acct_stop)
613 richard 133 
		stop_acct
134 
		;;
1477 franck 135 
	-e | --expire_user)
904 franck 136 
		expire_user
1477 franck 137 
		expire_group
904 franck 138 
		;;
1 root 139 
	*)
955 richard 140 
		echo "Unknown argument :$1";
1 root 141 
		echo "$usage"
142 
		exit 1
143 
		;;
144 
esac