【Mysql学习】MySQL 常见错误

转载:https://www.cnblogs.com/keme/p/10972415.html

 1. Too many connections

ERROR 1040 (HY000): Too many connections

导致结果:

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

该错误发生在有max_connections个客户连接了mysqld服务器, 应该重启mysqld, 用更大的max_connections变量值

1
2
3
4
5
6
7
8
#默认连接数
mysql> show variables like '%max_connection%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.00 sec)

  

解决问题思路:

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

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

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

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

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

2. Packet too large

结果:

如果写入大数据时,因为默认的配置太小,插入和更新操作会因为 max_allowed_packet 参数限制,而导致失败。

mysql根据max_allowed_packet参数来限制server接受的数据包大小。

当一个MySQL客户或mysqld服务器得到一个max_allowed_packet个字节长的包, 它发出一个Packet too large错误并终止连接。

1
2
3
4
5
6
mysql> show variables like 'max_allowed_packet';
+--------------------+---------+
| Variable_name      | Value   |
+--------------------+---------+
| max_allowed_packet | 4194304 |
+--------------------+---------+

默认是4M大小

可以使用mysqld的命令行选项设置max_allowed_packet为一个更大的尺寸。 例如, 如果将一个全长的BLOB存入一张表中, 需要用max_allowed_packet=24M选项来启动mysql。

Max_allowed_packet的取值范围是1024B~1GB

当然不要乱设置,根据具体环境要求,设置太大业务

1
2
3
4
# 具体设置max_allowed_packet大小
mysql> set @@global.max_allowed_packet=
#在my.cnf 加入这个
max_allowed_packet= 10M

 

3. 线上要修改mysql参数,怎么避免mysql 重启

首先确定改参数是动态参数还是静态参数

如果是静态参数还是要重启服务才会生效,动态参数则不用

这时候要修改全局变量, 必须要显示指定"GLOBAL"或者"@@global.", 同时必须要有SUPER权限.

例如修改最大连接数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
#默认连接数
mysql> show variables like '%max_connection%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.00 sec)
 
#修改连接数为500
mysql> set @@global.max_connections=500;
Query OK, 0 rows affected (0.00 sec)
 
#查看是否修改成功
mysql> show variables like '%max_connection%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 500   |
+-----------------+-------+
 
# 在my.cnf 的[mysqld]下面加上 max_connections=500就可以了,也不用重启服务

  

4. root密码忘了怎么办

 

忘记了MySQL的root用户的口令 在my.cnf中添加skip-grant-tables=1选项重启mysqld

1
2
3
4
5
6
7
8
9
10
11
[root@mysql-150 ~]# mysql -u root -h 127.0.0.1
mysql> flush privileges;
mysql> grant all privileges on *.* to root@'localhost' identified by '456789';
mysql> exit
# 在my.cnf 将skip-grant-tables=1选项去掉
# 重启mysqld之后就可以用最新的密码登录
[root@mysql-150 ~]# vim /etc/my.cnf
[root@mysql-150 ~]# service mysql restart
Shutting down MySQL............ SUCCESS!
Starting MySQL. SUCCESS!
[root@mysql-150 ~]# mysql -u root -p456789 -h 127.0.0.1

  

5. 账号被锁定

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
# 创建一个用户
mysql> create user keme@'localhost' identified by '123456';
# 给一个只读权限
mysql> grant select on *.* to keme@'localhost';
 
# 可以从本地登录
[root@mysql-150 ~]# mysql -u keme -p123456
 
# 把keme@'localhost' 给lock住,不让其使用
mysql> alter user keme@'localhost' account lock;
 
# 在看看能不能从本地登录
[root@mysql-150 ~]# mysql -u keme -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 3118 (HY000): Access denied for user 'keme'@'localhost'. Account is locked.
 
