phpmyadmin export with error 2006

Question

We have been taking backup of our website with phpmyadmin export tool. Last night we had to recover the database from the backup and we realized that around first 40 tables were completely backed up but remaining 20 tables were reported as having 0 rows, upon investigation, we found that the phpmyadmin export feature printed #2006, MySQL has gone away for all those tables in the export dump. We could never notice this because of the huge dump that was created. This was very frustrating and annoying and we had to rely on hosting provider backup solution.

Now the question I have are, is this the default behaviour of phpmyadmin? are there any reliable solutions of exporting mysql dump which doesn't lock the tables?

Thanks for your suggestions.

Answer 1

The solution lies in editing MySQL's my.ini file.

Look in your server for my.ini file and in that file search for max_allowed_packet and increase its value. In WAMP and XAMPP the default value is 1M, change it to 10M.

In my.ini it looks something like this

# The MySQL server
[mysqld]
port        = 3306
socket      = /tmp/mysql.sock
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64

Save the ini file and restart your server.