Difference between revisions of "Backup your database"

From MythTV Official Wiki
Jump to: navigation, search
(The mysqldump command: Instructions to restore on top of an existing DB can cause corruption)
(Turned outdated page into redirect)
 
(3 intermediate revisions by 2 users not shown)
Line 1: Line 1:
Before making any changes that may cause problems (such as upgrades), you should always back up your MythTV database.
+
#REDIRECT [[Database Backup and Restore]].
 
 
__TOC__
 
 
 
== The official MythTV backup (and restore) scripts ==
 
 
 
MythTV 0.22 adds a new scripted approach for performing database backups and restores, which generally requires issuing a single command without any arguments.  Although the scripts are only distributed with post-0.21 versions of MythTV, they were written to work with any version of MythTV (including 0.21-fixes and before) and can be easily downloaded and used without modifying your existing installation.  See [[Database Backup and Restore]].
 
 
 
== The <tt>mysqldump</tt> command ==
 
 
 
<pre>$ mysqldump -u <myth_user> -p --extended-insert --databases <myth_db_name> > mythdatabase.bak
 
Password: <myth_password></pre>
 
 
 
Be sure to replace <myth_user>, <myth_db_name>, and <myth_password> with the appropriate values.
 
 
 
The <tt>mysqldump</tt> command produces a text file that contains all of the mySQL commands necessary to recreate your database.  The syntax used here assumes you will delete the database and recreate it from scratch if you need to restore it.  In this case you probably will need to use the root mySQL user to do the restore because your <tt><myth_user></tt> userid may not have the necessary privileges in mySQL to create a new database.
 
 
 
The <tt>--extended-insert</tt> option causes <tt>mysqldump</tt> to generate multi-value INSERT commands inside the backup text file which results in the file being smaller and the restore running faster.
 
 
 
The command to restore the database using the backup file generated by <tt>mysqldump</tt> would be:
 
<pre>$ mysql -u <root_user> -p <mythdatabase.bak
 
Password: <root_password></pre>
 
 
 
If you get errors with this try using..
 
<pre>$mysql -u root -p mythconverg < myth_bakup.sql
 
Password: <password></pre>
 
 
 
Remember, the backup file is a text file and therefore can be compressed into a much smaller file.  If you plan to keep it around consider using something like gzip or bzip2 to save some major space.
 
 
 
Also, move the file some place safe if you are doing major surgery on your MythTV server.
 
 
 
== Utilities ==
 
