[mythtv-users] mythtv database - backup and size questions

Tony Lill ajlill at ajlc.waterloo.on.ca
Fri Jan 5 02:08:15 UTC 2007

devsk <funtoos at yahoo.com> writes:

> I didn't find much information on the list, so here I am asking. Please excuse if its a dupe.
> I was trying to see how to best backup my mythtv setup and current database like recorded shows, cutlists etc. I did a 'mysqldump -u mythtv -pmythtv mythconverg -c > mythtv_backup.sql' and noticed that the file was just 17MBytes, whereas 'du -sk /var/lib/mysql' returned 470MBytes.
> So, I have two questions:
> 1. Is 'mysqldump -u mythtv -pmythtv mythconverg -c > mythtv_backup.sql' a complete backup for a myth setup? I plan to do this everyday in a cron job and keep it with the other complete install backup which I do whenever I do major upgrades.


> 2. What are those <hostname-bin.xxxx> files in /var/lib/mysql and why is that dir so huge compared to the database size? Is that preallocated database files? Is that the directory I should be backing up?

These are a record of every change ever made to the database (modulo
whatever policy you set to clear out the files). It's basicly there to
allow you to restore your last backup and then replay the logs and get
back up to when the last complete transaction was made.

What I do is run    

mysqldump -ubackup -pbackup --single-transaction --flush-logs \
    --master-data --all-databases --delete-master-logs \
    | bzip2 > backup.sql.bz2

That backs up everything and cleans out the old hostname-bin.xxxx
files. You can turn the logs off completely and probably speed up your
database at the same time.
Tony Lill,                         Tony.Lill at AJLC.Waterloo.ON.CA
President, A. J. Lill Consultants        fax/data (519) 650 3571
539 Grand Valley Dr., Cambridge, Ont. N3H 2S2     (519) 241 2461
--------------- http://www.ajlc.waterloo.on.ca/ ----------------

Understatement of the century:
"Hello everybody out there using minix - I'm doing a (free) operating
 system (just a hobby, won't be big and professional like gnu) for
 386(486) AT clones"

	- Linus Torvalds, August 1991

More information about the mythtv-users mailing list