Subversion Repositories ALCASAR

Rev

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

Rev 3269 Rev 3274
Line 1... Line 1...
1 
#!/bin/bash
 1 
#!/bin/bash
2 
 
 2 
 
3 
# alcasar-mariadb.sh
 3 
# alcasar-mariadb.sh
4 
# by Franck BOUIJOUX, Pascal LEVANT and Richard REY
 4 
# by Franck BOUIJOUX, Pascal LEVANT, Richard REY & Laurent ROUX
5 
# This script is distributed under the Gnu General Public License (GPL)
 5 
# This script is distributed under the Gnu General Public License (GPL)
6 
 
 6 
 
- 
 
 7 
# Actions sur la base MariaDB 'radius'
- 
 
 8 
#	- Gestion (sauvegarde / import / RAZ)
7 
# Gestion (sauvegarde / import / RAZ) de la base MariaDB 'radius'. Fermeture des sessions de comptabilité ouvertes
 9 
#	- Fermeture des sessions de comptabilité ouvertes
- 
 
 10 
#	- Suppression des utilisateurs 7 jours après leur date d'expiration
8 
# Management of MariaDB 'radius' database (save / import / RAZ). Close the accounting open sessions
 11 
#	- Suppression des utilisateurs ne s'étant pas connectés depuis plus un an
- 
 
 12 
# Action on MariaDB 'radius' database
- 
 
 13 
#	- Management (save / import / RAZ)
- 
 
 14 
#	- Close the accounting open sessions
- 
 
 15 
#	- Remove users 7 days after their expiry date
- 
 
 16 
#	- Remove users who haven't logged in for more than a year
9 
 
 17 
 
10 
rep_tr="/var/Save/base"
 18 
rep_tr="/var/Save/base"
11 
DIR_BIN="/usr/local/bin"
 19 
DIR_BIN="/usr/local/bin"
12 
PASSWD_FILE="/root/ALCASAR-passwords.txt"
 20 
PASSWD_FILE="/root/ALCASAR-passwords.txt"
13 
DB_RADIUS="radius"
 21 
DB_RADIUS="radius"
14 
DB_USER=$(grep '^db_user=' $PASSWD_FILE | cut -d'=' -f 2-)
 22 
DB_USER=$(grep '^db_user=' $PASSWD_FILE | cut -d'=' -f 2-)
15 
DB_PASS=$(grep '^db_password=' $PASSWD_FILE | cut -d'=' -f 2-)
 23 
DB_PASS=$(grep '^db_password=' $PASSWD_FILE | cut -d'=' -f 2-)
16 
new="$(date +%G%m%d-%Hh%M)"			# date & hour of files
24 
new="$(date +%G%m%d-%Hh%M)"		# date & hour of files
17 
fichier="alcasar-users-database-$new.sql"
 25 
tmp_file="alcasar-users-database-$new.sql"
18 
 
 26 
 
19 
 
 - 
 
20 
stop_acct ()
 27 
stop_acct () # Close the accounting open sessions
21 
{
 28 
{
22 
	date_now=`date "+%F %X"`
 29 
	date_now=`date "+%F %X"`
23 
	echo "UPDATE radacct SET acctstoptime = '$date_now', acctterminatecause = 'Admin-Reset' WHERE acctstoptime IS NULL" | mariadb -u$DB_USER -p$DB_PASS $DB_RADIUS
 30 
	echo "UPDATE radacct SET acctstoptime = '$date_now', acctterminatecause = 'Admin-Reset' WHERE acctstoptime IS NULL" | mariadb -u$DB_USER -p$DB_PASS $DB_RADIUS
24 
}
 31 
}
- 
 
 32 
 
25 
check ()
 33 
check ()
26 
{
 34 
{
27 
	echo "check (and repair if needed) the database :"
 35 
	echo "check (and repair if needed) the database :"
28 
	mariadb-check --databases $DB_RADIUS -u $DB_USER -p$DB_PASS --auto-repair
 36 
	mariadb-check --databases $DB_RADIUS -u $DB_USER -p$DB_PASS --auto-repair
29 
}
 37 
}
30 
 
 38 
 
- 
 
 39 
