We use MYSQL InnoDB engine for insert and update operations, in order to improve the performance for query, we are considering using Memory table to store the latest data Ex. last two months data.
we can configure the MySQL to import data to Memory table when server start, but actual business data are updated all the time, we have to synchronize the data from InnoDB table to Meomory table frequently, but we cannot restart MySQL server every time when we want to synchronize the data.
Can anybody know how to synchronize the data without restart the MySQL?
You would typically do that with a trigger. My first idea would be to do it in two parts.
1) Create triggers for insert, update and delete (if that ever happens) on the innodb table that causes the same change in the memory table. Make sure no logic relies on that certain rows have been deleted from the memory table, it will hold the last 2 months and then some.
2) Create a background job to clear out the memory table of old data. If you have a high load against it consider a frequent job that nibbles of the old rows a few at a time.
Another solution would be to partition the innodb table by time and then make sure you include something like where time > subdate(now(), interval 2 month)