# 查看该用户是否锁定
mysql> select host,user,account_locked from mysql.user where user='keme';
+-----------+------+----------------+
| host      | user | account_locked |
+-----------+------+----------------+
| localhost | keme | Y              |
+-----------+------+----------------+
Y已锁定
# 然后解锁该keme用户
mysql> alter user keme@'localhost' account unlock;
 
# 再去登录keme用户
[root@mysql-150 ~]# mysql -u keme -p123456

  

6. 环境变量未设置

例如执行: mysqldump提示: -bash: command not found 是 环境变量设置的问题

 

临时添加:

1
2
# 首先要确定mysql 的安装位置
shell> export PATH=$PATH:/usr/local/mysql/bin

  

永久设置:

1
2
3
4
# 在/etc/profile 中末尾添加
PATH=$PATH:$HOME/bin:/usr/local/mysql/bin
export PATH
保存退出后执行: source /etc/bash_profile即可。

  

7. SQL MODE

MySQL服务器可以以不同的SQL模式来操作, 并且可以为不同客户端应用不同模式。 这样每个应用程序可以根据自己的需求来定制服务器的操作模式

模式定义MySQL应支持哪些SQL语法, 以及应执行哪种数据验证检查。 这样可以更容易地在不同的环境中使用MySQL, 并结合其它数据库服务器使用MySQL。

查看当前的sql_mode

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> show variables like 'sql_mode';
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                                     |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
 
mysql> select @@sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                                                |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+

  

7.1 主要的SQL_Mode值包括:

  • ANSI

更改语法和行为, 使其更符合标准SQL。

  • STRICT_TRANS_TABLES
  • TRADITIONAL

使MySQL的行为象“传统”SQL数据库系统。 该模式的简单描述是当在列中插入不正确的值时“给 出 错误 而不是警告” 等同STRICT_TRANS_TABLES、 STRICT_ALL_TABLES、NO_ZERO_IN_DATE、 NO_ZERO_DATE、 ERROR_FOR_DIVISION_BY_ZERO、 NO_AUTO_CREATE_USER。

sql mode常用值

  •  ONLY_FULL_GROUP_BY

对于GROUP BY聚合操作,如果在SELECT中的列,没有在GROUP BY中出现,那么这个SQL是不合法的,因为列不在GROUP BY从句中

但这有个条件:如果查询是主键列或是唯一索引且非空列,分组列根据主键列或者唯一索引且空(null)则sql 分组查询有效

  • NO_AUTO_VALUE_ON_ZERO

 该值影响自增长列的插入。默认设置下,插入0或NULL代表生成下一个自增长值。如果用户希望插入的值为0,而该列又是自增长的,那么这个选项就有用了。

  • STRICT_TRANS_TABLES

在该模式下,如果一个值不能插入到一个事务表中,则中断当前的操作,对非事务表不做限制

为事务存储引擎启用严格模式, 也可能为非事务存储引擎启用严格模式。

严格模式控制MySQL如何处理非法或丢失的输入值。 有几种原因可以使一个值为非法。 例如, 数据类型错 误, 不适合列, 或超出范围。 当新插入的行不包含某列的没有显示定义DEFAULT子句的值,则该值被丢失。 对于事务表, 当启用STRICT_ALL_TABLES或STRICT_TRANS_TABLES模式时, 如果语句中有非法或丢失值, 则会出现错误。 语句被放弃并回滚。

  •  NO_ZERO_IN_DATE

在严格模式下,不允许日期和月份为零

  • NO_ZERO_DATE

设置该值,mysql数据库不允许插入零日期,插入零日期会抛出错误而不是警告。

  • ERROR_FOR_DIVISION_BY_ZERO

在INSERT或UPDATE过程中,如果数据被零除,则产生错误而非警告。如果未给出该模式,那么数据被零除时MySQL返回NULL

  • NO_AUTO_CREATE_USER

禁止GRANT创建密码为空的用户

  • NO_ENGINE_SUBSTITUTION

如果需要的存储引擎被禁用或未编译,那么抛出错误。不设置此值时,用默认的存储引擎替代,并抛出一个异常

  • PIPES_AS_CONCAT

