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


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

Subversion Repositories ALCASAR

(root)/scripts/alcasar-mysql.sh – Rev 2412 → 2664