问题描述
迁移服务器,要把旧服务器上的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, 再正常导入就可以了……