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.

This question has nothing to do with programming, therefore it is off topic here on SO. The DBA sister site of SO provides help in database admin questions like this one. — Shadow, Mar 22 '21 at 14:36

score 0 · Answer 1 · answered Mar 22 '21 at 13:38

0

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

answered Mar 22 '21 at 13:38

Abdulmohsen Almasoud

56
6

I want do it this when every data processing so that I think ETL job not usefull. (3000 data /seconds) – Mert Ayten Mar 22 '21 at 14:02

score 0 · Accepted Answer · answered Mar 22 '21 at 13:48

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.

@MertAyten . . . You need two servers. The number of tables is much less relevant. — Gordon Linoff, Mar 22 '21 at 16:08

General Log Move Another Table

2 Answers2