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


#! /bin/bash
# $Id: alcasar-mysql.sh 909 2012-06-25 07:16:02Z franck $
 
# 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="9dthW7VG"
new="$(date +%G%m%d-%Hh%M)"  		# date et heure des fichiers
fichier="$DB_RADIUS-$new.$ext"		# nom du fichier de sauvegarde
 
#USER="franck"
#MYSQLCMD="/usr/bin/mysql -u$DB_USER -p$radiuspwd $DB_RADIUS --exec"
 
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
 
}
 
expire_user ()
{
	del_date=`date +%F`
	MYSQL=`/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
	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
}
usage="Usage: alcasar-mysql.sh { -d or --dump } | { -i or --import } | { -r or --raz } | { -acct_stop } | [ --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
		echo "Export de la base 'db_radius' dans le fichier : $fichier"
		mysqldump -u $DB_USER -p$radiuspwd --opt -BcQC  $DB_RADIUS > $rep_tr/$fichier
		echo "Fin de Sauvegarde mysql $( date "+%Hh %Mmn" )"
		;;
	-i | --import | -import)
		if [ $nb_args -ne 2 ]
			then
			echo "Entrez le nom d'un fichier SQL (.sql)"
			exit 0
		else
			mysql -u $DB_USER -p$radiuspwd < $2
			stop_acct	
		fi
		;;
	-r | --raz | -raz)
		mysqldump -u $DB_USER -p$radiuspwd --opt -BcQC  $DB_RADIUS > $rep_tr/$fichier && \
		mysql -u$DB_USER -p$radiuspwd $DB_RADIUS < /etc/raddb/radiusd-db-vierge.sql
		;;
	-acct_stop)
		stop_acct
		;;
	--expire_user)
		expire_user	
		;;
	*)
		echo "Argument inconnu :$1";
		echo "$usage"
		exit 1
		;;
esac
 

Rev 904	Rev 909
1	`#! /bin/bash`	1	`#! /bin/bash`
2	`# $Id: alcasar-mysql.sh 904 2012-06-23 11:35:25Z franck $`	2	`# $Id: alcasar-mysql.sh 909 2012-06-25 07:16:02Z franck $`
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="9dthW7VG"`	15	`radiuspwd="9dthW7VG"`
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="$DB_RADIUS-$new.$ext" # nom du fichier de sauvegarde`	17	`fichier="$DB_RADIUS-$new.$ext" # nom du fichier de sauvegarde`
18		18
19	`#USER="franck"`	19	`#USER="franck"`
20	`#MYSQLCMD="/usr/bin/mysql -u$DB_USER -p$radiuspwd $DB_RADIUS --exec"`	20	`#MYSQLCMD="/usr/bin/mysql -u$DB_USER -p$radiuspwd $DB_RADIUS --exec"`
21		21
22	`stop_acct ()`	22	`stop_acct ()`
23	`{`	23	`{`
24	date_now=`date "+%F %X"`	24	date_now=`date "+%F %X"`
25	`echo "UPDATE radacct SET acctstoptime = '$date_now', acctterminatecause = 'Admin-Reset' WHERE acctstoptime IS NULL" \| mysql -u$DB_USER -p$radiuspwd $DB_RADIUS`	25	`echo "UPDATE radacct SET acctstoptime = '$date_now', acctterminatecause = 'Admin-Reset' WHERE acctstoptime IS NULL" \| mysql -u$DB_USER -p$radiuspwd $DB_RADIUS`
26		26
27	`}`	27	`}`
28		28
29	`expire_user ()`	29	`expire_user ()`
30	`{`	30	`{`
-		31	del_date=`date +%F`
31	MYSQL=`/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';"`	32	MYSQL=`/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';"`
32	`for u in $MYSQL`	33	`for u in $MYSQL`
33	`do`	34	`do`
34	`/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'"`	35	`/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'"`
-		36	`if [ $? = 0 ]`
-		37	`then`
-		38	`echo "User $u was deleted $del_date" >> /var/log/mysqld/delete_user.log`
-		39	`else`
-		40	`echo "Delete User $u : Error $del_date" >> /var/log/mysqld/delete_user.log`
-		41	`fi`
35	`done`	42	`done`
36	`}`	43	`}`
37	`usage="Usage: alcasar-mysql.sh { -d or --dump } \| { -i or --import } \| { -r or --raz } \| { -acct_stop } \| [ --expire_user ]"`	44	`usage="Usage: alcasar-mysql.sh { -d or --dump } \| { -i or --import } \| { -r or --raz } \| { -acct_stop } \| [ --expire_user ]"`
38	`nb_args=$#`	45	`nb_args=$#`
39	`args=$1`	46	`args=$1`
40	`if [ $nb_args -eq 0 ]`	47	`if [ $nb_args -eq 0 ]`
41	`then`	48	`then`
42	`nb_args=1`	49	`nb_args=1`
43	`args="-h"`	50	`args="-h"`
44	`fi`	51	`fi`
45	`case $args in`	52	`case $args in`
46	`-\? \| -h* \| --h*)`	53	`-\? \| -h* \| --h*)`
47	`echo "$usage"`	54	`echo "$usage"`
48	`exit 0`	55	`exit 0`
49	`;;`	56	`;;`
50	`-d \| --dump \| -dump)`	57	`-d \| --dump \| -dump)`
51	`[ -d $rep_tr ] \|\| mkdir -p $rep_tr`	58	`[ -d $rep_tr ] \|\| mkdir -p $rep_tr`
52	`if [ -e $fichier ];`	59	`if [ -e $fichier ];`
53	`then rm -f $fichier`	60	`then rm -f $fichier`
54	`fi`	61	`fi`
55	`echo "Export de la base 'db_radius' dans le fichier : $fichier"`	62	`echo "Export de la base 'db_radius' dans le fichier : $fichier"`
56	`mysqldump -u $DB_USER -p$radiuspwd --opt -BcQC $DB_RADIUS > $rep_tr/$fichier`	63	`mysqldump -u $DB_USER -p$radiuspwd --opt -BcQC $DB_RADIUS > $rep_tr/$fichier`
57	`echo "Fin de Sauvegarde mysql $( date "+%Hh %Mmn" )"`	64	`echo "Fin de Sauvegarde mysql $( date "+%Hh %Mmn" )"`
58	`;;`	65	`;;`
59	`-i \| --import \| -import)`	66	`-i \| --import \| -import)`
60	`if [ $nb_args -ne 2 ]`	67	`if [ $nb_args -ne 2 ]`
61	`then`	68	`then`
62	`echo "Entrez le nom d'un fichier SQL (.sql)"`	69	`echo "Entrez le nom d'un fichier SQL (.sql)"`
63	`exit 0`	70	`exit 0`
64	`else`	71	`else`
65	`mysql -u $DB_USER -p$radiuspwd < $2`	72	`mysql -u $DB_USER -p$radiuspwd < $2`
66	`stop_acct`	73	`stop_acct`
67	`fi`	74	`fi`
68	`;;`	75	`;;`
69	`-r \| --raz \| -raz)`	76	`-r \| --raz \| -raz)`
70	`mysqldump -u $DB_USER -p$radiuspwd --opt -BcQC $DB_RADIUS > $rep_tr/$fichier && \`	77	`mysqldump -u $DB_USER -p$radiuspwd --opt -BcQC $DB_RADIUS > $rep_tr/$fichier && \`
71	`mysql -u$DB_USER -p$radiuspwd $DB_RADIUS < /etc/raddb/radiusd-db-vierge.sql`	78	`mysql -u$DB_USER -p$radiuspwd $DB_RADIUS < /etc/raddb/radiusd-db-vierge.sql`
72	`;;`	79	`;;`
73	`-acct_stop)`	80	`-acct_stop)`
74	`stop_acct`	81	`stop_acct`
75	`;;`	82	`;;`
76	`--expire_user)`	83	`--expire_user)`
77	`expire_user`	84	`expire_user`
78	`;;`	85	`;;`
79	`*)`	86	`*)`
80	`echo "Argument inconnu :$1";`	87	`echo "Argument inconnu :$1";`
81	`echo "$usage"`	88	`echo "$usage"`
82	`exit 1`	89	`exit 1`
83	`;;`	90	`;;`
84	`esac`	91	`esac`
85		92

Subversion Repositories ALCASAR

(root)/scripts/sbin/alcasar-mysql.sh – Rev 904 → 909