To create data warehousing server in MySQL

MySQL & MariaDB Other MySQL® Questions
1

We have single master DB and 3 replication server environment.
From that 3 replication servers we need one DB to be historical server ( i.e. Data warehousing )
We will be deleting some records from every table on master DB but the data should be archived on that single DB ( historical server )
We have tried many ways like xtrabackup utility which was not useful.
mysqldump was also not useful because size of DB is almost 3 T.B.

Kindly suggest any tool or utility which can be used for size of 3TB DB having almost 100 tables in it which should be archived on another server DB before deleting the records on Master server.

2

Hello @todkar2130,
You can try two different approaches:

  1. Before running any DELETE on source, run SET sql_log_bin=0 This will prevent the DELETE operations from being written to the binary log on the source, and thus not replication to any replicas. Downside is that all replicas will now be historical. You can then repeat the DELETEs for the non-historical replicas.

  2. Remove the historical server from replication, and use pt-archiver to archive data from source to the DW server.