?
Solved

Stop creation of logs: mysql-bin.000047, etc.

Posted on 2006-07-21
8
Medium Priority
?
1,951 Views
Last Modified: 2012-05-05
Mine is the default installation of MySQL 5.0 on Debian Linux. It seems to be creating logs of whatever data is operated on, thus leading to a huge amount of logs. In fact, the size of my MySQL logs is now greater than the size of my databases. How can I prevent mysql from logging anything to serious problems?

Thanks,
Jeff


mysql-bin.000047  mysql-bin.000100  mysql-bin.000153  mysql-bin.000206  mysql-bin.000259  mysql-bin.000312
mysql-bin.000048  mysql-bin.000101  mysql-bin.000154  mysql-bin.000207  mysql-bin.000260  mysql-bin.000313
mysql-bin.000049  mysql-bin.000102  mysql-bin.000155  mysql-bin.000208  mysql-bin.000261  mysql-bin.000314
mysql-bin.000050  mysql-bin.000103  mysql-bin.000156  mysql-bin.000209  mysql-bin.000262  mysql-bin.index
mysql-bin.000051  mysql-bin.000104  mysql-bin.000157  mysql-bin.000210  mysql-bin.000263
mysql-bin.000052  mysql-bin.000105  mysql-bin.000158  mysql-bin.000211  mysql-bin.000264
mysql-bin.000053  mysql-bin.000106  mysql-bin.000159  mysql-bin.000212  mysql-bin.000265
purple:/var/log/mysql# du -sh .
3.1G  
0
Comment
Question by:BerkeleyJeff
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 30

Expert Comment

by:todd_farmer
ID: 17152021
You can disable binary logging.  Note that binary logging is generally disabled by default, but is needed in certain situations (replication, for example, requires binary logging).

http://dev.mysql.com/doc/refman/5.0/en/binary-log.html
0
 
LVL 8

Expert Comment

by:hiteshgupta1
ID: 17152032
hi BerkeleyJeff

http://dev.mysql.com/books/hpmysql-excerpts/ch07.html

go to the "Configuration file entries" topic
that would help u
0
 

Author Comment

by:BerkeleyJeff
ID: 17152299
The only lines I see in my log that look related to what's mentioned in "configuration file entries" are the following:

log-bin                        = /var/log/mysql/mysql-bin.log
expire-logs-days       = 20
max_binlog_size         = 10485760

None of these lines appear to enable / disable binary logging. Moreover, none of the flags that the program is run with seem responsible.

/usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-locking --port=3306 --socket=/var/run/mysqld/mysqld.sock

Could it be that binary logging is enabled by default, and I need to add a special command to the my.cnf in order to disable it?

Thanks.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 8

Expert Comment

by:hiteshgupta1
ID: 17152424
u can disbale the binary logging by disabling "log-bin"
0
 
LVL 8

Accepted Solution

by:
hiteshgupta1 earned 2000 total points
ID: 17152441
Sorry forgot to mention the statement to disable binary logging
--disable-log-bin
0
 
LVL 30

Expert Comment

by:todd_farmer
ID: 17154157
If you remove the entries you listed above, that should disable binary logging.  When you specify a path for binary logs, that is a flag to enable it.
0
 
LVL 6

Expert Comment

by:chigs20
ID: 17154986
Before you go about disabling the binary logs you should know they are very useful.  First, as mentioned earlier, they are used for replication which helps create a HA solution.  Second, using a mixture of backups and binary logs can help you restore your database.  (e.g. use mysqldump @ 12am, the server crashes at 4am and requires a complete restore... well you restore the dump then use the binary logs to restore the db back to its lastest point, otherwise if you just used the backup you'll have lost 4 hours of data.  Can you afford that?)

A simple solution to save space due to the size of the logs:
Setup a cronjob to every N days to do a mysqldump (mysqldump ... --flush-logs > db.sql) the flush logs statement will force a new binary log to be created thus allowing you to delete the previous logs which saves disk space.

0
 

Author Comment

by:BerkeleyJeff
ID: 17159076
Thanks, everyone!
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month16 days, 22 hours left to enroll

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question