Difference between revisions of "Backup your database"

From MythTV Official Wiki
Jump to: navigation, search
(Scripts)
Line 51: Line 51:
 
  cp $infile.gz /drive2/backup/$filename.gz
 
  cp $infile.gz /drive2/backup/$filename.gz
 
  rm $infile.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>
  
 
== Issues ==
 
== Issues ==

Revision as of 01:52, 29 November 2007

Before making any changes that may cause problems (such as upgrades), you should always back up your MythTV database.

The mysqldump command

$ mysqldump -u<myth_user> -p --extended-insert --databases <myth_db_name> > mythdatabase.bak
Password: <myth_password>

Be sure to replace <myth_user>, <myth_db_name>, and <myth_password> with the appropriate values.

The mysqldump 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 <myth_user> userid may not have the necessary privileges in mySQL to create a new database.

The --extended-insert option causes mysqldump 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 mysqldump would be:

$ mysql -u<root_user> -p <mythdatabase.bak
Password: <root_password>

If you get errors with this try using..

$mysql -u root -p mythconverg < myth_bakup.sql
Password: <password>

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.

If you would simply like to back up the data in your database in a manner such that it can be restored back into an existing database without having to destroy and recreate the entire database, then adding the --no-create-db and --add-drop-table options to the mysqldump command will tell mysqldump not to generate the commands to create the database and to add commands to "DROP" each database table before recreating and reloading it, effectively clearing the tables of existing data before reloading them. This mysqldump command would look like this:

$ mysqldump -u<myth_user> -p --extended-insert --no-create-db --add-drop-table --databases <myth_db_name> >mythdatabase.bak
Password: <myth_password>

You could then use your <myth_user> to restore the data to the database:

$ mysql -u<myth_user> -p mythconverg < mythdatabase.bak
Password: <myth_password>

Utilities

You might also like to automate this activity on a daily Crontab schedule , otherwise you can use script utilities like : 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. - Sdkovacs

#! /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

Issues

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
--
simon