将”||”视为字符串的连接操作符而非或运算符,这和Oracle数据库是一样的,也和字符串的拼接函数Concat相类似

举例:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
# 创建一个测试表
CREATE TABLE `employee` (
`eid` int(11) NOT NULL,
`ename` varchar(64) DEFAULT NULL,
`sex` int(11) DEFAULT NULL,
PRIMARY KEY (`eid`)
) ENGINE=InnoDB;
# 插入几条数据
insert into employee (eid,ename,sex) values (1,'keme',18),(2,'xixi',22),(3,'yj',18),(4,'kk',18),(5,'yy',18),(6,'xx',35);
 
# 设置当前会话的sql_mode为如下
mysql> set @@sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';
 
mysql> select eid,ename,count(*) from employee group by ename;
+-----+-------+----------+
| eid | ename | count(*) |
+-----+-------+----------+
|   1 | keme  |        1 |
|   4 | kk    |        1 |
|   2 | xixi  |        1 |
|   6 | xx    |        1 |
|   3 | yj    |        1 |
|   5 | yy    |        1 |
+-----+-------+----------+
 
# 重新设置当前的sql_mode 为如下
mysql> set @@sql_mode='ONLY_FULL_GROUP_BY';
mysql> select eid,ename,count(*) from employee group by ename;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'beta.employee.eid' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
mysql> select eid,ename ,count(*) from employee group by eid;
+-----+-------+----------+
| eid | ename | count(*) |
+-----+-------+----------+
|   1 | keme  |        1 |
|   2 | xixi  |        1 |
|   3 | yj    |        1 |
|   4 | kk    |        1 |
|   5 | yy    |        1 |
|   6 | xx    |        1 |
+-----+-------+----------+
6 rows in set (0.00 sec)
 
 
 
mysql> set @@sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';
mysql> insert into employee values(7,'ke','male');
ERROR 1366 (HY000): Incorrect integer value: 'male' for column 'sex' at row 1
 
mysql> set @@sql_mode='ANSI';
Query OK, 0 rows affected (0.00 sec)
 
mysql> select @@sql_mode;
+--------------------------------------------------------------------------------+
| @@sql_mode                                                                     |
+--------------------------------------------------------------------------------+
| REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,ANSI |
+--------------------------------------------------------------------------------+
 
#改成ANSI模式就可以插入成功了,只不过识别成了0
mysql> insert into employee values(7,'ke','male');
Query OK, 1 row affected, 1 warning (0.01 sec)
 
mysql> select from employee where eid=7;
+-----+-------+------+
| eid | ename | sex  |
+-----+-------+------+
|   7 | ke    |    0 |
+-----+-------+------+
1 row in set (0.00 sec)
 
 
mysql> set @@sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected, 1 warning (0.00 sec)
 
mysql> select @@sql_mode;
+--------------------------------------------+
| @@sql_mode                                 |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.00 sec)
 
mysql> insert into employee values (8,'ww',17/0);
Query OK, 1 row affected (0.00 sec)
 
mysql> select from employee  where eid=8;
+-----+-------+------+
| eid | ename | sex  |
+-----+-------+------+
|   8 | ww    | NULL |
+-----+-------+------+
 
 
mysql> set @@sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,ERROR_FOR_DIVISION_BY_ZERO';
Query OK, 0 rows affected, 1 warning (0.00 sec)
 
mysql> select @@sql_mode;
+-----------------------------------------------------------------------+
| @@sql_mode                                                            |
+-----------------------------------------------------------------------+
| STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------+
1 row in set (0.00 sec)
 
mysql> insert into employee values (9,'ee',18/0);
ERROR 1365 (22012): Division by 0
 
mysql> alter table employee modify ename varchar(5);
mysql> set @@sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';
mysql> insert into employee values (9,'qweradsf',11);
ERROR 1406 (22001): Data too long for column 'ename' at row 1
 
