Solved

How to do Log rotation and set max log file size policy in mysql ?

Posted on 2012-03-26
2
416 Views
Last Modified: 2012-04-11
I know how to enable log for mysql:
i insert the following line in /etc/my.cnf:
log=/datalogs/mysql.log

But how to configure mysql for log rotation and to set the max size for the same?

Is it possible to set that only teh update,insert and delete commands be logged ... i dont want to log select queries.
0
Comment
Question by:pvinodp
2 Comments
 
LVL 24

Accepted Solution

by:
johanntagle earned 500 total points
ID: 37768785
Use the binary logs (http://dev.mysql.com/doc/refman/5.1/en/binary-log.html) for logging only DML statements.  The log is not readily readable (it is binary) but it's easy to process using the mysqlbinlog utility.  An added benefit is you can use them to update a backup to point of failure in case you encounter problems (see http://dev.mysql.com/doc/refman/5.1/en/point-in-time-recovery.html).  

In you my.cnf:

log-bin = /path/to/where/you/want/your/logs/prefix

(everything after "log-bin" is optional.  documentation says "If no base_name value is given, the default name is the value of the pid-file option (which by default is the name of host machine) followed by -bin")

You set size limit via:

max_binlog_size = <size_in_bytes>

I don't know of an actual log rotation functionality in mysql, but you can set logs to expire via:

expire_logs_days = <number_in_days>

Hope this helps
0
 

Author Closing Comment

by:pvinodp
ID: 37836079
THanks for pointing to binary logs. Also you can set the database for which you need the logs for. This is helpful in case you have multiple database in your mysql server.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Connect MySql database to wordpress site 3 20
html input clean up 3 32
nagios 1 12
Virtualizing very old guest OS 4 27
I. Introduction There's an interesting discussion going on now in an Experts Exchange Group — Attachments with no extension (http://www.experts-exchange.com/discussions/210281/Attachments-with-no-extension.html). This reminded me of questions tha…
Join Greg Farro and Ethan Banks from Packet Pushers (http://packetpushers.net/podcast/podcasts/pq-show-93-smart-network-monitoring-paessler-sponsored/) and Greg Ross from Paessler (https://www.paessler.com/prtg) for a discussion about smart network …
Learn how to get help with Linux/Unix bash shell commands. Use help to read help documents for built in bash shell commands.: Use man to interface with the online reference manuals for shell commands.: Use man to search man pages for unknown command…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

760 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

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now