You might also like to automate this activity on a daily Crontab schedule , otherwise you can use script utilities like : [http://sourceforge.net/projects/automysqlbackup/ automysqlbackup] to manage process. This particular script generates daily and monthly snapshots of your database.
 
 
 
== Scripts ==
 
 
 
There are many backup scripts out there, optimized for various things; we encourage your to post yours here, and don't forget to sign them.
 
 
 
My backup script runs as a cron job after optimize_mythdb.pl. Nothing too fancy, but easy to understand and modify for your needs.
 
#!/bin/bash
 
DATE=`date '+%Y%m%d'`
 
filename=mythdatabase.$DATE.bak
 
infile=/home/mythtv/$filename
 
mysqldump -umythtv -pmythtv -hlocalhost --extended-insert --databases mythconverg > $infile
 
gzip $infile
 
cp $infile.gz /drive1/backup/$filename.gz
 
cp $infile.gz /drive2/backup/$filename.gz
 
rm $infile.gz
 
 
 
 
 
My backup script below keeps up to a week's worth of database backups and has the option to copy the backup to another location after a successful backup. It should be run through cron on a daily basis.
 
- [[User:Sdkovacs|Sdkovacs]]
 
<pre>
 
#! /bin/bash
 
#
 
# Created on 3/22/2006 by sdkovacs
 
# Script: mythbackup
 
#
 
# Creates a backup of the mythconverg database in the form
 
# mythbackup.dayofweek.sql.gz. For example Monday would be mythbackup.Mon.sql.gz.
 
# This allows for a rotating 7-day backup of the mythconverg database. If ${BACKPATH} exists
 
# and contains a valid directory, the script will copy the backup to that directory in addition to
 
# /home/mythtv. This is useful for redundant backups. My ${BACKPATH} is a separate physical drive.
 
#
 
#
 
########################################################
 
BACKFILE="/home/mythtv/mythbackup.`date +%a`.sql.gz"
 
BACKPATH="/videos/backup/myth/"
 
 
 
mysqldump -u mythtv -pmythtv mythconverg -c | gzip -c > ${BACKFILE}
 
if [ $? -ne "0" ]
 
then
 
        echo "Mythtv database backup failed! Exiting..."
 
        exit 1
 
fi
 
 
 
if [[ -d ${BACKPATH} ]]
 
then
 
        cp ${BACKFILE} ${BACKPATH} || echo "Could not copy ${BACKFILE} to ${BACKPATH}. Please investigate."
 
fi
 
</pre>
 
 
 
My daily backup script gets your myth username and password from your config file, optionally compresses the backups, and rotates old backups by age, keeping backups from roughly 1, 2, 3, 5, 7, 11, 15, 23, and 31 days ago.  I put it in /usr/local/bin, and run it with a line like "HOME=/home/peter /usr/local/bin/mythdbbackup.sh -b" in my myth daily mantenance script in /etc/cron.daily .
 
--[[User:PeterSchachte|PeterSchachte]] 04:10, 26 April 2008 (UTC)
 
<pre>
 
#!/bin/sh
 
#
 
# mythdbbackup.sh - make a daily backup of the myth database, rotate old backups
 
#
 
# Peter Schachte (schachte at csse.unimelb.edu.au)
 
# Origin  13 May 2006, last updated 26 Apr 2008
 
 
 
# Defaults
 
backupdir="/var/backups"
 
backuplimit=12
 
backupbasename="mythdatabase"
 
 
 
usage="Usage:  $0 [options]
 
Options:
 
-d dir store backups in dir [default $backupdir]
 
-n name base name of backup file [default $backupbasename]
 
-k num keep at most backups [default $backuplimit]
 
-s sw pass sw as switches to mysqldump
 
-z compress backup with gzip
 
-b compress backup with bzip2
 
        -h      print this help text
 
 
 
Backup the myth database and rotate old backups.  Keeps backups of at least
 
the last two days, with older backups falling off exponentially.  The myth
 
username, password, and database name are found by reading the myth config
 
file (~/.mythtv/mysql.txt or /etc/mythtv/mysql.txt).  One of these must
 
exist.
 
 
 
You may pass multiple switches to mysqldump by repeating the -s option, or by
 
enclosing multiple options in quotes.
 
"
 
 
 
backupswitches=""
 
compresscmd=cat
 
suffix=""
 
while [ $# -gt 0 ] ; do
 
    case "$1" in
 
        -d)    backupdir="$2" ; shift ;;
 
-n) backupbasename="$2" ; shift ;;
 
        -k)    backuplimit="$2" ; shift ;;
 
        -s)    backupswitches="$backupswitches $2" ; shift ;;
 
        -z)    compresscmd="gzip" ; suffix=".gz" ;;
 
        -b)    compresscmd="bzip2" ; suffix=".bz" ;;
 
        -h | --help)
 
                echo "$usage" ; exit ;;
 
        -*)    echo "Unknown switch $1" ; echo "$usage" ; exit 1 ;;
 
        *)      echo "$usage" ; exit 1 ;;
 
    esac
 
    shift
 
done
 
 
 
if [ -r $HOME/.mythtv/mysql.txt ] ; then
 
    . $HOME/.mythtv/mysql.txt
 
elif [ -r /etc/mythtv/mysql.txt ] ; then
 
    . /etc/mythtv/mysql.txt
 
else
 
    echo "Can't read either ~/.mythtv/mysql.txt or $HOME/.mythtv/mysql.txt"
 
    echo "Ensure one of these files exist before using this script"
 
    exit 1
 
