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



#! /bin/bash
# $Id: alcasar-mysql.sh 1583 2015-03-05 08:39:04Z richard $
 
# alcasar-mysql.sh
# by Franck BOUIJOUX, Pascal LEVANT and Richard REY
# This script is distributed under the Gnu General Public License (GPL)
 

ext="sql"  				# extension des fichiers de sauvegarde
DB_RADIUS="radius"
DB_USER="radius"
radiuspwd="MotdePasse"			# Mot de passe adapté à l'install
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 "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

			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

			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 $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
			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
		gzip $rep_tr/$fichier
		mysql -u$DB_USER -p$radiuspwd $DB_RADIUS < /etc/raddb/radiusd-db-vierge.sql
		;;
	-a | --acct_stop | -acct_stop)
		stop_acct
		;;

Rev 1477	Rev 1583
Line 1...	Line 1...
1	`#! /bin/bash`	1	`#! /bin/bash`
2	`# $Id: alcasar-mysql.sh 1477 2014-11-04 21:36:39Z franck $`	2	`# $Id: alcasar-mysql.sh 1583 2015-03-05 08:39:04Z richard $`
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
Line 12...	Line 12...
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" # Mot de passe adapté à l'install`	15	`radiuspwd="MotdePasse" # Mot de passe adapté à l'install`
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="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"`
Line 26...	Line 26...
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 () # 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`
Line 43...	Line 43...
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 ()`	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`
Line 93...	Line 93...
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 $rep_tr/$fichier`
98	`echo "End of export $( date "+%Hh %Mmn" )"`	99	`echo "End of export $( date "+%Hh %Mmn" )"`
99	`;;`	100	`;;`
100	`-c \| --check \| -check)`	101	`-c \| --check \| -check)`
101	`check`	102	`check`
102	`;;`	103	`;;`
103	`-i \| --import \| -import)`	104	`-i \| --import \| -import)`
104	`if [ $nb_args -ne 2 ]`	105	`if [ $nb_args -ne 2 ]`
105	`then`	106	`then`
106	`echo "Enter a SQL file name (.sql)"`	107	`echo "Enter a SQL file name ('.sql' or '.sql.gz')"`
107	`exit 0`	108	`exit 0`
108	`else`	109	`else`
109	`mysql -u $DB_USER -p$radiuspwd < $2`	110	`mysql -u $DB_USER -p$radiuspwd < $2`
110	`stop_acct`	111	`stop_acct`
111	`fi`	112	`fi`
112	`;;`	113	`;;`
113	`-r \| --raz \| -raz)`	114	`-r \| --raz \| -raz)`
114	`mysqldump -u $DB_USER -p$radiuspwd --opt -BcQC $DB_RADIUS > $rep_tr/$fichier && \`	115	`mysqldump -u $DB_USER -p$radiuspwd --opt -BcQC $DB_RADIUS > $rep_tr/$fichier`
-		116	`gzip $rep_tr/$fichier`
115	`mysql -u$DB_USER -p$radiuspwd $DB_RADIUS < /etc/raddb/radiusd-db-vierge.sql`	117	`mysql -u$DB_USER -p$radiuspwd $DB_RADIUS < /etc/raddb/radiusd-db-vierge.sql`
116	`;;`	118	`;;`
117	`-a \| --acct_stop \| -acct_stop)`	119	`-a \| --acct_stop \| -acct_stop)`
118	`stop_acct`	120	`stop_acct`
119	`;;`	121	`;;`

Subversion Repositories ALCASAR

(root)/scripts/alcasar-mysql.sh @ 2664 – Rev 1477 → 1583