MySQL数据库的“十宗罪”

案例一

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.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: 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 -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:

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 = 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> exit
Bye
[root@node4 bin]# ./pt-slave-restart -uroot -proot123

2017-07-20T14:31:37 p=…,u=root node4-relay-bin.000005         283 1032

案例三

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 ended

170720 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/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

案例四

数据库密码忘记的问题

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

Enter 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-file=/etc/my.cnf  –skip-grant-tables &

这样启动,就可以不用输入密码,直接进入 MySQL 数据库了.然后在修改你自己想要改的 root 密码即可.

update mysql.user set password=password(‘root123′) where user=’root’;

案例五

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 对表进行删除操作,虽然可以回收表空间,但是会涉及自增属性问题.这些坑,我们不要轻易钻进去.

案例六

阿里云 MySQL 的配置文件

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

• lower_case_table_names = 0；默认情况.
• lower_case_table_names = 1；是不是区分大小写.

如果报你小写的表名找不到,那你就把远端数据库的表名改成小写,反之亦然.注意 Mybatis 的 Mapper 文件的所有表名也要相应修改.

案例七

数据库总会出现中文乱码的情况

有同学经常会问,为什么我的数据库总会出现中文乱码的情况.一堆中文乱码不知道怎么回事?当向数据库中写入创建表,并插入中文时,会出现这种问题.此报错会涉及数据库字符集的问题.

解决思路：对于中文乱码的情况,记住老师告诉你的三个统一就可以.还要知道在目前的 MySQL 数据库中字符集编码都是默认的 UTF8.

处理办法：

• 数据终端,也就是我们连接数据库的工具设置为 utf8.
• 操作系统层面,可以通过 cat /etc/sysconfig/i18n 查看,也要设置为 utf8.
• 数据库层面,在参数文件中的 mysqld 下,加入 character-set-server=utf8.

Emoji 表情符号录入 MySQL 数据库中报错：

Caused by: java.sql.SQLException: Incorrect string value: ‘😗🅒 for column ‘CONTENT’ at row 1

at 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 的超集.

案例八

使用 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 参数.

案例九