MySQL数据库“十宗罪”【十大经典错误案例】

╰+攻爆jí腚メ 2022-01-13 13:55 180阅读 0赞

[2019独角兽企业重金招聘Python工程师标准>>> ][2019_Python_] ![hot3.png][]

#### **Top 1：Too many connections（连接数过多，导致连接不上数据库，业务无法正常进行）** ####

**问题还原:**

![1b4d6eb3de04401ce9e8a51ceddc7194374.jpg][]

**解决问题的思路：**

*  1、首先先要考虑在我们 MySQL 数据库参数文件里面，对应的`max_connections 这个参数值是不是设置的太小了`，导致客户端连接数超过了数据库所承受的最大值。  
    ● 该值默认大小是151，我们可以根据实际情况进行调整。  
    ● **对应解决办法：set global max\_connections=500**  
    但这样调整会有隐患，因为我们无法确认数据库是否可以承担这么大的连接压力，就好比原来一个人只能吃一个馒头，但现在却非要让他吃 10 个，他肯定接受不了。反应到服务器上面，就有可能会出现宕机的可能。  
    所以这又反应出了，我们在新上线一个业务系统的时候，要做好压力测试。保证后期对数据库进行优化调整。
 *  2、其次可以`限制Innodb 的并发处理数量`，如果 **innodb\_thread\_concurrency = 0（这种代表不受限制）** 可以先改成 16或是64 看服务器压力。如果非常大，可以先改的小一点让服务器的压力下来之后,然后再慢慢增大,根据自己的业务而定。**个人建议可以先调整为 16** 即可。  
    **MySQL 随着连接数的增加性能是会下降的**，可以让开发配合设置 thread pool，连接复用。在MySQL商业版中加入了thread pool这项功能,另外对于有的监控程序会读取 information\_schema 下面的表，可以考虑关闭下面的参数

![573fe61aa5a45ae607c636751ed9827317f.jpg][]

#### **Top 2：（主从复制报错类型）** ####

**Last\_SQL\_Errno: 1062  （从库与主库数据冲突）**

![f96ade65d67767c2e41474c2bd28aa15765.jpg][]

**针对这个报错，我们首先要考虑是不是在`从库中误操作导致的`。结果发现，我们在从库中进行了一条针对有主键表的 sql 语句的插入，导致主库再插入相同 sql 的时候，主从状态出现异常。发生主键冲突的报错。**

**解决方法：**

在确保主从数据一致性的前提下，可以在从库进行错误跳过。一般使用 percona-toolkit 中的 pt-slave-restart 进行。  
**在从库完成如下操作**

![ba7cc50e1c0b1e4a6bee91e6ffbc4024051.jpg][]

*  之后最好在从库中开启 read\_only 参数，禁止在从库进行写入操作

**Last\_IO\_Errno: 1593（server-id冲突）**

![a7dea73a234b109ab0b74f04af93a78f6f9.jpg][]

在搭建主从复制的过程中，我们要确保两台机器的 server-id 是唯一的。这里再强调一下 server-id 的命名规则（服务器 ip 地址的最后一位+本 MySQL 服务的端口号）

**解决方法：**

在主从两台机器上设置不同的 server-id。

**Last\_SQL\_Errno: 1032（从库少数据，主库更新的时候，从库报错）**

![64f7085564894b91e2ecfc15b65b1acdbd3.jpg][]

**解决问题的办法：**

根据报错信息，我们可以获取到报错日志和position号，然后就能找到主库执行的哪条sql，导致的主从报错。  
在主库执行：

![b55f958a8288cfa3d960fdb8cb56969523a.jpg][]

获取到 sql 语句之后，就可以在从库反向执行 sql 语句。把从库缺少的 sql 语句补全，解决报错信息。  
在从库依次执行：

![b516ddb1a49a90f4d789694c6b04f3cdfb7.jpg][]

### ###

#### **Top 3：MySQL安装过程中的报错** ####

![6960f68fead49133d765af0df4ff038cd39.jpg][]

**解决思路：**

