Solved

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

Posted on 2006-07-21
8
1,902 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Mysqli Query 5 51
simple mysql statement 3 38
insert row field data graphically 4 24
Insert values are dynamic 11 41
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 …
Creating and Managing Databases with phpMyAdmin in cPanel.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

773 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