Mysql问题汇总
LiuSw Lv6
 2022-12-20 15:02:38      1.9k 字  9 分钟  

mysql问题汇总

1.Navicat连接mysql报1129错误

错误:

1
Host is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'

原因:

1
同一个ip在短时间内产生太多（超过MySQL数据库max_connection_errors的最大值）中断的数据库连接而导致的阻塞；

解决方案:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
1、提高允许的max_connection_errors数量（治标不治本）：

　　① 进入Mysql数据库查看max_connection_errors： show variables like '%max_connection_errors%';

　  ② 修改max_connection_errors的数量为1000： set global max_connect_errors = 1000;

　　③ 查看是否修改成功：show variables like '%max_connection_errors%';

2、使用mysqladmin flush-hosts 命令清理一下hosts文件（不知道mysqladmin在哪个目录下可以使用命令查找：whereis mysqladmin）；

　　① 在查找到的目录下使用命令修改：/usr/bin/mysqladmin flush-hosts -h192.168.1.1 -P3308 -uroot -prootpwd;

　　备注：1.其中端口号，用户名，密码都可以根据需要来添加和修改；

　　　　  2.配置有master/slave主从数据库的要把主库和从库都修改一遍的（我就吃了这个亏明明很容易的几条命令结果折腾了大半天）；

　　　　  3.第二步也可以在数据库中进行，命令如下：flush hosts;

终极解决方案:

大多数的百度结果是上面这样的,但对于某些特定的机子不行,经多次验证,发现需要加上主机地址，即执行以下命令:

1
mysqladmin flush-hosts -h 127.0.0.1 -uroot -p123456

备注：‘-h’,‘-u’和‘-p’后面跟的是访问的主机ip地址、用户名和密码，中间不需要空格

2.mysql8.0登录提示caching_sha2_password

错误:

1
2
3
Authentication plugin 'caching_sha2_password' cannot be loaded: dlopen(/usr/local/mysql/lib/plugin/caching_sha2_password.so, 2): image not found
mysqli_real_connect(): The server requested authentication method unknown to the client [caching_sha2_password]
mysqli_real_connect(): (HY000/2054): The server requested authentication method unknown to the client

原因:

1
在mysql8之前的版本使用的密码加密规则是mysql_native_password，但是在mysql8则是caching_sha2_password

解决方案1:

1
2
3
配置 mysql.cnf 配置默认身份验证插件
[mysqld]
default_authentication_plugin = mysql_native_password

解决方案2:

查看身份验证类型

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> use mysql;
Database changed

mysql> SELECT Host, User, plugin from user;
+-----------+------------------+-----------------------+
| Host      | User             | plugin                |
+-----------+------------------+-----------------------+
| %         | root             | caching_sha2_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session    | caching_sha2_password |
| localhost | mysql.sys        | caching_sha2_password |
| localhost | root             | caching_sha2_password |
+-----------+------------------+-----------------------+
5 rows in set (0.00 sec)

root 用户的验证器插件为 caching_sha2_password

创建一个新用户，并指定加密规则为mysql_native_password

1
CREATE USER  'your username'@'%' IDENTIFIED WITH mysql_native_password BY 'your password;

或者修改身份验证类型(修改密码)

1
2
3
4
5
6
7
mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
Query OK, 0 rows affected (0.01 sec)

mysql> FLUSH PRIVILEGES;

验证是否生效

1
2
3
4
5
6
7
8
9
10
11
mysql> SELECT Host, User, plugin from user;
+-----------+------------------+-----------------------+
| Host      | User             | plugin                |
+-----------+------------------+-----------------------+
| %         | root             | mysql_native_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session    | caching_sha2_password |
| localhost | mysql.sys        | caching_sha2_password |
| localhost | root             | mysql_native_password |
+-----------+------------------+-----------------------+
5 rows in set (0.00 sec)

3.mysql提示：Statement violates GTID consistency

错误:

1
2
错误代码： 1786
Statement violates GTID consistency: CREATE TABLE ... SELECT.

解决方案:

1
2
3
4
5
6
7
8
9
10
# vi /etc/my.cnf

gtid_mode = ON
enforce_gtid_consistency = ON

# 改为
gtid_mode = OFF
enforce_gtid_consistency = OFF

# 要两个参数一起改,然后重启mysql

4.mysql提示:Can’t find error-message file’/usr/share/mysql/errmsg.sys

错误:

1
[ERROR] Can't find error-message file '/usr/share/mysql/errmsg.sys'. Check error-message file location and 'lc-messages-dir' configuration directive.

解决方案1:

1
2
3
vi /etc/my.cnf
# 添加以下参数
basedir=/data/mysql
1
2
3
4
5
6
7
8
9
10
11
12
find / -name errmsg.sys
# 查找errmsg.sys文件位置
# /var/lib/mysql5.7/share/bulgarian/errmsg.sys
# /var/lib/mysql5.7/share/czech/errmsg.sys
# /var/lib/mysql5.7/share/danish/errmsg.sys
# /var/lib/mysql5.7/share/dutch/errmsg.sys
# /var/lib/mysql5.7/share/english/errmsg.sys
# /var/lib/mysql5.7/share/estonian/errmsg.sys
# ******
# 将errmsg.sys的english版本文件拷到mysql相关路径下，路径不存在需要创建
mkdir -p /data/mysql/share/mysql/
cp /var/lib/mysql5.7/share/english/errmsg.sys /data/mysql/share/mysql/errmsg.sys

解决方案2:

