?
Solved

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

Posted on 2006-07-21
8
Medium Priority
?
1,923 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

Question has a verified solution.

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

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
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…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

771 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