[mythtv-users] How to: Recover 2GB from /var/log/mysql (Ubuntu, Mysql)
Steven Adeff
adeffs.mythtv at gmail.com
Fri Mar 23 14:27:03 UTC 2007
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
More information about the mythtv-users
mailing list