CODEWITHSUNDEEP
shellawksedtext-processing
user1208081
user1208081

Reputation: 1117

remove a block of text which match a pattern

i have a hard problem, i want to remove some block from a text which have a special word,here is a example , what i want is remove match backup_all and one line before match line ,and three line after match line

# Time: 2018-01-23T03:41:41.454104+08:00
# User@Host: backup_all[backup_all] @  [127.0.0.1]  Id: 3168695
# Query_time: 0.129250  Lock_time: 0.000062 Rows_sent: 3535  Rows_examined: 3535
SET timestamp=1516650101;
SELECT /*!40001 SQL_NO_CACHE */ * FROM `legend_gather_customer_note_effect`;
# Time: 2018-01-23T03:41:41.527587+08:00
# User@Host: backup_all[backup_all] @  [127.0.0.1]  Id: 3168695
# Query_time: 0.066378  Lock_time: 0.000059 Rows_sent: 193  Rows_examined: 193
SET timestamp=1516650101;
SELECT /*!40001 SQL_NO_CACHE */ * FROM `legend_gather_performance_config`;
# Time: 2018-01-23T03:41:41.558254+08:00
# User@Host: backup_all[backup_all] @  [127.0.0.1]  Id: 3168695
# Query_time: 0.025533  Lock_time: 0.000058 Rows_sent: 296  Rows_examined: 296
SET timestamp=1516650101;
SELECT /*!40001 SQL_NO_CACHE */ * FROM `legend_gift`;
# Time: 2018-01-23T03:42:09.536559+08:00
# User@Host: zabbix_agent[zabbix_agent] @  [127.0.0.1]  Id: 3169056
# Query_time: 0.000304  Lock_time: 0.000162 Rows_sent: 1  Rows_examined: 1
SET timestamp=1516650129;
SELECT SUM(trx_rows_locked) AS rows_locked, SUM(trx_rows_modified) AS rows_modified, SUM(trx_lock_memory_bytes) AS lock_memory FROM information_schema.INNODB_TRX;

after removing ,the text become the following output,how to use sed to get this goal?

# Time: 2018-01-23T03:42:09.536559+08:00
# User@Host: zabbix_agent[zabbix_agent] @  [127.0.0.1]  Id: 3169056
# Query_time: 0.000304  Lock_time: 0.000162 Rows_sent: 1  Rows_examined: 1
SET timestamp=1516650129;
SELECT SUM(trx_rows_locked) AS rows_locked, SUM(trx_rows_modified) AS rows_modified, SUM(trx_lock_memory_bytes) AS lock_memory FROM information_schema.INNODB_TRX;

update

this text is from mysql slow log ,i want to remove all sql info from user 'backup_all' , as you see ,this text product by user 'backup_all' is definite to be regular ,but other text product by other user may not be same,they will be 

# Time: 2018-01-23T03:41:24.490723+08:00
# User@Host: zabbix_agent[zabbix_agent] @  [127.0.0.1]  Id: 3169038
# Query_time: 0.000669  Lock_time: 0.000334 Rows_sent: 1  Rows_examined: 27
SET timestamp=1516650084;
select count(*) Slownum from information_schema.processlist where COMMAND = 'Query' and info not like '%information_schema.processlist%' and TIME > 0;
# Time: 2018-01-23T03:41:40.628284+08:00
# User@Host: backup_all[backup_all] @  [127.0.0.1]  Id: 3168695
# Query_time: 78.333179  Lock_time: 0.000073 Rows_sent: 13269064  Rows_examined: 13269064
SET timestamp=1516650100;
SELECT /*!40001 SQL_NO_CACHE */ * FROM `legend_final_inventory`;
# Time: 2018-01-23T03:41:40.925596+08:00
# User@Host: backup_all[backup_all] @  [127.0.0.1]  Id: 3168695
# Query_time: 0.175956  Lock_time: 0.000065 Rows_sent: 5101  Rows_examined: 5101
SET timestamp=1516650100;
SELECT /*!40001 SQL_NO_CACHE */ * FROM `legend_finance_account`;

or

