Introduction of tool developer

He Chunyang is the head of THE DBA team of Fanpurkinke. He is the author of the first and second editions of “the Way of MySQL Management: Performance Tuning, High Availability and Monitoring”. He used to work for China Mobile Feisen and Anzhuo Jifeng. Dedicated to MariaDB, MongoDB and other open source technology research, mainly responsible for database performance tuning, monitoring and architecture design.

Tools download: click on the article [reading] or login at the end of the cloud disk at https://pan.baidu.com/s/1dDJfkRMKn3zwDlVvHqnLdA

 

/ * *

* Percona pt-kill Refactoring (PHP)

 * https://github.com/hcymysql/pt-kill

 *

 * UPDATE:

 * Modified by: hcymysql 2018/11/27

* 1, add slow SQL mail alarm function

* 2, add slow SQL wechat alarm function

 *

* Environmental preparation:

 * shell> yum install -y php-process php php-mysql

 *  

* /

 

An overview of the

 

The native Percona pt-kill (Perl) tool simply kills slow SQL while it is running. It cannot be used as a monitoring tool, such as the lack of email alarm or wechat alarm function, so it must be rebuilt.

 

Refactoring PT – kill (PHP) from information_schema. In the table the PROCESSLIST capture the SELECT | in the running of the ALTER DML/DDL consume resources such as too many query, filter, and then kill them (can choose not to kill), And send an email/wechat alarm to THE DBA and relevant developers to avoid a certain degree of damage to the database due to the slow SQL execution time. (Note: Slow SQL is recorded in slow.log only after execution, not during execution.)

 

Use methods and parameter options

 

Usage:

 

 The Options:

 

  -u  username

  -p  password

  -h  host ip

  -P  port

-b busytime time seconds Sets the alarm trigger time for slow SQL execution

-i interval time seconds Sets the interval monitoring time of the daemon process

–kill Specifies this option if you want to kill slow queries.

– match – the info matches to kill the SELECT | INSERT | UPDATE statement

–match-user Matches the user to be killed

Daemon 1 Start the daemon process. 0 Stop the daemon process

–mail Enables sending mail alarm

— Weixin enable sending wechat alarm

  –help  Help

 

Example:

 

The front desk operation

   Shell > PHP pt – kill. PHP -u admin 10.10.159.31 – p – 123456 – p h 10-3306 – B match – info = ‘select | alter’ – match – user = ‘dev’ –kill –mail –weixin

 

The background

   Shell > PHP pt – kill. PHP -u admin 10.10.159.31-3306 – p – 123456 – p h B 10-15 – match – info = I ‘select | alter’ –match-user=’dev’ –kill –mail –weixin –daemon 1

       

Close background running

   shell> php pt-kill.php –daemon 0

 

The above is how to use the tool and the parameter options.

 

The important parameters

 

Here are some important parameters:

 

–kill If you want to kill slow queries, add this option later;

 

–match-info can be used alone or in combination with –match-user.

 

Daemon 1: enables background daemons. If this option is not enabled, crontab can be used instead.

 

This option must be used together with -i 10 (seconds), that is, monitoring every 10 seconds of sleep. 0 indicates that the background daemon process is disabled.

 

4, –mail To enable the sending of email alarm, you need to set smtp_config. PHP, change your own email account information

 

 

5, –weixin to enable wechat alarm, you need to install simplejson-3.8.2.tar.gz first

 

 

Then edit the pt-kill.php script and find

 

$status1 = system(“/usr/bin/python whitepath. Py ‘hcymysql’ {$row[‘DB’]}) ‘{$content1}'”);

 

Change the ‘hcymysql’ my wechat id to your own.

 

  • Wechat enterprise number setting

Move to www.cnblogs.com/linuxprobe/p/5717776.html

See this tutorial for configuration.

 

6. A kill. TXT file will be generated in the tool directory to save slow SQL.

 

shell> cat kill.txt

The 2018-11-27 16:41:22

User name: root

Source IP address: localhost

Database name: hcy

Run time: 18

SQL statement: select sleep(60)

 

7, By default, only slow SQL is killed in the connection, keep the session connection, if you want to kill the connection also, remove the QUERY

 

Modified pt – kill. PHP

 

//$kill_sql = “KILL QUERY {$row[‘ID’]}”; 

$kill_sql = “KILL {$row[‘ID’]}”;

 

For details, see pt_kill Demo Video.avi in the cloud disk link.

 

Download the way

 

Link to log in to the cloud disk:

https://pan.baidu.com/s/1dDJfkRMKn3zwDlVvHqnLdA

You can download the tool script

 

Percona pt-kill reconstruction (PHP) is now available for free download through dbaPlus community. If you have any questions or suggestions, please feel free to contact us.

 

Dba + tools: Pt-ARCHIver reconfigured to easily archive large table data

 

More free tools & scripts

 

Details and download: http://dbaplus.cn/list-142-1.html