1. ssh隧道连接数据库报错:
80070007：SSH Tunnel：Server does not support diffie-hellman-group1-sha1 for keyexchange
①. /etc/ssh/sshd_config最下面加入下面配置:
KexAlgorithms diffie-hellman-group1-sha1,[email protected],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.035000s2. 原因:
①. 对于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