Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm trying to make heads and tails of my binary logs and am coming up short. I have many entries similar to the following from mysqlbinlog but I'm missing log data that I know should be there.

# at 1050548
#130617 18:40:03 server id 1  end_log_pos 1050619   Query   thread_id=844988    exec_time=0 error_code=0
SET TIMESTAMP=1371519603/*!*/;
BEGIN
/*!*/;
# at 1050619
# at 1050782
#130617 18:40:03 server id 1  end_log_pos 1050809   Xid = 33121028
COMMIT/*!*/;

It's puzzling because I get expected SQL in the mysqlbinlog output for statements executed in phpmyadmin but those coming from other PHP-based remote web servers appear to not be recorded.

My settings bin logging are:

bin_log = /path_to_sql_incrementals
binlog_ignore_db = mysql

Am I missing a logging option?

Mysql 5.0.95 / CentOS 5.9

share|improve this question
add comment

2 Answers

You need to use the --base64-output=decode-rows and --verbose options with mysqlbinlog or you will not always see everything that's in the log.

You should also consider removing binlog_ignore_db from your configuration, because this not only prevents logging changes to the mysql schema, it also stops logging of events from your session that reference other schemas any time you USE mysql until you USE another database because of the way replication rules are interpreted.

share|improve this answer
    
I've tried with both --verbose and --base64-ouput – neither of which had any affect. –  Greg Jun 20 '13 at 20:30
    
Also, you've tried them together, right? Because they do need to be used together. If that's not it, then it's very likely that either your binlog_ignore_db is preventing things from being logged or ... uh ... you're not expecting SELECT queries to be logged -- right? The binlog only captures DDL and DML, not SELECT. –  Michael - sqlbot Jun 20 '13 at 21:16
    
Tried them together - check. Not expecting SELECTS. Looking for INSERT and UPDATE specifically. I've dug into numerous examples of mysqlbinlog and it just seems like the data isn't there... –  Greg Jun 20 '13 at 23:11
    
Have you removed the binlog_ignore_db and restarted the server? Also, what are your binlog_format and tx_isolation set to? –  Michael - sqlbot Jun 20 '13 at 23:22
    
Ah... so, I've tried to duplicate what you are seeing... are you using any other arguments, such as --database? ... you shouldn't be ... just use mysqlbinlog --verbose --base64-output=decode-rows *filename* ... –  Michael - sqlbot Jun 20 '13 at 23:34
show 3 more comments
up vote 0 down vote accepted

MySQL doesn't log to the binary log file when you use direct database.table syntax in your update queries (eg. UPDATE Foo.Bar SET ...). My web application never specifies a default database so the data is never logged.

http://dev.mysql.com/doc/refman/5.0/en/replication-rules-db-options.html

I solved the problem my calling mysql_select_db before my INSERT and UPDATE queries.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.