(Has it's own page)
|Line 1:||Line 1:|
== MythConverg database maintenance ==
== MythConverg database maintenance ==
Revision as of 02:25, 20 November 2012
I maintain the myth* ports for Mac OS X at MacPorts. Currently, that is mythtv-core.25 and mythweb.
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...
#!/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 firstname.lastname@example.org # # 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="email@example.com" 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.
#!/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 is the sender # sys.argv is the filename pointing to the list of recipients # sys.argv is the subject # sys.argv 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).readlines(): r = line.split('#').strip() if r: rList.append(r) sender = sys.argv subj = sys.argv date = time.ctime(time.time()) msg = 'From: %s\nTo: %s\nDate: %s\nSubject: %s\n%s' \ % (sender, ', '.join(rList), date, subj, open(sys.argv).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/.
<?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>