CODEWITHSUNDEEP
mysqldatabasearchiveperconamysql-5.7
Aman Aggarwal
Aman Aggarwal

Reputation: 18469

How to disable binlog in pt-archiver

I am archiving the large table using percona's pt-archive utility. But it fills my binary log a lot.

Is there any option to disable the binary logging during the archiving from this utility.

I have read its documentation that we can do something in --analyze option but its not cleared how to define this. Because this option also optimize / analyze the table that i don't want.

can you please provide any working example ?

Upvotes: 2

Views: 690

Answers (1)

Haohmaru
Haohmaru

Reputation: 578

In short you need to add the options b=true and --local

Note that b=true is a DSN option and need to be added after --source or --dest depending on your needs. I will show an example on the bottom of this answer.

You can easily try out with this dummy test:

 CREATE TABLE `dummyids` (
  `ids` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`ids`)
) ENGINE=InnoDB ;


insert into dummyids
    select @i := @i + 1 as id
     from (select true union all select true) t0
    join (select true union all select true) t1
    join (select true union all select true) t2
    join (select true union all select true) t3
    join (select true union all select true) t4
    join (select true union all select true) t5
    join (select true union all select true) t6
    join (select true union all select true) t7
    join (select true union all select true) t8
    join (select true union all select true) t9
    join (select true union all select true) t10
    join (select true union all select true) t11
    join (select true union all select true) t12
    join (select true union all select true) t13
    join (select true union all select true) t14
    join (select true union all select true) t15
    join (select true union all select true) t16
    join (select true union all select true) t17
    join (select true union all select true) t18
    join (select true union all select true) t19
;

Now you can check the size of the binlog and last position (it will be the latest binlog pls change your path and binlog name)

# ls -lh /path/to/mysql/data/

# sudo mysqlbinlog mysql-bin.000012 | tail -n 100

Record the position and size of the binlog to compare after running pt archive

pt-archiver --source h=localhost,D=bar,t=dummyids,b=true --local --where "1 = 1" --limit=10000 --progress=50000 --txn-size=50000 --statistics --purge --bulk-delete

Now run the commands to check the size and latest position of the binlog again you should notice they haven't changed.

Hope this helps more people with the same issue.

The options are explained on percona tool manual pt-archiver options

Upvotes: 2

Related Questions