[mythtv-users] How to: Recover 2GB from /var/log/mysql (Ubuntu, Mysql)

Jon Boehm boehm100 at comcast.net
Wed Mar 21 19:56:57 UTC 2007


I see this in my Ubuntu Edgy Server install but it may apply to others as

Seeing the issue:
cd /var/log/mysql
du -h

Mine showed 2.2G of mysql-bin.#### files.  These are log files that show a
play by play of what is happening in the database.  They are important for
recovery but if you do a backup of the database these files can be restarted
at the moment of the backup. You should be backing up your database in some

The commonly suggested command is this:
mysqldump -u mythtv -pyour_pass_here mythconverg -c > mythtv_backup.sql

I suggest this:
mysqldump -u root --single-transaction --flush-logs  --master-data
--all-databases --delete-master-logs  | bzip2 > mythtv_backup.sql.bz2

The reason to use the root user is that this command requires RELOAD
privilege.  After issuing this command the large mysql-bin.#### files
restart from the point of the database dump.  Thus since I back up daily the
bin log files only grow to ~10% of there former size. (Ubuntu edgy is set up
to keep 10 days of these logs by default)

WARNING: This all assumes that the old database recovery method still works
with this new command.  I don't see why it would not, but use this
suggestion at your own risk. I have not first hand experience at recovery
and hope I never do.

Suggestions?  Anyone see any glaring problem?

More information about the mythtv-users mailing list