1. ssh隧道连接数据库报错:

80070007：SSH Tunnel：Server does not support diffie-hellman-group1-sha1 for keyexchange

①. /etc/ssh/sshd_config最下面加入下面配置:
KexAlgorithms diffie-hellman-group1-sha1,curve25519-sha256@libssh.org,ecdh-sha2-nistp256,ecdh-sha2-nistp384,ecdh-sha2-nistp521,diffie-hellman-group14-sha1
 
Ciphers 3des-cbc,blowfish-cbc,aes128-cbc,aes128-ctr,aes256-ctr

②. 执行ssh-keygen命令:
cd
ssh-keygen -A

③. 重启ssh:
service sshd restart

– 导出dbname表结构

mysqldump -uroot -p123456 -d dbname > dbname.sql


– 导出dbname表数据

mysqldump -uroot -p123456 -t dbname > dbname.sql


– 导出dbname表结构和数据

mysqldump -uroot -p123456 dbname > dbname.sql


docker run -p 3306:3306 --name mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7


duso docker run -p 3306:3306 --name mysql \

-v /usr/local/docker/mysql/conf:/etc/mysql \

-v /usr/local/docker/mysql/logs:/var/log/mysql \

-v /usr/local/docker/mysql/data:/var/lib/mysql \

-e MYSQL_ROOT_PASSWORD=123456 \

-d mysql:5.7



1. 报错:

1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'csc_risk.a.DefaultDate' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by, Time: 0.035000s

2. 原因:

①. 对于GROUP BY聚合操作:
    a. 如果在SELECT中的列,没有在GROUP BY中出现,那么这个SQL是不合法的,因为列不在GROUP BY从句中.
    b. 5.7下mysql的model默认开启了ONLY_FULL_GROUP_BY.
       (1). select version   // 查看数据库版本

②. 简而言之,就是SELECT后面接的列必须被GROUP BY后面接的列所包含,或者被聚合函数包裹.

③. 这个配置会使得GROUP BY语句环境变得十分狭窄,所以一般都不加这个配置.
    a. 一旦开启only_full_group_by,感觉group by将变成和distinct一样,只能获取受到其影响的字段信息,无法和其他未受其影响的字段共存.

④. 示例:
    select a,b from table group by a,b,c;    // 正确
    select a,b,c from table group by a,b;    // 错误,因为select的列没有在group by中出现

2. 解决方法:

(1). 会话级别,重启mysql服务后,设置又被重置:

①. 去掉ONLY_FULL_GROUP_BY,重新设置值重启.

②. 方案:
    a. 方案一:
       SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
    b. 方案二:
       SELECT @@sql_mode;
       set @@sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

(2). 修改my.conf文件:

[mysqld]
......
# 设置模式
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION