• mysql空闲连接


    0、概念理解:用户会话和连接线程是什么关系?

    用户会话和用户连接线程是一一对应的关系,一个会话就一个用户连接线程。

    问题描述:

      如果系统因为执行了一个非常大的dml或者ddl操作导致系统hang住,我们想断掉这个操作,怎么办?

    解决办法:

    1、kill thread:杀死用户的会话

      但是时间长,效果不佳:前滚+回滚,前提是已经进行了很长时间,回滚就需要更多的时间

    2、kill mysqld进程:推荐,用这种杀进程的方式,速度快

      kill -9 进程号(ps aux 查看进程号)

      数据库先前滚,不主动回滚,直接可以对外进行服务了,当读到哪个未提交事务时再去慢慢回滚。

     

    一、kill用户会话及用户连接线程

    1、如何查看用户会话,如何杀掉用户会话

    [root@localhost ~]# netstat -anp |grep 3306
    tcp        0      0 :::3306                     :::*                        LISTEN      17324/mysqld        
    [root@localhost ~]# ps -ef |grep 'mysql -px x'
    root     20510 20483  0 15:59 pts/1    00:00:00 mysql -px x
    root     20528 46646  0 15:59 pts/4    00:00:00 grep mysql -px x
    root     45626 45565  0 05:44 pts/3    00:00:00 mysql -px x
    [root@localhost ~]# kill -9 20510 

      # kill -9 <mysql会话进程PID>  //快速释放资源(推荐)

    注意:千万不要将mysqld给kill掉了,不要杀掉LISTEN进程

    2、如何查看用户连接线程,如何杀掉用户连接线程

    mysql> show processlist;
    +--------+
    | Id     |
    +--------+
    | 194850 |
    | 194851 |
    +--------+
    mysql> kill 194850;
    mysql> show processlist;
    +--------+
    | Id     |
    +--------+
    | 194851 |
    | 194852 |
    +--------+

    注意:如果将用户连接线程杀死断掉,而会话没有杀掉的话,该用户会话又会重新开启一个用户连接线程。

    3、杀掉用户连接线程的工作过程、弊端风险

      1、过程:

        rollback--->释放资源--->kill线程

      2、弊端与风险:

        1、可能出现系统更加繁忙的情况(因为大量的rollback)

        2、会话释放需要很长的时间

    Q:假设现在有1000个用户连接,如何快速杀掉?

    A:

      1、使用concat写脚本

        mysql> select concat('kill ',ID,';') into outpfile '/tmp/kill.txt' from information_schema.PROCESSLIST;

        shell> cat kill.txt

        kill 194850;

        kill 194851;

      然后,复制到mysql中进行执行,将用户连接线程都kill掉。

      2、使用awk取出用户会话进程ID都kill掉

      shell> netstat -anp|grep mysql|grep -v mysqld|awk '{print $8}'|awk -F '/' '{print $1}'|xargs kill -9

    关于mysqld_safe的注意点:

      在os层面将用户连接线程kill掉,通过pstree -p可以看到MySQL相关进程及线程ID,#kill -9 mysql任一线程,会导致该mysqld进程被kill;但是通过mysqld_safe的安全机制,又会重新启一个mysqld进程;如此原来的用户连接线程也就随原来的进程一块被干掉了。

     

    二、MySQL客户端的连接

    1、最大连接数

    mysql> show variables like 'max_connections';
    +-----------------+-------+
    | Variable_name   | Value |
    +-----------------+-------+
    | max_connections | 151   |
    +-----------------+-------+

    由上可见,MySQL默认客户端的最大连接数是151,但是,在大并发下一百多的连接数就会不够用,需要调整最大连接数,修改并写入配置文件中:

      max_connections = 1000

      wait_timeout = 1000000  #超时时间

    重启MySQL服务

    2、查看当前有多少连接

    mysql> show status like '%Threads_connected%';
    +-------------------+-------+
    | Variable_name     | Value |
    +-------------------+-------+
    | Threads_connected | 1     |
    +-------------------+-------+
    1 row in set (0.01 sec)
    
    mysql> show processlist;
    +-------+------+-----------+------+---------+------+----------+------------------+
    | Id    | User | Host      | db   | Command | Time | State    | Info             |
    +-------+------+-----------+------+---------+------+----------+------------------+
    | 17219 | root | localhost | NULL | Query   |    0 | starting | show processlist |
    +-------+------+-----------+------+---------+------+----------+------------------+
    1 row in set (0.01 sec)

    3、最大失败连接数:max_connect_errors

    mysql> show variables like 'max%errors';
    +--------------------+-------+
    | Variable_name      | Value |
    +--------------------+-------+
    | max_connect_errors | 100   |
    +--------------------+-------+
    1 row in set (0.00 sec)

      是一个MySQL中与安全有关的计数器值,负责阻止过多尝试失败的客户端以暴力破解密码的情况,值的大小与性能无太大的关系。

      默认是100,也就是说如果某一客户端尝试连接此MySQL服务器,但是失败(如密码错误等等)10次,则MySQL会无条件强制阻止此客户端连接。如果想重置计数器对某一客户端的值,则必须重启mysqld或者mysql> flush hosts;,当该客户端成功连接一次mysqld后,针对此客户端的max_connect_errors会清零。

      如果max_connect_errors的设置过小,则网页可能提示无法连接数据库服务器。

      一般来说建议数据库服务器不监听来自网络的连接,仅仅通过sock连接,这样可以防止绝大多数针对mysql的攻击;如果必须要开启mysql的网络连接,则最好设置此值,以防止穷举密码的攻击手段。

     

    参考:https://www.cnblogs.com/geaozhang/p/7137858.html

     

    mysql> show full processlist;
    +----+------+--------------------+------+---------+-------+-------+-----------------------+
    | Id | User | Host               | db   | Command | Time  | State | Info                  |
    +----+------+--------------------+------+---------+-------+-------+-----------------------+
    |  1 | root | localhost          | NULL | Sleep   |    19 |       | NULL                  |
    |  2 | root | 192.168.100.1:7437 | test | Sleep   |  8042 |       | NULL                  |
    |  3 | root | 192.168.100.1:7438 | NULL | Sleep   | 24355 |       | NULL                  |
    |  5 | root | 192.168.100.1:7443 | NULL | Sleep   | 24324 |       | NULL                  |
    |  7 | root | 192.168.100.1:7450 | test | Sleep   | 24279 |       | NULL                  |
    |  9 | root | 192.168.100.1:5152 | test | Query   |     0 | init  | show full processlist |
    +----+------+--------------------+------+---------+-------+-------+-----------------------+
    6 rows in set
    

    各个列的含义:

    ①.id列,用户登录mysql时,系统分配的"connection_id",可以使用函数connection_id()查看
    ②.user列,显示当前用户。如果不是root,这个命令就只显示用户权限范围的sql语句
    ③.host列,显示这个语句是从哪个ip的哪个端口上发的,可以用来跟踪出现问题语句的用户
    ④.db列,显示这个进程目前连接的是哪个数据库
    ⑤.command列,显示当前连接的执行的命令,一般取值为休眠(sleep),查询(query),连接(connect)等
    ⑥.time列,显示这个状态持续的时间,单位是秒
    ⑦.state列,显示使用当前连接的sql语句的状态,很重要的列。state描述的是语句执行中的某一个状态。一个sql语句,以查询为例,可能需要经过copying to tmp table、sorting result、sending data等状态才可以完成
    ⑧.info列,显示这个sql语句,是判断问题语句的一个重要依据

  • 相关阅读:
    Docker的安装、配置及其基本使用
    Java提升七:注解
    Java提升六:泛型
    Java提升五:反射与动态代理
    MySQL中如何将主键默认值设为UUID()
    图解Mybatis框架原理及使用
    Java提升四:Stream流
    Java提升三:函数式接口
    Java提升二:Lambda表达式与方法引用
    java提升一:内部类
  • 原文地址:https://www.cnblogs.com/youxin/p/14035359.html
Copyright © 2020-2023  润新知