遇到mysql数据库堵塞情况处理步骤

2014-08-12 9:44 网站活动打不开，处理方法：

1，查看服务负载--top 2，查看mysql负载 --(主) mysql show process list 没有问题 3, 查看（从）mysql 负载，发现负载超高 4，188：快速切换到主（前提不能再提交代码到dianping）--dianping代码库（188）的config-->database->$db['slave'] = $slave[$slave_key];改为$db['slave'] = ‘’; 5,190：快速切换到主（前提不能再提交代码到dianping）--dianping代码库（190）的config-->database->$db['slave'] = $slave[$slave_key];改为$db['slave'] = ‘’;

6，回到测试机用shell代码: ./kill_mysql_www_slave.sh（执行它就可以），如果杀主库的用 ./kill_mysql_www.sh

kill_mysql_www_slave.sh代码：

1. !/bin/bash

for id in `/usr/local/mysql/bin/mysqladmin -uroot -p'fmb_1qazxsw2' -h59.151.119.190 -P3306 processlist | grep Waiting | awk '{print $2}'` do echo kill thread ${id} /usr/local/mysql/bin/mysqladmin -uroot -p'fmb_1qazxsw2' -h59.151.119.190 -P3306 kill ${id} done

kill_mysql_www.sh代码：

1. !/bin/bash

for id in `/usr/local/mysql/bin/mysqladmin -uroot -p'hJYUC3N--HXuBm9tD' -h59.151.119.189 -P3306 processlist | grep Waiting | awk '{print $2}'` do echo kill thread ${id} /usr/local/mysql/bin/mysqladmin -uroot -p'hJYUC3N--HXuBm9tD' -h59.151.119.189 -P3306 kill ${id} done

7，回到4，5步，把代码再改回来，启用从库

8，查找根本原因，彻底去掉隐患:

    此次的原因是：活动列表有个参数c没有get的时候做intval,导致SQL注入

   注入代码：/mobile/activity/free?o=0&c=31 and sleep(20) and 33=33
  导致的SQL：select count(*) as total
 from fmb_new_activity
where status = 'pass'
  and (is_apply = 1 or
      ((select min(goods_price)
           from fmb_tickets
          where ticket_group_id =
                (select ticket_group_id
                   from fmb_group_tickets
                  where aid = fmb_new_activity.aid
                    and status = 1 limit 1)
            and is_on_sale = 1
            and is_delete = 0
            and (((unix_timestamp(ticket_time) - sell_limit_time) >
                unix_timestamp(now()) and valid_end_time > now() and
                ticket_type = 1) or
                ((unix_timestamp(valid_end_time) - sell_limit_time) >
                unix_timestamp(now()) and ticket_type = 2) or
                ((valid_end_time = '0000-00-00 00:00:00' and
                ticket_type = 3) or
                (valid_end_time > now() and ticket_type = 3)))) < '1.00' and
      is_sell = 1))
  and category_id = 31
  and sleep(20)
  and 33 = 33
  and city_id in (1, 0);

总结了一下，发现有遗漏的地方大家补一下，方便日后类似问题快速处理。