1.导入数据时 USING BTREE 错误解决办法。
原始:UNIQUE KEY ` uk_gi_barcode` (`gi_barcode`) USING BTREE ,
原因:MySQL版本差异BUG。
解决:把USING BTREE 放到索引字段前面即可。
修改:UNIQUE KEY ` uk_gi_barcode` USING BTREE (`gi_barcode`) ,
2.Access denied for user 'root'@'localhost' (using password: YES)。
原因:无权限。
解决:Navicat-工具-命令列界面。
GRANT ALL PRIVILEGES ON 数据库名.* TO root@'%' IDENTIFIED BY '密码' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON 数据库名.* TO root@'localhost' IDENTIFIED BY '密码' WITH GRANT OPTION;
#刷新权限
FLUSH PRIVILEGES;
3.修改默认密码
3.1取消取消登录密码
#vi /etc/my.cnf
在[mysqld]的段中加上一句:skip-grant-tables
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-grant-tables
位置不能乱写,保存并且退出vi,重启mysql: servive mysqld restart。
查看mysql服务状态:
service mysqld status
3.2.修改密码,免密进入mysql:
#mysql
#update mysql.user set authentication_string=password('密码') where user='root' ;
#flush privileges;
3.3.去掉skip-grant-tables,并重启。
4.MySQL5.7 group by 新特性报错。
原因:ONLY_FULL_GROUP_BY规定SELECT中的列必须在GROUP BY中。
解决:
1.查询sql_mode查看是否包含:ONLY_FULL_GROUP_BY
select @@GLOBAL.sql_mode;
临时去除:重启mysql服务之后,会恢复:
set @@global.sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
永久去除:在my.cnf中加入以下设置,然后重启服务,查询sql_mode验证:
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
5.MySQL5.7导入导出时,报错:1290 - The MySQL server is running with the --secure-file-priv option so it cannot execute this statement。
原因:MySQL默认文件安全设置。
解决:
MySQL5.7安装目录下只有my-default.ini
C:\Program Files\MySQL\MySQL Server 5.7
找到默认路径(此目录可能是隐藏的),其中包含my.ini
C:\ProgramData\MySQL\MySQL Server 5.7
在my.ini加入(尽量不要导出到C盘,可能有系统权限问题)
secure_file_priv = "E:\MySQL_bak"
重启:
net stop mysql57
net start mysql57
查看是否修改成功:SHOW VARIABLES LIKE "secure_file_priv";
导出(导出路径必须要双斜杠,否则也报1290错误):
SELECT gg_title,gg_url FROM ggt_goods
INTO OUTFILE 'E:\\MySQL_bak\\demo.csv'
CHARACTER SET gbk
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';
6.[ERR] 2006 - MySQL server has gone away
原因有多种,以下是自己遇到的。
mysql> show global variables like 'max_allowed_packet';
+--------------------+-----------+
| Variable_name | Value |
+--------------------+-----------+
| max_allowed_packet | 999999488 |
+--------------------+-----------+
1 row in set (0.01 sec)
解决:
mysql> set global max_allowed_packet=999999488;
7.[ERR] 2013 - Lost connection to MySQL server during query
mysql> show global variables like '%timeout';
+---------------------------------+----------+
| Variable_name | Value |
+---------------------------------+----------+
| connect_timeout | 60 |
| delayed_insert_timeout | 300 |
| slave_net_timeout | 60 |
| thread_pool_idle_timeout | 60 |
| wait_timeout | 31536000 |
+---------------------------------+----------+
19 rows in set (0.01 sec)
解决:
mysql> set global wait_timeout=31536000;
8.防止执行sql文件中文乱码
连接数据库时设置编码
mysql -u root -p --default-character-set=utf8
use database_name;
source db.sql;
参考:
https://blog.csdn.net/oarsman/article/details/77742799