- The original content of GreatSQL community cannot be used without authorization. Please contact Xiaobian and indicate the source for reprint.
View mode
- It is known that there are at least two ways to achieve this
1. Turn on general_log can be observed
- Open command
mysql> set global general_log=ON;
- Perform some operations
[root@mgr2 ~]# mysql -uGreatSQL -pGreatSQL -h192.168.6.217 -P3306 mysql> use test; mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | book | | student | | t1 | +----------------+ 3 rows in set (0.01 sec) mysql> select * from t1; +------+--------+ | id | name | +------+--------+ | 1 | ha-ha | | 2 | Hello | | 3 | ha-ha | +------+--------+ 5 rows in set (0.00 sec) mysql> delete from test.t1 where id=4; Query OK, 1 row affected (0.02 sec)
- View general_ The log information has been completely recorded.
2022-01-20T21:46:17.073491-05:00 32 Connect GreatSQL@192.168.6.216 on using TCP/IP 2022-01-20T21:46:17.074048-05:00 32 Query select @@version_comment limit 1 2022-01-20T21:46:20.217080-05:00 32 Query SELECT DATABASE() 2022-01-20T21:46:20.217657-05:00 32 Init DB test 2022-01-20T21:46:20.218960-05:00 32 Query show databases 2022-01-20T21:46:20.220347-05:00 32 Query show tables 2022-01-20T21:46:20.222050-05:00 32 Field List book 2022-01-20T21:46:20.222644-05:00 32 Field List student 2022-01-20T21:46:20.223106-05:00 32 Field List t1 2022-01-20T21:46:22.856100-05:00 32 Query show tables 2022-01-20T21:46:31.156616-05:00 32 Query select * from t1 2022-01-20T21:46:43.136448-05:00 32 Query delete from test.t1 where id=4
2. Bag grabbing
- MySQL Sniffer is used to capture packets.
1. Introduction to MySQL sniffer
- MySQL Sniffer is a packet capturing tool based on MySQL protocol. It captures requests from MySQL Server in real time and formats the output.
- The output content includes access time, access user, source IP, access Database, command time, number of returned data lines, execution statements, etc. It has a variety of use modes, such as batch fetching multiple ports, background operation, log segmentation and so on, with convenient operation and friendly output.
- Open source producer: Qihoo 360
- github address: https://github.com/Qihoo360/m...
2. Install dependent packages
yum install gcc gcc-c++ cmake libpcap-devel glib2-devel libnet-devel -y
3. Installation command
git clone https://github.com/Qihoo360/mysql-sniffer.git cd mysql-sniffer mkdir proj cd proj cmake ../ make
There are some problems during compilation. Please refer to https://www.cnblogs.com/kerry... Solution, thank you!
3. View help
- After installation, the tool is in the bin directory.
[root@mgr3 bin]# ./mysql-sniffer -help
Usage ./mysql-sniffer [-d] -i eth0 -p 3306,3307,3308 -l /var/log/mysql-sniffer/ -e stderr
[-d] -i eth0 -r 3000-4000
-d daemon mode.
-s how often to split the log file(minute, eg. 1440). if less than 0, split log everyday
-i interface. Default to eth0
-p port, default to 3306. Multiple ports should be splited by ','. eg. 3306,3307
this option has no effect when -f is set.
-r port range, Don't use -r and -p at the same time
-l query log DIRECTORY. Make sure that the directory is accessible. Default to stdout.
-e error log FILENAME or 'stderr'. if set to /dev/null, runtime error will not be recorded
-f filename. use pcap file instead capturing the network interface
-w white list. dont capture the port. Multiple ports should be splited by ','.
-t truncation length. truncate long query if it's longer than specified length. Less than 0 means no truncation
-n keeping tcp stream count, if not set, default is 65536. if active tcp count is larger than the specified count, mysql-sniffer will remove the oldest one4. Test
- Refer to GITHUB for testing
1. Capture the port information in real time and print it to the screen
The output format is: time, access user, source IP, access Database, command time, number of returned data lines, and execution statement.
- Console monitoring
- Perform some operations
mysql> use test; Database changed mysql> create table t1 (id int(11) not null auto_increment, name varchar(64), primary key(id)); Query OK, 0 rows affected (0.06 sec) mysql> insert into t1 values (1,'Xiao Ming'),(2,'Xiao Chen'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> delete from t1 where id=1; Query OK, 1 row affected (0.00 sec) mysql> select * from t1; +----+--------+ | id | name | +----+--------+ | 2 | Xiao Chen | +----+--------+ 1 row in set (0.00 sec)
- console output
2022-01-21 14:37:09 GreatSQL 192.168.6.216 NULL 0ms 1 select @@version_comment limit 1 2022-01-21 14:37:31 GreatSQL 192.168.6.216 NULL 0ms 2 show databases 2022-01-21 14:37:37 GreatSQL 192.168.6.216 NULL 0ms 1 SELECT DATABASE() 2022-01-21 14:37:37 GreatSQL 192.168.6.216 test 0ms 0 use test 2022-01-21 14:37:37 GreatSQL 192.168.6.216 test 0ms 2 show databases 2022-01-21 14:37:37 GreatSQL 192.168.6.216 test 11ms 0 show tables 2022-01-21 14:41:58 GreatSQL 192.168.6.216 test 60ms 0 create table t1 (id int(11) not null auto_increment, name varchar(64), primary key(id)) 2022-01-21 14:42:40 GreatSQL 192.168.6.216 test 0ms 2 insert into t1 values (1,'Xiao Ming'),(2,'Xiao Chen') 2022-01-21 14:42:54 GreatSQL 192.168.6.216 test 0ms 1 delete from t1 where id=1 2022-01-21 14:43:04 GreatSQL 192.168.6.216 test 0ms 1 select * from t1
- At present, it seems that there are normal information captured
2. The packet capturing data can be stored in a file
- The file name is the port name. If the file has no content, you can change it to MySQL user permission group to try
./mysql-sniffer -i eth1 -p 33061 -l ./
[root@mgr3 bin]# more 33061.log 2022-01-21 15:00:11 NULL 192.168.6.216 test 0ms 0 use test 2022-01-21 15:00:11 NULL 192.168.6.216 test 0ms 1 SELECT DATABASE()
- Other functions will not be tested one by one
- Special attention, the above is on mysql5 According to the test of version 6, in version 8.0, the data packet may not be caught due to the change of MySQL protocol, and the tool has a certain packet loss.
Enjoy GreatSQL :)
Article recommendation:
GreatSQL MGR FAQ
https://mp.weixin.qq.com/s/J6...
Wan Da #12, how can the MGR cluster automatically select the master without manual intervention
https://mp.weixin.qq.com/s/07...
"2021 data technology Carnival · ON LINE": evolution and practice of MySQL high availability architecture
https://mp.weixin.qq.com/s/u7...
Packet capture analysis of where an sql statement is slow
https://mp.weixin.qq.com/s/AY...
Wan Da #15, what are the conditions that may cause the MGR service to fail to start
https://mp.weixin.qq.com/s/in...
Technology sharing | why MGR consistency mode does not recommend AFTER
https://mp.weixin.qq.com/s/rN...
About GreatSQL
GreatSQL is a MySQL branch maintained by Wanli database. It focuses on improving the reliability and performance of MGR and supports the parallel query feature of InnoDB. It is a branch version of MySQL suitable for financial applications.
Gitee:
https://gitee.com/GreatSQL/Gr...
GitHub:
https://github.com/GreatSQL/G...
Bilibili:
https://space.bilibili.com/13...
Wechat & QQ group:
You can search and add GreatSQL community assistant wechat friends, send verification information "add group" to join GreatSQL/MGR communication wechat group
QQ group: 533341697
Wechat assistant: wanlidbc
This article is composed of blog one article multi posting platform OpenWrite release!