title: ERROR 2006 (HY000) at line MySQL server has gone away
body: <h1>Problem</h1>
  
  <p>I encountered this error during a Mysql DB dump and restore. None of the solutions posted anywhere solved my problem, so I thought I post my own answer I found on my own for posterity.</p>
  
  <ul>
  <li><b>Source Env:</b>
  CentOS 4 i386 ext3, Mysql 5.5 dump, Most tables engines are MySIAM, with a few InnoDBs.</li>
  <li><b>Destination Env:</b>
  CentOS 6 x66_64 XFS, Mysql 5.6</li>
  </ul>
  
  <p>Source DB is 25GB on disk, and a gzipped dump is 4.5GB.</p>
  
  <h2>Dump</h2>
  
  <p>Dump command from source -> destination was run like so:</p>
  
  <pre><code>mysqldump $DB_NAME | gzip -c | sudo ssh $USER@$IP_ADDRESS 'cat &gt; /$PATH/$DB_NAME-`date +%d-%b-%Y`.gz'
  </code></pre>
  
  <p>This makes the dump, gzips on the fly, and writes it over SSH to the source. You don't have to do it this way, but it is convenient.</p>
  
  <h2>Import</h2>
  
  <p>On the new source DB I ran the import like so:</p>
  
  <pre><code>gunzip &lt; /$PATH/$DB_NAME.gz | mysql -u root $DB_NAME
  </code></pre>
  
  <p>Note that you have to issue <code>CREATE DATABASE DB_NAME</code> SQL to make the new empty detination DB before starting the import.</p>
  
  <p>Everytime I tried this I got this type of error:</p>
  
  <pre><code>ERROR 2006 (HY000) at line MySQL server has gone away
  </code></pre>
  
  <h2>Source DB conf</h2>
  
  <p>My source DB is a virt server using VMWare so I can resize the RAM/CPU as needed. For this project I temporarily scaled up to 8CPU/16GB of RAM, and then scaled back down after the import. This is a luxury I had, that you may not.</p>
  
  <p>With so much RAM I was able to tune the heck out of the /etc/my.cnf file. Everyone else had suggested increasing </p>
  
  <pre><code>max-allowed-packet
  bulk_insert_buffer_size
  </code></pre>
  
  <p>To double or triple default values. This didn't fix it for me. Then I tried increasing timeouts after reading more online. </p>
  
  <pre><code>interactive_timeout
  wait_timeout
  net_read_timeout
  net_write_timeout
  connect_timeout
  </code></pre>
  
  <p>I did this and it still didn't work. So then I went crazy and set everything unreasonably high. Here is what I ended up with:</p>
  
  <pre><code>key_buffer_size=512M
  table_cache=2G
  sort_buffer_size=512M
  max-allowed-packet=2G
  bulk_insert_buffer_size=2G
  innodb_flush_log_at_trx_commit = 0
  net_buffer_length=1000000
  innodb_buffer_pool_size=3G
  innodb_file_per_table
  interactive_timeout=600
  wait_timeout=600
  net_read_timeout=300
  net_write_timeout=300
  connect_timeout=300
  </code></pre>
  
  <p>Still no luck. I felt deflated. Then I noticed that the import kept failing at the same spot. So I reviewed the SQL. I noticed nothing strange. Nothing in the log files either.</p>
  
  <h1>Solution</h1>
  
  <p>There's something about the DB structure that's causing the import to fail. I suspect it's size related, but who knows.</p>
  
  <p>To fix it I started splitting the dumps up into smaller chunks. The source DB has about 75 tables. So I made 3 dumps with approx 25 each. You just have to pass the table names to the dump command. For ex:</p>
  
  <pre><code>mysqldump $DB_NAME $TABLE1&gt; $TABLE2....$TABLE25 | gzip -c | sudo ssh $USER@$IP_ADDRESS 'cat &gt; /$PATH/$DB_NAME-TABLES1-25`date +%d-%b-%Y`.gz'
  </code></pre>
  
  <p>Then I simply imported each chunk independently on the destination. Finally, no errors. Hopefully this is useful to someone else.</p>