遇到这样的报错信息，我们要学会时时去关注错误日志 error log 里面的内容。看见了关键的报错点`Permission denied。证明当前 MySQL 数据库的数据目录没有权限。`

**解决方法：**

![25132e1467f07bc806c756fd1397c539fcd.jpg][]

**如何避免这类问题，个人建议在安装`MySQL初始化的时候，一定加上--user=mysql，`这样就可以避免权限问题。**

![21ab8215622720e28d4f4f483536850d07d.jpg][]

### ###

#### **Top 4：数据库密码忘记的问题** ####

![38c114f26473af86ba433a5fc90bac363af.jpg][]

**解决思路：**

目前是进入不了数据库的情况，所以我们要考虑是不是`可以跳过权限`。因为在数据库中，mysql数据库中user表记录着我们用户的信息。

**解决方法：**

启动 MySQL 数据库的过程中，可以这样执行：

![9e2fec48a37e90ce076c83dc23bf6e61e23.jpg][]

#### **Top 5：truncate 删除数据，导致自动清空自增ID，前端返回报错 not found。** ####

这个问题的出现，就要`考虑下truncate 和 delete 的区别了。`

**看下实验演练：**

![7e23e7fee90ec6c1d3bd89f8a49760d49dd.jpg][]

结果发现truncate把自增初始值重置了，自增属性从1开始记录了。当前端用主键id进行查询时，就会报没有这条数据的错误。  
个人建议不要使用truncate对表进行删除操作，虽然可以回收表空间，但是会涉及自增属性问题。这些坑，我们不要轻易钻进去。

#### **Top 6：阿里云 MySQL 的配置文件中，需要注意一个参数设置就是：** ####

![d2a742bc27e2485bcab9a6ef565500a2c55.jpg][]

### ###

#### **Top 7：数据库总会出现中文乱码的情况** ####

**解决思路：**

对于中文乱码的情况，记住老师告诉你的三个统一就可以。还要知道在目前的mysql数据库中字符集编码都是默认的UTF8

处理办法：

> 1、数据终端，也就是我们连接数据库的工具设置为 utf8  
> 2、操作系统层面；可以通过 cat /etc/sysconfig/i18n 查看；也要设置为 utf8  
> 3、数据库层面；在参数文件中的 mysqld 下，加入 character-set-server=utf8。

**Emoji 表情符号录入 mysql 数据库中报错。**

![538b81cc533059c233eb257252a762556e9.jpg][]

**解决思路：**针对表情插入的问题，一定还是字符集的问题。  
**处理方法：**我们可以直接在参数文件中，加入

![502d78b5330cc7a6b3456bbcbb554505c36.jpg][]

#### **Top 8：使用 binlog\_format=statement 这种格式，跨库操作，导致从库丢失数据，用户访问导致出现错误数据信息。** ####

![5165082c701645740e702e782f7b4f9060f.jpg][]

**Top 9：MySQL 数据库连接超时的报错 **

![6af16b4aef8f69b7c2369c2a0f7c66bd57b.jpg][]

这个问题是由`两个参数影响的，wait_timeout 和 interactive_timeout。`数据默认的配置时间是28800（8小时）意味着，超过这个时间之后，MySQL 数据库为了节省资源，就会在数据库端断开这个连接，Mysql服务器端将其断开了，但是我们的程序再次使用这个连接时没有做任何判断，所以就挂了。

**解决思路：**

先要了解这两个参数的特性；这两个参数必须同时设置，而且必须要保证值一致才可以。  
我们可以适当加大这个值，8小时太长了，不适用于生产环境。因为一个连接长时间不工作，还占用我们的连接数，会消耗我们的系统资源。

**解决方法：**

可以适当在程序中做判断；强烈建议在操作结束时更改应用程序逻辑以正确关闭连接；然后设置一个比较合理的timeout的值（根据业务情况来判断）

#### **Top 10 ：can't open file (errno:24)** ####

有的时候，数据库跑得好好的，突然报不能打开数据库文件的错误了。

**解决思路：**

首先我们要先查看数据库的error log。然后判断是表损坏，还是权限问题。还有可能磁盘空间不足导致的不能正常访问表；操作系统的限制也要关注下；用 perror 工具查看具体错误！

