MySQL常见的经典错误与解决办法

今天给大家列举 MySQL 数据库中，最经典的八大错误案例，并附有处理问题的解决思路和方法，希望能给刚入行，或数据库爱好者一些帮助。

忘记密码，无法登陆

报错现象

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

处理过程

在MySQL中，若密码丢失则无法直接找回，只能通过特殊方式来修改密码。
在配置文件中添加如下一行，重启 MySQL 登录则不需要密码。
skip-grant-tables

cat /etc/my.cnf

重启 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新的密码登录了！

修改简易密码报错

报错现象

alter user root@‘localhost’ identified with mysql_native_password by ‘root’;
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

处理过程

查询密码策略
mysql> SHOW VARIABLES LIKE ‘validate_password%’;

去除密码验证策略
–默认关闭，设置为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：检查长度、数字、大小写、特殊字符字典文件。

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

大小写的敏感报错

报错现象

最近接了一个项目跟团队在开发，在自己本机windows上开发和测试过程中一直没有问题，
临近项目交付的时候，部署到linux服务器上后，发现有报错，日志信息大概是：
MySQLSyntaxErrorException: Table ‘mes_db.student’ doesn’t exist

处理过程

在本机Window环境查看如下：
mysql> show variables like ‘%case%’;

在Linux服务器查看如下：

出现了问题，有点郁闷，本地开发好好的，怎么部署服务器就不行了。
有鬼…不过莫慌。看着错误提示很明显，不就是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则是区分大小写的，因此，建议在开发测试环境下就严格控制代码大小写敏感，
提高代码的兼容和严谨

导出与导入报错

报错现象

在进行数据导出的时候出现:
secure-file-priv option so it cannot execute this statement

处理过程

直需要修改参数文件即可
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 ‘"’;
逗号分割,双引号闭合
注意：导出的文件只能在服务器上

连接数过多，无法连接MySQL

报错现象

处理过程

解决问题的思路：
1、首先先要考虑在我们 MySQL 数据库参数文件里面，对应的 max_connections 这个参数值是不是设置的太小了，导致客户端连接数超过了数据库所承受的最大值。
● 该值默认大小是151，我们可以根据实际情况进行调整。
● 对应解决办法：set global max_connections=500
但这样调整会有隐患，因为我们无法确认数据库是否可以承担这么大的连接压力，就好比原来一个人只能吃一个馒头，但现在却非要让他吃 10 个，他肯定接受不了。反应到服务器上面，就有可能会出现宕机的可能。
所以这又反应出了，我们在新上线一个业务系统的时候，要做好压力测试。保证后期对数据库进行优化调整。
2、其次可以限制Innodb 的并发处理数量 ，如果 innodb_thread_concurrency = 0（这种代表不受限制） 可以先改成 16或是64 看服务器压力。如果非常大，可以先改的小一点让服务器的压力下来之后,然后再慢慢增大,根据自己的业务而定。个人建议可以先调整为 16 即可。

磁盘爆满，无法写二进制日志

报错现象

Binlog是MySQL中一个很重要的日志，记录了对数据库进行变更的操作，
但是不包括 select操作以及 show 操作，因为这类操作对数据库本身没有没有修改。
如果想记录 select和 show 的话，那就需要开启全查询日志。
另外 binlog 还包括了执行数据库更改操作时间和执行时间等信息。
binlog 是 MySQL Server 层记录的二进制日志文件，逻辑层面

清理二进制日志

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 ;

主键错误导致主从复制报错

报错现象

show slave statusG
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)’

处理过程

如果我们了解产生异常的具体事件，而且能够掌控，
可以通过设置 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。
这些方法都会导致主从数据不一致。