- GreatSQL社区原创内容未经受权不得随便应用，转载请分割小编并注明起源。
查看形式
- 已知至多有两种形式能够实现
1.开启 general_log 就能够察看到
- 开启命令
mysql> set global general_log=ON;- 执行一些操作
[root@mgr2 ~]# mysql -uGreatSQL -pGreatSQL -h192.168.6.217 -P3306mysql> 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 | 哈哈 || 2 | 你好 || 3 | 呵呵 |+------+--------+5 rows in set (0.00 sec)mysql> delete from test.t1 where id=4;Query OK, 1 row affected (0.02 sec)- 查看
general_log日志信息，曾经残缺记录到了。
2022-01-20T21:46:17.073491-05:00 32 Connect GreatSQL@192.168.6.216 on using TCP/IP2022-01-20T21:46:17.074048-05:00 32 Query select @@version_comment limit 12022-01-20T21:46:20.217080-05:00 32 Query SELECT DATABASE()2022-01-20T21:46:20.217657-05:00 32 Init DB test2022-01-20T21:46:20.218960-05:00 32 Query show databases2022-01-20T21:46:20.220347-05:00 32 Query show tables2022-01-20T21:46:20.222050-05:00 32 Field List book2022-01-20T21:46:20.222644-05:00 32 Field List student2022-01-20T21:46:20.223106-05:00 32 Field List t12022-01-20T21:46:22.856100-05:00 32 Query show tables2022-01-20T21:46:31.156616-05:00 32 Query select * from t12022-01-20T21:46:43.136448-05:00 32 Query delete from test.t1 where id=42.抓包
- 这里采纳 MySQL Sniffer 进行抓包。
1.MySQL Sniffer 介绍
- MySQL Sniffer 是一个基于 MySQL 协定的抓包工具，实时抓取 MySQL Server 端的申请，并格式化输入。
- 输入内容包拜访括工夫、拜访用户、起源 IP、拜访 Database、命令耗时、返回数据行数、执行语句等。有批量抓取多个端口，后盾运行，日志宰割等多种应用形式，操作便捷，输入敌对。
- 开源出品方：奇虎360
- github 地址：https://github.com/Qihoo360/m...
2.装置依赖包
yum install gcc gcc-c++ cmake libpcap-devel glib2-devel libnet-devel -y3.装置命令
git clone https://github.com/Qihoo360/mysql-sniffer.gitcd mysql-sniffermkdir projcd projcmake ../make编译过程中呈现一些问题，参考 https://www.cnblogs.com/kerry... 解决，感激！
3.查看帮忙
- 装置后工具在 bin 目录下。
[root@mgr3 bin]# ./mysql-sniffer -helpUsage ./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.测试
- 参考GITHUB进行测试
1.实时抓取某端口信息并打印到屏幕
输入格局为：工夫，拜访用户，起源 IP，拜访 Database，命令耗时，返回数据行数，执行语句。
- 控制台监听
- 执行一些操作
mysql> use test;Database changedmysql> 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,'小明'),(2,'小陈');Query OK, 2 rows affected (0.00 sec)Records: 2 Duplicates: 0 Warnings: 0mysql> delete from t1 where id=1;Query OK, 1 row affected (0.00 sec)mysql> select * from t1;+----+--------+| id | name |+----+--------+| 2 | 小陈 |+----+--------+1 row in set (0.00 sec)- 控制台输入
2022-01-21 14:37:09 GreatSQL 192.168.6.216 NULL 0ms 1 select @@version_comment limit 12022-01-21 14:37:31 GreatSQL 192.168.6.216 NULL 0ms 2 show databases2022-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 test2022-01-21 14:37:37 GreatSQL 192.168.6.216 test 0ms 2 show databases2022-01-21 14:37:37 GreatSQL 192.168.6.216 test 11ms 0 show tables2022-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,'小明'),(2,'小陈')2022-01-21 14:42:54 GreatSQL 192.168.6.216 test 0ms 1 delete from t1 where id=12022-01-21 14:43:04 GreatSQL 192.168.6.216 test 0ms 1 select * from t1- 目前看有失常抓取到信息
2、能够把抓包的数据存储到文件
- 文件名称就是端口名称，如果文件没内容，能够改成MySQL用户权限组试一下
./mysql-sniffer -i eth1 -p 33061 -l ./[root@mgr3 bin]# more 33061.log2022-01-21 15:00:11 NULL 192.168.6.216 test 0ms 0 use test2022-01-21 15:00:11 NULL 192.168.6.216 test 0ms 1 SELECT DATABASE()- 其余性能就不一一测试了
- 特地留神，以上是在 MySQL5.6 版本测试获取的，在 8.0 版本中可能因为MySQL协定变更导致抓不到数据包，同时该工具存在肯定的丢包状况。
Enjoy GreatSQL :)
文章举荐：
GreatSQL MGR FAQ
https://mp.weixin.qq.com/s/J6...
万答#12，MGR整个集群挂掉后，如何能力主动选主，不必手动干涉
https://mp.weixin.qq.com/s/07...
『2021数据技术嘉年华·ON LINE』：《MySQL高可用架构演进及实际》
https://mp.weixin.qq.com/s/u7...
一条sql语句慢在哪之抓包剖析
https://mp.weixin.qq.com/s/AY...
万答#15，都有哪些状况可能导致MGR服务无奈启动
https://mp.weixin.qq.com/s/in...
技术分享 | 为什么MGR一致性模式不举荐AFTER
https://mp.weixin.qq.com/s/rN...
对于 GreatSQL
GreatSQL是由万里数据库保护的MySQL分支，专一于晋升MGR可靠性及性能，反对InnoDB并行查问个性，是实用于金融级利用的MySQL分支版本。
Gitee:
https://gitee.com/GreatSQL/Gr...
GitHub:
https://github.com/GreatSQL/G...
Bilibili：
https://space.bilibili.com/13...
微信&QQ群：
可搜寻增加GreatSQL社区助手微信好友，发送验证信息“加群”退出GreatSQL/MGR交换微信群
QQ群：533341697
微信小助手：wanlidbc
本文由博客一文多发平台 OpenWrite 公布！