General Log Move Another Table

Question

Using MYSQL, I want to record my data from the general_log table on server A to a table on server B instantly at every data and delete the data from server A at the end of the day. I tried to use Trigger for this, but the general_log does not allow me to write triggers because it sees the system file. Alternatively, when I use the Fedareted table, when I delete the data on server A, those on server B are also deleted. Thanks in advance for your help.

Answer 1

I would recommend the following strategy:

First, partition the data on in general_log by date. You can learn about table partitioning in the documentation.

Second, set up replication so server B is identify to server A in real time. Once again, you may need to refer to the documentation.

Third, set up a job to remove the previous partition from A shortly after midnight.

To be honest, if you don't understand table partitioning and replication, you should get a DBA involved. In fact, if you are trying to coordinate multiple database servers, you should have a DBA involved, who would understand these concepts and how best to implement them in your environment.

Answer 2

I recommend to develop an ETL job to move the data every day and delete it from the old server