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

[Steven Adeff]

On 3/21/07, Jon Boehm <boehm100 at comcast.net> 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?


I encountered this problem over last weekend, these logs filled my
drive and caused some serious headache.

I discovered this operative in the MySQL my.cnf file,
expire_logs_days         = 2

which supposedly removes logs older than the number of days you
specify, and so far this looks to be working on my machine.

I also have a cron job that runs nightly,

#!/bin/sh
#Dumps the mythconverg database - daily backup
#Keeps the last 7 days
DAY=($(date +%u))
DUMPFILE="mythbe_"$DAY".sql"
mysqldump -u mythtv -pmythtv mythconverg -c > /MythTV/tv/MYSQL/Backup/$DUMPFILE
exit 0

which dumps from my main C drive to my RAID recording drive.

-- 
Steve
Before you ask, read the FAQ!
http://www.mythtv.org/wiki/index.php/Frequently_Asked_Questions
then search the Wiki, and this list,
http://www.gossamer-threads.com/lists/mythtv/
Mailinglist etiquette -
http://www.mythtv.org/wiki/index.php/Mailing_List_etiquette