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

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  
BerkeleyJeffAsked:
Who is Participating?
 
hiteshgupta1Connect With a Mentor Commented:
Sorry forgot to mention the statement to disable binary logging
--disable-log-bin
0
 
todd_farmerCommented:
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
 
hiteshgupta1Commented:
hi BerkeleyJeff

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

go to the "Configuration file entries" topic
that would help u
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
BerkeleyJeffAuthor Commented:
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
 
hiteshgupta1Commented:
u can disbale the binary logging by disabling "log-bin"
0
 
todd_farmerCommented:
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
 
chigs20Commented:
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
 
BerkeleyJeffAuthor Commented:
Thanks, everyone!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.