MySQL数据库经典错误

  作者:会飞的zxl

案例一Too many connections

案例一

Too many connections

(连接数过多,导致连接不上数据库,业务无法正常进行)

问题还原:

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

解决问题的思路:

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

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

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

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

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

2、其次可以限制 InnoDB的并发处理数量,如果 innodb_thread_concurrency = 0(这种代表不受限制) 可以先改成 16 或是 64 看服务器压力。

如果非常大,可以先改的小一点让服务器的压力下来之后,然后再慢慢增大,根据自己的业务而定,个人建议可以先调整为 16 即可。

MySQL 随着连接数的增加性能是会下降的,在 MySQL 5.7 之前都需要让开发配合设置 thread pool,连接复用。MySQL 5.7 之后数据库自带 thread pool 了,连接数问题也得到了相应的解决。

另外对于有的监控程序会读取 information_schema 下面的表,可以考虑关闭下面的参数:

  • innodb_stats_on_metadata=0

  • set global innodb_stats_on_metadata=0

案例二

主从复制报错类型

Last_SQL_Errno: 1062 (从库与主库数据冲突)

Last_Errno:1062Last_Error:Couldnot execute Write_rowsevent on table test.t;Duplicate entry 4for key PRIMARY’,Error_code:1062; handler error HA_ERR_FOUND_DUPP_KEY;  the events master log mysql-bin.000014, end_log_pos 1505

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

解决方法:在确保主从数据一致性的前提下,可以在从库进行错误跳过。一般使用 percona-toolkit 中的 pt-slave-restart 进行。

在从库完成如下操作:

  • [root@zs bin]# ./pt-slave-restart -uroot -proot123

  • 2017-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:Couldnot execute Update_rowsevent on table test.t;Cant find record  int’,Error_code:1032; handler error HA_ERR_KEY_NOT_FOUND; the  events 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 = 654  COMMIT/*!*/;  DELIMITER ;# End of log file  ROLLBACK /* 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 -proot123  2017-07-20T14:31:37 p=…,u=root node4-relay-bin.0000052831032

案例三

MySQL安装过程中的报错

[root@zs data]#/usr/local/mysql/bin/mysqld_safe defaults-file=/etc/my.cnf &[1]3758[root@zs data]#17072014:41:24 mysqld_safe Logging to ‘/data/mysql/error.log’.17072014: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 ended  17072014:41:24 mysqld_safe Starting mysqld daemon with databases from/data/mysql2017-07-2014:41:250[Warning] TIMESTAMP withimplicit DEFAULT valueis deprecated.Pleaseuseexplicit_defaults_for_timestamp server option  (see documentation for more details)./usr/local/mysql/bin/mysqld:File‘/data/mysql/mysql-bin.indexnot found (Errcode:13Permission denied)2017-07-2014:41:254388[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/my.cnf &[1]4402[root@zs data]#17072014:45:56 mysqld_safe Logging to ‘/data/mysql/error.log’.17072014: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 

案例四

数据库密码忘记的问题

[root@zs ~]# mysql -uroot -pEnter password:  ERROR 1045(28000):Access denied for user root’@’localhost’(using password: YES)[root@zs ~]# mysql -uroot -p  Enter 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<