Mysql使用过程遇到的问题记录


阅读数:   

解决远程连接mysql很慢的方法

开发某应用系统连接公司的测试服务器的mysql数据库连接打开的很慢，但是连接本地的mysql数据库很快。但是测试站点打开速度正常，ping访问正常。

解决方法：

找到mysql配置文件, 增加配置参数：

1
2
[mysqld]
skip-name-resolve

注意该配置项是加到mysqld下的，该配置的作用是避免mysql对外部的连接进行DNS解析，若使用此设置，那么远程主机连接时只能使用ip，而不能使用域名（但是我这里还是可以使用域名，可能是因为公司内的这个域名是内部连接）

Linux下配置文件一般在 /etc/my.cnf 或者 /etc/mysql/my.cnf, Windows下配置文件是mysql安装目录下的my.ini文件

修改配置文件后需要重启mysql

其他优化配置建议：

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
[mysqld]
max_allowed_packet=16M 
增加该变量的值十分安全，这是因为仅当需要时才会分配额外内存。例如，仅当你发出长查询或mysqld必须返回大的结果行时mysqld才会分配更多内存。该变量之所以取较小默认值是一种预防措施，以捕获客户端和服务器之间的错误信息包，并确保不会因偶然使用大的信息包而导致内存溢出。 
如果你正是用大的BLOB值，而且未为mysqld授予为处理查询而访问足够内存的权限，也会遇到与大信息包有关的奇怪问题。如果怀疑出现了该情况，请尝试在mysqld_safe脚本开始增加ulimit -d 256000，并重启mysqld。 
##########################################
#####   MySQL怎样打开和关闭数据库表 #####
##########################################
table_cache, max_connections和max_tmp_tables影响服务器保持打开的文件的最大数量。如果你增加这些值的一个或两个，你可以遇到你的操作系统每个进程打开文件描述符的数量上强加的限制。然而，你可以能在许多系统上增加该限制。请教你的OS文档找出如何做这些，因为改变限制的方法各系统有很大的不同。 
table_cache与max_connections有关。例如，对于200个打开的连接，你应该让一张表的缓冲至少有200 * n，这里n是一个联结(join)中表的最大数量。
打开表的缓存可以增加到一个table_cache的最大值（缺省为64；这可以用mysqld的-O table_cache=#选项来改变）。一个表绝对不被关闭，除非当缓存满了并且另外一个线程试图打开一个表时或如果你使用mysqladmin refresh或mysqladmin flush-tables。 
当表缓存满时，服务器使用下列过程找到一个缓存入口来使用： 
不是当前使用的表被释放，以最近最少使用（LRU）顺序。 
如果缓存满了并且没有表可以释放，但是一个新表需要打开，缓存必须临时被扩大。 
如果缓存处于一个临时扩大状态并且一个表从在用变为不在用状态，它被关闭并从缓存中释放。 
对每个并发存取打开一个表。这意味着，如果你让2个线程存取同一个表或在同一个查询中存取表两次(用AS)，表需要被打开两次。任何表的第一次打开占2个文件描述符；表的每一次额外使用仅占一个文件描述符。对于第一次打开的额外描述符用于索引文件；这个描述符在所有线程之间共享

skip-networking

不在tcp/ip端口上进行监听，所有的连接都是通过本地的socket文件连接，这样可以提高安全性，确定是不能通过网络连接数据库。

skip-locking

避免mysql的外部锁定，增强稳定性
skip-name-resolve

避免mysql对外部的连接进行DNS解析，若使用此设置，那么远程主机连接时只能使用ip，而不能使用域名

max_connections = 3000

指定mysql服务所允许的最大连接进程数，

max_connect_errors = 1000

每个主机连接允许异常中断的次数，当超过该次数mysql服务器将禁止该主机的连接请求，直到mysql服务重启，或者flushhosts命令清空host的相关信息

table_cache = 614k

表的高速缓冲区的大小，当mysql访问一个表时，如果mysql表缓冲区还有空间，那么这个表就会被打开通放入高速缓冲区，好处是可以更快速的访问表中的内容。
如果open_tables和opened_tables的值接近该值，那么久该增加该值的大小

max_allowed_packet = 4M

设定在网络传输中一次可以传输消息的最大值，系统默认为1M，最大可以是1G

sort_buffer_size = 16M

排序缓冲区用来处理类似orderby以及groupby队列所引起的排序，系统默认大小为2M,该参数对应分配内存是每个连接独占的，若有100个连接，实际分配的排序缓冲区大小为6*100；推荐设置为6M-8M

join_buffer_size 8M

联合查询操作所使用的缓冲区大小。

thread_cache_size = 64


设置threadcache池中可以缓存连接线程的最大数量，默认为0，该值表示可以重新利用保存在缓存中线程的数量，当断开连接时若缓存中还有空间，那么客户端的线程将被放到缓存中，如果线程重新被请求，那么请求将从缓存中读取，若果缓存中是空的或者是新的请求，那么线程将被重新创建。设置规律为：1G内存设置为8,2G内存设置为16,4G以上设置为64

query_cache_size = 64M

指定mysql查询缓冲区的大小，用来缓冲select的结果，并在下一次同样查询的时候不再执行查询而直接返回结果，根据Qcache_lowmem_prunes的大小，来查看当前的负载是否足够高

