MySQL数据库⼗⼤经典错误案例
我在刚开始学习数据库的时候，没少⾛弯路。经常会遇到各种稀奇古怪的 error 信息，遇到报错会很慌张，急需⼀个解决问题的办法。跟⽆头苍蝇⼀样，会不加思索地把错误粘到百度上，希望赶紧查⼀下有没有好的处理问题的⽅法。我想这个应该是刚从事数据库的⼩⽩，都会遇到窘境。
今天就给⼤家列举 MySQL 数据库中，最经典的⼗⼤错误案例，并附有处理问题的解决思路和⽅法，希望能给刚⼊⾏，或数据库爱好者⼀些帮助，今后再遇到任何报错，我们都可以很淡定地去处理。
学习任何⼀门技术的同时，其实就是⾃我修炼的过程。沉下⼼，尝试去拥抱数据的世界！
Top 1：
Too many connections（连接数过多，导致连接不上数据库，业务⽆法正常进⾏）
问题还原
解决问题的思路：
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 下⾯的表，可以考虑关闭下⾯的参数
innodb_stats_on_metadata=0
set global innodb_stats_on_metadata=0
Top 2：（主从复制报错类型）
Last_SQL_Errno: 1062 （从库与主库数据冲突）
Last_Errno: 1062 Last_Error: Could not execute Write_rows event on table test.t; Duplicate entry '4' for key 'PRIMARY',
Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000014, end_log_pos 1505
针对这个报错，我们⾸先要考虑是不是在从库中误操作导致的。结果发现，我们在从库中进⾏了⼀条
针对有主键表的 sql 语句的插⼊，导致主库再插⼊相同 sql 的时候，主从状态出现异常。发⽣主键冲突的报错。
解决⽅法：
在确保主从数据⼀致性的前提下，可以在从库进⾏错误跳过。⼀般使⽤ percona-toolkit 中的 pt-slave-restart 进⾏。
在从库完成如下操作
[root@zs bin]# ./pt-slave-restart -uroot -proot1232017-07-20T14:05:30 p=...,u=root node4-relay-bin.000002 1506 1062
之后最好在从库中开启 read_only 参数，禁⽌在从库进⾏写⼊操作
Last_IO_Errno: 1593（server-id冲突）
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it)
这个报错出现之后，就看⼀⽬了然看到两台机器的 server-id 是⼀样的。
在搭建主从复制的过程中，我们要确保两台机器的 server-id 是唯⼀的。这⾥再强调⼀下 server-id 的命名规则（服务器 ip 地址的最后⼀位+本 MySQL 服务的端⼝号）
解决⽅法：
在主从两台机器上设置不同的 server-id。
Last_SQL_Errno: 1032（从库少数据，主库更新的时候，从库报错）
Last_SQL_Error:Could not execute Update_rows event on table test.t; Can't find record in 't', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000014, end_log_pos 1708
解决问题的办法：
根据报错信息，我们可以获取到报错⽇志和position号，然后就能到主库执⾏的哪条sql，导致的主从报错。
在主库执⾏：
/usr/local/mysql/bin/mysqlbinlog --no-defaults -v -v --base64-output=decode-rows /data/mysql/mysql-bin.000014 |grep -A 10 1708 > 1.log
cat 1.log
#170720 14:20:15 server id 3 end_log_pos 1708 CRC32 0x97b6bdec Update_rows: table id 113 flags: STMT_END_F### UPDATE `test`.`t`### WHERE### @1=4 /* INT meta=0 nullable=0 is_null=0 */### @2='dd' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */### SET### @1=4 /* INT meta=0 nullable=0 is_null=0 */### @2='ddd' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */# at 1708#170720 14:20:15 server id 3 end_log_pos 1739 CRC32 0xecaf1922 Xid =
654COMMIT/*!*/;DELIMITER ;# End of log fileROLLBACK /* added by mysqlbinlog */;/*!50003 SET
COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
获取到 sql 语句之后，就可以在从库反向执⾏ sql 语句。把从库缺少的 sql 语句补全，解决报错信息。
在从库依次执⾏：
mysql> insert into t (b) values ('ddd');Query OK, 1 row affected (0.01 sec)mysql> stop slave;Query OK, 0 rows affected
(0.00 sec)mysql> exitBye[root@node4 bin]# ./pt-slave-restart -uroot -proot1232017-07-20T14:31:37 p=...,u=root node4-relay-bin.000005 283 1032 Top 3：MySQL安装过程中的报错 [root@zs data]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/myf &[1] 3758[root@zs data]# 170720 14:41:24 mysqld_safe Logging to '/data/mysql/error.log'.170720 14:41:24 mysqld_safe Starting mysqld daemon with databases from /data/mysql170720 14:41:25 mysqld_safe mysqld from pid file /data/mysql/node4.pid ended170720 14:41:24 mysqld_safe Starting mysqld daemon with databases from
/data/mysql2017-07-20 14:41:25 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --
explicit_defaults_for_timestamp server option (see documentation for more details)./usr/local/mysql/bin/mysqld: File
'/data/mysql/mysql-bin.index' not found (Errcode: 13 - Permission denied)2017-07-20 14:41:25 4388 [ERROR] Aborting
解决思路：
遇到这样的报错信息，我们要学会时时去关注错误⽇志 error log ⾥⾯的内容。看见了关键的报错点 Permission denied。证明当前MySQL 数据库的数据⽬录没有权限。
解决⽅法：
[root@zs data]# chown mysql:mysql -R mysql[root@zs data]# /usr/local/mysql/bin/mysqld_safe --defaults-
file=/etc/myf &[1] 4402[root@zs data]# 170720 14:45:56 mysqld_safe Logging to '/data/mysql/error.log'.170720 14:45:56 mysqld_safe Starting mysqld daemon with databases from /data/mysql
启动成功。
如何避免这类问题，个⼈建议在安装 MySQL 初始化的时候，⼀定加上--user=mysql，这样就可以避免权限问题。
./mysql_install_db --basedir=/usr/local/mysql/ --datadir=/data/mysql/ --defaults-file=/etc/myf --user=mysql
Top 4：数据库密码忘记的问题 [root@zs ~]# mysql -uroot -pEnter password: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)[root@zs ~]# mysql -uroot -pEnter password: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
我们有可能刚刚接⼿别⼈的 MySQL 数据库，⽽且没有完善的交接⽂档。root 密码可以丢失或者忘记了。
解决思路：
⽬前是进⼊不了数据库的情况，所以我们要考虑是不是可以跳过权限。因为在数据库中，mysql数据库中user表记录着我们⽤户的信息。
解决⽅法：
启动 MySQL 数据库的过程中，可以这样执⾏：
/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/myf --skip-grant-tables &
这样启动，就可以不⽤输⼊密码，直接进⼊ mysql 数据库了。然后在修改你⾃⼰想要改的root密码即可。
update mysql.user set password=password('root123') where user='root'; Top 5：truncate 删除数据，导致⾃动清空⾃增ID，前端返回报错 not found。
这个问题的出现，就要考虑下 truncate 和 delete 的区别了。
看下实验演练：
⾸先先创建⼀张表；
CREATE TABLE `t` ( `a` int(11) NOT NULL AUTO_INCREMENT, `b` varchar(20) DEFAULT NULL, PRIMARY KEY (`a`), KEY `b` (`b`)) ENGINE=InnoDB AUTO_INCREMENT=300 DEFAULT CHARSET=utf8
插⼊三条数据：
mysql> insert into t (b) values ('aa');Query OK, 1 row affected (0.00 sec)mysql> insert into t (b) values ('bb');Query OK, 1 row affected (0.00 sec)mysql> insert into t (b) values ('cc');Query OK, 1 row affected (0.00 sec)mysql> select * from t;+-----+------+| a | b |+-----+------+| 300 | aa || 301 | bb || 302 | cc |+-----+------+3 rows in set (0.00 sec)
先⽤ delete 进⾏删除全表信息，再插⼊新值。
结果发现 truncate 把⾃增初始值重置了，⾃增属性从1开始记录了。当前端⽤主键id进⾏查询时，就会报没有这条数据的错误。
个⼈建议不要使⽤ truncate 对表进⾏删除操作，虽然可以回收表空间，但是会涉及⾃增属性问题。这些坑，我们不要轻易钻进去。
Top 6：
阿⾥云 MySQL 的配置⽂件中，需要注意⼀个参数设置就是：
lower_case_table_names = 0；默认情况
lower_case_table_names = 1；是不区分⼤⼩写 . 如果报你⼩写的表名不到, 那你就把远端数据库的表名改成⼩写 , 反之亦然 . 注意Mybatis 的 Mapper ⽂件的所有表名也要相应修改
Top 7：
有同学经常会问张⽼师，为什么我的数据库总会出现中⽂乱码的情况。⼀堆不知道怎么回事。当向数据库中写⼊创建表，并插⼊中⽂时，会出现这种问题。此报错会涉及数据库字符集的问题。
解决思路：
对于中⽂乱码的情况，记住⽼师告诉你的三个统⼀就可以。还要知道在⽬前的mysql数据库中字符集编码都是默认的UTF8
处理办法：
1、数据终端，也就是我们连接数据库的⼯具设置为 utf8
2、操作系统层⾯；可以通过 cat /etc/sysconfig/i18n 查看；也要设置为 utf8
3、数据库层⾯；在参数⽂件中的 mysqld 下，加⼊ character-set-server=utf8。
Emoji 表情符号录⼊ mysql 数据库中报错。
Caused by: java.sql.SQLException: Incorrect string value: '\\xF0\\x9F\\x98\\x97\\xF0\\' for column 'CONTENT' at row sql.ateSQLException(SQLError.java:1074)at
解决思路：针对表情插⼊的问题，⼀定还是字符集的问题。
处理⽅法：我们可以直接在参数⽂件中，加⼊
vim /etc/myf[mysqld]init-connect='SET NAMES utf8mb4'character-set-server=utf8mb4注：utf8mb4 是 utf8 的超集。
Top 8：使⽤ binlog_format=statement 这种格式，跨库操作，导致从库丢失数据，⽤户访问导致出现错误数据信息。
当前数据库⼆进制⽇志的格式为：binlog_format=statement
在主库设置binlog-do-db=mydb1（只同步mydb1这⼀个库）
在主库执⾏use mydb2；
insert into mydb1.t1 values ('bb')；这条语句不会同步到从库。
但是这样操作就可以；
use mydb1;
insert into mydb1.t1 values ('bb')；因为这是在同⼀个库中完成的操作。
在⽣产环境中建议使⽤binlog的格式为row，⽽且慎⽤binlog-do-db参数。
Top 9：MySQL 数据库连接超时的报错 ；org.hibernate.util.JDBCExceptionReporter - SQL Error:0, SQLState:
08S01org.hibernate.util.JDBCExceptionReporter - The last packet successfully received from the server was43200 milliseconds ago.The last packet sent successfully to the server was 43200 milliseconds ago, which is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection
'autoReconnect=true' to avoid hibernate.event.def.AbstractFlushingEventListener - Could not synchronize database state with ption.JDBCConnectionException: Could not execute JDBC batch
08003org.hibernate.util.JDBCExceptionReporter - No operations allowed after connection closed. Connection was implicitly closed due to underlying exception/error:** BEGIN NESTED EXCEPTION **
⼤多数做 DBA 的同学，可能都会被开发⼈员告知，你们的数据库报了这个错误了。赶紧看看是哪⾥的问题。
这个问题是由两个参数影响的，wait_timeout 和 interactive_timeout。数据默认的配置时间是28800（8⼩时）意味着，超过这个时间之后，MySQL 数据库为了节省资源，就会在数据库端断开这个连接，Mysql服务器端将其断开了，但是我们的程序再次使⽤这个连接时没有做任何判断，所以就挂了。
解决思路：
先要了解这两个参数的特性；这两个参数必须同时设置，⽽且必须要保证值⼀致才可以。
我们可以适当加⼤这个值，8⼩时太长了，不适⽤于⽣产环境。因为⼀个连接长时间不⼯作，还占⽤我们的连接数，会消耗我们的系统资源。
解决⽅法：
可以适当在程序中做判断；强烈建议在操作结束时更改应⽤程序逻辑以正确关闭连接；然后设置⼀个⽐较合理的timeout的值（根据业务情况来判断）
Top 10 ：can't open file (errno:24)
有的时候，数据库跑得好好的，突然报不能打开数据库⽂件的错误了。
解决思路：
⾸先我们要先查看数据库的 error log。然后判断是表损坏，还是权限问题。还有可能磁盘空间不⾜导致的不能正常访问表；操作系统的限制也要关注下；⽤ perror ⼯具查看具体错误！
linux:/usr/local/mysql/bin # ./perror 24OS error code 24: Too many open files
超出最⼤打开⽂件数限制！ulimit -n查看系统的最⼤打开⽂件数是65535，不可能超出！那必然是数据库的最⼤打开⽂件数超出限制！
在 MySQL ⾥查看最⼤打开⽂件数限制命令：show variables like 'open_files_limit';
发现该数值过⼩，改为2048，重启 MySQL，应⽤正常
处理⽅法：
repair table ；
chown mysql权限
清理磁盘中的垃圾数据
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论