庖丁解牛,这八个MySQL经典错误,你遇到几个?

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
日志服务 SLS,月写入数据量 50GB 1个月
简介: 庖丁解牛,这八个MySQL经典错误,你遇到几个?

🚀 1.忘记密码,无法登陆


🌈 1.1 报错现象


ERROR 1130 (HY000): Host ‘172.18.1.1’ is not allowed to connect to this MySQL server --提示无法登陆


d96fa511394b4cb3b8e62067b3430320.png


🌈 1.2 处理过程


在MySQL中,若密码丢失则无法直接找回,只能通过特殊方式来修改密码。

在配置文件中添加如下一行,重启 MySQL 登录则不需要密码。

skip-grant-tables


cat /etc/my.cnf

a3756a944e4e41b78268b697579af8ca.png


重启 MySQL

systemctl start mysqld

[root@binlog2sql ~]# mysql -uroot -p

–此时空密码可以进入

✨✨ MySQL8修改密码

mysql> alter user root@‘localhost’ identified with mysql_native_password by ‘root1’;

✨✨ MySQL8以下版本修改密码

mysql> update mysql.user set authentication_string=password(‘root’) where user=‘root’;

mysql> flush privileges;


最好把/etc/my.cnf中的skip-grant-tables注释掉,然后重启mysql,即:service mysqld restart

好了,下面就可以用r新的密码登录了!


🚀 2.修改简易密码报错


🌈 2.1 报错现象


alter user root@‘localhost’ identified with mysql_native_password by ‘root’;

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements


🌈 2.1 处理过程


查询密码策略

mysql> SHOW VARIABLES LIKE ‘validate_password%’;


152fed975aba462d9194a074b375f40d.png


去除密码验证策略

–默认关闭,设置为ON时可以将密码设置成当前用户名

mysql> set global validate_password.check_user_name=OFF;

–密码强度检查等级,0/LOW、1/MEDIUM、2/STRONG。默认是1,即MEDIUM,

mysql> set global validate_password_policy=0;

mysql> set global validate_password.length=4;

–特殊字符

mysql> set global validate_password.mixed_case_count=0;

mysql> set global validate_password.number_count=0;

mysql> flush privileges;

–密码验证策略

0/LOW:只检查长度。

1/MEDIUM:检查长度、数字、大小写、特殊字符

2/STRONG:检查长度、数字、大小写、特殊字符字典文件。

1af161d77afc483495c6aada834ef30c.png


此时修改为密码root则OK,因为已经去除密码策略


🚀 3.大小写的敏感报错


🌈 2.1 报错现象


最近接了一个项目跟团队在开发,在自己本机Windows上开发和测试过程中一直没有问题,

临近项目交付的时候,部署到Linux服务器上后,发现有报错,日志信息大概是:

MySQLSyntaxErrorException: Table ‘mes_db.student’ doesn’t exist


🌈 2.2 处理过程


🚩在本机Window环境查看如下:

mysql> show variables like ‘%case%’;


5f141d2d6f2d4212af41f48b81d910db.png

🚩在Linux服务器查看如下:

6bbed4e3a03d4a98ba3b7950bf2f57de.png


出现了问题,有点郁闷,本地开发好好的,怎么部署服务器就不行了。

有鬼…不过莫慌。看着错误提示很明显,不就是student表不存在吗!

①于是我不慌不忙打开navicat,查看这个表在不在,一看还真在,

数据库中显示的student,不过s是小写;

②查看代码发现代码中还真把表名写成Student,就一个s写成大写S了。

问题找到了,原来是不小心写SQL的时候没有写对表名,改一下表名就搞定了,功能也一切正常了


从上面的结果已经可以看出不同了

当 lower_case_table_names 为 0 时表示区分大小写,为 1 时表示不区分大小写

在Windows上,默认值为1;在macOS上,默认值为2;在Linux上不支持值2;服务器强制该值为0

0 --大小写敏感。(Unix,Linux默认)

1–大小写不敏感。(Windows默认)

2 --大小写不敏感(macOS默认)

并且官网也提示说:如果在数据目录驻留在不区分大小写的文件系统

(例如Windows或macOS)上的系统上运行MySQL,

则不应将lower_case_table_names设置为0。

我自己在我的window10环境尝试设置lower_case_table_names为0的时候,

MySQL的服务怎么也启动不能,启动服务报错,因为windows系统对大小写不敏感


而Linux则是区分大小写的,因此,建议在开发测试环境下就严格控制代码大小写敏感,

