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


#! /bin/bash
# $Id: alcasar-mysql.sh 1827 2016-04-19 09:47:29Z raphael.pion $
 
# 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" 	 	# répertoire d'accueil des sauvegardes
ext="sql"  				# extension des fichiers de sauvegarde
DB_RADIUS="radius"
DB_USER="radius"
radiuspwd="MotdePasse"
new="$(date +%G%m%d-%Hh%M)"  		# date et heure des fichiers
fichier="alcasar-users-database-$new.$ext"		# nom du fichier de sauvegarde
 
 
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$radiuspwd $DB_RADIUS
}
check ()
{
	echo "check (and repair if needed) the database :"
	mysqlcheck --databases $DB_RADIUS -u $DB_USER -p$radiuspwd --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$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';"`
	for u in $MYSQL_USER
	do
		 /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';"
		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$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';"`
	for g in $MYSQL_GROUP
	do
		MYSQL_USERGROUP=""
		MYSQL_USERGROUP=`/usr/bin/mysql -u$DB_USER -p$radiuspwd $DB_RADIUS -ss --exec  "SELECT username FROM radusergroup WHERE groupname = '$g';"`
		for u in $MYSQL_USERGROUP
		do
		 	/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';"
			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$radiuspwd $DB_RADIUS --exec "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"
		mysqldump -u $DB_USER -p$radiuspwd --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$radiuspwd
				stop_acct	
				;;
			*.sql )
				mysql -u $DB_USER -p$radiuspwd < $2
				stop_acct	
				;;
			esac
		fi
		;;
	-r | --raz | -raz)
		mysqldump -u $DB_USER -p$radiuspwd --opt -BcQC  $DB_RADIUS > $rep_tr/$fichier
		gzip -f $rep_tr/$fichier
		mysql -u$DB_USER -p$radiuspwd $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 1800	Rev 1827
1	`#! /bin/bash`	1	`#! /bin/bash`
2	`# $Id: alcasar-mysql.sh 1800 2016-02-28 18:02:27Z richard $`	2	`# $Id: alcasar-mysql.sh 1827 2016-04-19 09:47:29Z raphael.pion $`
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	`ext="sql" # extension des fichiers de sauvegarde`
13	`DB_RADIUS="radius"`	13	`DB_RADIUS="radius"`
14	`DB_USER="radius"`	14	`DB_USER="radius"`
15	`radiuspwd="MotdePasse"`	15	`radiuspwd="MotdePasse"`
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.$ext" # 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$radiuspwd $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$radiuspwd --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$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';"`
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$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';"`
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`
44	`fi`	44	`fi`
45	`done`	45	`done`
46	`}`	46	`}`
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$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';"`
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$radiuspwd $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$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';"`
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$radiuspwd $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`
73	`fi`	73	`fi`
74	`done`	74	`done`
75	`}`	75	`}`
76		76
77	`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 ]"`	77	`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	`nb_args=$#`	78	`nb_args=$#`
79	`args=$1`	79	`args=$1`
80	`if [ $nb_args -eq 0 ]`	80	`if [ $nb_args -eq 0 ]`
81	`then`	81	`then`
82	`nb_args=1`	82	`nb_args=1`
83	`args="-h"`	83	`args="-h"`
84	`fi`	84	`fi`
85	`case $args in`	85	`case $args in`
86	`-\? \| -h* \| --h*)`	86	`-\? \| -h* \| --h*)`
87	`echo "$usage"`	87	`echo "$usage"`
88	`exit 0`	88	`exit 0`
89	`;;`	89	`;;`
90	`-d \| --dump \| -dump)`	90	`-d \| --dump \| -dump)`
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"`
97	`mysqldump -u $DB_USER -p$radiuspwd --opt -BcQC $DB_RADIUS > $rep_tr/$fichier`	97	`mysqldump -u $DB_USER -p$radiuspwd --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`
103	`;;`	103	`;;`
104	`-i \| --import \| -import)`	104	`-i \| --import \| -import)`
105	`if [ $nb_args -ne 2 ]`	105	`if [ $nb_args -ne 2 ]`
106	`then`	106	`then`
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$radiuspwd`
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$radiuspwd < $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$radiuspwd --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$radiuspwd $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)`
131	`expire_user`	131	`expire_user`
132	`expire_group`	132	`expire_group`
133	`;;`	133	`;;`
134	`*)`	134	`*)`
135	`echo "Unknown argument :$1";`	135	`echo "Unknown argument :$1";`
136	`echo "$usage"`	136	`echo "$usage"`
137	`exit 1`	137	`exit 1`
138	`;;`	138	`;;`
139	`esac`	139	`esac`
140		140

Subversion Repositories ALCASAR

(root)/scripts/alcasar-mysql.sh – Rev 1800 → 1827