[mythtv-users] How to: Recover 2GB from /var/log/mysql (Ubuntu, Mysql)
Mike Perkins
mikep at randomtraveller.org.uk
Thu Mar 22 09:55:38 UTC 2007
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
More information about the mythtv-users
mailing list