It's OK to max out the max_allowed_packet
to 1G. Whenever a MySQL Packet is constructed, it will not jump to 1G from the start. Why?
First you need to know what a MySQL Packet. Page 99 of the Book
explains it in paragraphs 1-3 as follows:
MySQL network communication code was
written under the assumption that
queries are always reasonably short,
and therefore can be sent to and
processed by the server in one chunk,
which is called a packet in MySQL
terminology. The server allocates the
memory for a temporary buffer to store
the packet, and it requests enough to
fit it entirely. This architecture
requires a precaution to avoid having
the server run out of memory---a cap
on the size of the packet, which this
option accomplishes.
The code of interest in relation to
this option is found in
sql/net_serv.cc. Take a look at my_net_read(), then follow the call to my_real_read() and pay
particular attention to
net_realloc().
This variable also limits the length
of a result of many string functons.
See sql/field.cc and
sql/intem_strfunc.cc for details.
Compare that with the MySQL Documentation on max_allowed_packet
:
The maximum size of one packet or any generated/intermediate string,
or any parameter sent by the mysql_stmt_send_long_data() C API
function. The default is 4MB as of MySQL 5.6.6, 1MB before that.
The packet message buffer is initialized to net_buffer_length bytes,
but can grow up to max_allowed_packet bytes when needed. This value by
default is small, to catch large (possibly incorrect) packets.
You must increase this value if you are using large BLOB columns or
long strings. It should be as big as the largest BLOB you want to use.
The protocol limit for max_allowed_packet is 1GB. The value should be
a multiple of 1024; nonmultiples are rounded down to the nearest
multiple.
When you change the message buffer size by changing the value of the
max_allowed_packet variable, you should also change the buffer size on
the client side if your client program permits it. On the client side,
max_allowed_packet has a default of 1GB. Some programs such as mysql
and mysqldump enable you to change the client-side value by setting
max_allowed_packet on the command line or in an option file.
Given this information, you should be glad MySQL will expand and contract the MySQL Packet as needed. Therefore, go ahead and
Master and Slave should match in terms of who they transmit data, especially BLOB data.
UPDATE 2013-07-04 07:03 EDT
From your messages concerning the relay log, it looks like you have the following
- a corrupt relay log
- a good master log
SUGGESTION
SHOW SLAVE STATUS\G
STOP SLAVE;
CHANGE MASTER TO
MASTER_LOG_FILE='(Relay_Master_Log_File from SHOW SLAVE STATUS\G)',
MASTER_LOG_POS=(Exec_Master_Log_Pos from SHOW SLAVE STATUS\G);
START SLAVE;
Running CHANGE MASTER TO
clears all relay logs and starts with a new one. You will be replicating from the Last Master BinLog Event (BinLog,Position) that executed on the Slave.
Give it a Try !!!
Since the --database option does not screen out queries that explicitly name the database with the table, you should redirect that to grep and locate primary_database
and INSERTs, UPDATEs, DELETEs:
mysqlbinlog --database=**database** --start-datetime="$start_datetime" --stop-datetime="$stop_datetime" list_of_binlog_file_names | grep "primary_database\." | grep "[IUD][NPE][SDL][EA][RT][TE]"
This may not be a perfect solution. Perhaps a good perl or python script and nail this one.
Best Answer
Flushing the log on the master solves your problem pretty much by accident. When the master log file rotates,
mysqlbinlog
closes the current file and opens the next one, which implicitly flushes any buffered data.You can't trigger
mysqlbinlog
to flush its log on demand the way you can an actual server, but I've found a way to trigger it to maintain the file in an always flushed state, which seems to be a viable way to improve your results.When Oracle added the
--raw
option tomysqlbinlog
, which, in conjunction with--read-from-remote-server
and--stop-never
can be used to capture a real-time backup of the master's binlogs, they overlooked at least a couple of seemingly-obvious things.In light of the documented purpose of the feature...
...it seems like a distinct oversight that
mysqlbinlog
does not bother to flush its output stream after each write.Maybe that's why they put “live” in quotes. :)
My testing confirms that
mysqlbinlog
, as shipped, leaves dangling data in its buffer that could have been flushed, but this seems like a fairly straightforward problem to fix.For example, this little change in
mysql-5.6.27/client/mysqlbinlog.cc
at line 2477:This change results in the size of the file created by
mysqlbinlog
continually matching the size returned bySHOW MASTER STATUS;
, rather than always seeming to be a few bytes behind. I've always known of this slight lag, but never looked into it in depth. Of course, continually flushing the buffer is going to come with a cost, but this seems like a non-negotiable necessity for "backup" purposes. Note that this is only a flush of the user space buffers, it's not anfsync()
or similar, so I don't think it's going to be that different in performance terms... and in any event, it's not going to impact the master.The logical
||
is used becausemy_fwrite()
returns 0 on success, so we short-circuit and don't try to flush if that fails, and we error out if either operation fails, sincefflush()
also returns 0 on success.This change should significantly improve the behavior you see.
Of course, it's still always possible for you to end up with an incomplete log, since it's still an open file and still being written to... but when testing this fix in a low traffic environment, hot-copying the file results in a usable binlog for me -- while, otherwise, in the same environment, the file end is almost always incomplete when using the stock binary.
For different versions (of 5.6+ only -- this feature wasn't implemented at all before 5.6) the line in the source may be different but it should still be pretty easy to spot, and not far from the line numbers indicated here.
Facebook's fork of MySQL 5.6 features a similar but more sophisticated fix, and a new option
--flush-result-file
that causes the flushing to be a little less aggressive, but configurable. However, I found it unnecessarily difficult to compile in its current form, so I developed the little patch above.As long as you're patching mysqlbinlog, you might find another small tweak useful for solving another design oversight. The
mysqlbinlog
utility in the official distribution lacks support for the--compress
command line option (although Percona had the good sense to add it to theirs.)But, it's also easy enough to enable compression. I just came up with the fix above for this question, but I've been using the fix, below, for quite some time now, since -- again -- it has always seemed to me like an obviously missing feature.
From the 5.6.27 source, it looks like this:
This change doesn't enable support for passing the
--compress
command line option -- it just turns on the client compression protocol on the connection to the master, for any master that supports it (which they essentially all do), thereby reducing the amount of network bandwidth needed to transport the logs -- sometimes by a factor of 10:1 or higher, depending on the specific payload. It does not change the contents of the generated files -- it just enables compression of the data traversing the network. On the wire, this has the same effect from the master's perspective as enablingslave_compressed_protocol
on the slave server. (The documentation is ambiguous, but this setting has no effect on the master unless the master is also a slave of another server.)