提高代码的兼容和严谨


🚀 4.MySQL无法启动


🌈 4.1 报错现象


Windows 无法启动Mysql服务 错误1053:服务没有及时响应启动或控制请求


98d8b548cea24f15bf1056d4d5948d70.png


🌈 4.2 处理过程


🚩 结束进程

1、在命令行中敲入tasklist查看进程

2、根据进程名杀死进程

taskkill /f /t /im 进程名称

66c202f262214c78986356c4735e5444.png

4020bf0a2f53482388859cb9ce7fe8b6.png


1)、计算机->管理->本地用户和组->组 双击,效果图如下:


e4409ef4fdea499087f2bf97da4f2620.png

(2)、双击Administrators,并点击添加,再点击高级

(3)、把 NETWORK SERVICE添加到Administrators组

58525bb59c5b40f2ba7d033420d962d4.png


此处记住,重新安装Mysql就可以,2步操作重新执行(删除原有Mysql文件,全部重新安装即可)

还有个坑就是,如果还是启动不了, 记得要开启这个服务Windows Installer

Windows Installer是一种通用的软件发布方式,用于安装软件。

默认情况下,该服务是手动启动,需要进入服务管理中开启。

右击开始——运行——输入“services.msc”——Windows Installer——启动


08db3d0e2a04407991b92d812e7ab61f.png


🚀 5.导出与导入报错


🌈 5.1 报错现象


在进行数据导出的时候出现:

secure-file-priv option so it cannot execute this statement


🌈 5.2 处理过程


直需要修改参数文件即可

echo “secure-file-priv=” >> /etc/my.cnf

secure-file-priv

参数是用来限制 LOAD DATA, SELECT … OUTFILE, and LOAD_FILE()传到哪个指定目录的。

a.seure_file_priv 的值为 null ,表示限制 mysqld 不允许导入|导出

b.当 secure_file_priv 的值为/tmp/ ,表示限制 mysqld 的导入|导出只能发生在/tmp/目录下

c.当 secure_file_priv 的值没有具体值时,表示不对 mysqld 的导入|导出做限制

修改参数只能在服务器参数文件中修改,修改后重新服务器:

secure-file-priv=


🚩 导出命令

select host,user from mysql.user into outfile ‘/tmp/user.csv’

Fields terminated by ‘,’ enclosed by ‘"’;

逗号分割,双引号闭合

注意:导出的文件只能在服务器上


🚀 6.连接数过多,无法连接MySQL


🌈 6.1 报错现象


4f59792d3e9f44daaab1ee0bd423ed53.png


🌈 6.2 处理过程


解决问题的思路:

1、首先先要考虑在我们 MySQL 数据库参数文件里面,对应的 max_connections 这个参数值是不是设置的太小了,导致客户端连接数超过了数据库所承受的最大值。

● 该值默认大小是151,我们可以根据实际情况进行调整。

● 对应解决办法:set global max_connections=500

但这样调整会有隐患,因为我们无法确认数据库是否可以承担这么大的连接压力,就好比原来一个人只能吃一个馒头,但现在却非要让他吃 10 个,他肯定接受不了。反应到服务器上面,就有可能会出现宕机的可能。

所以这又反应出了,我们在新上线一个业务系统的时候,要做好压力测试。保证后期对数据库进行优化调整。

2、其次可以限制Innodb 的并发处理数量 ,如果 innodb_thread_concurrency = 0(这种代表不受限制) 可以先改成 16或是64 看服务器压力。如果非常大,可以先改的小一点让服务器的压力下来之后,然后再慢慢增大,根据自己的业务而定。个人建议可以先调整为 16 即可。


🚀 7.磁盘爆满,无法写二进制日志


🌈 7.1 报错现象


Binlog是MySQL中一个很重要的日志,记录了对数据库进行变更的操作,

但是不包括 select操作以及 show 操作,因为这类操作对数据库本身没有没有修改。

如果想记录 select和 show 的话,那就需要开启全查询日志。

另外 binlog 还包括了执行数据库更改操作时间和执行时间等信息。

binlog 是 MySQL Server 层记录的二进制日志文件,逻辑层面


18ef2cda2b3a4cb69f0992c5d99a750a.png


🌈 7.2 清理二进制日志


mysql> show variables like ‘%binlog_expire_logs_seconds%’ ;

mysql 8开始 expire_logs_days废弃

启用binlog_expire_logs_seconds设置binlog自动清除日志时间

