Difference between revisions of "User:Pvr4me"

Revision as of 17:07, 17 September 2011

MythConverg database maintenance

The Perl script 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 even though 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
# Under Mac OS X launchd, stdout and stderr can be redirected to wherever we want.
#
# Last modifed: 21Aug2009 -- absolute paths to mysql utilities
#				23Aug2009 -- tweaks
# Craig Treleaven  ctreleaven@cogeco.ca
#
# To do ...
#	better way to mail?
#	zip the database backup
#	
#

mu=mythtv
mp=mythtv
mdb=mythconverg
mbak="/Volumes/Theatre/MythBackup/mythconverg_backup_before_repair.sql"
mlog="/tmp/OptimizeMythDBResults.txt"
sender="ctreleaven@cogeco.ca"
wholist="/Applications/AudioVisualMusic/OptimizeMythDB/who_to"


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

/usr/local/mysql/bin/mysqldump --user=$mu --password=$mp --extended-insert --result-file=$mbak $mdb 	>> $mlog 2>&1

if [ $? = 0 ] ; then
	echo $(date)										>> $mlog
	echo Checking and repairing, if necessary $mdb...	>> $mlog
	/usr/local/mysql/bin/mysqlcheck --user=$mu --password=$mp --check --auto-repair --silent $mdb 	>> $mlog 2>&1

    if [ $? = 0 ] ; then
		echo $(date)									>> $mlog
		echo Optimizing and analyzing $mdb...			>> $mlog
		/usr/local/mysql/bin/mysqlcheck --user=$mu --password=$mp --optimize --silent $mdb			>> $mlog 2>&1
		/usr/local/mysql/bin/mysqlcheck --user=$mu --password=$mp --analyze --silent $mdb			>> $mlog 2>&1
    fi
fi

# Email the results
#
echo $(date)											>> $mlog
echo Done												>> $mlog
/usr/bin/python /Applications/AudioVisualMusic/OptimizeMythDB/smtp.py $sender $wholist "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 day. 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.

I've set this up as a 'user agent', so the plist is stored in ~/Library/LaunchAgents/.

org.mythtv.optimizeMythDB.plist

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple Computer//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
<dict>
	<key>Label</key>
	<string>org.mythtv.optimizeMythDB</string>
	<key>ProgramArguments</key>
	<array>
		<string>/Applications/AudioVisualMusic/OptimizeMythDB/OptimizeMythDB.sh</string>
	</array>
	<key>StartCalendarInterval</key>
	<dict>
		<key>Hour</key>
		<integer>4</integer>
		<key>Minute</key>
		<integer>22</integer>
	</dict>
</dict>
</plist>