The MySQL database (named mythconverg) that is used to store all of the information used by MythTV is very important. Without it, you ain't got nothin'. MythTV's backend and frontend won't run, you won't know what shows are recorded, no upcoming shows will be recorded, you won't be able to find any of your movies on your video server, etc. Heck, if you don't look out the window, you won't even be able to tell what the weather is like.
So, it should be pretty clear that having a regular backup of this database is very, very important.
The good news is that Mythbuntu, out of the can, comes with a regular backup procedure installed. The bad news is: that it only runs once a week; that it doesn't notify you of success or failure; that it only backs itself up to the local hard disk (where the backup will get destroyed right along with the database when the hard disk goes south).
So, if you're paranoid like us, you might want to tweak the backup process a bit to make it more robust. We altered the backup script to create and keep six daily backups as well as a couple months worth of weekly epoch backups, FTP the latest backup to a remote location, and send email when the backup succeeds.
Here is a copy of the altered backup script, which can be installed in the /etc/cron.daily directory (although, see the following "Scheduling Jobs When You Want Them To Run" section) instead of /etc/cron.weekly:
/etc/cron.daily/mythtv-database:
#!/bin/bash
#
# /etc/cron.daily/mythtv-database script - check and backup mythconverg tables
#
# Copyright 2005/12/02 2006/10/08 Paul Andreassen
#
# Modified 2009/12/08 by Eric Wilde
# Switch to daily operation. Create weekly epoch backups as well
# as daily backups (this lets us keep multiple weeks of epoch
# backups, as well as a full week of daily backups). Email backup
# status, if requested. FTP backup to another server, if requested.
# Fix documentation.
#
# Modified 2012/06/07 by Eric Wilde
# Take advantage of the new database backup script, if it is
# available. Better error discovery and reporting.
#
# Note that the new database backup script (as of MythTV 0.25) is dain
# bramaged when it comes to database parameter handling. Its behavior is
# to check for a "config.xml" file in the ".mythtv" directory of the logged
# in user. Since the backup script is typically run periodically by
# dropping it into the "cron.daily" or "cron.weekly" directory, it is run
# under the user "root".
#
# The default installation of Mythbuntu makes a symlink to "/etc/config.xml"
# from "/root/.mythtv/config.xml" so that, when the backup script runs, it
# finds "/etc/config.xml". Unfortunately, the default installation of
# Mythbuntu installs an empty "/etc/config.xml". The database parameter
# handling in the backup script finds the empty file and gives up looking
# further, despite the fact that it hasn't found the database name,
# database user name, or anything else needed to backup the database.
#
# What it should do is carry on looking for parameters in the
# "/etc/mythtv/mysql.txt" file but it does not. If you will be running
# this script from either the "cron.daily" or "cron.weekly" directory, the
# easiest fix for this problem is to delete the symlink to the bogus empty
# config file, like this:
#
# sudo rm -f /root/.mythtv/config.xml
#
# Should you lose the mythconverg, MySQL database and you are running a
# pre-0.22 version of MythTV (i.e. one that doesn't have the database backup
# script), begin by deleting what's left of the database, if anything.
#
# Beware that performing this step will remove the entire database. You
# will lose all of your settings and will need to re-run the
# /usr/share/mythtv/sql/mc.sql script to set up the database structure
# before running the restore.
#
# To delete the database, do:
#
# mysql -u mythtv -p 'drop database mythconverg'
#
# To restore (assuming that you've dropped the database), do:
#
# mysql -u mythtv -p 'create database mythconverg'
# /usr/share/mythtv/sql/mc.sql
# zcat /var/backups/mythconverg.sql.gz | mysql -u mythtv -p mythconverg
#
# For more information on how to drop the database and create a new database,
# see:
#
http://www.mythtv.org/docs/mythtv-HOWTO-23.html#dropdb
http://www.mythtv.org/docs/mythtv-HOWTO-6.html#setupdb
#
# If you need to restore the mythconverg, MySQL database under a post-0.21
# (i.e. 0.22 and up) version of MythTV, do this:
#
# ./mythconverg_restore.pl --hostname=localhost \
# --drop_database --create_database \
# --directory=/var/backups --filename=mythconverg.sql.gz
#
set -e -u
# Get the mythconverg database parameters.
if [ -f /etc/mythtv/mysql.txt ]; then
. /etc/mythtv/mysql.txt
fi
# Set the options and parameters used to backup the database.
DBNAME="mythconverg"
BACKDIR="/var/backups"
DEBIAN="--defaults-extra-file=/etc/mysql/debian.cnf"
OPTIONS="--all --complete-insert --extended-insert --quick --quote-names \
--lock-tables"
SAVEEPOCHS=8
EMAILSTATUS="root"
FTPHOST="myotherserver"
FTPUSER="mythtv"
FTPPASSWD="abigsecret"
# If the mysql.txt file for MythTV sets the database name, use it instead of
# the default.
if [ "x$DBName" != "x" ] ; then
DBNAME=$DBName
fi
# Figure out which kind of a backup (daily or epoch) we're doing. Daily is
# M-Sa, epoch is Su.
DayNum=`date +%w`
if [ "x$DayNum" != "x0" ] ; then
BackupFile="${DBNAME}-daily.sql"
SAVEEPOCHS=6
else
BackupFile="${DBNAME}.sql"
fi
BackupPath="${BACKDIR}/${BackupFile}.gz"
# Check the database for consistency.
/usr/bin/mysqlcheck $DEBIAN -s $DBNAME
# Save some copies of the backup. If we're doing the daily backup, we'll
# always keep 6 copies (M-Sa). If we're doing the weekly (epoch) backup,
# we'll save as many as the user asked for.
/usr/bin/savelog -c $SAVEEPOCHS -l -n -q $BackupPath
# Dump and zip the database backup. Its either a daily or epoch backup,
# depending on which day of the week it is.
#
# If the backup script exists, we'll use it because, presumably, it is
# better. Otherwise, we just do a straight dump. Basically, they both
# end up running the mysqldump command.
if [ -f /usr/share/mythtv/mythconverg_backup.pl ]; then
/usr/share/mythtv/mythconverg_backup.pl --hostname=localhost \
--dbname=${DBNAME} --directory=${BACKDIR} \
--filename=${BackupFile}
else
/usr/bin/mysqldump $DEBIAN $OPTIONS $DBNAME | gzip > $BackupPath
fi
# In order to see if the backup succeeded, we test to see if a non-zero
# file was created. Since savelog will have moved up any previous files,
# there should be no file with the unadorned backup file name before the
# backup starts. Thus, if it creates a file and fills it with some bytes
# its a pretty good bet that it worked.
if [ ! -s $BackupPath ]; then
# If we're not emailing status to anyone, we're all done. Otherwise,
# send a failure email message to the appropriate user.
if [ ! "x$EMAILSTATUS" != "x" ] || [ ! -f /usr/bin/mail ]; then
exit 1
fi
HostName=`hostname`
/usr/bin/mail -s "Message from MythTV, failed database backup" \
root <<-ENDMSG
The $DBNAME database on $HostName faile to be backed up
to $BackupPath
ENDMSG
exit 1
fi
Add a note to the log.
/usr/bin/logger -p daemon.info -i -t${0#*/} "$DBNAME checked and backed up."
# FTP the backup somewhere safe, just in case the local disk goes south.
if [ "x$FTPHOST" != "x" ] && [ "x$FTPUSER" != "x" ]; then
echo -e "user ${FTPUSER} ${FTPPASSWD}\\nbin\\nput ${BackupPath} \
${BackupFile}.gz" | ftp -n $FTPHOST
fi
# If we're not emailing status to anyone, we're all done. Otherwise, send an
# email message to the appropriate user.
if [ ! "x$EMAILSTATUS" != "x" ] || [ ! -f /usr/bin/mail ]; then
exit 0
fi
HostName=`hostname`
/usr/bin/mail -s "Message from MythTV, database backup" root <<-ENDMSG
The $DBNAME database on $HostName has been successfully backed up
to $BackupPath
ENDMSG
# End of file.
Note that you should remove the original mythtv-database script from the /etc/cron.weekly directory so that it doesn't run once a week and conflict with the running of this script. You can simply delete it or move it to some other directory, if you feel the need to keep it.
If you are running on a later version of MythTV (i.e. 0.22 or greater), you also need to remove the symlink in /root/.mythtv, like this:
sudo rm -f /root/.mythtv/config.xml
To have the script email a completion message to you when it runs, set the EMAILSTATUS variable to the email address that you wish to send the message to. In the example, we've set EMAILSTATUS to "root". If you don't wish to send email anywhere, set EMAILSTATUS to "".
To have the script FTP a copy of the database to another server, you first need to set up a userid on that server. We use another Linux server and set the user's login script to /sbin/nologin so that nobody can actually login on that userid. The userid should, however, be created with a home directory so that FTP has somewhere to drop the files.
The name of the server should be set in the FTPHOST variable (in this case we've used "myotherserver") and the userid and password should be set in the FTPUSER and FTPPASSWD variables (in this case we've used "mythtv" and "abigsecret"). By setting FTPHOST and FTPUSER to something other than "", we cause the script to copy the database backup file to another server, where it will be safe if anything happens to Mythbuntu. The files will be copied to the home directory of the "mythtv" user on that server.
We then use the following logrotate file to rotate the copied files once a week and thereby keep a number of copies on the backup server:
/etc/logrotate.d/mythtv-database:
/home/mythtv/mythconverg.sql.gz {
missingok
notifempty
nocreate
rotate 8
}
In this case, we're keeping eight copies of the backup file and rotating once a week so we have eight weeks of backups.