Difference between revisions of "User:Pvr4me"

Revision as of 03:01, 9 August 2009

MythConverg database maintenance

optimize mythdb.pl does not work for me...I can't get the DBI/DBD module installed correctly. I'm running Myth on Mac OS X; I don't know Perl. I've tried various incantations to fix the problem but the OS X/perl neophyte combination makes it tough to find the solution.

Eventually, I noticed that mySQL includes a couple of utility programs that essentially do the same as optimize_mythdb.pl.

I've whipped up a script to do the mySQL maintenance. I only know enough shell scripting to be dangerous!  ;-) Suggestions, enhancements and wholesale re-writes are welcome.

First, the script uses mysqldump to back up mythconverg. If that succeeds, it calls mysqlcheck with the --auto-repair option to straighten out any data problems. If that succeeds, it uses mysqlcheck with the --optimize and --analyze options to reclaim free space and tune up the indexes, respectively. The three invocations of mysqlcheck use the --silent option to suppress the list of tables worked on. I hope that any errors will still be reported but I haven't experienced any yet. The reported results are accumulated in a log file and the script emails it to me for my perusal.

You'll need to edit your mysql userid and password if they're not mythtv/mythtv. Probably need to fix some paths...

optimizeMythDB.sh

#!/bin/sh
#
# A shell script to optimize Myth's database since I can't make the perl script work
#
# Version 1 -- 8 Aug 2009
# Craig Treleaven  ctreleaven@cogeco.ca
#
# To do ...
#	better way to mail?
#	zip the database backup
#	
#

mu=mythtv
mp=mythtv
mdb=mythconverg
mlog=OptimizeMythDBResults.txt

echo >$mlog
echo Check, optimize and analyse $mdb >>$mlog
echo ========================================== >>$mlog
echo  >>$mlog
echo $(date) >>$mlog
echo Backing up $mdb database >>$mlog

#pwd
mysqldump --user=$mu --password=$mp --result-file=mythConverg_backup_before_repair.sql  $mdb

if [ $? = 0 ] ; then
	echo $(date) >>$mlog
	echo Checking and repairing, if necessary $mdb... >>$mlog
#	pwd
	mysqlcheck --user=$mu --password=$mp --check --auto-repair --silent $mdb >>$mlog

    if [ $? = 0 ] ; then
		echo $(date) >>$mlog
		echo Optimizing and analyzing $mdb... >>$mlog
#		pwd
		mysqlcheck --user=$mu --password=$mp --optimize --silent $mdb >>$mlog
		mysqlcheck --user=$mu --password=$mp --analyze --silent $mdb >>$mlog
 
    fi
fi

echo $(date) >>$mlog
echo Done >>$mlog
/usr/bin/python smtp.py ctreleaven@cogeco.ca who_to "Optimize Myth DB $(date)" $mlog

The last step in the script above mails out a log file. I hope that errors are going to show despite the --silent options. The following is the python code You need to add your own smtp mail server address. You also need to create a file containing the email addresses the report should go to. I've used a file called 'who_to'. Obviously, you can comment out this step if you don't want to fool with mail.

smtp.py

#!/usr/bin/env python

import smtplib, sys, time

# change this to a new SMTP server if desired
#smtpHost = 'some.smtp.mail.server'
smtpHost = 'smtp.cogeco.ca'

# sys.argv[1] is the sender
# sys.argv[2] is the filename pointing to the list of recipients
# sys.argv[3] is the subject
# sys.argv[4] is the message content

if len(sys.argv) != 5:
  print 'Usage: ./smtp <sender> <recipient FN> <subj> <msg FN>'
  sys.exit(1)

# each recipient takes one line; '#' signals comments
rList = []
for line in open(sys.argv[2]).readlines():
  r = line.split('#')[0].strip()
  if r: rList.append(r)

sender = sys.argv[1]
subj = sys.argv[3]
date = time.ctime(time.time())
msg = 'From: %s\nTo: %s\nDate: %s\nSubject: %s\n%s' \
       % (sender, ', '.join(rList), date, subj, open(sys.argv[4]).read())

server = smtplib.SMTP(smtpHost) # connect, no login step
failed = server.sendmail(sender, rList, msg)
server.quit() 

if failed:
  print 'smtp.py: Failed recipients:', failed
else:
  print 'smtp.py: No errors.'

Mac OS X Periodic Execution

I want to run the database maintenance once a week. Starting with 10.4, Apple encourages the use of launchd for things that cron did. The easiest way I've found to set up the schedule is with Peter Borg's Lingon. Peter isn't developing Lingon any more but it seems to work fine with 10.5.7.