# Time: 2018-01-23T04:29:26.903048+08:00
# User@Host: yun_rw_legend[yun_rw_legend] @  [10.162.86.162]  Id: 3167670
# Query_time: 0.000150  Lock_time: 0.000053 Rows_sent: 6  Rows_examined: 32
SET timestamp=1516652966;
select id, value from legend_precheck_value where value_type = 8 and is_deleted = 'N';
# Time: 2018-01-23T04:29:31.825823+08:00
# User@Host: yun_rw_legend[yun_rw_legend] @  [10.162.86.162]  Id: 3167670
# Query_time: 0.000826  Lock_time: 0.000146 Rows_sent: 0  Rows_examined: 947
SET timestamp=1516652971;
select

         id as id,
        is_deleted as isDeleted,
        gmt_create as gmtCreate,
        creator as creator,
        gmt_modified as gmtModified,
        modifier as modifier,

            shop_id as shopId,
            customer_id as customerId,
            account_id as accountId,
            customer_car_id as customerCarId

        from legend_customer_car_rel

         WHERE is_deleted = 'N'




                   and shop_id = 3374




                    and customer_car_id = 1307177;
# Time: 2018-01-23T04:30:01.149529+08:00
# User@Host: backup_all[backup_all] @  [127.0.0.1]  Id: 3170398
# Query_time: 0.003047  Lock_time: 0.000169 Rows_sent: 0  Rows_examined: 385
SET timestamp=1516653001;
SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IS NOT NULL GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE, EXTRA ORDER BY LOGFILE_GROUP_NAME;
# Time: 2018-01-23T04:30:01.151783+08:00
# User@Host: backup_all[backup_all] @  [127.0.0.1]  Id: 3170398
# Query_time: 0.002082  Lock_time: 0.000119 Rows_sent: 0  Rows_examined: 385
SET timestamp=1516653001;
SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME;

Upvotes: 1

Views: 81

Answers (2)

Guy
Guy

Reputation: 647

Obligatory awk solution which seems to work:

awk '
    $2!~/backup_all/ {
        printf "# Time%s", $0
    }' RS='# Time' ORS='' FS='
' example_in

It splits the input into records on every # Time, and fields at \n to give each line as a field. If the second line (field) doesn't have backup_all in it, print

output for first example:

# Time: 2018-01-23T03:42:09.536559+08:00
# User@Host: zabbix_agent[zabbix_agent] @  [127.0.0.1]  Id: 3169056
# Query_time: 0.000304  Lock_time: 0.000162 Rows_sent: 1  Rows_examined: 1
SET timestamp=1516650129;
SELECT SUM(trx_rows_locked) AS rows_locked, SUM(trx_rows_modified) AS rows_modified, SUM(trx_lock_memory_bytes) AS lock_memory FROM information_schema.INNODB_TRX

and for the second:

# Time: 2018-01-23T04:29:26.903048+08:00
# User@Host: yun_rw_legend[yun_rw_legend] @  [10.162.86.162]  Id: 3167670
# Query_time: 0.000150  Lock_time: 0.000053 Rows_sent: 6  Rows_examined: 32
SET timestamp=1516652966;
select id, value from legend_precheck_value where value_type = 8 and is_deleted = N;
# Time: 2018-01-23T04:29:31.825823+08:00
# User@Host: yun_rw_legend[yun_rw_legend] @  [10.162.86.162]  Id: 3167670
# Query_time: 0.000826  Lock_time: 0.000146 Rows_sent: 0  Rows_examined: 947
SET timestamp=1516652971;
select

         id as id,
        is_deleted as isDeleted,
        gmt_create as gmtCreate,
        creator as creator,
        gmt_modified as gmtModified,
        modifier as modifier,

            shop_id as shopId,
            customer_id as customerId,
            account_id as accountId,
            customer_car_id as customerCarId

        from legend_customer_car_rel

         WHERE is_deleted = N




                   and shop_id = 3374




                    and customer_car_id = 1307177;

Upvotes: 1

potong
potong

Reputation: 58430

This might work for you (GNU sed):

sed -n '/^#/{:a;N;/^SELECT/M!ba;/backup_all/!p}' file

As this is a reduction/filtering of lines use seds grep-like nature achieved by setting the -n option. Gather up lines that begin with a comment line (#) and end with a line beginning SELECT. If these lines do not contain the word backup_all print them.

N.B. the M flag on regexp invokes a multiline mode, where ^ and $ can be matched on start/end of lines. An alternative regexp would be /\nSELECT/.

Upvotes: 0

Related Questions