Solved

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

Posted on 2006-07-21
8
1,912 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
Webinar: MongoDB® Index Types

Join Percona’s Senior Technical Services Engineer, Adamo Tonete as he presents “MongoDB Index Types, How, When and Where Should They be Used?” on Wednesday, July 12, 2017 at 11:00 am PDT / 2:00 pm EDT (UTC-7).

 
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 500 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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

707 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