Use packetbeat of elk beats to audit the network packet capture of mysql.

Use packetbeat of elk beats to audit the network packet capture of mysql.

I used the plug-in type to audit mysql. One is that two mysql instances crash, and the other has a great impact on performance. Therefore, I am looking for other solutions.
Later I found the elk beats project and tried it. Then I launched 200 instances and ran them for 2 months. There was no problem, so I would like to share it with you briefly, for more information, see the official documentation.

Packetbeat supports packet capture over multiple protocols. It is very convenient to send packet capture results to es for storage.

 

Performance test:

24-core 64 GB single-host mysql sysbench test:

The query write performance is affected by about 5%, with no disk I/O impact. The network traffic is increased by 20% compared with the original one, and the beat process cpu usage is between 40% and 70%.

Disadvantages:

1. The content has no account information because it is a network packet capture.

2. Information loss may occur in the path of the location field in the database table. It is estimated that the information is related to the orm framework used.

 

Official Address:
Beats project: https://www.elastic.co/products/beats
Packetbeat subitem document: https://www.elastic.co/guide/en/beats/packetbeat/current/index.html

1. rpm installation:
Centos:

sudo yum install libpcapcurl -L -O https://artifacts.elastic.co/downloads/beats/packetbeat/packetbeat-5.5.1-x86_64.rpmsudo rpm -vi packetbeat-5.5.1-x86_64.rpm

 

2. Edit the configuration file:

vim /etc/packetbeat/packetbeat.yml

 

Packetbeat. protocols. mysql: ports: [3306, 3307, 3308, 3309] output. elasticsearch: hosts: ["es address: Port"] index: "mysqlaudit-% {+ yyyy. MM. dd} "name: 1.1.1.1

Note:
-If the index is not set, the default name in es is the packetbeat-2017.08.16, to set your own name, you need to change to customer_name-% {+ yyyy. MM. dd}, which can also maintain an index file every day as the default one.
-If the name parameter is not set, it is the host name of the machine. We recommend that you set it to an ip address so that the captured packets of different machines 127.0.0.1 can also be analyzed. The corresponding field in es is beat. name.

3. Start packetbeat:
!! Important !!
Before starting the first beat, load the corresponding template in es. The following beat startup is not required:

Curl-H 'content-Type: application/json'-XPUT 'HTTP: // es address: Port/_ template/packetbeat '-d @/etc/packetbeat. template. json

!! If you forget this step, first Delete the index of the day in es, and then restart start beat !!

!! If you have customized the index name, you need to modify the name of the last row in/etc/packetbeat. template. json and then post
Start:

/Etc/init. d/packetbeat start or systemctl start packetbeat

 

4. Optional: Import dashboard
If you want to create a dashboard chart officially, you can import the chart template:

/Usr/share/packetbeat/scripts/import_dashboards-es address: Port-I mysqlaudit -*

Note: If you use a custom index name, you must add the-I parameter. Otherwise, the default index Used in the graph is packetbeat -*

5. open kibana-> Management-> Index Pattern-> + Add New-> packetbeat/custom name-*-> Check Index contains time-based events-> select @ timestamp-> Create

:

 

 

6. Single data content:

 

7. index and dashboard