remove_old_users ()	# Remove users who haven't logged in for more than a year
- 
 
 40 
{
- 
 
 41 
	SQL_USER=""
- 
 
 42 
	SQL_USER=`/usr/bin/mariadb -u$DB_USER -p$DB_PASS $DB_RADIUS -ss --execute  "SELECT username FROM (SELECT DISTINCT username FROM radacct WHERE acctstarttime >= NOW() - INTERVAL 1 YEAR) AS actifs;"`
- 
 
 43 
	for u in $SQL_USER
- 
 
 44 
	do
- 
 
 45 
		/usr/bin/mariadb -u$DB_USER -p$DB_PASS $DB_RADIUS --execute "DELETE FROM radusergroup WHERE username = '$u'; DELETE FROM radreply WHERE username = '$u'; DELETE FROM userinfo WHERE UserName = '$u'; DELETE FROM radcheck WHERE username = '$u';"
- 
 
 46 
		if [ $? = 0 ]
- 
 
 47 
		then
- 
 
 48 
			echo "User $u was deleted $del_date" >> /var/log/mysqld/delete_user.log
- 
 
 49 
		else
- 
 
 50 
			echo "Delete User $u : Error $del_date" >> /var/log/mysqld/delete_user.log
- 
 
 51 
		fi
- 
 
 52 
 	 done
- 
 
 53 
}
- 
 
 54 
 
31 
expire_user ()	# remove users whom expiration date has passed to 7 days
 55 
expire_users ()	# Remove users 7 days after their expiry date
32 
{
 56 
{
33 
	del_date=`date +%F`
 57 
	del_date=`date +%F`
34 
	SQL_USER=""
 58 
	SQL_USER=""
35 
	SQL_USER=`/usr/bin/mariadb -u$DB_USER -p$DB_PASS $DB_RADIUS -ss --execute  "SELECT username FROM radcheck WHERE ( DATE_SUB(CURDATE(),INTERVAL 7 DAY) > STR_TO_DATE(value,'%d %M %Y')) AND attribute='Expiration';"`
 59 
	SQL_USER=`/usr/bin/mariadb -u$DB_USER -p$DB_PASS $DB_RADIUS -ss --execute  "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 $SQL_USER
 60 
	for u in $SQL_USER
37 
	do
 61 
	do
38 
		 /usr/bin/mariadb -u$DB_USER -p$DB_PASS $DB_RADIUS --execute "DELETE FROM radusergroup WHERE username = '$u'; DELETE FROM radreply WHERE username = '$u'; DELETE FROM userinfo WHERE UserName = '$u'; DELETE FROM radcheck WHERE username = '$u';"
 62 
		/usr/bin/mariadb -u$DB_USER -p$DB_PASS $DB_RADIUS --execute "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 ]
 63 
		if [ $? = 0 ]
40 
		then
 64 
		then
41 
			echo "User $u was deleted $del_date" >> /var/log/mysqld/delete_user.log
 65 
			echo "User $u was deleted $del_date" >> /var/log/mysqld/delete_user.log
42 
		else
 66 
		else
43 
			echo "Delete User $u : Error $del_date" >> /var/log/mysqld/delete_user.log
 67 
			echo "Delete User $u : Error $del_date" >> /var/log/mysqld/delete_user.log
44 
		fi
 68 
		fi
45 
 	 done
 69 
 	 done
46 
}
 70 
}
47 
 
 71 
 
48 
expire_group () # remove users of group whom expiration date has passed to 7 days
 72 
expire_groups () # remove users of group whom expiration date has passed to 7 days
49 
{
 73 
{
50 
	del_date=`date +%F`
 74 
	del_date=`date +%F`
51 
	SQL_GROUP=""
 75 
	SQL_GROUP=""
52 
	SQL_GROUP=`/usr/bin/mariadb -u$DB_USER -p$DB_PASS $DB_RADIUS -ss --execute  "SELECT groupname FROM radgroupcheck WHERE ( DATE_SUB(CURDATE(),INTERVAL 7 DAY) > STR_TO_DATE(value,'%d %M %Y')) AND attribute='Expiration';"`
 76 
	SQL_GROUP=`/usr/bin/mariadb -u$DB_USER -p$DB_PASS $DB_RADIUS -ss --execute  "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 $SQL_GROUP
 77 
	for g in $SQL_GROUP
Line 72... Line 96...
72 
			echo "Delete Group $g : Error $del_date" >> /var/log/mysqld/delete_group.log
 96 
			echo "Delete Group $g : Error $del_date" >> /var/log/mysqld/delete_group.log
73 
		fi
 97 
		fi
74 
 	 done
 98 
 	 done
75 
 }
 99 
 }
76 
 
 100 
 
77 
usage="Usage: alcasar-mariadb.sh { -d or --dump } | { -c or --check } | { -i or --import } | { -r or --raz } | { -a or --acct_stop } | [ -e or --expire_user ]"
 101 
usage="Usage: alcasar-mariadb.sh { -d or --dump } | { -c or --check } | { -i or --import } | { -r or --raz } | { -a or --acct_stop } | { -e or --expire_user } | { -o or --old_users }"
78 
nb_args=$#
 102 
nb_args=$#
79 
args=$1
 103 
args=$1
80 
if [ $nb_args -eq 0 ]
 104 
if [ $nb_args -eq 0 ]
81 
then
 105 
then
82 
	nb_args=1
 106 
	nb_args=1
Line 87... Line 111...
87 
		echo "$usage"
 111 
		echo "$usage"