mysql> set @@sql_mode='ANSI';
mysql> insert into employee values (9,'qweradsf',11);
mysql> select from employee where eid=9;
+-----+-------+------+
| eid | ename | sex  |
+-----+-------+------+
|   9 | qwera |   11 |
+-----+-------+------+
 
 
mysql> set @@sql_mode='TRADITIONAL';
Query OK, 0 rows affected, 1 warning (0.00 sec)
 
mysql> select @@sql_mode;
TRADITIONAL模式有如下值:
|STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |

  

8. 用户的资源限制

MySQL提供了对每个用户的资源限制管理

MAX_QUERIES_PER_HOUR : 一个用户在一个小时内可以执行查询的次数(基本包含 所 有 语 句 )

MAX_UPDATES_PER_HOUR:一个用户在一个小时内可以执行修改的次数(仅包含修 改数据库或表的语句)

MAX_CONNECTIONS_PER_HOUR:允许用户每小时连接的次数

MAX_USER_CONNECTIONS:一个用户可以在同一时间连MySQL实例的数量

通过执行create user/alter user设置/修改用户的资源限制

1
2
3
4
5
6
7
8
9
10
11
12
# 创建一个用户并设置其资源限制
CREATE USER 'keme1'@'localhost' IDENTIFIED BY
'123456' WITH MAX_QUERIES_PER_HOUR 20
MAX_UPDATES_PER_HOUR 10
MAX_CONNECTIONS_PER_HOUR 5
MAX_USER_CONNECTIONS 2;
#keme1 这个用户 一个小时可以查询20次, 修改10次,一个小时可以连接5次,同一时刻只允许两个用户
 
#取消某项资源限制既是把原先的值修改成0
mysql> alter user 'keme1'@'localhost' WITH MAX_CONNECTIONS_PER_HOUR 0;
 
# 当针对某个用户的max_user_connections非0时, 则忽略全局系统参数max_user_connections, 反之则全局系统参数生效

  

9. 主从同步错误

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

解决方法:

在确保主从数据一致性的前提下,可以在从库进行错误跳过。

像从库如果不提供什么服务的话可以在从库中开启 read_only 参数,禁止在从库进行写入操作,还有用户必须没有super 权限,设置read_only才会生效。

9.1 一般主从复制错误的解决办法

这是正常的状态

10.0.0.150 是主

10.0.0.151 是从

