為什麼總是提示 “ Got a packet bigger than 'max_allowed_packet' bytes” 呢？

問題描述

遷移伺服器，要把舊伺服器上的mysql資料複製到新伺服器的mysql上。

我先是把舊伺服器的資料庫用mysqldump複製了一份，然後想匯入到新伺服器，用命令匯入，結果提示

            ERROR 1153 (08S01): Got a packet bigger than 'max_allowed_packet' bytes
            ERROR 2006 (HY000): MySQL server has gone away
            No connection. Trying to reconnect...
            Connection id:    54350

            ERROR 1231 (42000): Variable 'time_zone' can't be set to the value of 'NULL'
            ERROR 1231 (42000): Variable 'sql_mode' can't be set to the value of 'NULL'
            ERROR 1231 (42000): Variable 'foreign_key_checks' can't be set to the value of 'NULL'
            ERROR 1231 (42000): Variable 'unique_checks' can't be set to the value of 'NULL'
            ERROR 1231 (42000): Variable 'character_set_client' can't be set to the value of 'NULL'
            Query OK, 0 rows affected (0.00 sec)

            ERROR 1231 (42000): Variable 'collation_connection' can't be set to the value of 'NULL'
            ERROR 1231 (42000): Variable 'sql_notes' can't be set to the value of 'NULL'

根據網上說的方案，我在命令列加上--max_allowed_packet=128M

   mysql --max_allowed_packet=128M -u user -ppass database < database.sql 

結果還是提示 Got a packet bigger than 'max_allowed_packet' bytes

然後我又嘗試用MysqlWorkBench客戶端匯入，還是提示 Got a packet bigger than 'max_allowed_packet' bytes 。

實在太奇怪了，明明這個.sql檔案只有82K啊！

解決方案（感謝@blacktulip提供）

mysql -u root -p -e "set global net_buffer_length=1000000; set global max_allowed_packet=1000000000;"

然後重啟 mysqld， 再正常匯入就可以了……