|
|
| (12 intermediate revisions by the same user not shown) |
| Line 1: |
Line 1: |
| − | == MythConverg database maintenance ==
| + | __TOC__ |
| − | [[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. You'll need to edit your mysql userid and password if they're not mythtv/mythtv.
| + | == MacPorts == |
| − | | + | I maintain the myth* ports for Mac OS X at [[MacPorts]]. Currently, that is mythtv-core.27, mythweb.27 along with the older mythtv-core.25, mythweb.25, mythtv-core.26 and myth web.26. |
| − | 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.
| |
| − | | |
| − | | |
| − | optimizeMythDB.sh
| |
| − | | |
| − | <pre>
| |
| − | #!/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
| |
| − | </pre>
| |
| − | | |
| − | | |
| − | | |
| − | 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
| |
| − | | |
| − | <pre>
| |
| − | #!/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.'
| |
| − | | |
| − | </pre>
| |
| − | ---- | |
| − | | |
| − | == 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 [http://sourceforge.net/projects/lingon/ Lingon]. Peter isn't developing Lingon any more but it seems to work fine with 10.5.7.
| |