fi
 
 
 
backupcmd="mysqldump -u $DBUserName -p$DBPassword $backupswitches $DBName"
 
 
 
date="$(date +%F)"
 
timestamp="$(expr $(date +%s) / 86400)"
 
backupfilepattern="${backupdir}/${backupbasename}_*"
 
backupfilestart="${backupdir}/${backupbasename}_[0-9]+-[0-9]+-[0-9]+_"
 
backupfileend=".sql${suffix}"
 
newbackupfile="${backupdir}/${backupbasename}_${date}_${timestamp}.sql${suffix}"
 
backupfilestart=$(echo "$backupfilestart" | sed 's|\([\\/]\)|\\\1|g' )
 
backupfileend=$(  echo "$backupfileend"  | sed 's|\([\\/]\)|\\\1|g' )
 
 
 
# First make new backup; overwrites any earlier backup from the same day
 
$backupcmd | $compresscmd > $newbackupfile
 
if [ ! $? ] ; then
 
    echo "$0:  backup failed"
 
    exit 1
 
fi
 
 
 
# Do we have too many backups?
 
backupcount=$(echo $backupfilepattern | wc -w)
 
[ -z "$backuplimit" -o $backupcount -le $backuplimit ] && exit 0
 
 
 
# Too many backups, pick one to delete
 
# Specifically, by default, this script keeps backups from roughly 1, 2, 3,
 
# 5, 7, 11, 15, 23, and 31 days ago.  These ages vary, but the script tries
 
# to keep the *spacing* between the backup ages to a pattern of 1, 1, 2, 2,
 
# 4, 4, 8, 8, etc days.  Each time a new backup is written, if there are too
 
# many backups, the script decides which backup to delete by looking for a
 
# place where a spacing repeats 3 times, and deletes the second oldest of the
 
# backups with that spacing, effectively doubling the spacing before the
 
# oldest two of those backups.
 
 
 
target=$(echo $backupfilepattern | tr ' ' '\n' | awk "
 
    {
 
fname[NR] = \$0;
 
sub(/$backupfilestart/,\"\");
 
sub(/$backupfileend/,\"\");
 
num[NR] = \$0;
 
    }
 
END {
 
maxgaprun = 0;
 
maxgap = 0;
 
gaprun = 1;
 
# go through files from newest to oldest looking at days between them
 
for (i=NR-1; i>=1; --i) {
 
    gap = num[i+1] - num[i];
 
    if (gap > maxgap) {
 
maxgap = gap;
 
gaprun = 1;
 
    } else {
 
++gaprun;
 
    }
 
    if (gaprun > maxgaprun) {
 
maxgaprun = gaprun;
 
if (maxgaprun > 2) target = fname[i+1];
 
    }
 
}
 
if (target) print target; else print fname[1];
 
    }
 
")
 
rm $target
 
</pre>
 
 
 
== Issues ==
 
 
 
{|border=1 cellspacing=0
 
|'''Simon Kenyon <simon@koala.ie> posted the following to the mythtv-dev mailing list:'''
 
|-
 
|i thought i'd pass this on - because it took me a long time to diagnose what was going on.
 
 
 
some time ago i was having problems with my database, so i decided to dump in and then recreate it. all went fine and thing seemed to work. i kept having strange problems. i finally tracked it down to this:
 
 
 
for some versions of mysql, the mysqldump command would not save either the fact that a column was auto_increment, or the current value of the auto_increment variable.
 
 
 
so i had a database with no auto_increment attribute on some of the columns.
 
 
 
well i fixed it by dumping the database and then restoring it after editing the dump with the help of mythtv/libs/libmythtv/dbcheck.cpp
 
 
 
perhaps not the most elegant solution, but functional.
 
 
 
hope this helps somebody else in the future
 
<br />--
 
<br />simon
 
|}
 
 
 
[[Category:HOWTO]]
 
[[Category:MySQL]]
 

Latest revision as of 04:40, 9 April 2010

.