Continuously move data from server A to Server B while deleting the data in server A?
I don't think that your approach is wrong, but I don't have much information. Your question is clear, but this space is limited compared to the complexity of your system.

Definitely you shouldn't consider using Clickhouse for OLTP. Not only because DELETE and UPDATE are not (yet) supported, but also because this database is designed to provide good performance for analytics. It lacks more or less everything is needed to optimize an OLTP workload.

Kafka is a good idea? Maybe. But you won't have transactions, for example.

I suggest to try to optimize your MySQL environment first. Some very generic points - sorry if they sound obvious to you, but of course I can't know your skills lever:


Tune your configuration for speed, as far as I understand you can give up some consistency.
Use READ UNCOMMITTED wherever there is not a risk of conflicts. The benefits of this isolation level are greatly underestimated.
Instead of running many DELETEs, consider partitioning tables by time and periodically drop the oldest partition. This is much faster.
Consider switching to a storage engine optimized for write-intensive workloads, namely MyRocks or TokuDB.
Consider using multiple slaves for scaling reads.
Consider using multiple masters for scaling writes.
These servers can even be distributed geografically, if needed.
Don't consider Galera or InnoDB Cluster, as writes don't really scale.


Hope these hints are useful.