Subversion Repositories ALCASAR

Rev

Rev 3061 | Only display areas with differences | Regard whitespace | Details | Blame | Last modification | View Log

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