WebSVN – ALCASAR – Diff – /scripts/alcasar-mysql.sh


#!/bin/bash
# $Id: alcasar-mysql.sh 3202 2024-06-01 17:10:35Z rexy $
 
# alcasar-mysql.sh
# by Franck BOUIJOUX, Pascal LEVANT and Richard REY
# This script is distributed under the Gnu General Public License (GPL)
 
# Gestion (sauvegarde / import / RAZ) de la base MySQL 'radius'. Fermeture des sessions de comptabilité ouvertes
# Management of mysql 'radius' database (save / import / RAZ). Close the accounting open sessions
 
rep_tr="/var/Save/base"
DIR_BIN="/usr/local/bin"
PASSWD_FILE="/root/ALCASAR-passwords.txt"
DB_RADIUS="radius"
DB_USER=$(grep '^db_user=' $PASSWD_FILE | cut -d'=' -f 2-)
DB_PASS=$(grep '^db_password=' $PASSWD_FILE | cut -d'=' -f 2-)
new="$(date +%G%m%d-%Hh%M)"			# date & hour of files 
fichier="alcasar-users-database-$new.sql"
 
 
stop_acct ()
{
	date_now=`date "+%F %X"`
	echo "UPDATE radacct SET acctstoptime = '$date_now', acctterminatecause = 'Admin-Reset' WHERE acctstoptime IS NULL" | mysql -u$DB_USER -p$DB_PASS $DB_RADIUS
}
check ()
{
	echo "check (and repair if needed) the database :"
	mysqlcheck --databases $DB_RADIUS -u $DB_USER -p$DB_PASS --auto-repair
}
 
expire_user ()	# remove users whom expiration date has passed to 7 days
{
	del_date=`date +%F`
	MYSQL_USER=""
	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';"`
	for u in $MYSQL_USER
	do
		 /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';"
		if [ $? = 0 ]
		then
			echo "User $u was deleted $del_date" >> /var/log/mysqld/delete_user.log
		else
			echo "Delete User $u : Error $del_date" >> /var/log/mysqld/delete_user.log
		fi
 	 done
}
 
expire_group () # remove users of group whom expiration date has passed to 7 days
{
	del_date=`date +%F`
	MYSQL_GROUP=""
	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';"`
	for g in $MYSQL_GROUP
	do
		MYSQL_USERGROUP=""
		MYSQL_USERGROUP=`/usr/bin/mysql -u$DB_USER -p$DB_PASS $DB_RADIUS -ss --execute  "SELECT username FROM radusergroup WHERE groupname = '$g';"`
		for u in $MYSQL_USERGROUP
		do
		 	/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';"
			if [ $? = 0 ]
			then
				echo "User $u was deleted $del_date" >> /var/log/mysqld/delete_user.log
			else
				echo "Delete User $u : Error $del_date" >> /var/log/mysqld/delete_user.log
			fi
	 	 done
		 /usr/bin/mysql -u$DB_USER -p$DB_PASS $DB_RADIUS --execute "DELETE FROM radgroupreply WHERE groupname = '$g'; DELETE FROM radgroupcheck WHERE groupname = '$g';"
		if [ $? = 0 ]
		then
			echo "Group $g was deleted $del_date" >> /var/log/mysqld/delete_group.log
		else
			echo "Delete Group $g : Error $del_date" >> /var/log/mysqld/delete_group.log
		fi
 	 done
 }
 
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 ]"
nb_args=$#
args=$1
if [ $nb_args -eq 0 ]
then
	nb_args=1
	args="-h"
fi
case $args in
	-\? | -h* | --h*)
		echo "$usage"
		exit 0
		;;
	-d | --dump | -dump)	
		[ -d $rep_tr ] || mkdir -p $rep_tr
		if [ -e  $fichier ];
			then rm -f  $fichier 
		fi
		check
		echo "Export the database in file : $fichier.gz"
		mysqldump -u $DB_USER -p$DB_PASS --opt -BcQC  $DB_RADIUS > $rep_tr/$fichier
		gzip -f $rep_tr/$fichier
		echo "End of export $( date "+%Hh %Mmn" )"
		;;
	-c | --check | -check)	
		check
		;;
	-i | --import | -import)
		if [ $nb_args -ne 2 ]
			then
				echo "Enter a SQL file name ('.sql' or '.sql.gz')"
			exit 0
		else
			case $2 in
			*.sql.gz )
				gunzip -f < $2 | mysql -u $DB_USER -p$DB_PASS
				stop_acct	
				;;
			*.sql )
				mysql -u $DB_USER -p$DB_PASS < $2
				stop_acct	
				;;
			esac
			migrationsPath="$DIR_BIN/alcasar-db-migrations"
			"$migrationsPath/alcasar-migration_db-3.2.0.sh"
			"$migrationsPath/alcasar-migration_db-3.3.0.sh"
			"$migrationsPath/alcasar-migration_db-3.3.1.sh"
			"$migrationsPath/alcasar-migration_db-3.7.0.sh"
		fi
		;;
	-r | --raz | -raz)
		mysqldump -u $DB_USER -p$DB_PASS --opt -BcQC  $DB_RADIUS > $rep_tr/$fichier
		gzip -f $rep_tr/$fichier
		mysql -u$DB_USER -p$DB_PASS $DB_RADIUS < /etc/raddb/empty-radiusd-db.sql
		;;
	-a | --acct_stop | -acct_stop)
		stop_acct
		;;
	-e | --expire_user)
		expire_user	
		expire_group	
		;;
	*)
		echo "Unknown argument :$1";
		echo "$usage"
		exit 1
		;;
esac
 

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

Subversion Repositories ALCASAR

(root)/scripts/alcasar-mysql.sh @ 2868 – Rev 3061 → 3202