先模拟故障

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
# 这是本次的表结构
mysql> show create table students;
| students | CREATE TABLE `students` (
  `sid` int(11) NOT NULL,
  `sname` varchar(20) DEFAULT NULL,
  `sex` int(11) DEFAULT NULL,
  PRIMARY KEY (`sid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
#主库执行,是个空表
mysql> select from students;
Empty set (0.00 sec)
 
#在从库 ,给students 加1条数据:
mysql> insert into students  values (1,'keme',0);
mysql> show slave status\G;
...
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes
...
看主从状态是正常的
# 从库查看students 数据
mysql> select from students;
+-----+-------+------+
| sid | sname | sex  |
+-----+-------+------+
|   1 | keme  |    0 |
+-----+-------+------+
 
 
# 在主库查看students 表
mysql> select from students;
Empty set (0.00 sec)
 
# 插入相同主键的值
mysql> insert into students values (1,'keme',1);
 
# 查看students表
mysql> select from students;
+-----+-------+------+
| sid | sname | sex  |
+-----+-------+------+
|   1 | keme  |    1 |
+-----+-------+------+
 
 
# 查看从库状态
mysql> show slave status\G;
...
Slave_IO_Running: Yes
Slave_SQL_Running: No
Last_SQL_Error: Could not execute Write_rows event on table beta.students; Duplicate entry '1' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000023, end_log_pos 11789
...
# 主从状态不一致了,造成的原因是主键冲突

  

解决办法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
# 停止从库
mysql> stop slave;
 
# 在从库删除主键冲突的那条语句, 把主库执行的那条语句在从库执行
mysql> delete from students where sid=1;
mysql> insert into students values (1,'keme',1);
 
# 同步跳过临时错误
mysql> set global sql_slave_skip_counter = 1;
mysql> start slave;
mysql> show slave status\G;
...
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...
 
# 主库再次插入数据,看看从库是不是能够同步
mysql> insert into students  values (2,'keme',1);
 
# 从库查看
mysql> select from students;
+-----+-------+------+
| sid | sname | sex  |
+-----+-------+------+
|   1 | keme  |    1 |
|   2 | keme  |    1 |
+-----+-------+------+
2 rows in set (0.00 sec)
 
# OK,同步成功了, 一般主从错误也就解决了

  

那这时候有问题,主从问题不一致了, 主上面插入了很多数据, 这时候该怎么解决了。

首先主从问题不一致了,你的监控预警机制了,给你发短信或者钉钉,这时候你应该尽快去修复从库,比如就像上面跳过临时同步错误,暂时让其恢复正常同步。

其次 后期就是用pt工具:比如用pt-table-checksum 找出主从表数据不一致的, pt-table-sync进行修复从库

 

9.2 GTID 主从复制错误解决办法

现在修改我的主从模式为GTID,这是我的测试环境随便改,

生产环境不能这样瞎改

搭建GTID主从时,需要注意的 mysql 参数:

server_id:设置 mysql 实例的 server_id,每个实例的server_id必须不一样

gtid_mode=on:MYSQL 实例开启GTID 模式。

enforce_gtid_consitency=on :使用GTID模式复制时,需要开启此参数,用来保证GTID的一致性。

log-bin=on :Msql 做主从必须开启binlog

log-slave-updates=1 :觉得slave 从master 接收到的更新且执行完之后,执行的binlog是否记录到slave的binlog中,建议开启

binlog_format=row :强烈建议binlog_format使用row格式 在mysql 5.7.6 版本以后默认就是row

skip-slave-start=1 :当slave 数据库启动的时候,slave 不会自动开启复制

 

主库操作,在[mysqld] 加一下参数,我这个做过主从, 只加一部分参数

1
2
3
4
5
# my.cnf 中内容
[mysqld]
gtid-mode=on
enforce-gtid-consistency=on
log-slave-updates=1

  

从库操作

1
2
3
4
5
6
# my.cnf 中内容
[mysqld]
gtid-mode=on
enforce-gtid-consistency=on
log-slave-updates=1
skip-slave-start=1

重启主从数据库

 

 

在从库 操作重新设置主从库的复制关系

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> CHANGE MASTER TO
MASTER_HOST = '10.0.0.150',
MASTER_PORT = 3306,
MASTER_USER = 'repl',
MASTER_PASSWORD = '123456',
MASTER_AUTO_POSITION = 1;
mysql> start slave;
 
#查看主从状态
mysql> show slave status\G;
...
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes
...

  

 

如果是在GTID模式下出现复制报错, 则使用SQL_SLAVE_SKIP_COUNTER语句会报错

在GTID 模式的复制情况下,如果slave 发生错误,则可以通过跳过该事务的方式恢复主从复制。

 

 现在人为制造slave错误

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
# 在从库的sutdents 表插入一条数据
mysql> select from students;
+-----+-------+------+
| sid | sname | sex  |
+-----+-------+------+
|   1 | keme  |    1 |
|   2 | keme  |    1 |
+-----+-------+------+
mysql> insert into students values (3,'keme',0);
 
# 在查看从库的数据
mysql> select from students;
+-----+-------+------+
| sid | sname | sex  |
+-----+-------+------+
|   1 | keme  |    1 |
|   2 | keme  |    1 |
|   3 | keme  |    0 |
+-----+-------+------+
 
# 主库也插入主键为3这条数据,引发主从同步错误
mysql> insert into students values (3,'keme',1);

  

 

 主从报错了:

 

 从图中可以看出,出错事务的binlog文件为mysql-bin.000026

开始位置(Exec_Master_Log_Pos)是154 ,结束位置是(end_log_pos ) 395,可以去主库分析下binlog ,看一下发生冲突的事务是哪个。

可以看到接收并且执行了GTID事件 是

1
2
3
4
5
6
从库执行了这些
5a13910d-1496-11e9-8375-000c29f859ce:1-3,
f6c31435-38dd-11e9-ac93-000c299bcbee:1-53096
 
收到却没执行的事务号:
Retrieved_Gtid_Set: f6c31435-38dd-11e9-ac93-000c299bcbee:53097

  

可以看出发现冲突的事务号是:f6c31435-38dd-11e9-ac93-000c299bcbee:53097,这时候就要确定哪一个事务发生了冲突,还可以直接从show slave status\G;结果中通过比对的方式找到冲突位置。

严谨起见,通过对binlog 内容分析得知冲突事务是插入了一条数据,主键为3。在从库中查看这条记录是否真的存在

1
2
3
4
5
6
mysql> select from students where sid=3;
+-----+-------+------+
| sid | sname | sex  |
+-----+-------+------+
|   3 | keme  |    0 |
+-----+-------+------+

  

发现slave 中存在这条记录了,这时,可以通过跳过该事务的方式来放弃该事务在slave上的执行,使slave 能够正常运行。

基于GTID模式的复制,跳过一个事务,需要利用一个空事务来完成。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
 
mysql> set GTID_NEXT='f6c31435-38dd-11e9-ac93-000c299bcbee:53097';
Query OK, 0 rows affected (0.00 sec)
 
mysql> begin;commit;
Query OK, 0 rows affected (0.00 sec)
 
Query OK, 0 rows affected (0.00 sec)
 
mysql> set GTID_NEXT='AUTOMATIC';
Query OK, 0 rows affected (0.00 sec)
 
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

  

查看slave 状态

 

 哪主从库数据是否一致,就看3那条

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 主库数据
mysql> select from students where sid=3;
+-----+-------+------+
| sid | sname | sex  |
+-----+-------+------+
|   3 | keme  |    1 |
+-----+-------+------+
 
#从库数据
mysql> select from students where sid=3;
+-----+-------+------+
| sid | sname | sex  |
+-----+-------+------+
|   3 | keme  |    0 |
+-----+-------+------+

  

解决不一致数据:

1 手动修改或者插入

2 用pt用具 来修复或者检查不一致数据

 

由于我这是我的本地环境,我只手动修改数据,再看主从状态

1
2
3
mysql> update students set  sex=1 where sid=3;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

 

注:不止是要主从解决错误,还要主从数据的一致性 

如果是生产的核心库主从不一致,一定要查明原因,不然老是 dba 或者运维 背锅

还有如果主从不一致性实在是太多太多不一致了,就重做数据库吧

如果检查的数据某几张表不一致的情况下,可以把这几张道出来,恢复到从库

 

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

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

10.1 解决乱码的几个方面

对于中文乱码的情况,从三个方面

  • 数据终端: 就是我们连接数据库的工具设置为utf8
  • 操作系统层面:linux 系统通过 在命令争端查看当前编码echo $LANG或者locale

如何修改了系统编码了:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# centos 6.x 版本是/etc/sysconfig/i18n
修改这个文件
shell> vim /etc/sysconfig/i18n
# 这一行改为utf8
LANG=en_US.UTF-8
# 修改完,不要重启,立即生效如下
shell> source /etc/sysconfig/i18n
 
 
# centos 7.x 版本是/etc/locale.conf 这个文件
[root@mysql-150 ~]# vim /etc/locale.conf
LANG="en_US.UTF-8"
#立即生效
[root@mysql-150 ~]# source /etc/locale.conf

  

  • 数据库层面:

在参数文件中的[mysqld] 下,加入相应utf8字符集

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
# 注意数据库的系统版本
5.6.x 和 5.7.x设置字符集参数不一样,8.x和5.7.x设置是一样的
#查看当前数据库的字符集参数,查看当前字符集参数
mysql> show variables like '%character%';
 
# 查看数据库支持的字符编码,和编码的排序规则
mysql> show character set;
 
# 修改sutdents表中sname 字段的字符编码
mysql> alter table students modify sname varchar(66) character set gbk;
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0
 
#看看表结构
mysql> show create table students;
...
| students | CREATE TABLE `students` (
  `sid` int(11) NOT NULL,
  `sname` varchar(66) CHARACTER SET gbk DEFAULT NULL,
  `sex` int(11) DEFAULT NULL,
  PRIMARY KEY (`sid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
...
 
# 查看连接级字符集和排序规则
mysql> show variables like '%collation%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_general_ci |
| collation_server     | utf8_general_ci |
+----------------------+-----------------+

  

从上面示例可以得出:

如果修改数据库字符集,需要从以下考

  • 列级别字符集

  • 表级别字符集

  • 库级别字符集

  • mysql 实例字符集

10.2 怎么合理修改mysql字符集了

在/etc/my.cnf 加一下参数

1
2
3
4
5
6
7
8
9
10
11
vim /etc/my.cnf
[mysqld]
init-connect='SET NAMES utf8'
character-set-server=utf8
然后去数据库操作:
mysql> set @@global.character_set_server=utf8;
Query OK, 0 rows affected (0.00 sec)
 
mysql> set @@global.init_connect='SET NAMES utf8';
Query OK, 0 rows affected (0.00 sec)
# 注 用户操作的时候看看有没有super权限,对super用户权限 set names 不生效

  

有人说,修改完还是乱码, 这时候就乱码是哪个库的字符集,哪个表的字符集,哪个字段的字符集,还有操作系统字符集,程序连接的字符集,这些都的查看。

在/etc/my.cnf中init-connect='SET NAMES utf8'是什么意思:

让每个客户端连接都自动设置字符集,但缺点是对拥有super权限的用户不生效

init_connect表示服务器为每个连接的客户端执行的字符串。字符串由一个或多个SQL语句组成。要想指定多个语句,用分号间隔开 。

 比如:

1
2
3
4
5
6
# 举例init_connect
mysql> SET @@GLOBAL.init_connect='SET AUTOCOMMIT=0;set names
utf8';
shell> vim my.cnf
[mysqld]
init_connect='SET AUTOCOMMIT=0;set names utf8'

10.3 连接级字符集和排序规则

  • 每个数据库客户端连接都有自己的字符集和排序规则属性,

    客户端发送的语句的字符集是由character_set_client决定,

    而与服务端交互时会根据character_set_connection和collation_connection两个参数将接收到的语句转化。当涉及到显示字符串的比较时,由collation_connection参数决定,

    而当比较的是字段里的字符串时则根据字段本身的排序规则决定

  • character_set_result参数决定了语句的执行结果以什么字符集返回给客户端

  • 客户端可以很方便的调整字符集和排序规则,比如使用SET NAMES 'charset_name' [COLLATE 'collation_name']表明后续的语句都以该字符集格式传送给服务端,而执行结果也以此字符集格式返回。

  

set names 字符集

1
2
3
4
set names charset_name 语句相当于执行了以下三行语句:
SET character_set_client = charset_name;
SET character_set_results = charset_name;
SET character_set_connection = charset_name;

  

或者执行SET CHARACTER SET 'charset_name'命令 :此命令和set names非常类似,唯一不同是将connection的字符集设置为当前数据库的字符集,所以相当于执行以下三行语句:

1
2
3
SET character_set_client = charset_name;
SET character_set_results = charset_name;
SET character_set_connection = @@character_set_database;

小结: 中文乱码从:数据终端,操作系统,数据库

数据库从: 全局数据库server字符集——>数据库字符集——> 表字符集——> 列字符集

10.4 表情乱码不能识别

修改存表情字段的字符集为utf8mb4  

11 can't opet file(errno:24)

有的时候,数据库跑得好好的,突然报不能打开数据库文件的错误了。

解决思路:

首先我们要先查看数据库的 error log。然后判断是表损坏,还是权限问题。还有可能磁盘空间不足导致的不能正常访问表,操作系统的限制也要关注下,相关应用限制也要关注下;

1
2
3
#ulimit -n 查看系统的最大打开文件数
[root@mysql-150 ~]# ulimit -n
65535

查看数据库的打开文件数

1
2
3
4
5
6
7
mysql> show variables like 'open_files_limit';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 5000  |
+------------------+-------+
# 根据业务实际情况修改打开文件数一般足够用了,低版本的打开文件数,可能有点小, 注意一下

哪就是其他的问题了,可能是表的权限,也可能是表出问题,根据错误日志,具体分析  

处理方法

  • repair table tablename

  • chown mysql.mysql 权限 目录

  • 清理磁盘中的垃圾数据

12. sleep 线程过多怎么解决

结果:严重消耗mysql服务器资源(主要是cpu, 内存),并可能导致mysql崩溃。

12.1 知道 sleep 线程过多原因

首先要知道到底是什么原因导致的 sleep 线程过多的:

  1. 程序逻辑问题,导致连接一直不释放;

  2. mysql 参数的问题,是不是参数配置的不合理,一直不释放连接;

  3. mysql 语句的问题,数据库查询不够优化,过度耗时。

  4. 大并发情况问题,导致 sleep 情况过多;

12.2 临时解决 sleep 线程

很多人都是重启大法,重启大法确实好, 能够释放,生产重启对业务有影响的,不能随便重启的

shell脚本+cron计划任务,来kill sleep 线程,这个不靠谱啊, 你不知道 sleep 线程,里面是不是还有事务还在执行没有提交,也是sleep 状态,这个kill 操作有点莽夫,对生产数据数据库还是要理智啊。

我临时解决的办法:

  1. 对用户资源做限制,看看那个用户连接的sleep线程比较多,对这个用户连接多的做一些限制,比如一个小时可以连接多少次啊等等

  2. 修改 mysql 参数问题 ,修改wait_timeout 和interactive_timeout默认都是28800秒有,也就是8个小时以后才释放空链接。

例子:

 

 

不同用户登录到数据库,wait_timeout和interactive_timeout都是28800秒

修改参数,我生产环境设置的是半个小时,也就是1800秒

1
2
3
4
5
6
7
mysql> set global wait_timeout=1800;
mysql> set global interactive_timeout=1800;
 
shell> vim my.cnf
[mysqld]
wait_timeout=1800
interactive_timeout=1800

  

这样修改完,由于已近保持的会话连接需要等到8个小时才会释放, 所以修改了wait_timeout和interactive_timeout不会立即生效的原因,这时候就要修改连接过多的用户资源了来释放sleep线程了

如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
#开启两个会话窗口
mysql> # 重新登录了会话,wait_timeout和interactive会生效
mysql> show variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 1800  |
+---------------+-------+
1 row in set (0.00 sec)
 
mysql> show variables like 'interactive_timeout';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| interactive_timeout | 1800  |
+---------------------+-------+
1 row in set (0.00 sec)
 
#session2
mysql> #这个是其他用户连接的mysql,这个会话一直没有断开,参数还是28800
mysql> show variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+
1 row in set (0.01 sec)
 
mysql> show variables like 'interactive_timeout';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| interactive_timeout | 28800 |
+---------------------+-------+
1 row in set (0.00 sec)

 

13.3 怎么根本解决了

这时候就要知道 造成 sleep 线程过多的原因来解决:

  1. 程序执行完毕,应该显式调用mysql_close

  2. 程序中根据业务访问情况,选择长连接还是短连接

  3. 能逐步分析系统的SQL查询,找到查询过慢的SQL优化

  4. 合理设置mysql参数值

posted @ 2020-06-13 15:16  gtea  阅读(398)  评论(0编辑  收藏  举报