老司机DBA不得不遇到的十大MySQL错误

前1名：Too Many Connections（连接过多，导致无法连接数据库，业务无法正常继续）

问题恢复

mysql> show variables like '%max_connection%';| Variable_name   | Value |max_connections | 151   | mysql> set global max_connections=1;Query OK, 0 rows affected (0.00 sec)[root@node4 ~]# mysql -uzs -p123456 -h 192.168.56.132ERROR 1040 (00000): Too many connections

事情解决问题：

• 1。首先我们需要考虑我们的MySQL数据库参数文件中的参数max_connections对应的值是否设置得太小，使得客户端连接数不超过数据库的最大容量。价值。 ● 该值默认大小为151，我们可以根据当前情况进行调整。 ● 相应的解决方法：设置global max_connections=500。但这样的修改会存在隐患，我们无法确认数据库是否能够承受这么大的连接压力。就好像以前只能吃一个馒头，现在却必须吃一个。 10、他当然不能接受。如果它影响服务器，可能会导致中断。所以这反映出我们在推出新的交易系统时，需要做压力测试。确保后期对数据库进行优化和修改。
• 2。其次，您可以限制并发Innodb进程的数量。如果innodb_thread_concurrency = 0（意思是没有限制），可以先改成16或者64，看看服务器压力。如果很大，可以先改小一点，减轻服务器的压力，然后根据业务慢慢增加。我个人的建议是先调整为16，MySQL性能会随着连接数的增加而下降。开发人员可以设置线程池来重用连接。 MySQL商业版中添加了线程池功能。另外，一些监控程序会读取信息方案下面的表格。可以考虑关闭以下参数

innodb_stats_on_metadata=0set 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（服务器id冲突）Master-从机创建 在复制过程中，我们需要确保两台计算机的服务器 ID 是唯一的。这里再次强调一下server-id命名规则（服务器IP地址最后一位+本MySQL服务的端口号）

解决方案：

在主从机上设置不同的服务器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;

#当前数据库二进制日志的格式为：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参数。

问题解决：

根据错误信息，我们可以获取错误日志以及位置号，然后我们就可以查出是主库运行了哪条sql，导致了主从错误。在主库中执行：

/usr/local/mysql/bin/mysqlbinlog --no-defaults -v -v --base64-output=decode-rows /data/mysql/mysql-bin.000014 |grep -A 10 1708 > 1.logcat 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/my.cnf &[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

解决方案：

当我们遇到此类错误信息时，我们要学会时刻关注错误日志的内容。我看到错误报告关键点权限被拒绝。证明当前MySQL数据库的数据目录没有权限。

解决方案：

[root@zs data]# chown mysql:mysql -R mysql[root@zs data]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf &[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/my.cnf --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/my.cnf  --skip-grant-tables &这样启动，就可以不用输入密码，直接进入 mysql 数据库了。然后在修改你自己想要改的root密码即可。update mysql.user set password=password('root123') where user='root';

Top 5：裁剪会删除数据，会导致自增ID自动删除，前端会返回未找到错误。

出现这个问题时，我们必须考虑截断和删除的区别。

查看实验教程：

#首先先创建一张表；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 进行删除全表信息，再插入新值。

原来裁剪会重置自增初始值，自增属性从1开始记录。前端使用主键ID查询时会报错此数据不可用。个人建议不要使用截断来删除表。虽然表空间可以回收，但是这会涉及到自增属性的问题。我们不应该轻易掉进这些坑。

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

lower_case_table_names = 0;默认情况lower_case_table_names = 1;是不区分大小写 . 如果报你小写的表名找不到, 那你就把远端数据库的表名改成小写 , 反之亦然 . 注意 Mybatis 的 Mapper 文件的所有表名也要相应修改

Top 7：数据库中总是出现中文乱码

解决方案：中文乱码情况。只要记住老师告诉你的三个统一就可以了。又知道当前mysql数据库中的字符集编码默认是UTF8

如何处理：

1。数据终端，我们用来连接数据库的工具，设置为utf82，操作系统级别；你可以使用cat /检查etc/sysconfig/i18n；还设置为utf83和数据库级别；在参数文件中mysqld下添加charset-server=utf8。

Emoji表情插入mysql数据库，报错。

Caused by: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x98\x97\xF0\x9F...' for column 'CONTENT' at row 1at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1074)at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4096)at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4028)at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2490)at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2651)at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2734)at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1379)

解决方案：对于表情嵌入问题，肯定还是字符集问题。 处理方法：我们可以直接添加

vim /etc/my.cnf[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 this problem.org.hibernate.event.def.AbstractFlushingEventListener - Could not synchronize database state with sessionorg.hibernate.exception.JDBCConnectionException: Could not execute JDBC batch updatecom.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Connection.close() has already been called. Invalid operation in this state.org.hibernate.util.JDBCExceptionReporter - SQL Error:0, SQLState: 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小时太长，不适合生产环境。因为连接长时间空闲，仍然占用我们的连接数，所以消耗我们的系统资源。

解决方法：

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

前10名：打不开文件（errno:24）

有时候数据库运行正常，但是突然报错打不开数据库文件被报道。

解决方案：

首先我们需要检查数据库错误日志。然后确定表是否已损坏或权限问题。也有可能是由于磁盘空间不足导致表无法正常访问；您还应该注意操作系统的限制；使用 perror 工具检查具体错误！

linux:/usr/local/mysql/bin # ./perror 24OS error code  24:  Too many open files

超出打开文件的最大数量！ ulimit -n 检查系统上打开文件的最大数量，为65535。不能超过！这肯定是因为数据库的最大打开文件数超过了限制！ ？