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


#! /bin/bash
# $Id: alcasar-mysql.sh 1056 2013-03-26 21:46:36Z stephane $
 
# 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="iDTxxBGa"
new="$(date +%G%m%d-%Hh%M)"  		# date et heure des fichiers
fichier="$DB_RADIUS-$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 ()
{
	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 } | { -c or --check } | { -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
		check
		echo "Export the database in file : $fichier"
		mysqldump -u $DB_USER -p$radiuspwd --opt -BcQC  $DB_RADIUS > $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)"
			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 "Unknown argument :$1";
		echo "$usage"
		exit 1
		;;
esac
 

Rev 958	Rev 1056
1	`#! /bin/bash`	1	`#! /bin/bash`
2	`# $Id: alcasar-mysql.sh 958 2012-07-19 09:01:30Z franck $`	2	`# $Id: alcasar-mysql.sh 1056 2013-03-26 21:46:36Z stephane $`
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="iDTxxBGa"`	15	`radiuspwd="iDTxxBGa"`
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		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 ()`	31	`expire_user ()`
32	`{`	32	`{`
33	del_date=`date +%F`	33	del_date=`date +%F`
34	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';"`	34	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';"`
35	`for u in $MYSQL`	35	`for u in $MYSQL`
36	`do`	36	`do`
37	`/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'"`	37	`/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	`if [ $? = 0 ]`	38	`if [ $? = 0 ]`
39	`then`	39	`then`
40	`echo "User $u was deleted $del_date" >> /var/log/mysqld/delete_user.log`	40	`echo "User $u was deleted $del_date" >> /var/log/mysqld/delete_user.log`
41	`else`	41	`else`
42	`echo "Delete User $u : Error $del_date" >> /var/log/mysqld/delete_user.log`	42	`echo "Delete User $u : Error $del_date" >> /var/log/mysqld/delete_user.log`
43	`fi`	43	`fi`
44	`done`	44	`done`
45	`}`	45	`}`
46	`usage="Usage: alcasar-mysql.sh { -d or --dump } \| { -c or --check } \| { -i or --import } \| { -r or --raz } \| { -acct_stop } \| [ --expire_user ]"`	46	`usage="Usage: alcasar-mysql.sh { -d or --dump } \| { -c or --check } \| { -i or --import } \| { -r or --raz } \| { -acct_stop } \| [ --expire_user ]"`
47	`nb_args=$#`	47	`nb_args=$#`
48	`args=$1`	48	`args=$1`
49	`if [ $nb_args -eq 0 ]`	49	`if [ $nb_args -eq 0 ]`
50	`then`	50	`then`
51	`nb_args=1`	51	`nb_args=1`
52	`args="-h"`	52	`args="-h"`
53	`fi`	53	`fi`
54	`case $args in`	54	`case $args in`
55	`-\? \| -h* \| --h*)`	55	`-\? \| -h* \| --h*)`
56	`echo "$usage"`	56	`echo "$usage"`
57	`exit 0`	57	`exit 0`
58	`;;`	58	`;;`
59	`-d \| --dump \| -dump)`	59	`-d \| --dump \| -dump)`
60	`[ -d $rep_tr ] \|\| mkdir -p $rep_tr`	60	`[ -d $rep_tr ] \|\| mkdir -p $rep_tr`
61	`if [ -e $fichier ];`	61	`if [ -e $fichier ];`
62	`then rm -f $fichier`	62	`then rm -f $fichier`
63	`fi`	63	`fi`
64	`check`	64	`check`
65	`echo "Export the database in file : $fichier"`	65	`echo "Export the database in file : $fichier"`
66	`mysqldump -u $DB_USER -p$radiuspwd --opt -BcQC $DB_RADIUS > $rep_tr/$fichier`	66	`mysqldump -u $DB_USER -p$radiuspwd --opt -BcQC $DB_RADIUS > $rep_tr/$fichier`
67	`echo "End of export $( date "+%Hh %Mmn" )"`	67	`echo "End of export $( date "+%Hh %Mmn" )"`
68	`;;`	68	`;;`
69	`-c \| --check \| -check)`	69	`-c \| --check \| -check)`
70	`check`	70	`check`
71	`;;`	71	`;;`
72	`-i \| --import \| -import)`	72	`-i \| --import \| -import)`
73	`if [ $nb_args -ne 2 ]`	73	`if [ $nb_args -ne 2 ]`
74	`then`	74	`then`
75	`echo "Enter a SQL file name (.sql)"`	75	`echo "Enter a SQL file name (.sql)"`
76	`exit 0`	76	`exit 0`
77	`else`	77	`else`
78	`mysql -u $DB_USER -p$radiuspwd < $2`	78	`mysql -u $DB_USER -p$radiuspwd < $2`
79	`stop_acct`	79	`stop_acct`
80	`fi`	80	`fi`
81	`;;`	81	`;;`
82	`-r \| --raz \| -raz)`	82	`-r \| --raz \| -raz)`
83	`mysqldump -u $DB_USER -p$radiuspwd --opt -BcQC $DB_RADIUS > $rep_tr/$fichier && \`	83	`mysqldump -u $DB_USER -p$radiuspwd --opt -BcQC $DB_RADIUS > $rep_tr/$fichier && \`
84	`mysql -u$DB_USER -p$radiuspwd $DB_RADIUS < /etc/raddb/radiusd-db-vierge.sql`	84	`mysql -u$DB_USER -p$radiuspwd $DB_RADIUS < /etc/raddb/radiusd-db-vierge.sql`
85	`;;`	85	`;;`
86	`-acct_stop)`	86	`-acct_stop)`
87	`stop_acct`	87	`stop_acct`
88	`;;`	88	`;;`
89	`--expire_user)`	89	`--expire_user)`
90	`expire_user`	90	`expire_user`
91	`;;`	91	`;;`
92	`*)`	92	`*)`
93	`echo "Unknown argument :$1";`	93	`echo "Unknown argument :$1";`
94	`echo "$usage"`	94	`echo "$usage"`
95	`exit 1`	95	`exit 1`
96	`;;`	96	`;;`
97	`esac`	97	`esac`
98		98

Subversion Repositories ALCASAR

(root)/scripts/sbin/alcasar-mysql.sh – Rev 958 → 1056