1
2
3
4
5
6
7
8
9
10
11
12
find / -name errmsg.sys
# 查找errmsg.sys文件位置
# /var/lib/mysql5.7/share/bulgarian/errmsg.sys
# /var/lib/mysql5.7/share/czech/errmsg.sys
# /var/lib/mysql5.7/share/danish/errmsg.sys
# /var/lib/mysql5.7/share/dutch/errmsg.sys
# /var/lib/mysql5.7/share/english/errmsg.sys
# /var/lib/mysql5.7/share/estonian/errmsg.sys
# ******
# 将errmsg.sys的english版本文件拷到mysql相关路径下，路径不存在需要创建
mkdir -p /data/mysql/share/mysql/
cp /var/lib/mysql5.7/share/english/errmsg.sys /data/mysql/share/mysql/errmsg.sys
1
2
3
4
vi /etc/my.cnf

# 添加以下参数，同时去掉basedir=/data/mysql参数
lc-messages-dir=/data/mysql/share/mysql/errmsg.sys

5.mysql8.0+版本提示Account is locked（用户或表格上锁）

原因:

1
操作Mysql数据库的时候难免会遇到锁住用户登录不进去的情况发生（一般是密码输错很多次，我是学习的时候手动修改了）。实质是account_locked栏位变为Y了，导致后台文件卡控无法登录。

解决方案1:

1
2
# 查询是否上锁，登录不了直接解锁
select host,user,account_locked from mysql.user 
1
2
3
4
# 跳过权限管控，启动mysql
mysqld --console --skip-grant-tables --shared-memory

# 或者在my.cnf中在[mysqld]下添加skip-grant-tables

再次连接一个窗口进行mysql连接

1
2
3
4
5
6
7
8
9
10
11
# 解除语句
ALTER USER ‘root’@‘localhost’ ACCOUNT UNLOCK;

# 双重解除
update mysql.user set account_locked=‘N’ where user=‘root’;

# 提交，很重要的步骤
commit;

# 最终查询下是否解除成功
select user,host,account_locked from mysql.user;

最后重启服务

1
2
# 关掉之前mysqld --console --skip-grant-tables --shared-memory启动的服务，重新正常启动。
# 或者去掉my.cnf中在[mysqld]下的skip-grant-tables重启mysqld服务。

6.mysql提示:Error starting thread: Resource temporarily unavailable

错误:

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

121031 18:53:17  InnoDB: Unable to open the first data file
InnoDB: Error in opening ./ibdata1
121031 18:53:17  InnoDB: Operating system error number 11 in a file operation.
InnoDB: Error number 11 means 'Resource temporarily unavailable'.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/operating-system-error-codes.html
121031 18:53:17 InnoDB: Could not open or create data files.
121031 18:53:17 InnoDB: If you tried to add new data files, and it failed here,
121031 18:53:17 InnoDB: you should now edit innodb_data_file_path in my.cnf back
121031 18:53:17 InnoDB: to what it was, and remove the new ibdata files InnoDB created 
121031 18:53:17 InnoDB: in this failed attempt. InnoDB only wrote those files full of 
121031 18:53:17 InnoDB: zeros, but did not yet use them in any way. But be careful: do not
121031 18:53:17 InnoDB: remove old data files which contain your precious data!
121031 18:53:17 [ERROR] Plugin 'InnoDB' init function returned error.
121031 18:53:17 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 
121031 18:53:17 [ERROR] Unknown/unsupported storage engine: INNODB
121031 18:53:17 [ERROR] Aborting
 
121031 18:53:17 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete
 
121031 18:53:17 mysqld_safe mysqld from pid file /data/mysql/mysql_3301/mysql_3301.pid ended
121031 18:54:29 mysqld_safe Starting mysqld daemon with databases from /data/mysql/mysql_3301
121031 18:54:29 [Warning] The syntax '--log-slow-queries' is deprecated and will be removed in a future release. Please use '--slow-
query-log'/'--slow-query-log-file' instead.
121031 18:54:29 InnoDB: The InnoDB memory heap is disabled
121031 18:54:29 InnoDB: Mutexes and rw_locks use GCC atomic builtins
121031 18:54:29 InnoDB: Compressed tables use zlib 1.2.3
121031 18:54:30 InnoDB: Initializing buffer pool, size = 2.9G
121031 18:54:30 InnoDB: Completed initialization of buffer pool
InnoDB: Unable to lock ./ibdata1, error: 11
InnoDB: Check that you do not already have another mysqld process
InnoDB: using the same InnoDB data or log files.
121031 18:54:30  InnoDB: Retrying to lock the first data file
InnoDB: Unable to lock ./ibdata1, error: 11
InnoDB: Check that you do not already have another mysqld process
InnoDB: using the same InnoDB data or log files.
121031 18:54:30  InnoDB: Retrying to lock the first data file
InnoDB: Unable to lock ./ibdata1, error: 11
InnoDB: Check that you do not already have another mysqld process
InnoDB: using the same InnoDB data or log files.
InnoDB: Unable to lock ./ibdata1, error: 11
InnoDB: Check that you do not already have another mysqld process
InnoDB: using the same InnoDB data or log files.
InnoDB: Unable to lock ./ibdata1, error: 11

解决方案

发现是”max user processes “参数有问题，通过root用户调整大小至 12000,线程数也随着增大。

1
2
3
4
5
6
7
vi /etc/security/limits.conf

* soft nproc 12000
* hard nproc 12000

# 重新加载系统参数
sysctl -p

nproc就是”max user processes”，完整描述是: nproc - max number of processes

参数含义:

单个用户可以启动的线程数，因为进程也会启动一个线程，所以也间接对进程数有限制。

注意：

该参数只对普通用户有用，root用户不在此限制。 所以用root用户可以启动几万个线程，无法重现这个问题.

End

 评论