# Pt-archiver prevent binlogs

**URL:** <https://forums.percona.com/t/pt-archiver-prevent-binlogs/32416>\
**Category:** Percona Toolkit\
**Created:** [August 19, 2024, 8:49am UTC](https://forums.percona.com/t/pt-archiver-prevent-binlogs/32416 "2024-08-19T08:49:22Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Jost](https://avatars.discourse-cdn.com/v4/letter/j/4da419/32.png) [@Jost](https://forums.percona.com/u/Jost)\
**Post date:** [August 19, 2024, 8:49am UTC](https://forums.percona.com/t/pt-archiver-prevent-binlogs/32416/1 "2024-08-19T08:49:22Z")

</div>

Hi everyone,  
I’m currently using an environment with an older Percona 5.7 cluster, and I’m facing the challenge of needing to drop a very large table—approximately 3.5 TB. To minimize impact, I’m planning to use pt-archiver to clean up the table in small batches.

However, there’s an additional complication: the developers have enabled row-based binary logging in this cluster. If I’m not wrong this might significantly increase the volume of binlogs generated.

I noticed that pt-archiver has a DSN option that, if set to true, disables binary logging with SQL\_LOG\_BIN. Would using this option effectively prevent the cleanup from generating excessive binlogs? Or is there a more efficient approach that I might have overlooked?

Thanks in advance for your insights!

---

<div class="post-metadata">

**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\
**Post date:** [August 19, 2024, 2:02pm UTC](https://forums.percona.com/t/pt-archiver-prevent-binlogs/32416/2 "2024-08-19T14:02:28Z")

</div>

> [@Jost](#):
>
> Would using this option effectively prevent the cleanup from generating excessive binlogs?

Yes it would. SQL\_LOG\_BIN=0 is a session variable. pt-archiver will set this session variable at the start and it will only affect pt-archiver. Other sessions/connections will continue to get binlog’d as usual.

Keep in mind too that disabling binlogs means if there are any replicas, these rows will not get deleted from any replicas. If you are using the `--purge` option in pt-archiver, the binlog entry for the deletes should be small and not cause too much bloat. You can monitor how much binlog space you are using and `PURGE BINARY LOGS TO ...` to clean up excessive binlogs.

---

<div class="post-metadata">

**Author:** ![Jost](https://avatars.discourse-cdn.com/v4/letter/j/4da419/32.png) [@Jost](https://forums.percona.com/u/Jost)\
**Post date:** [August 23, 2024, 10:26am UTC](https://forums.percona.com/t/pt-archiver-prevent-binlogs/32416/3 "2024-08-23T10:26:25Z")

</div>

Hi Matthew,

Thank you so much for your feedback.  
I tested the option you suggested, but it seems it’s not working as expected, or I may have made a mistake in the process. Here’s what I did:

I started the archiver with a Kubernetes job using the following options:

```auto
pt-archiver --source h=db01-pxc-db-haproxy,u=$DB_USER,p=$DB_PASSWORD,P=3306,D=basedb,t=ftp_send_log,b=1 --progress 1000 --where "1=1" --statistics --charset=UTF8mb4 --limit=1000 --txn-size 1000 --primary-key-only --purge --sleep 1

```

Then, I checked the binlog with:

```auto
mysqlbinlog ./mysql-bin.000132 | grep -i -E 'Table_map|insert|update|delete'

```

However, I still see the deleted rows appearing:

```auto
#240823 10:00:04 server id 1 end_log_pos 47430607 CRC32 0x009a9f08 Table_map: `basedb`.`ftp_send_log` mapped to number 890
#240823 10:00:04 server id 1 end_log_pos 47431233 CRC32 0x192e2dbf Delete_rows: table id 890 flags: STMT_END_F
#240823 10:00:04 server id 1 end_log_pos 47431351 CRC32 0xe722146f Table_map: `basedb`.`ftp_send_log` mapped to number 890
#240823 10:00:04 server id 1 end_log_pos 47431977 CRC32 0xe0cea8d5 Delete_rows: table id 890 flags: STMT_END_F

```

Using `b=1` to `b=true` doesn’t make a difference.  
Do you have any suggestions on what might be going wrong?

---

<div class="post-metadata">

**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\
**Post date:** [August 24, 2024, 12:27am UTC](https://forums.percona.com/t/pt-archiver-prevent-binlogs/32416/4 "2024-08-24T00:27:13Z")

</div>

prefix the pt-archiver command with `PTDEBUG=1 ` and run it. See if you can find anything related to sql\_log\_bin in the output.