ERROR 2006 (HY000): MySQL server has gone away

当你要导入一个比较大的mysql数据库的时候,可能会产生这个错误:

mysql> source file.sql
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 2
Current database: *** NONE ***

ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 3
Current database: *** NONE ***

这是因为max_allowed_packet设置的太小了,再/etc/my.cnf中,将这个参数设置为64M或者根据实际情况设为更大的值就可以了

max_allowed_packet=64M

mysql文档:

https://dev.mysql.com/doc/refman/5.5/en/replication-features-max-allowed-packet.html

 

Mysql 快速备份和恢复

shell> mysqldump db1 > dump.sql
shell> mysqladmin create db2
shell> mysql db2 < dump.sql

Do not use –databases on the mysqldump command line because that causes USE db1 to be included in the dump file, which overrides the effect of naming db2 on the mysql command line.

有的时候root的密码不为空,这个时候我们就需要:

shell> mysqldump -uroot -p db1 > dump.sql

这个我们就可以把db1的表给倒出来了

MySql 的 ONLY_FULL_GROUP_BY

最近把mysql 升级到了5.7.18 然后就发现了一个很明显的变化, ONLY_FULL_GROUP_BY 这个mysql mode 被加了进来, 按照sql 99 的标准这是好事.

但是实际上, mysql 的这个版本只能说almost identical or very close to, 不能说完全相等

简单的说,就是

That is, MySQL 5.7.5m15 will by default reject only those GROUP 
BY-queries that include non-aggregated expressions in the SELECT-list that 
are not functionally dependent upon the GROUP BY-list.

This not only means that you cannot mess up your GROUP BY-queries anymore 
(as MySQL will now reject an improper GROUP BY query), 
it will also not require you to write non-sensical "dummy" aggregates
 over expressions that can only have one value per aggregated 
result row. Hurrah!

详细的解释请看这篇文章:

http://rpbouman.blogspot.nl/2014/09/mysql-575-group-by-respects-functional.html