query_cache_limit = 4M

只有小于该值的结果才被缓冲，放置一个极大的结果将其他所有的查询结果都覆盖
tmp_table_size 256M

内存临时表的大小，如果超过该值，会将临时表写入磁盘

default_storage_engine = MYISAM

创建表时默认使用的存储引擎

log-bin=mysql-bin

打开二进制日志功能

key_buffer_size = 384M

指定索引缓冲区的大小，内存为4G时刻设置为256M或384M

read_buffer_size = 8M

用来做MYISAM表全表扫描的缓冲大小

无法启动Mysql

报错截图：

image-20210225180336117

image-20210225180345817

image-20210225180355586

解决：

这里是根据最后一点日志中的报错 mysqld: Can't create/write to file '/tmp/ibo4T3D4' (Errcode: 13 - Permission denied)解决的。

  1. top查看mysql的用户是什么？

    1
    root@ip:/# top | grep mysql

    image-20210225180717620

  2. 修改mysql的临时目录（这里可以不修改，但是不修改直接给/tmp赋权限我感觉不安全）

    找到mysql的配置文件，我的位置在 /etc/mysql/my.cnf

    1
    2
    3
    4
    vim /etc/mysql/my.cnf

    # 修改tmpdir
    tmpdir = /tmpdir/mysql

  1. 给新的mysql临时目录赋予权限

    1
    2
    3
    4
    # 设置目录/tmpdir/mysql的文件所有者和文件关联组
    chown -R mysql:mysql /tmpdir/mysql
    # 下面的是以防万一，给所有用户和组赋予所有权限，可以不加
    chmod -R 777 /tmpdir/mysql

    mysql 是上面top查看到的USER名称， 这里因为是临时文件，我感觉没有必要区分用户，直接就给了所有权限

  2. 重启mysql

    1
    2
    3
    4
    service mysql stop
    service mysql start
    # 或者
    service mysql restart

    如果重启还是失败，直接给 /tmp 777 的权限吧，暴力直接，但是不清楚会不会有其他后果。我这里就遇到过这个问题，就只能设置成 /tmp 目录才不会报错，原因不明，等待大佬给我答疑解惑。

修改mysql的缓存目录

因为公司的海外服务器经常丢包，会较为频繁更换公网IP，但是发现公网IP更换后 /tmp 目录的权限会重置（成drwx——T）。但是mysql的缓存目录需要权限777， 所以希望修改mysql的缓存目录。

tmpdir = /tmp => /tmpdir

重启mysql ( service mysql restart ), 报错：Job for mysql.service failed because the control process exited with error code. See "systemctl status mysql.service" and "journalctl -xe" for details.

日志显示：

image-20210304114425960

文件夹权限：

image-20210304114443579

未解决

unblock with ‘mysqladmin flush-hosts’”

image-20241106170121954

这个错误信息表明 MySQL 服务器因为多次连接错误而阻止了来自 XXX.XX.XX.XX 主机的连接请求。为了解除阻止，你需要在 MySQL 服务器上执行 mysqladmin flush-hosts 命令。

解决步骤

  1. 登录到 MySQL 服务器： 你需要以具有足够权限的用户（如 root）登录到 MySQL 服务器。

    1
    mysql -u root -p

  2. 执行 flush-hosts 命令： 在 MySQL 命令行中执行以下命令来刷新主机缓存，解除阻止。

    1
    FLUSH HOSTS;

  3. 检查和调整连接错误阈值： 为了避免将来再次发生类似问题，你可以检查并调整 MySQL 的连接错误阈值。你可以在 MySQL 配置文件（通常是 my.cnf 或 my.ini）中找到并修改以下参数：

    1
    max_connect_errors = 100  # 默认值通常是 100

    你可以根据需要增加这个值，例如：

    1
    max_connect_errors = 1000

  4. 重启 MySQL 服务： 修改配置文件后，重启 MySQL 服务以使更改生效。

    1
    sudo systemctl restart mysql

mysql 里面执行下面语句也可以查看和设置 max_connect_errors，但是重启后设置会失效。另外 SET GLOBAL 需要权限较高，通常需要 SUPER 权限或 SYSTEM_VARIABLES_ADMIN 权限。

1
2
SHOW VARIABLES LIKE 'max_connect_errors';
SET GLOBAL max_connect_errors = 1000;
本文作者： ionluo
本文链接： http://www.ionluo.cn/blog/posts/c599ad35.html
版权声明： 本作品采用 知识共享署名-非商业性使用-相同方式共享 4.0 国际许可协议 进行许可。转载请注明出处！
知识共享许可协议

扫一扫，分享到微信

微信分享二维码

tag:

    缺失模块。
    1、请确保node版本大于6.2
    2、在博客根目录（注意不是yilia根目录）执行以下命令：
    npm i hexo-generator-json-content --save

    3、在根目录_config.yml里添加配置： 

      jsonContent:
    meta: false
    pages: false
    posts:
      title: true
      date: true
      path: true
      text: false
      raw: false
      content: false
      slug: false
      updated: false
      comments: false
      link: false
      permalink: false
      excerpt: false
      categories: false
      tags: true