【mysql】问题合集

原创已于 2024-05-10 14:43:52 修改 · 2.6k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #MySQL

于 2019-09-29 09:02:35 首次发布

数据库同时被 2 个专栏收录

14 篇文章

订阅专栏

bug

9 篇文章

订阅专栏

设置

查询不区分大小写

【mysql】查询不区分大小写（用户密码登录不区分大小写）

忘记密码、重置密码、修改密码

【mysql8.0.21】mysql数据库忘记密码、重置密码、修改密码

优化

查询优化

避免使用SELECT *，而是显式地指定所需的列
避免在查询中使用函数操作，特别是对索引字段进行函数操作会导致索引失效
在多表查询中，使用JOIN代替子查询
尽量避免使用子查询，特别是在外层查询结果集较大的时候
使用GROUP BY时，指定索引列和ORDER BY时，尽量只使用索引列，避免使用非索引列

索引策略

频繁查询的字段适合，例如手机号
频繁更新的不适合，例如状态
where和on条件的字段适合
区分度高的字段（取值不同的数量多的字段）适合
区分度低的不适合，例如性别
有序的字段适合
无序的不适合
过长的字段不适合
联合索引超过三个字段或者经常单独被查询等，则优先选择单字索引
使用联合索引，区分度高的字段放前面
.频繁操作的数据表，避免创建过多索引

bug

Operation not allowed when innodb_forced_recovery ＞ 0

MySQL在转储文件或者导入数据的过程中，出现中断、失败或者异常，造成数据无法回滚，可以通过innodb_force_recovery强力迫使InnoDB存储引擎运行，同时阻止后台操作运行，以便转储表数据。
应用程序在使用数据库时，innodb_force_recovery必须为0，否则应用启动失败。

my.cnf文件 innodb_force_recovery = 1修改为innodb_force_recovery = 0

my.cnf在安装MySQL服务器的/etc目录下
修改后重启

重启命令
方法一：
```
 service mysqld restart
```
方法二：
```
/etc/inint.d/mysqld restart
```
方法三：
切换到安装目录/usr/local/mysql/bin
1. 先停掉
```
 ./mysqladmin shutdown
```
也可用kill -9强制关掉相关进程
1. 再启动
```
./mysqld_safe &
```

this is incompatible with sql_mode=only_full_group_by

mysql5.6以上版本使用group by问题
报错：
Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'xxx.table.column' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

解决方案1（推荐）：

优化group by相关查询语句，select 只能对group by后的字段进行直接查询，其他字段需要借助函数查询，例如max,min等

解决方案2：

mysql -uroot -p
查看是否有only_full_group_by，show VARIABLES like 'sql_mode';，

若存在，在配置文件设置模式去掉only_full_group_by，vi /etc/my.cnf

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
# 设置模式
sql_mode =STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

重启数据库，systemctl restart mysqld

is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts

最大链接错误限制
报错：
"Host '39.98.131.85' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'"

方法一（max_connect_errors调高，但效果一般，总会超过）：

mysql -uroot -p
show global variables like '%max_connect_errors%';
set global max_connect_errors=1000;
show global variables like '%max_connect_errors%';
exit;

方法二（有效，但隔天后需要重新flush hosts；）：
```
mysql -uroot -p
flush hosts；
exit;
```

方法三（关闭host_cache）：

max_connect_errors = 6000
host_cache_size=0