From 63ecf1966655d05b276abb37239b676f8f6d8dbb Mon Sep 17 00:00:00 2001 From: mwolson_admin Date: Wed, 27 Aug 2008 22:35:23 -0400 Subject: [PATCH 1/1] Miscellaneous accumulated scripts that were never checked in before. --- mysql-grant-table-drop | 34 +++++ quotacheck | 329 +++++++++++++++++++++++++++++++++++++++++ update-shadow-volumes | 7 + vos-syncall | 7 + 4 files changed, 377 insertions(+) create mode 100755 mysql-grant-table-drop create mode 100755 quotacheck create mode 100755 update-shadow-volumes create mode 100755 vos-syncall diff --git a/mysql-grant-table-drop b/mysql-grant-table-drop new file mode 100755 index 0000000..3cf59db --- /dev/null +++ b/mysql-grant-table-drop @@ -0,0 +1,34 @@ +#!/bin/bash + +# +# This script finds all tables in all databases and grants DROP permission +# on DBNAME.TBLNAME to corresponding DB owners. +# We have to do this because granting DROP on DBNAME.* also gives permission +# to drop DBNAME itself, which we can't allow. +# +# docelic@hcoop.net, Mon Sep 24 16:25:34 EDT 2007 +# + +# Exit for now; I don't know how to handle this without messing up +# user's password. + +MYSQL_DBS=`sudo -H mysqlshow | tail -n +4 | head -n -1 | cut -d' ' -f 2 | xargs` +#echo "Mysql Databases are: $MYSQL_DBS" + +for DB in $MYSQL_DBS; do + if test "$DB" != "information_schema"; then + if test "$DB" != "mysql"; then + USER=${DB%%_*} + + MYSQLDB_TABLES=`sudo -H mysqlshow $DB | tail -n +5 | head -n -1 | cut -d' ' -f 2 | xargs` + #echo Tables in $DB are $MYSQLDB_TABLES + for TBL in $MYSQLDB_TABLES; do + + #echo "OWNER FOR $DB.$TBL is $USER" + `sudo -H mysql -e "GRANT ALL PRIVILEGES ON $DB.$TBL TO $USER@'%.hcoop.net'"`; + + done + + fi # information_schema + fi # mysql +done diff --git a/quotacheck b/quotacheck new file mode 100755 index 0000000..4145294 --- /dev/null +++ b/quotacheck @@ -0,0 +1,329 @@ +#!/usr/bin/perl + +# docelic, Sat Dec 22 18:35:37 EST 2007 + +# Script warns people when they're approaching quota. The purpose +# is not to 'behave' users but to give them a notice they should +# fill quota increase request. (We want to prevent people from +# ever actually running into quota limits, especially for Mail +# and Database volumes. We use quotas to keep the shop clean, not +# to impose restrictions). + +# Script retrieves volume info from deleuze, from there it +# reads mail., user. and db. volumes into %vol, then +# sees which volumes have > $threshold usage, relative to volume +# quota. +# +# For volumes above threshold, it extracts the owner name from +# volume name (like, mail.USERNAME) and saves info to %warn. +# +# vos listvol is parsed into structure like this: +# +# $vol{user.docelic}{diskused} = 36718 ( All fields from the +# output of vos listvol are loaded into the hash. list of +# fields is at the bottom of this script ) +# +# people who are over quota are in %warn: +# +# $warn{docelic}{user} = 0.9 +# $warn{docelic}{mail} = 0.74 +# +# "Offenders" are saved to $state{$user} = [ run_number, exp_inc ] +# and removed when they resolve their quota situation. For this +# to work (and emails to get sent), pass --auto (most certain when +# running from cron). +# +# run_number is the run (tick) when they were first spotted to +# be over safe value, and exp_inc is the interval at which they +# need to be reminded. (We use 1, 2, 4, 8, 16...). Each 'tick' +# and number usually refers to 1 day (if ran from cron daily) + +# In the current implimentation, folks will be reminded on +# 1st, 3rd, 7th, 15th and 31st day after their quota overrun. +# Then they will be given 2 months of no messages, and then +# after 3 months all together, the "warning cycle" will start again. + +# - If user is over 0.96 on mail or db volume, we will add 2% to his +# quota one-time to alleviate immediate problem. + +use warnings; +use strict; +use Storable qw/nstore retrieve/; +use Fatal qw/open write read close/; +use Data::Dumper qw/Dumper/; + +# Warn threshold +our %threshold = ( user => 0.8, mail => 0.6, db => 0.4 ); +# AFS servers with volumes +our @servers = qw/deleuze/; +# Which volumes we want? +# (Your volumes need to be in format (TYPE).USERNAME, otherwise +# you will have to modify below where we take .USERNAME to mean +# person's USERNAME). Changing this will also trigger changes +# in template email (see DATA below). +our $volume_pattern = '(mail|user|db)\.[a-zA-Z0-9]+'; # implicit ^...$ +# Cache file +our $statefile = '/var/cache/hcoop/quotacheck'; +# Hash structure containing load of $statefile +our %state; +# Update cache & send out emails (--auto), or just run terminal report +our $auto = grep {/^--auto$/} @ARGV; +# Dump statefile in readable format to terminal and exit (--dump) +our $dump = grep {/^--dump$/} @ARGV; +# Do not send emails: +our $nomail = grep {/^--nomail$/} @ARGV; +# E-mail template that is evaled before sending +our @template = ; + + +# WARMUP + +# If statefile is there, eventually load it +if ( -e $statefile ) { + if ( $auto or $dump) { %state = %{ retrieve($statefile) } } +} + +# Only dump wanted? +if ( $dump ) { print Dumper \%state; exit 0 } + +# Increment tick +$state{_tick}++; + + +# BEGIN WOROK + +# Get volume lists from all servers +my @list; +for (@servers) { push @list, `vos listvol $_ -format` } +chomp for @list; + +my $current; # name of volume being parsed +my %vol; # will contain hash result of parsing +my %warn; # list of people and data for > $threshold folks +my %nowarn; # same data as in %warn, but for people NOT over threshold + +# +# Copy vol from @list (vos listvol output) into %vol hash. +# We read in all values; all values from vol listvol -format +# output for a volume are read in. Such as +# $vol{user.docelic}{type} = 'RW' +# +for (@list) { + my ($parm, $value) = split(/\s+/, $_, 2); + defined $parm and defined $value or next; + + if ( $parm eq 'name' ) { + if ( $value =~ /^$volume_pattern$/ ) { + $current = $value + } else { + $current = '' + } + } elsif ($current) { + $vol{$current}{$parm} = $value + } +} + +# +# Copy > $threshold people to %warn hash like this: +# $warn{docelic}{mail} = 0.9 +# +while (my($k,$v) = each %vol) { + next unless $$v{maxquota}; + my ($type,$user) = split(/\./, $k); + + my $perc = sprintf('%.3f', $$v{diskused} / $$v{maxquota}); + + if ( $perc > $threshold{$type} ) { + $warn{$user}{$type} = $perc; + } + + # A bit of duplication with %warn, but wth.. + $nowarn{$user}{$type} = $perc; +} + +# +# Reset counter for people who solved their quota thing somehow +# +my @tmp = keys %state; +for my $person ( @tmp ) { + next if $person eq '_tick'; + unless ( $warn{$person} ) { + print "User $person resolved quota problem\n"; + delete $state{$person} + } +} + +# +# Go over %warn, print info to terminal, update %state +# when needed to create new entry for new quota detections. +# +while (my($k,$v) = each %warn) { + print "$k "; + my @line; + while (my($k2,$v2) = each %$v) { + push @line, "$k2=$v2"; + } + print "@line"; + + # Register person in %state + if ( ! $state{$k} ) { + $state{$k} = [ $state{_tick}, 2 ]; + } + + print "\n"; +} + +# +# See who's entitled to receiving an email at this point; +# that is, go over %state, send emails to people who need +# to get it in this turn, and exponentially prolong the time +# till next message from us. +# (They receive message on 1st, 3rd, 7th, 15th, 31st...) +# +while (my($user,$state) = each %state ) { + next if $user eq '_tick'; + #print " $$state[0] + $$state[1] == $state{_tick} + 2\n"; + + if ( $$state[0] + $$state[1] == $state{_tick} + 2 ) { + + if ($auto and !$nomail) { + print "Notifying $user, next notice in $$state[1] days.\n"; + email_notify($user); + } + + $$state[1] *= 2; + + # Following code would re-start the notifications we + # send to people after 120 days of not clearing + # their issue, but let's keep it disabled for + # now.. They get a notice on 127th day, and then + # on 255th etc., we don't reset the sequence. + + #$$state[1] >= 120 and do { + # $$state[0] = $state{_tick}; + # $$state[1] = 2; + #}; + } +} + + +# Write cache +if ( $auto ) { + # Already done above, with also printing a nice + # message + #for ( keys %warn ) { + # delete $nowarn{$_} + #} + #for ( keys %nowarn) { + # delete $state{$_}; + #} + + nstore \%state, $statefile; +} + +DONE: +exit 0; +############################################## +# Helpers below + + +sub email_notify { + my $user = shift; + + my @msg = @template; + for ( @msg ) { + $_ =~ s/\$vol{(\w+)}{(\w+)}/$vol{"$1.$user"}{$2}/mge; + $_ =~ s/\$threshold{(\w+)}/$threshold{$1}/mge; + $_ =~ s/\$warn{(\w+)}/$nowarn{$user}{$1}/mge; + $_ =~ s/\$CRIT{(\w+)}/$warn{$user}{$1} ? " (APPROACHING QUOTA)" : ""/mge; + } + #print join('', @msg); + open(MAIL, "| mail -s 'Approaching quota limit at HCoop' -a 'From: admins\@hcoop.net' -b 'docelic' '$user\@hcoop.net'"); + { no warnings; print main::MAIL join('', @msg); } + close MAIL; +} + + +# BEGIN_OF_ENTRY +# name user.docelic +# id 536872079 +# serv 69.90.123.67 deleuze.hcoop.net +# part /vicepa +# status OK +# backupID 536872081 +# parentID 536872079 +# cloneID 536955593 +# inUse Y +# needsSalvaged N +# destroyMe N +# type RW +# creationDate 1195841197 Fri Nov 23 13:06:37 2007 +# accessDate 0 Wed Dec 31 19:00:00 1969 +# updateDate 1198364408 Sat Dec 22 18:00:08 2007 +# backupDate 1198322931 Sat Dec 22 06:28:51 2007 +# copyDate 1195843557 Fri Nov 23 13:45:57 2007 +# flags 0 (Optional) +# diskused 36718 +# maxquota 400000 +# minquota 0 (Optional) +# filecount 6338 +# dayUse 20097 +# weekUse 172438 (Optional) +# spare2 0 (Optional) +# spare3 0 (Optional) +# END_OF_ENTRY + +__DATA__ +Hello, + +Some of your data volumes at HCoop are approaching quota +limit. + +We use quotas to "keep the shop clean", but we regularly +grant quota increase requests to all members who submit them. + +We wouldn't want you hit the quota limit, hence the early +notice. This is especially true for the mail and database +volumes where the data can accumulate "by itself", and where +reaching quota limits may be particularly inconvenient. +If you plan to store more data, it might be a good +time to submit the quota change request through our portal[1]. + +Here are your volume statistics: + +Home: + size=$vol{user}{diskused} KB + quota=$vol{user}{maxquota} KB + threshold=$threshold{user} + use=$warn{user} $CRIT{user} + +Mail: + size=$vol{mail}{diskused} KB + quota=$vol{mail}{maxquota} KB + threshold=$threshold{mail} + use=$warn{mail} $CRIT{mail} + +Databases: + size=$vol{db}{diskused} KB + quota=$vol{db}{maxquota} KB + threshold=$threshold{db} + use=$warn{db} $CRIT{db} + +Please submit your disk quota increase request in a timely +manner at: + + [1] https://members.hcoop.net/portal/quota + + +These automatic notices will be sent to you at exponential +intervals; that means in 2 days, 4 days, 8 days, 16 days and +so on. The method ensures that, if you retain status-quo +(don't submit a quota increase and don't reduce your data size), +you get to receive the reminders less and less often. +When and if the usage percentage of all your volumes drops below +the corresponding quota thresholds, the notifications will stop +and reset. + +Thank you, +HCoop + diff --git a/update-shadow-volumes b/update-shadow-volumes new file mode 100755 index 0000000..bd370ce --- /dev/null +++ b/update-shadow-volumes @@ -0,0 +1,7 @@ +#!/bin/bash + +# generate volume list +for A in `vos listvol krunk /viceps -quiet | cut -b0-34` +do \ + vos shadow $A deleuze /vicepa krunk /viceps -incremental -localauth +done diff --git a/vos-syncall b/vos-syncall new file mode 100755 index 0000000..cf55e55 --- /dev/null +++ b/vos-syncall @@ -0,0 +1,7 @@ +#!/bin/bash + +HOSTS="deleuze krunk" +for A in $HOSTS; do vos syncserv $A; done +for A in $HOSTS; do vos syncvldb $A; done +for A in $HOSTS; do vos syncserv $A; done +for A in $HOSTS; do vos syncvldb $A; done -- 2.20.1