保存时间 以秒为单位;默认2592000 30天

14400 4小时;86400 1天;259200 3天

##自动删除

mysql> set global binlog_expire_logs_seconds=86400;

mysql> set global binlog_expire_logs_seconds=2592000;

##手动删除

默认日志文件达到 1G 都会重新生成一个新的二进制日志文件

mysql> select @@max_binlog_size;

#binlog.000025 之前的日志都会被删除

mysql> PURGE BINARY LOGS TO ‘binlog.000025’;

#时间’2020-04-28 23:59:59’之前的日志都会被删除

mysql> PURGE BINARY LOGS BEFORE ‘2020-04-28 23:59:59’;

#清空历史二进制日志,从 000001 开始重新

mysql> RESET MASTER;

mysql> select @@binlog_format ;


🚀 8.主键错误导致主从复制报错


🌈 8.1 报错现象

cf8a1ad842e845d8ae0a5620d2d95b1c.png

show slave status\G

Last_SQL_Errno: 1062

Last_SQL_Error: Error ‘Duplicate entry ‘1’ for key ‘PRIMARY’’ on query. Default database:‘test’. Query: ‘insert into test values(1,2,3,4,5,6)’


🌈 8.2 处理过程


如果我们了解产生异常的具体事件,而且能够掌控,

可以通过设置 sql_slave_skip_counter 参数来跳过当前错误。

setglobalsql_slave_skip_counter=1; --如果是10,就是跳过接下来的10个错误

set global sql_slave_skip_counter=1;

start slave sql_thread;

或者使用 slave_skip_errors 参数(read only variable),指定跳过某种类型的错误:

参数文件中设置:

slave_skip_errors=1062 #跳过 1062 错误

遇到错误时,不要一通百度后,然后根据看起来很类似的操作直接来进行操作。

因为网上大部分解决 sql_thread 异常的方法是:

a、 直接 set global sql_slave_skip_counter=n;(n 设置很大的值,即:跳过所有错误),

b、 设置 slave_skip_errors=all;跳过所有类型的错误

c、 直接查看主库的 binlog,然后在从库上直接执行 change master to。

这些方法都会导致主从数据不一致。


相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
7月前
|
关系型数据库 MySQL 数据库
MySQL的10大经典错误详解
MySQL的10大经典错误详解
76 0
|
SQL 关系型数据库 MySQL
庖丁解牛,MySQL执行计划Explain的2大核心
庖丁解牛,MySQL执行计划Explain的2大核心
125 0
庖丁解牛,MySQL执行计划Explain的2大核心
|
SQL 关系型数据库 MySQL
庖丁解牛|图解 MySQL 8.0 优化器查询转换篇
本篇介绍子查询、分析表和JOIN的复杂转换过程
571 0
庖丁解牛|图解 MySQL 8.0 优化器查询转换篇
|
SQL Cloud Native 算法
庖丁解牛-图解MySQL 8.0优化器查询解析篇
本文重点介绍了优化器的基于规则的其中一部分优化,更多的偏重于SQL中的基本操作符
3701 0
庖丁解牛-图解MySQL 8.0优化器查询解析篇
|
SQL Cloud Native 关系型数据库
庖丁解牛-图解MySQL 8.0优化器查询解析篇
SQL优化器本质上是一种高度抽象化的数据接口的实现,经过该设计,客户可以使用更通用且易于理解的SQL语言,对数据进行操作和处理,而不需要关注和抽象自己的数据接口,极大地解放了客户的应用程序。
庖丁解牛-图解MySQL 8.0优化器查询解析篇
|
算法 Java 关系型数据库
11月16日云栖精选夜读 | 这 10 个 MySQL 经典错误,遇到过才是老司机!
今天就给大家列举 MySQL 数据库中,最经典的十大错误案例,并附有处理问题的解决思路和方法,希望能给刚入行,或数据库爱好者一些帮助。 今后再遇到任何报错,我们都可以很淡定地去处理。学习任何一门技术的同时,其实就是自我修炼的过程。
9059 0
|
SQL 关系型数据库 Java
这十个MySQL经典错误,老司机一定遇到过!你呢?
Top  1:Too many connections(连接数过多,导致连接不上数据库,业务无法正常进行) 问题还原 解决问题的思路: 1、首先先要考虑在我们 MySQL 数据库参数文件里面,对应的max_connections 这个参数值是不是设置的太小了,导致客户端连接数超过了数据库所承受的最大值。
1433 0