MySQL bin log missing data

backupMySQLmysqlbinlog

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

Best Answer

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.

Related Solutions

Mysql – Applying MySQL 5.1 bin log to MySQL 5.5

It is possible to replicate from an older version 'master' to a new version master for the next higher major version (5.1 to 5.5 for example): http://dev.mysql.com/doc/refman/5.5/en/replication-compatibility.html

Of course bin logs are portable to the same architecture running on different machines, or replication would be almost useless (there are difficulties with running on different OSes, though).

Since your question only asked about applying a bin log to the second server, I might mention that if you don't have the 'master position' of when the second server should begin reading the binary log, you might have inconsistencies in data. Especially if it has been a long time since you've taken a full backup and no longer have the relevant log entries.

But at the heart of it, Binary Logs from an older major version to the next major version is supported.

Mysql – Why do the MySQL bin log files still exist after a purge or flush

The PURGE BINARY LOGS statement deletes all the binary log files listed in the log index file prior to the specified log file name or timestamp. Deleted log files also are removed from the list recorded in the index file, so that the given log file becomes the first in the list.

I hope you have purged binary logs upto mysql-bin.000019 using command

PURGE BINARY LOGS TO 'mysql-bin.000019';

If you need to purge all logs do like

PURGE BINARY LOGS TO 'mysql-bin.000025';

This will remove binary logs upto mysql-bin.000025.

UPDATE

You can try

RESET MASTER;

RESET MASTER Deletes all binary log files listed in the index file, resets the binary log index file to be empty, and creates a new binary log file

The effects of RESET MASTER differ from those of PURGE BINARY LOGS in 2 key ways:

RESET MASTER removes all binary log files that are listed in the index file, leaving only a single, empty binary log file with a numeric suffix of .000001, whereas the numbering is not reset by PURGE BINARY LOGS.
RESET MASTER was not intended to be used while any replication slaves are running. The behavior of RESET MASTER when used while slaves are running is undefined (and thus unsupported), whereas PURGE BINARY LOGS may be safely used while replication slaves are running.

CAVEAT by RolandoMySQLDBA

If you run RESET MASTER with Slaves connected and running, the IO Thread of each Slave will immediately lose its place. Replication is thus broken and you will have to spend time getting the data on all Slaves sync'd again. If you want to safely delete binary logs from a Master without breaking Replication integrity, here is what you do:

Run SHOW SLAVE STATUS\G on each Slave.
Take note of Relay_Master_Log_File. This is the binary log whose latest statement was successfully executed in the Slave).
From all displays of SHOW SLAVE STATUS\G, determine which Relay_Master_Log_File is the oldest (For example, 'mysql-bin.00123').
You can run PURGE BINARY LOGS TO 'mysql-bin.00123'; None of the Slaves will lose its place.

The overall effect? This will leave behind binary logs on the Master whose statements that have not been executed on all Slaves as of yet.

Best Answer

Related Solutions

Mysql – Applying MySQL 5.1 bin log to MySQL 5.5

Mysql – Why do the MySQL bin log files still exist after a purge or flush

CAVEAT by RolandoMySQLDBA

Related Question