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

[Jon Boehm]

Hi,

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

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
fashion.

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?
Jon