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:?‘e???e?…’?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 参数.

案例九