From: Joe Presbrey <presbrey@mit.edu>
Date: Fri, 24 Aug 2007 03:42:35 +0000 (+0000)
Subject: added SQL cron jobs
X-Git-Url: http://andersk.mit.edu/gitweb/sql.git/commitdiff_plain/8145dbcc1f707acb781583a288f55923866d6be2

added SQL cron jobs

git-svn-id: svn://presbrey.mit.edu/sql@145 a142d4bd-2cfb-0310-9673-cb33a7e74f58
---

diff --git a/bin/backup.php_OLD b/bin/backup.php_OLD
deleted file mode 100644
index b5a3870..0000000
--- a/bin/backup.php_OLD
+++ /dev/null
@@ -1,134 +0,0 @@
-#!/usr/bin/env php
-<?php
-
-$EXCEPTIONS[] = 'presbrey+aol';
-
-ini_set('memory_limit', '-1');
-set_time_limit(0);
-ignore_user_abort(1);
-
-define('BACKUP_RUN_STATS', 0);
-define('BACKUP_CRON', 1);
-
-$bin_mysql='mysql -umit-backup';// --defaults-file=/afs/athena.mit.edu/user/p/r/presbrey/web/etc/sql-mit-edu.my.cnf';
-$bin_mysqldump='mysqldump -umit-backup';// --defaults-file=/afs/athena.mit.edu/user/p/r/presbrey/web/etc/sql-mit-edu.my.cnf';
-$dump_path='/afs/athena.mit.edu/contrib/sql/backup';
-//$dump_exec = "$bin_mysqldump -CefqQK --single-transaction --result-file=$dump_path/%s %s 2>&1";
-//$dump_exec = "$bin_mysqldump -CefqQK --result-file=$dump_path/%s %s 2>&1";
-$dump_exec = "$bin_mysqldump -CefqQK %s | gzip > $dump_path/%s.sql.gz ";
-$dump_host='localhost';
-$dump_user='mit-backup';
-$dump_pass='';
-$sql_resource = mysql_connect($dump_host,$dump_user,$dump_pass);
-
-$sql_databases = mysql_list_dbs($sql_resource);
-$stat['nDatabases'] = mysql_num_rows($sql_databases);
-
-if (BACKUP_RUN_STATS):
-	if (!BACKUP_CRON) {
-		printf("Polling statistics for {$stat['nDatabases']} databases...\n");
-	}
-
-$time[0][0] = microtime(true);
-while($row1 = mysql_fetch_row($sql_databases)) {
-	$i_database++;
-	$databases[$row1[0]]['name'] = $row1[0];
-	$sql_tables = mysql_query("SHOW TABLE STATUS FROM `{$row1[0]}`");
-	$databases[$row1[0]]['Tables'] = mysql_num_rows($sql_tables);
-	while($row2 = mysql_fetch_assoc($sql_tables)) {
-		$databases[$row1[0]]['Rows'] += $row2['Rows'];
-		$databases[$row1[0]]['Data_length'] += $row2['Data_length'];
-		$databases[$row1[0]]['Size'] += $row2['Data_length'];
-	}
-	if ($i_database%100==0) {
-		if (!BACKUP_CRON) {
-			printf("polled $i_database databases: %d%%\n", $i_database/$stat['nDatabases']*100);
-		}
-		break;
-	}
-}
-$time[0][1] = microtime(true);
-
-foreach($databases as $name=>$info) {
-	$stat['nTables'] += $info['Tables'];
-	$stat['nRows'] += $info['Rows'];
-	$stat['nSize'] += $info['Size'];
-}
-
-if (!BACKUP_CRON) {
-	printf("\nPreparing backup...\n\nDatabases:\t%d\nTables:\t\t%d\nRows:\t\t%d\nSize:\t\t%d MB\n\n",
-		$stat['nDatabases'],
-		$stat['nTables'],
-		$stat['nRows'],
-		$stat['nSize']/1024/1024);
-}
-
-else:
-
-while($row1 = mysql_fetch_row($sql_databases)) {
-	if ($row1[0] == 'information_schema')
-		continue;
-	$databases[$row1[0]]['name'] = $row1[0];
-}
-
-endif;
-
-$i_database = 1;
-$time[1][0] = microtime(true);
-foreach($databases as $name=>$info) {
-	if (in_array($name, $EXCEPTIONS)) {
-		if (!BACKUP_CRON) {
-			printf('Skipping EXCEPTIONed database: %s', $name);
-		}
-		continue;
-	}
-	if (stristr($name,'+')) {
-		$owner = array_shift(explode('+',$name)).'/';
-		@mkdir("$dump_path/$owner");
-		`fs sa $dump_path/$owner $owner rl 2>/dev/null`;
-		`fs sa $dump_path/$owner system:authuser none`;
-	} else {
-		$owner = 'root/';
-		@mkdir("$dump_path/$owner");
-		`fs sa $dump_path/$owner system:authuser none`;
-		`fs sa $dump_path/$owner system:sql-backup none`;
-	}
-	$exec = sprintf($dump_exec, $name, $owner.$name);
-
-	if (file_exists("$dump_path/$owner$name.sql.gz")) {
-		if (!BACKUP_CRON) {
-			printf("Saving old dump file: %s\n",
-			"$dump_path/$owner$name.sql.gz");
-		}
-		rename("$dump_path/$owner$name.sql.gz","$dump_path/OLD/$name.sql.gz");
-	}
-	
-	if (!BACKUP_CRON) {
-		printf("Dumping %d of %d / %d%% (%s):\n",
-		$i_database,
-		$stat['nDatabases'],
-		$i_database/$stat['nDatabases']*100,
-		$name);
-	}
-	//printf("Executing $exec\n");
-
-	$time[$name][0] = microtime(true);
-	$dump_proc = popen($exec, 'r');
-	while(!feof($dump_proc))
-		echo fread($dump_proc, 1024);
-	pclose($dump_proc);
-	$time[$name][1] = microtime(true);
-
-	if (!BACKUP_CRON) {
-		printf("\n");
-	}
-
-	$i_database++;
-}
-$time[1][1] = microtime(true);
-
-if (!BACKUP_CRON) {
-	printf("Finished.\n%s seconds.\n", $time[1][1]-$time[1][0]);
-}
-
-?>
diff --git a/libexec/afs-renew-tickets.sh b/libexec/afs-renew-tickets.sh
new file mode 100755
index 0000000..076331e
--- /dev/null
+++ b/libexec/afs-renew-tickets.sh
@@ -0,0 +1,4 @@
+#!/bin/bash
+
+/usr/kerberos/bin/kinit -k -t /root/keytab-sql.mit.edu daemon/sql.mit.edu
+/usr/bin/aklog
diff --git a/bin/backup.php b/libexec/afs-run-backup.php
similarity index 100%
rename from bin/backup.php
rename to libexec/afs-run-backup.php
diff --git a/bin/permissions.php b/libexec/afs-set-permissions.php
similarity index 100%
rename from bin/permissions.php
rename to libexec/afs-set-permissions.php
diff --git a/libexec/cron-daily-afs.sh b/libexec/cron-daily-afs.sh
new file mode 100755
index 0000000..bca3dfb
--- /dev/null
+++ b/libexec/cron-daily-afs.sh
@@ -0,0 +1,5 @@
+#!/bin/bash
+
+/srv/sql/libexec/afs-renew-tickets.sh
+/srv/sql/libexec/afs-run-backup.php
+/srv/sql/libexec/afs-set-permissions.php
diff --git a/libexec/cron-daily-local.sh b/libexec/cron-daily-local.sh
new file mode 100755
index 0000000..0b926bb
--- /dev/null
+++ b/libexec/cron-daily-local.sh
@@ -0,0 +1,9 @@
+#!/bin/bash
+
+NEWBF=/srv/backup/daily/sql.mit.edu_`date +%m-%d-%Y`.sql.gz
+
+/usr/bin/mysqldump -umit-backup -efqQK --all-databases | gzip > $NEWBF
+
+if [[ -s $NEWBF && `ls /srv/backup/daily/ | wc -l` -ge 4 ]]; then
+	rm -f /srv/backup/daily/`ls -t /srv/backup/daily/ | tail -n1`
+fi
diff --git a/libexec/cron-weekly-local.sh b/libexec/cron-weekly-local.sh
new file mode 100755
index 0000000..3857aa4
--- /dev/null
+++ b/libexec/cron-weekly-local.sh
@@ -0,0 +1,5 @@
+#!/bin/bash
+
+if [[ `ls /srv/backup/daily/ | wc -l` -ge 3 ]]; then
+	mv /srv/backup/daily/`ls -t /srv/backup/daily/ | tail -n1` /srv/backup/weekly/
+fi