88 
		exit 0
 112 
		exit 0
89 
		;;
 113 
		;;
90 
	-d | --dump | -dump)
114 
	-d | --dump | -dump)
91 
		[ -d $rep_tr ] || mkdir -p $rep_tr
 115 
		[ -d $rep_tr ] || mkdir -p $rep_tr
92 
		if [ -e  $fichier ];
 116 
		if [ -e  $tmp_file ];
93 
			then rm -f  $fichier
117 
			then rm -f  $tmp_file
94 
		fi
 118 
		fi
95 
		check
 119 
		check
96 
		echo "Export the database in file : $fichier.gz"
 120 
		echo "Export the database in file : $tmp_file.gz"
97 
		mariadb-dump -u $DB_USER -p$DB_PASS --opt -BcQC  $DB_RADIUS > $rep_tr/$fichier
 121 
		mariadb-dump -u $DB_USER -p$DB_PASS --opt -BcQC  $DB_RADIUS > $rep_tr/$tmp_file
98 
		gzip -f $rep_tr/$fichier
 122 
		gzip -f $rep_tr/$tmp_file
99 
		echo "End of export $( date "+%Hh %Mmn" )"
 123 
		echo "End of export $( date "+%Hh %Mmn" )"
100 
		;;
 124 
		;;
101 
	-c | --check | -check)
125 
	-c | --check | -check)
102 
		check
 126 
		check
103 
		;;
 127 
		;;
Line 108... Line 132...
108 
			exit 0
 132 
			exit 0
109 
		else
 133 
		else
110 
			case $2 in
 134 
			case $2 in
111 
			*.sql.gz )
 135 
			*.sql.gz )
112 
				gunzip -f < $2 | mariadb -u $DB_USER -p$DB_PASS
 136 
				gunzip -f < $2 | mariadb -u $DB_USER -p$DB_PASS
113 
				stop_acct
137 
				stop_acct
114 
				;;
 138 
				;;
115 
			*.sql )
 139 
			*.sql )
116 
				mariadb -u $DB_USER -p$DB_PASS < $2
 140 
				mariadb -u $DB_USER -p$DB_PASS < $2
117 
				stop_acct
141 
				stop_acct
118 
				;;
 142 
				;;
119 
			esac
 143 
			esac
120 
			migrationsPath="$DIR_BIN/alcasar-db-migrations"
 144 
			migrationsPath="$DIR_BIN/alcasar-db-migrations"
121 
			"$migrationsPath/alcasar-migration_db-3.2.0.sh"
 145 
			"$migrationsPath/alcasar-migration_db-3.2.0.sh"
122 
			"$migrationsPath/alcasar-migration_db-3.3.0.sh"
 146 
			"$migrationsPath/alcasar-migration_db-3.3.0.sh"
123 
			"$migrationsPath/alcasar-migration_db-3.3.1.sh"
 147 
			"$migrationsPath/alcasar-migration_db-3.3.1.sh"
124 
			"$migrationsPath/alcasar-migration_db-3.7.0.sh"
 148 
			"$migrationsPath/alcasar-migration_db-3.7.0.sh"
125 
		fi
 149 
		fi
126 
		;;
 150 
		;;
127 
	-r | --raz | -raz)
 151 
	-r | --raz | -raz)
128 
		mariadb-dump -u $DB_USER -p$DB_PASS --opt -BcQC  $DB_RADIUS > $rep_tr/$fichier
 152 
		mariadb-dump -u $DB_USER -p$DB_PASS --opt -BcQC  $DB_RADIUS > $rep_tr/$tmp_file
129 
		gzip -f $rep_tr/$fichier
 153 
		gzip -f $rep_tr/$tmp_file
130 
		mariadb -u$DB_USER -p$DB_PASS $DB_RADIUS < /etc/raddb/empty-radiusd-db.sql
 154 
		mariadb -u$DB_USER -p$DB_PASS $DB_RADIUS < /etc/raddb/empty-radiusd-db.sql
131 
		;;
 155 
		;;
132 
	-a | --acct_stop | -acct_stop)
 156 
	-a | --acct_stop | -acct_stop)
133 
		stop_acct
 157 
		stop_acct
134 
		;;
 158 
		;;
135 
	-e | --expire_user)
 159 
	-e | --expire_user)
136 
		expire_user
160 
		expire_users
137 
		expire_group
161 
		expire_groups
- 
 
 162 
		;;
- 
 
 163 
	-o | --old_user)
- 
 
 164 
		remove_old_users
138 
		;;
 165 
		;;
139 
	*)
 166 
	*)
140 
		echo "Unknown argument :$1";
 167 
		echo "Unknown argument :$1";
141 
		echo "$usage"
 168 
		echo "$usage"
142 
		exit 1
 169 
		exit 1