欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

统计MySQL binlog中的DML语句

程序员文章站 2022-03-06 23:10:19
...

统计MySQL binlog中的DML语句 MASTER@root@test 07:44:52gt;create table tab02(id int(10) primary key,name varchar(20)); Q

统计MySQL binlog中的DML语句

MASTER@root@test 07:44:52>create table tab02(id int(10) primary key,name varchar(20));
Query OK, 0 rows affected (0.10 sec)

MASTER@root@test 08:34:54>
MASTER@root@test 08:34:54>
MASTER@root@test 08:34:54>insert into tab02 values(1,'ddd');
Query OK, 1 row affected (0.00 sec)

MASTER@root@test 08:34:59>insert into tab02 values(2,'ddd');
Query OK, 1 row affected (0.00 sec)

MASTER@root@test 08:35:01>insert into tab02 values(3,'ddd');
Query OK, 1 row affected (0.00 sec)

MASTER@root@test 08:35:03>insert into tab02 values(4,'ddd');
Query OK, 1 row affected (0.00 sec)

MASTER@root@test 08:35:04>insert into tab02 values(5,'ddd');
Query OK, 1 row affected (0.01 sec)

MASTER@root@test 08:35:07>insert into tab02 values(6,'ddd');
Query OK, 1 row affected (0.00 sec)

MASTER@root@test 08:35:09>delete from tab02 where id=6;
Query OK, 1 row affected (0.02 sec)

MASTER@root@test 08:35:19>update tab02 set where id=1
-> ;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

MASTER@root@test 08:35:39>update tab02 set where id=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

[root@mynode1 mysql]# /service/mysql/bin/mysqlbinlog binlog-master.000006 |\
> grep -i -e "^update" -e "^insert" -e "^delete" -e "^replace" -e "^alter" | \
> cut -c1-100 | tr '[A-Z]' '[a-z]' | \
> sed -e "s/\t/ /g;s/\`//g;s/(.*$//;s/ set .*$//;s/ as .*$//" | sed -e "s/ where .*$//" | \
> sort | uniq -c | sort -nr
7 insert into tab02 values
2 update tab02
1 delete from tab02

相关阅读:

MySQL 大DML操作建议

MySQL常用DDL、DML、DCL语言整理(附样例)

MySQL中binlog日记清理

统计MySQL binlog中的DML语句