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

[Mike Perkins]

Jon Boehm wrote:
> 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
> 
> 
> 
> _______________________________________________
> mythtv-users mailing list
> mythtv-users at mythtv.org
> http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users
> 
This method, whilst it works is a bit of overkill for the required 
result. For instance, if you run more than one database, as I do, you 
get them all in the same dump file - not really convenient if you then 
need to edit the SQL before you reload one of them. You can get away 
with the following, which just uses the mythtv username, so can run in 
the maintenance overnight cron job (run as user mythtv):

mysqldump --delete-master-logs mythconverg > /path/mythtv-backup.sql

Works for me.

Mike Perkins