![6ceef46adac30f24d271a734d03bdec4786.jpg][]

超出最大打开文件数限制！ulimit -n查看系统的最大打开文件数是65535，不可能超出！那必然是数据库的最大打开文件数超出限制！  
`在MySQL里查看最大打开文件数限制命令：`

show variables like 'open\_files\_limit';

**处理方法：**

![05ee412611ceb2792bf981d3eea236eb8f0.jpg][]

原文作者：张甦

来源：http://blog.51cto.com/sumongodb

https://mp.weixin.qq.com/s/DVoBlBBwcvZ3zqhvflTWTg

转载于:https://my.oschina.net/u/3803405/blog/3047476

[2019_Python_]: https://my.oschina.net/u/2663968/blog/3061697
[hot3.png]: /images/20220113/831e444839784b5b95c3899c6998210a.png
[1b4d6eb3de04401ce9e8a51ceddc7194374.jpg]: /images/20220113/06fdd8c1c9b6427d9d660d354d5652a8.png
[573fe61aa5a45ae607c636751ed9827317f.jpg]: /images/20220113/013f3e84417a473aa51266187de6656a.png
[f96ade65d67767c2e41474c2bd28aa15765.jpg]: /images/20220113/1117020330104ecdadd4f25b5371ea69.png
[ba7cc50e1c0b1e4a6bee91e6ffbc4024051.jpg]: /images/20220113/5c6b7fb5ac754c74b83d90e6ccafdbe5.png
[a7dea73a234b109ab0b74f04af93a78f6f9.jpg]: /images/20220113/42e7f608159d4f6fa5b0612300924cc3.png
[64f7085564894b91e2ecfc15b65b1acdbd3.jpg]: /images/20220113/8506beeb6d0b486384f5c3e916ca5adb.png
[b55f958a8288cfa3d960fdb8cb56969523a.jpg]: /images/20220113/f58c4260519a4cdeb24b4c64d239c7d8.png
[b516ddb1a49a90f4d789694c6b04f3cdfb7.jpg]: /images/20220113/68993531b8994e5bbd51e235f293bb50.png
[6960f68fead49133d765af0df4ff038cd39.jpg]: /images/20220113/65a0d78d2e494613a71c3d2f81ec2641.png
[25132e1467f07bc806c756fd1397c539fcd.jpg]: /images/20220113/8bf5831d5533497ea6f9b54ff719b7bf.png
[21ab8215622720e28d4f4f483536850d07d.jpg]: /images/20220113/c3b86c0c6d8b4427a8fed1fd10648731.png
[38c114f26473af86ba433a5fc90bac363af.jpg]: /images/20220113/04138944f8104b01905a609331665d1c.png
[9e2fec48a37e90ce076c83dc23bf6e61e23.jpg]: /images/20220113/ce8ced5bf8184f7683b44e663f2c01bf.png
[7e23e7fee90ec6c1d3bd89f8a49760d49dd.jpg]: /images/20220113/57480019d37d4fa5a9c3fbb6f022913d.png
[d2a742bc27e2485bcab9a6ef565500a2c55.jpg]: /images/20220113/cde5f580b728498eaeafeed15196402c.png
[538b81cc533059c233eb257252a762556e9.jpg]: /images/20220113/73d33b5948f2446b8aae75bdde030b43.png
[502d78b5330cc7a6b3456bbcbb554505c36.jpg]: /images/20220113/8e199db6666d49d191a4c6c9dd2828ad.png
[5165082c701645740e702e782f7b4f9060f.jpg]: /images/20220113/5ec4ebb629f5487aaf1c02afd5067fe0.png
[6af16b4aef8f69b7c2369c2a0f7c66bd57b.jpg]: /images/20220113/72f111dee0af4e89a6e1ae51a3ae2e04.png
[6ceef46adac30f24d271a734d03bdec4786.jpg]: /images/20220113/2040912bd79442fe902bc0ca986ae486.png
[05ee412611ceb2792bf981d3eea236eb8f0.jpg]: /images/20220113/5c89eea9b05840cdb41760a74d6a2073.png