How does max_allowed_packet affect the backup and restore of a database? I have a database based on mysql 5.0.75-0Ubuntu10.2, running under Ubuntu 9.04. I have a table in that database with a medium blob column. I have been using automysqlbackup to do nightly backups/dumps of the database, then restoring them periodically to either a staging server we have or to my MacBookPro. 

We recently had some trouble restoring with errors related to the packet size. Late last week, I changed the max_packet_size setting within automysqlbackup to 1MB (it was unspecified). After that change, the database backups stop at the table with the blob column. Actually, it stopped with the first record in that table. Do I have to set the max_packet_size setting to be at LEAST as large as the largest blob stored in that column?
I saw an interesting answer to a question about the biggest blob you may have. Here is the statement I saw in ServerFault : innodb_log_file_size and innodb_log_buffer_size combined must be larger than ten times your biggest blob object if you have a lot of large ones.

Based on that ServerFault post from Nils-Anders Nøttseter, you should query the table and find out which BLOB is the biggest, multiply that number by 11 or more, and use that answer as the max_allowed_packet going forward. 

It's funny that I addressed another question where I suggested sizing the max_allowed packet to hopefully solve the issue.

CAVEAT

According to the page 99 of "Understanding MySQL Internals" (ISBN 0-596-00957-7), here are paragraphs 1-3 explaining MySQL Packets:


  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.


Knowing this about MySQL Packets allows a DBA to size them up to accommodate multiple BLOBs inside one packet even if they are obnoxiously large.

In the past, I have had issues like this with MySQL Replication where the Master had a smaller value for the max_allowed packet than the Slave. It required me to bump up max_allowed_packet to 256M and restart mysql on the Master. In 99% of issues I have had, this would solve it. A few times I had to set to 512M.